SQL Server：加快這個查詢

我有一個相當奇怪的情況。有一個表ProductStock有 2 個觸發器：一個用於 afterInsert 和一個用於 afterDelete 都使用更改後的值進行插入STOCKDEBUGTRIGGERED

刪除後：

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

插入後

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

但這是困擾我的查詢：

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

請不要建議臨時表，因為這確實有效並且在 sql 2008 上執行。它只有高負載，因為它每 5 分鐘由監控工具執行一次。

以下是客戶統計數據：

這是查詢計劃：

讓我煩惱的是帶有黃色驚嘆號的 44% 排序：

那麼我錯過了什麼嗎？我為查詢創建了一個視圖並輸入了三個WITH(NOLOCK)語句。但這並沒有讓它跑得更快。沒有索引建議。

編輯已閱讀https://stackoverflow.com/questions/29001721/is-it-possible-to-allocate-memory-to-a-query-in-ms-sql-server因為我想看看是否有辦法為此查詢分配 5mb 記憶體以跳過 tempdb 的使用。並已閱讀https://www.mssqltips.com/sqlservertip/4132/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics/但我的情況並非如此認為是因為我將其限制為 250 行。

edit2如果我刪除子查詢：

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

排序增加到 89%，由於 tempdb，記憶體負載仍然為 15mb

edit3 作為對@TH 的回复他的回答：

執行時間現在不到原來的 1% 或 2%！為什麼 azure 的 perf advisor 或 ssms 都沒有建議這些索引？

edit4證明@TH 是正確的。您可以確切地看到我創建他的兩個建議索引的時間。由於該查詢高達 20% dtu，它每 x 次都有一個峰值。所有尖峰都消失了，1% avg dtu 很好。

我相信基本問題是由於沒有索引支持，查詢正在執行多次繁重的表掃描。

嘗試添加以下索引：

在 STOCKDEBUGTRIGGERED (ChangeDate) 上創建非聚集索引測試

在 STOCKDEBUG(ProductID, StockOld, StockNew) 上創建非聚集索引測試

可能會有進一步的調整，所以請發布添加了這些索引的統計數據和執行計劃。

引用自：https://dba.stackexchange.com/questions/164399