Why is statistics update slower on a clustered columnstore than on a rowstore?

Question

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

Answer 1

You are allowing SQL Server to choose the sampling rate for the statistics.

Run the test again with a specific sample for both and you should see more comparable times.

UPDATE STATISTICS dbo.rowstore 
WITH SAMPLE 1 PERCENT;

UPDATE STATISTICS dbo.columnstore 
WITH SAMPLE 1 PERCENT;

or better yet

UPDATE STATISTICS dbo.rowstore 
WITH SAMPLE 1000000 ROWS;

UPDATE STATISTICS dbo.columnstore 
WITH SAMPLE 1000000 ROWS;

It's difficult to estimate the number of rows in a sample percentage due to columnstore compression.

There is a bit more work to do on the columnstore to decompress data and assemble the columns into rows. This would normally be more than made up for due to compression benefits and batch mode processing, but DDL plans do not support batch mode yet.

Incremental statistics are something you could test to see whether they work out for you. Whether they are a win or not depends on your priorities. It can be difficult to get the initial sample size right and the optimizer does not currently take advantage of the per-partition statistics. If you update statistics a lot, and the time taken is your biggest concern, this might be the right move.

Although the bug referenced has been fixed, you might be interested in the general observations given in the excellent answer to Statistics disappear after incremental update.