SQL Server - 防止對特定表進行聚集索引掃描
我的數據庫包含一個非常大的特定表（250+M 行，100+GB 數據空間）。
在這個表上生成聚集索引掃描總是一個壞主意。
這個表有多個索引，我們從來沒有在沒有在索引列上指定謂詞的情況下對這個表執行查詢。
自從我們更新伺服器以來，我們經歷了隨機查詢計劃的降級。將在幾秒鐘內執行的查詢在數十分鐘後隨機開始超時。
伺服器改動如下：
- 兼容性級別從 2008 年更新到 2016 年，因此基數估計發生了變化。
- SQL Server 版本已從 Standard 更新為 Enterprise。
- SQL Server 版本 (2016) 未更改。
- 儲存 (SSD) 未更改。
當然，我可以通過添加提示輕鬆解決一個特定查詢的這個問題，但是我的應用程序中有太多查詢，無法廣泛應用這個解決方案。
有沒有辦法全域阻止 SQL Server 在此表上生成聚集索引掃描？
我無法從以前的生產問題中重現相同的錯誤查詢計劃（錯誤的查詢計劃隨機發生），但我能夠使用新的簡單查詢生成類似的查詢計劃。我上傳了沒有提示和提示的簡單查詢的計劃。
OPTION (RECOMPILE)可以解決問題。但這並不能保證新的查詢計劃會更好，或者 SQL Server 不會在幾天內切換到另一個查詢計劃。我不是在尋找一種方法來改進一個特定的查詢或改進一個特定的查詢計劃。我想在我的表上全域防止聚集索引掃描。我有許多查詢許多不同列的查詢，我不能在所有索引中包含所有列。
一個可能會解決我的問題的全域選項是使用遺留基數估計器，但我希望找到更好的東西。
有沒有辦法全域阻止 SQL Server 在此表上生成聚集索引掃描？
是的。將聚集索引替換為聚集列儲存索引。它將被高度壓縮。並且掃描可以消除不需要的列，以及（關鍵這裡）不需要的行組。
這裡的查詢很難優化：
select t.Id, t.ExternalId, t.Source, tn.Code from XXX.LargeTable t join Security tn on t.SecurityId = tn.Id where t.TransactionTime >= @d1 and t.TransactionTime <= @d2 and tn.NationalityId = 22最佳計劃幾乎完全取決於範圍有多廣
$$ @d1, @d2 $$是。但是 SQL Server 不能對同一個查詢有多個計劃。由於雙重嵌套循環連接，您將在此處進行表掃描，因為替代計劃的估計成本很高。事實證明，如果事務表中有足夠的行符合條件，那麼該計劃將比表掃描便宜，但 SQL 必須提出一個對@d1 和@d2 的任何值都適用的計劃。 如果日期範圍非常窄，就像這裡一樣，聚集索引掃描是一個糟糕的計劃，因為它需要讀取整個表。
但在 Clustered Columstore SQL Server 的情況下，只需掃描 TransactionTime 列即可找到匹配的行。不僅如此，每個 1,000,000 行的列段在列段標題中都有最小值和最大值。因此，如果範圍很窄，則可以消除許多行組，而無需實際掃描所有 TransactionTime 值。
“壞”計劃主要是 IO 等待
<Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="523619" WaitCount="83735" /> <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="13910" WaitCount="14455586" />為同一查詢掃描 CCI 將需要聚集索引掃描所需 IO 的一小部分。