在更新統計資訊之前，查詢執行計劃很糟糕

我希望你們能在這裡幫助我。我們的應用程序每 3 秒輪詢一次消息表以查找要發送的通知。這適用於我們所有的客戶（單租戶數據庫），除了一個。他們一天 23 小時沒有活動，然後一次載入數千條消息 (3000+)。在其他情況下，這個卷什麼都不是，我們可以輕鬆處理它，除了在這種情況下，下面的 SQL 查詢大約需要 30 秒才能執行，並且隨著隊列在更新時備份，需要排他鎖和因此阻止所有其他查詢，因此問題會導致各種破壞。這都是由於一個糟糕的查詢計劃。

我們每天早上 5 點執行每日重新索引（重組 < 30%，重建 > 30%，忽略 <5%）以及更新統計資訊。這些都來自 Ola Hallengren 維護解決方案。我們也在 SQL Server 2016 上並且完全是最新的 (13.0.5492.2)

我手頭沒有這 2 個計劃，但基本上糟糕的計劃是對 MessagesSent 表（3.5m 行）進行全表掃描。

我的理論是，因為查詢一整天都沒有返回任何內容，某些部分沒有執行，因此錯誤查詢是 SQL 最有效的查詢。

這將在刷新查詢計劃後繼續，因為它只是生成相同的計劃，但是當我在 MessagesSent 表上更新統計資訊時，會創建好的計劃並且一切正常，查詢在大約 10-30 毫秒內執行。

有誰知道我如何微調它以始終使用更好的計劃，即使查詢返回的數據不存在？作為一個修補程序，我們在應用程序中添加了重新編譯選項，但我認為這不是每 3 秒執行一次查詢的理想解決方案。

這是查詢：

WITH TopMessage
   AS
   (
       SELECT TOP 1 ID, BatchID FROM MessagesSent 
       JOIN Units ON Unit = idUnit 
       WHERE   MessageDate <= GETDATE() 
         AND         Active = 'True' 
         AND         Status = 'Queued' 
         AND NOT(DialString = 'null') 
         AND           Unit = ('29') 
         AND System in ('SystemName', 'Q1', '') 
       ORDER BY 
           CASE 
               WHEN QPriority IS NULL 
                   THEN 
                       CASE 
                           WHEN DefaultPriority IS NULL 
                               THEN 999999 
                               ELSE DefaultPriority 
                       END
                ELSE QPriority 
           END ASC,
           Retries ASC, 
           MessageDate ASC, 
           ID ASC
   ),
   BatchCalls
   AS
   (
       SELECT * FROM MessagesSent 
       WHERE (
                (LEN(BatchID) > 0 
                 AND BatchID = (SELECT TOP 1 BatchID FROM TopMessage)
                ) 
       OR ID = (SELECT TOP 1 ID FROM TopMessage)
       )
       AND Status = 'Queued' AND Active = 'True'
   )

   UPDATE BatchCalls
   SET LastUpdated = @dtNow
   OUTPUT INSERTED.*
   WHERE Status = 'Queued'

非常感謝您的時間和尋找。

您可以使用手動計劃指南來執行所需的計劃。

或者，您可以使用Query Store通過 GUI強制執行計劃指南。

此外，您可以在大量消息載入後執行更新統計作業。我在我的部落格上寫了一篇文章，展示了一種簡單的方法。

由於您在這些查詢之間有一些共同的過濾，您可以通過在“活動”和“狀態”列上添加過濾索引來加快處理速度並減少鎖定/阻塞的數量。

如果沒有架構，很難分辨哪些列屬於哪些表，但索引看起來像這樣：

CREATE NONCLUSTERED INDEX IX_BatchID_Filtered
ON dbo.MessagesSent (BatchID, Active, Status)
WHERE Active = 'True' AND Status = 'Queued';

注意：可能有比 BatchID 更好的前導列，並且您可能希望包含表中的其他列（如 DialString、System、QPriority 等）——我只是不知道哪些列屬於哪些表，以及它們的數據是什麼類型是等

該索引將僅包括（希望很小的）符合這些標準的行子集，並在面對有些過時的統計數據時提供更可預測的性能。

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