Update Statistics on a Columnstore index failed
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:
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
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:
UPDATE STATISTICS with both
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:
indexproperty(id, name, 'iscolumnstore') = 0
I’ve added the following exclusion to my stats update code:
WHERE NOT EXISTS ( --exclude columnstore indexes that cannot have stats updates
FROM sys.indexes i
WHERE i.object_id = st.object_id
AND i.index_id = st.stats_id
i.type_desc = N'CLUSTERED COLUMNSTORE'
i.type_desc = N'NONCLUSTERED COLUMNSTORE'
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.
If you found this post useful, please
consider donating a small amount
to help keep the lights on and site running.
[…] Hannah Vernon walks us through some problems trying to update statistics on columnstore indexes: […]