SQL Server : speed up this query

Question

I have a rather strange situation. There is a table ProductStock which has 2 triggers: one for afterInsert and one for afterDelete both do an insert with the changed value in STOCKDEBUGTRIGGERED

afterDelete:

INSERT INTO STOCKDEBUGTRIGGERED
 (ProductID,Amount,StockOld,StockNew)
 select deleted.ProductID, count(deleted.ProductStockID), 
 (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = deleted.productid) + count(deleted.ProductStockID)
 , (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = deleted.productid) 
 from deleted
 group by deleted.ProductID

afterInsert

INSERT INTO STOCKDEBUGTRIGGERED
 (ProductID,Amount,StockOld,StockNew)
 select inserted.ProductID, count(inserted.ProductStockID), 
 (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = inserted.productid) - count(inserted.ProductStockID)
 , (select count(productstock.ProductStockID) from PRODUCTSTOCK where productstock.ProductID = inserted.productid) 
 from inserted
 group by inserted.ProductID

But this is the query which troubles me:

SELECT TOP 250
 STOCKDEBUGTRIGGERED.ProductID,
 productcode,
 'http://urltomyimages.com/' + ProductPictureName as img,
 Amount,
 STOCKDEBUGTRIGGERED.StockOld,
 STOCKDEBUGTRIGGERED.StockNew,
 ChangeDate,
 (select top 1 STOCKDEBUG.StockDebugWho from STOCKDEBUG
 where STOCKDEBUG.ProductID = STOCKDEBUGTRIGGERED.ProductID
 and STOCKDEBUG.StockOld = STOCKDEBUGTRIGGERED.StockOld
 and STOCKDEBUG.StockNew = STOCKDEBUGTRIGGERED.StockNew
 ) as who
 FROM STOCKDEBUGTRIGGERED
 inner join products on STOCKDEBUGTRIGGERED.productid = products.productid
 order by ChangeDate desc

Please do not advice temporal tables, because this does work and is running on sql 2008. It only has a high load because this gets executed by a monitoring tool every 5 minutes.

Here are the client statistics: client stats

and here is the query plan: query plan

What troubles me is the 44% sort with the yellow exclamation mark: sort detail

So is there a thing I missed? I made a view for the query and threw in three WITH(NOLOCK) statements. But that did not make it run faster. No index suggestion.

edit have read https://stackoverflow.com/questions/29001721/is-it-possible-to-allocate-memory-to-a-query-in-ms-sql-server because I wanted to see if there was a way to allocate 5mb memory for this query to skip the use of the tempdb. And have read https://www.mssqltips.com/sqlservertip/4132/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics/ but that is not the case for me I think because I limit it to 250 rows.

edit2 if I remove the subquery:

SELECT TOP 250
 STOCKDEBUGTRIGGERED.ProductID,
 productcode,
 'http://ourimages.com/' + ProductPictureName as img,
 Amount,
 STOCKDEBUGTRIGGERED.StockOld,
 STOCKDEBUGTRIGGERED.StockNew,
 ChangeDate
 FROM STOCKDEBUGTRIGGERED
 inner join products on STOCKDEBUGTRIGGERED.productid = products.productid
 order by ChangeDate desc

the sort increases to 89% and there is still a 15mb memory load because of the tempdb

enter image description here

edit3 as reply on @T.H. his answer:

plan

stats

execution time is now less than 1% or 2% of original! Why did neither the perf advisor from azure or the ssms suggest these indexes?

edit4 To prove that @T.H. is right. You can see exactly when I created his two suggested indexes. It had a spike every x time because of that query up to 20% dtu. All spikes are gone and it is nice 1% avg dtu.

dtu load

• Biju jose: changing the sub query and turning that into a cte may have a better plan

Answer 1

I believe the basic issue is that the query is doing multiple heavy table scans due to no index support.

Try adding the following indexes:

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUGTRIGGERED (ChangeDate)

CREATE NONCLUSTERED INDEX TEST ON STOCKDEBUG(ProductID, StockOld, StockNew)

There may be further tweaks, so please post the stats and execution plan with these indexes added.

• JP Hellemons: Thanks but why didn't the azure perf advisor give me this advice or the ssms client?
• T.H.: The automatic index advice is extremely limited, and often misleading, so can only be considered as a starting point. Your EDIT3 execution plan doesn't seem to include the subquery. If you don't actually need that data, then you don't need the new index on STOCKDEBUG.
• JP Hellemons: True, I excluded it because the sort which caused the largest load was not in the subquery. I have created both indexes and have the subquery in my live database. It does speed up everything a lot! Thank you!