SQL Server 每週一次恢復到低效計劃（聚集索引掃描）

我有一個非常簡單的查詢：

INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC

針對這張表：

CREATE TABLE [dbo].[Comments] (
   [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
   [CommentDate] datetime NOT NULL DEFAULT (getdate()),
   [UserID] int NULL ,
   [Body] nvarchar(max) NOT NULL,
--a couple of other int and bit cols, no indexes on them
)

我在UserID列上有一個簡單的索引（不包括列），一切都很好而且超級快。

但是每 5-8 天我會在應用程序的那部分看到超時。所以我去查詢儲存中調查，我看到伺服器停止使用我的索引並恢復到愚蠢的“集群掃描”。刪除臨時表沒有幫助。

為了解決這個問題 - 我為這個特定的查詢重置了計劃記憶體（只是為了記錄，我是這樣做的）

select plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
where text like '%SELECT TOP 50 CommentID FROM hdComments%'
--blahblahblah skipped some code
DBCC FREEPROCCACHE (@plan_handle)

然後再次開始正常工作。

執行計劃：慢 快快

這幾天我一直在摸不著頭腦……有什麼想法嗎？

您對 UserID 的索引不是該查詢的最佳索引。它使優化器可以選擇使用它並需要按 CommentID 進行額外排序或掃描表（向後）以獲取已按 commentID 排序並由 where 子句和 top 運算符動態過濾的行。雖然集群的 PK 列包含在每個非集群的列中，但它只是作為指針，因此不能用於排序。

對於您描述的關鍵查詢，避免它的最佳方法是提供最佳索引，因此優化器更有可能每次都選擇它。根據您提供的資訊，您的索引應該是 (UserID, CommentID DESC) 上的複合非聚集索引，這將允許直接訪問使用者行，並且可以按照 CommentID 的順序掃描前 50 行，使其成為最佳選擇，而不考慮統計和選擇性。

SQL Server 足夠聰明，可以實現它。試一試… HTH

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