向表中添加列儲存索引會影響在同一表上使用行儲存索引的查詢的讀取性能嗎？

我正在對大約 5 億行的單個表上的列儲存索引進行一些測試。聚合查詢的性能提升非常棒（以前執行大約需要 2 分鐘的查詢現在執行在 0 秒內即可聚合整個表）。

但我也注意到另一個利用在同一張表上的現有行儲存索引上查找的測試查詢現在的執行速度是之前創建列儲存索引之前的 4 倍。我可以反复展示刪除列儲存索引時，行儲存查詢在 5 秒內執行，並且通過在列儲存索引中添加回行儲存查詢在 20 秒內執行。

我一直關注行儲存索引查詢的實際執行計劃，無論列儲存索引是否存在，這兩種情況幾乎完全相同。（它在這兩種情況下都使用行儲存索引。）

行儲存測試查詢是：

SELECT *
INTO #TEMP
FROM Table1 WITH (FORCESEEK)
WHERE IntField1 = 571
   AND DateField1 >= '6/01/2020'

此查詢中使用的行儲存索引是：CREATE NONCLUSTERED INDEX IX_Table1_1 ON Table1 (IntField1, DateField1) INCLUDE (IntField2)

列儲存測試查詢是：

SELECT COUNT(DISTINCT IntField2) AS IntField2_UniqueCount, COUNT(1) AS RowCount
FROM Table1
WHERE IntField1 = 571 -- Some other test columnstore queries also don't use any WHERE predicates on this table
   AND DateField1 >= '1/1/2019' 

列儲存索引為：CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Table1_2 ON Table1 (IntField2, IntField1, DateField1)

這是我創建列儲存索引之前行儲存索引查詢的執行計劃：

這是我創建列儲存索引後行儲存索引查詢的執行計劃：

我注意到這兩個計劃之間的唯一區別是，在創建列儲存索引後，排序操作的警告消失了，而鍵查找和表插入 (#TEMP) 運算符需要更長的時間。

這是帶有警告的排序操作資訊（在創建列儲存索引之前）：

這是沒有警告的排序操作資訊（在創建列儲存索引之後）：

我會認為在這兩種情況下專門利用相同行儲存索引和執行計劃的讀取查詢在每次執行時應該具有大致相同的性能，而不管該表上存在哪些其他索引。這裡給出了什麼？

編輯：這是創建索引之前的 TIME 和 IO 統計資訊：

以下是創建列儲存索引後的統計資訊：

添加非聚集列儲存索引允許在第二個執行計劃中進行批處理模式排序。這會導致所有處理都在一個執行緒上完成 - 因此即使查詢具有並行計劃，它本質上也是串列執行的。您可以通過查看不同運營商的詳細資訊來了解這一點。

我在本地重現了您的問題，這是每個執行緒計數的排序運算符 - 正如您所看到的，一切都線上程 1 上：

注意“實際執行模式”是“批處理”。

排序之後的所有內容（嵌套循環連接、鍵查找等）本質上都是串列的，這會減慢查詢速度。

有關詳細資訊和可能的解決方案，請參閱此知識庫文章：

添加跟踪標誌 9358 以禁用 SQL Server 2016 中復雜並行查詢中的批處理模式排序操作

批處理模式排序是在 SQL Server 2016 中在兼容級別 130 下引入的。如果查詢執行計劃包含並行批處理模式排序以及直接上游並行運算符，則與行模式排序計劃等效項相比，您可能會遇到性能下降。

這是由於並行批處理排序通過單個執行緒將完全排序的數據輸出到上游並行運算符（例如，並行合併連接運算符）。由於傳入的單執行緒批處理模式排序運算符，上游並行運算符使用單執行緒處理時會發生性能下降。

為完整起見，此處列出的選項包括：

• 啟用 TF 9358
• 啟用查詢優化器修補程序（通過 TF 4199、QUERY_OPTIMIZER_HOTFIXES數據庫選項或ENABLE_QUERY_OPTIMIZER_HOTFIXES查詢提示）

---

擺脫排序是這個問題的另一個解決方案。排序僅用於嘗試防止來自嵌套循環連接的過多隨機 I/O，它使用無序預取，如 Craig Freedman 的這篇文章中所述：

通過排序優化 I/O 性能——第 1 部分

該計劃使用非聚集索引來避免不必要地觸及許多行。然而，執行 64,000 次隨機 I/O 仍然相當昂貴**，因此 SQL Server 添加了一種排序。** 通過對聚集索引鍵上的行進行排序，SQL Server 將隨機 I/O 轉換為順序 I/O。

您可以通過以下方式擺脫排序：

• 消除對鍵查找的需要（通過選擇更少的列，或創建覆蓋的非聚集索引）
• OPTION (QUERYTRACEON 9115)通過向查詢添加（未記錄，不支持的跟踪標誌）來禁用嵌套循環預取

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