為什麼聚集列儲存上的統計資訊更新比行儲存上的慢？

我已經將幾個大表（每個表有 >10^9 行和幾十列）從聚集行儲存移動到 SQL Server 2014 實例上的聚集列儲存索引，並註意到這些表的統計資訊更新（預設採樣，在我們的 ETL 中觸發）或來自 Hallengren 腳本）現在需要更長的時間。

一個更理論的問題是為什麼會這樣？我的瘋狂猜測是統計資訊更新會產生大量隨機讀取，這與列儲存索引不兼容，因為它們更適合大量數據的順序讀取。我很高興知道更“深入”的解釋。

更重要的問題是我是否可以針對它做點什麼。我已經在 SQL Server 2017 實例上嘗試了針對具有單個 bigint 列（見下文）的表的測試案例，結果相同。增量統計似乎是一個很好的解決方案。我需要重新創建所有統計對象（目前不是增量的，可能是因為歷史原因），擴展 ETL 邏輯並更新我們的 Hallengren 腳本版本（我們目前使用舊版本）。如果有人在我進入這個兔子洞之前分享他/她的經驗，我將不勝感激。

重現步驟：

/*Create a rowstore and a columnstore table with a single bigint column*/
CREATE TABLE dbo.rowstore (col1 BIGINT);
GO

CREATE TABLE dbo.columnstore (col1 BIGINT);
GO

CREATE CLUSTERED COLUMNSTORE INDEX CCI_columnstore ON dbo.columnstore;
GO

/*Fill both tables with 400 * 10^6 rows. This results in a 15GB large rowstore and a 3,1GB large columnstore tables*/
;WITH e1(n) AS
(
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
   SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 1 FROM e2 CROSS JOIN e2 AS b), -- 100*100
e4(n) AS (SELECT 1 FROM e3 CROSS JOIN e3 AS b)  -- 10000*10000
INSERT dbo.rowstore WITH (TABLOCK)
 SELECT CAST(CAST(NEWID() AS VARBINARY(8)) AS BIGINT) FROM e4;

GO 4

INSERT dbo.columnstore WITH (TABLOCK)
SELECT * FROM dbo.rowstore
GO

/*Trigger stats creation*/
SELECT TOP 1 * FROM dbo.rowstore WHERE col1>0

SELECT TOP 1 * FROM dbo.columnstore WHERE col1>0
GO

SET STATISTICS TIME, IO ON

/*This runs 1,5 seconds*/
UPDATE STATISTICS dbo.rowstore

/*This runs 8 seconds and becomes much slower than rowstore on really large tables*/
UPDATE STATISTICS dbo.columnstore

您允許 SQL Server 選擇統計數據的採樣率。

使用兩者的特定樣本再次執行測試，您應該會看到更多可比較的時間。

UPDATE STATISTICS dbo.rowstore 
WITH SAMPLE 1 PERCENT;

UPDATE STATISTICS dbo.columnstore 
WITH SAMPLE 1 PERCENT;

或者更好

UPDATE STATISTICS dbo.rowstore 
WITH SAMPLE 1000000 ROWS;

UPDATE STATISTICS dbo.columnstore 
WITH SAMPLE 1000000 ROWS;

由於列儲存壓縮，很難估計樣本百分比中的行數。

在列儲存上還有一些工作要做，以解壓縮數據並將列組合成行。由於壓縮優勢和批處理模式處理，這通常可以彌補，但 DDL 計劃尚不支持批處理模式。

增量統計是您可以測試的東西，看看它們是否適合您。他們是否獲勝取決於您的優先事項。獲得正確的初始樣本大小可能很困難，並且優化器目前沒有利用每個分區的統計資訊。如果您經常更新統計數據，並且花費的時間是您最關心的問題，那麼這可能是正確的舉措。

儘管引用的錯誤已得到修復，但您可能會對Statistics 的出色答案中給出的一般觀察結果在增量更新後消失。

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