I've moved several large tables (each with >10^9 rows and a couple of dozens columns) from clustered rowstore to clustered columnstore indexes on a SQL Server 2014 instance and noticed that statistics updates on those (default sampling, triggered in our ETL or from Hallengren scripts) now take significantly longer.
A more theoretical question is why is it so? My wild guess is that statistics update produces a lot of random reads which doesn't work well with the columnstore indexes, as they are more suited for sequential reads of large amounts of data. I would be happy to know a more "in-depth" explanation.
More important question is whether I can do something against it. I've tried my test case for a table with a single bigint column (see below) on a SQL Server 2017 instance with the same result. Incremental statistics seems to be on a paper a good solution. I would need to recreate all statistics objects (which are currently not incremental, probably because of historical reasons), extend the ETL logic and update our version of the Hallengren scripts (we currently use an old one). I would appreciate if someone would share his/her experience before I go down this rabbit hole.
Steps to reproduce:
/*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