First compare the execution plan of both queries to see if, there are differences, may one don't use the right index etc.
SQL Statement taking long to execute
I have two SQL Databases that is on the same SQL server but on one database it only takes 4 seconds to complete the SQL statement but on the other SQL database it takes 25 minutes to complete. Both databases uses the same SQL elastic pool and I don't see the CPU usage, memory or I/O being a bottleneck. How would I diagnose the reason for such an issue since both databases setup is the same.
4 answers
Sort by: Most helpful
-
Deleted
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
-
-
MikeyQiaoMSFT-0444 2,470 Reputation points
2024-07-16T07:20:20.9766667+00:00 Check the execution plan and analyze the points that take the longest time.
- Is the compilation time too long?
- The statistics are outdated, and SQL Server has chosen an inefficient execution plan.
- The table does not have good indexes, making it difficult for the query to search effectively and quickly.
- Resource contention, with blocking or locking in the table.
- The statement is too complex.
dbcc freeproccache go dbcc dropcleanbuffers go Set statistics io on -- Check the logical reads and physical reads of the execution go Set statistics time on -- Check the compilation time and execution time to determine if the long compilation time is causing low query efficiency go set statistics profile on -- Return the execution plan of the statement go %Your excute statement% go Set statistics time off go Set statistics io off go set statistics profile off
Post your output results, ad we will help you analyze the issue.
Best regards,
Mikey Qiao
-
Erland Sommarskog 109.3K Reputation points MVP
2024-07-21T20:58:04.8166667+00:00 Sorry for the delay, but here are some observations. Let me first say that even with actual execution plans it is not always possible to say things with certainty. Having the table and index definitions could help more, but it is still a handicap not having access to the actual data. And for that matter not knowing the business rules.
Anyway, the original question was why there is a performance difference between Prod and Test. The answer is the amount of data is not the same. It is approximately the same, but the difference is big enough to result in a plan difference. More precisely, the difference is in this part of the query:
from TPS_POLine_Source tl inner join dbo.THK_PurchPurchaseOrderLineStaging pl on pl.PURCHASEORDERNUMBER = tl.PurchaseOrderNumber and pl.LINENUMBER = tl.LineNumber
In Prod this join is carried out as a Nested Loop join, whereas in test this is done with a Hash Join. An important difference is also that the Key Lookup required for THK_PurchPurchaseOrderLineStaging is before the join in Test, but after in Prod.
So what can be done about this query? I would recommend removing the index hints in the query. I don't think they serve any purpose. It can very well be that for both THK_PurchPurchaseOrderLineStaging and LSG_Order_Tracking a Clustered Index Scan is a better choice. Whether removing the hints is sufficient remains to see.
As for the inflated estimates we discussed earlier, they seem to come from this join:
left join dbo.LSG_Order_Tracking ot on ot.POInventTransID = tl.POInventTransId
The big estimates suggests that POInventTransID is not a key in either table. But the actual values suggests that it may in practice be. Do you have any more information about this column?