Update Statistics on a Columnstore index failed

· by · in

SQL Server relies upon good, up-to-date statistics when creating query execution plans. Out-of-date statistics can cause queries to run slowly, or suddenly start consuming far more resources than is typically required. Columnstore statistics are quite different from typical SQL Server table statistics. “Normal” statistics objects can, and should, be updated on an ongoing basis. One of our earlier posts, Statistics Update Job, has one such script that does exactly that. However, attempting to update statistics on a columnstore index results in the following error:

UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.  UPDATE STATISTICS is valid only when used with the STATS_STREAM option. [SQLSTATE 42000] (Error 35337)

Msg 35337, Level 16, State 1, Line 1
UPDATE STATISTICS failed because statistics cannot be updated on a columnstore index.  UPDATE STATISTICS is valid only when used with the STATS_STREAM option.

The first error above would be seen if you have a SQL Server Agent job that updates statistics. The second error is how it looks in an SSMS Query window.

The error message claims that UPDATE STATISTICS can only be used on a columnstore index with the STATS_STREAM option. However, the Microsoft Docs UPDATE STATISTICS shows very “thin” documentation for the option, showing only these two tidbits:

<update_stats_stream_option>
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Syntax is:

update statistics t1 (a) with stats_stream = 0x01;

Valid values for the STATS_STREAM option seem completely undocumented. Running an UPDATE STATISTICS command with the STATS_STREAM clause returns the following error for every hex value I tried:

Msg 9105, Level 16, State 1, Line 6
The provided statistics stream is corrupt.

As far as I can tell, nothing is actually corrupted. This fairly accurately represents the expression on my face from trying lots of hex digits with this:

this fellow seems a bit tired, possibly from attempting to update statistics on a columnstore index

Poor fellow seems a might tired; perhaps from attempting to update statistics on a columnstore index.

Running UPDATE STATISTICS with both FULLSCAN and STATS_STREAM results in the following error:

Msg 1052, Level 15, State 1, Line 1
Conflicting UPDATE STATISTICS options "STATS_STREAM" and "FULLSCAN".

Microsoft’s built-in stored procedure for updating statistics database-wide, sp_updatestats, uses the following to exclude columnstore indexes from stats updates:

I’ve added the following exclusion to my stats update code:

Let me know if you have any questions, and check out the other posts in our series on maintenance.

You might be interested in reading this article from Microsoft about statistics and columnstore indexes.