Sql-server – SQL Server : speed up this query

performanceperformance-tuningquery-performancesql serverssms

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

Best Answer

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.

Related Question