列儲存索引謂詞未用於索引掃描

我們正在使用具有約 50M 記錄的聚集列儲存索引表，並且在GCP雲 sql 上執行與使用相同的 db 架構和數據在本地執行（僅導出和導入 bak 文件）時性能大幅下降。

使用下面的查詢，我們發現 GCP 雲 sql ( https://www.brentozar.com/pastetheplan/?id=ByexjqpCF ) 上的執行計劃沒有在索引掃描中使用 projectId 謂詞，而是僅在進一步的過濾步驟。在本地執行時（https://www.brentozar.com/pastetheplan/?id=rJLO59pRK），謂詞被推入索引掃描，從而減少了要掃描的行數和更好的性能。

造成這種差異的原因可能是什麼？

SELECT YEAR(ReferenceDate) RefDateYear, MONTH(ReferenceDate) RefDateMonth,sum(diffsum) DiffSum
into #res
FROM Journal jp
WHERE jp.projectId='582b02e2-add0-4b50-94f7-4e7e07497cf6' AND ReferenceDate < '20220101' AND batch != 9998 
GROUP BY YEAR(ReferenceDate), MONTH(ReferenceDate)

另請參閱表架構：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Journal](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [projectId] [uniqueidentifier] NOT NULL,
   [diffSum] [money] NOT NULL,
   [batch] [int] NOT NULL,
   [referenceDate] [datetime2](7) NOT NULL,
   ...
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Journal] ADD  CONSTRAINT [Journal_pkey] PRIMARY KEY NONCLUSTERED 
(
   [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [CCI_journal] ON [dbo].[Journal] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
GO

SQL Server Standard Edition 有許多與列儲存性能相關的限制。

1 In-Memory OLTP 數據大小和列儲存段記憶體受限於Scale Limits部分中由 edition 指定的記憶體量。對於 SQL Server Standard Edition，批處理模式操作的並行度 (DOP)限制為 2，對於 SQL Server Web 和 Express Edition，限制為 1。這是指在基於磁碟的表和記憶體優化表上創建的列儲存索引。

2聚合下推、字元串謂詞下推和 SIMD 優化是 SQL Server 企業版可擴展性增強

來自Columnstore 索引 - 新增功能 - 數據庫兼容性級別 120 或 130 的性能：

• 字元串謂詞下推加速了比較 VARCHAR/CHAR 或 NVARCHAR/NCHAR 類型字元串的查詢。這適用於常見的比較運算符，包括使用點陣圖過濾器的運算符，例如 LIKE。這適用於所有支持的排序規則。在 SQL Server 上，此增強功能保留用於企業版。

儘管您的謂詞是 UUID，但它在這裡算作字元串謂詞。如果沒有字元串謂詞下推，則謂詞在單獨的過濾器中進行評估，無需特殊優化。

列儲存索引 - 查詢性能的文件中對字元串謂詞下推有更全面的描述。

字元串謂詞下推利用為列創建的主/輔助字典來提高查詢性能。例如，讓我們考慮由 100 個不同的字元串值組成的行組中的字元串列段。這意味著假設有 100 萬行，每個不同的字元串值平均被引用 10,000 次。

使用字元串謂詞下推，查詢執行會根據字典中的值計算謂詞，如果符合條件，則所有引用字典值的行都會自動限定。這通過兩種方式提高了性能：

1. 只返回符合條件的行，減少需要流出 SCAN 節點的行數。
2. 字元串比較的次數顯著減少。在此範例中，與 100 萬次比較相比，只需要 100 次字元串比較。有一些限制，如下所述：

* 增量行組沒有字元串謂詞下推。增量行組中的列沒有字典。 * 如果字典超過 64 KB 條目，則沒有字元串謂詞下推。 * 不支持評估 NULL 的表達式。

看看標準版中的列儲存有多大用處？由 Erik Darling 提供其他範例。

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