SQL Statement taking long to execute

WingHong Shiu 5

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

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
Olaf Helper 43,166 Reputation points

2024-07-15T06:04:47.76+00:00

First compare the execution plan of both queries to see if, there are differences, may one don't use the right index etc.
Please sign in to rate this answer.
Erland Sommarskog 106.5K Reputation points

2024-07-15T09:12:22+00:00

Olaf is right on the money. Comparing the query plans is definitely the place to start. Very most likely, they are different. Possible reasons for this include:

The data profile is different. For instance, one table that has a handful of rows on the fast database has millions and millions of rows in the other. Or the data distribution is different.

One database has an index to support the query, the other has not.

The slow plan is due to that it was sniffed for atypical values.

If you feel that you cannot make out heads or tails from the plan, you can upload them on http://www.pastetheplan.com and share the links here.

ShaktiSingh-MSFT 14,381 Reputation points Microsoft Employee

2024-07-16T05:18:27.66+00:00

Hi WingHong Shiu •,

Just checking if above suggestions provided by Erland Sommarskog and Olaf Helper helped?

If you have more queries, please let us know.

Thanks
Sign in to comment
MikeyQiaoMSFT-0444 1,755 Reputation points

2024-07-16T07:20:20.9766667+00:00
Hi，WingHong Shiu

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
Please sign in to rate this answer.
Samuel Inagalur 0 Reputation points

2024-07-18T03:34:25.5133333+00:00

Hi Mikey Qiao,

On behalf of Wing Hong Shui.

This is the output results of the SQL.

Thank you,

Sam

MikeyQiaoMSFT-0444 1,755 Reputation points

2024-07-18T06:57:56.1966667+00:00

Samuel Inagalur

By examining your execution plan, it was found that a large amount of time is spent on the filter operation, with the estimateRows reaching 7.6 billion rows. May I ask if the original table contains such a large amount of data? If so, please create an index on the filter column in the original table's filter condition, and then update the statistics.

Best regards

Mikey Qiao

Samuel Inagalur 0 Reputation points

2024-07-18T07:24:58.1033333+00:00

Hi Mikey,

These are number of rows in all the 3 tables used in the SQL.

Test vs Prod.

I have checked the indexes in both Test & Prod and they are the same.

The execution time in Prod is 4 seconds and in Test is over 25 minutes.

Thanks,

Sam.

Erland Sommarskog 106.5K Reputation points

2024-07-18T08:56:18.65+00:00

By examining your execution plan, it was found that a large amount of time is spent on the filter operation, with the estimateRows reaching 7.6 billion rows.

Not really. Where the time is spent, we can't tell from this output.

What is striking though is that up to the row below the Hash Match with the Right Outer Join, the actual and estimated number of rows match exactly. In real life, the number of rows are reduced with around 25%, going down from 432903 to 316527, where as the estimate is that the number of rows will explode. Bad estimates often results in bad plans.

As for what might be going on there, I don't know, but I remember having run into something like that on one occasion.

To understand this better, I think we need to see:

The actual query.

The actual execution plans from both databases in XML format, and as I said before, you can share these on http://www.pastetheplan.com.

The format that Mikey lured you to use has less information and is more difficult to read. And it is difficult to work from images.

Samuel Inagalur 0 Reputation points

2024-07-18T09:10:41.8466667+00:00

Hi Erland,

Here's the details.

Thanks,

Sam.

Prod DB - https://www.brentozar.com/pastetheplan/?id=Hk2ziU8d0

Test DB - https://www.brentozar.com/pastetheplan/?id=Sk3YiIUuC

SQL:

Select pl.PURCHASEORDERLINESTATUS,isnull(ot.SOCustRef,pl.CUSTOMERREFERENCE),isnull(ot.SOCustRequisition,pl.CUSTOMERREQUISITIONNUMBER),

pl.INVENTTRANSID,case pl.INVENTREFID when '' then '' else ot.SOInventLotID end,

isnull(ot.SONum,''),ot.SOLineStatus,isnull(ot.SOCustItemNum,'')

From TPS_POLine_Source tl inner join dbo.THK_PurchPurchaseOrderLineStaging pl with (index(IX_THK_PurchPurchaseOrderLineStaging_PO_Line)) on pl.PURCHASEORDERNUMBER = tl.PurchaseOrderNumber and pl.LINENUMBER = tl.LineNumber left join dbo.LSG_Order_Tracking ot with (index(IX_LSG_Order_Tracking_POInvent)) on ot.POInventTransID = tl.POInventTransId and tl.POInventTransID <> '' where tl.SourceSystem = 'D' and ( tl.[SalesOrderNumber] <> isnull(ot.SONum,'') or tl.[CustomerItemNumber] <> isnull(ot.SOCustItemNum,'') or tl.POLineStatusId <> pl.PURCHASEORDERLINESTATUS or tl.POLineStatusId is null or tl.[CustomerReference] <> isnull(ot.SOCustRef,pl.CUSTOMERREFERENCE) or tl.[CustomerRequisitionNumber] <> isnull(ot.SOCustRequisition,pl.CUSTOMERREQUISITIONNUMBER) or tl.SOInventLotId <> isnull(ot.SOInventLotID,'') ) ;

MikeyQiaoMSFT-0444 1,755 Reputation points

2024-07-18T09:14:44.73+00:00

where as the estimate is that the number of rows will explode. Bad estimates often results in bad plans.

Exactly.，Thanks for the added response, Erland.

The issue arises with the right outer join in the hash match. The data volume of the two result sets is concentrated in the hundreds of thousands of rows, but after the right outer join, the query processor estimates 7.6 billion rows.If possible, could you provide the original query?

Samuel Inagalur 0 Reputation points

2024-07-18T09:19:07.67+00:00

deleted

Samuel Inagalur 0 Reputation points

2024-07-18T09:26:59.04+00:00

Hi Erland,

Here's the memory allocation of Prod & Test and both using the same elastic pool.

Thanks,

Sam.

Erland Sommarskog 106.5K Reputation points

2024-07-18T09:37:30.2333333+00:00

Thanks for the plans. However, they are plans with estimates only. We want the actual execution plans. That is, you select Include Actual Execution Plan from the Query menu and then run the query. Yes, one query takes 25 minutes to complete, but we can wait. :--)

Samuel Inagalur 0 Reputation points

2024-07-19T03:02:24.1166667+00:00

Hi Erland,

Here's the actual execution plans

Test - https://www.brentozar.com/pastetheplan/?id=r1N3U8v_C

Prod - https://www.brentozar.com/pastetheplan/?id=HJyWP8wOA

Thank you,

Sam.

Erland Sommarskog 106.5K Reputation points

2024-07-19T08:13:40.23+00:00

Thanks for the plans! Unfortunately for you, it is a sunny summer day here in Stockholm, and we just have too few of these, so my priority today is to be outdoors. Therefore, it may take some time before I get the time to look into this. But since this is an interesting problem, I really like to dive into it.

In the meanwhile, would be it possible for you to assemble the CREATE TABLE and CREATE INDEX statements for the tables?

And, yes, the reason for the difference in execution time is that the plans are different, but to understand why and how to avoid it, requires a bit of time.
Sign in to comment
Erland Sommarskog 106.5K Reputation points

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?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

SQL Statement taking long to execute

4 answers