Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.
on January 17, 2017
I don’t find bugs in SQL Server all that often. I find bugs in my own code all the time.
In this case I double checked, and I think it’s a real SQL Server bug.
In SQL Server 2016 SP1, I see an issue with how SQL Server tracks and reports on modifications to column statistics on tables with a clustered columnstore index: it reports more modifications than actually occurred (and for columns that weren’t modified). The modification counter is useful for knowing approximately how much has changed since statistics were last updated.
Note: I only see this issue when the table has a clustered columnstore index. Things look normal when the table has a nonclustered columnstore index, or no columnstore index at all.
Please vote up my bug for visibility
Even if you’re not using clustered columnstore indexes yet, they’ll probably be in your future before long. After all, as of SQL Server 2016 SP1, you can now use these sweet babies in Standard Edition. (YES!)
Do Future You a favor and take a moment to vote up my bug so it gets reviewed, and hopefully fixed if Microsoft confirms the issue.
Code-free description of this bug
Here’s the set up – a simple table with a clustered columnstore index, and several column statistics:
- Create a table with several columns
- Populate it with rows
- Create a clustered columnstore index on it
- Run some queries with predicates to create column level stats on individual columns
- Check modification_counter for column statistics using a query that joins to sys.dm_db_stats_properties (at this point, they will be 0)
The test is simple:
- Update one column for a single row
- Check modification_counter for column statistics using a query that joins to sys.dm_db_stats_properties
Expected outcome: modification_counter should be 1 for a single column statistic (or it should be NULL for all column statistics if this simply isn’t supported on tables with clustered columnstore indexes)
Actual outcome: modification_counter is 2 for ALL column statistics
Repro code
I’ve created a gist with code to create a simple database and table and reproduce the bug. (This is the same code attached to the connect item.)