SQL Statement taking long to execute

Question

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

Your answer

Answer 1

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

Answer 2

Olaf Helper 46,546

First compare the execution plan of both queries to see if, there are differences, may one don't use the right index etc.

Erland Sommarskog 120.2K Reputation points MVP

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 16,276 Reputation points

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

Answer 3

MikeyQiaoMSFT-0444 3,300

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

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 3,300 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 120.2K Reputation points MVP

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

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 3,300 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 120.2K Reputation points MVP

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 120.2K Reputation points MVP

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.

Answer 4

Erland Sommarskog 120.2K MVP

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?

Erland Sommarskog 120.2K Reputation points MVP

2024-07-25T09:12:53.28+00:00

I usually don't do nag posts, but in this case I'm curious how this has progressed. Were my suggestions of any help to you? Or did you get swamped with something else, and have had the time to look at this?
Samuel Inagalur 0 Reputation points

2024-07-26T03:13:04.0933333+00:00
Hi Erland,

Appologise for late reply.

We have tried below suggestions but it didnt improve the query performance in Test DB

Force to use inner loop join in Test DB

Removing the index hints

Created index for "tl.POInventTransId" in TPS_POLine_Source table

Dropped the 3 tables used in the query from Test DB and recreated using the srcipts from Prod DB to make sure all the indexes etc are the same

As a final attempt, we cloned the Prod DB to Test DB instance and refreshed the data and the query performance issue is resolved.

Thank you for all your inputs and following-up on this.

Best regards,

Sam.
Erland Sommarskog 120.2K Reputation points MVP

2024-07-26T08:19:06.6866667+00:00

Thanks for the update!

The fact that a refresh from Prod to Test resolved the urgent problem tells us one thing: the more humble specs for Test are not a concern.

However, the real problem has not been solved, but a change in data profile could cause the bad plan to come back. And that could just as well be in Production.

Now, would that happen, there is actually a quick way out, as you can force the old plan from Query Store. Then again, I like to think of that as a makeshift solution for which I would like to find a long-term solution.

I am not surprised that my suggestion to remove the index hints did not resolve the issue. I have found that often when I have performance issues like this one, I have to do some trial-and-error. Which is of course very difficult, when I don't have access to the database!

But since the problematic database has been overwritten, we can't but to put this issue to rest for now.

Share via

SQL Statement taking long to execute

4 answers

Your answer