Bug: Incorrect modification_counter for Column Stats on Tables with a Clustered Columnstore Index

What’s that on my clustered columnstore index?

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.)


Previous Post
How to Query Histogram Target XML in Extended Events
Next Post
Why You Should Switch in Staging Tables Instead of Renaming Them (Dear SQL DBA Episode 26)

Related Posts

No results found

6 Comments. Leave new

  • Derek B. Bell
    January 17, 2017 9:20 am

    I ran this on my server [2014 Enterprise SP2 CU2] and had the same results, so this is not specific to SQL Server 2016 SP1.

  • Laurent Pasquier
    February 17, 2017 3:08 am


    As already posted to connect.microsoft…

    I ran the repro script on different servers with the following results :

    > On a server with Microsoft SQL Server 2014 (SP2-CU1) (KB3178925) – 12.0.5511.0 (X64) => I have the problem with the modification_counter
    > On a server with Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) – 13.0.2164.0 (X64) => no problem, the modification counter is correct.
    > On a server with Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64) => I have the problem with the modification_counter

    We can classify this bug in the “yo-yo” category… It goes and it comes back.

  • Laurent Pasquier
    August 17, 2017 1:04 am

    Hello Kendra,

    Microsoft closed your post on this subject :

    As far as I’m concerned, this bug is not solved.

    I have tables with very large historical data partitions and small partition with “hot data”.
    CRUD on small hot partitions data need to update statistics on the not changing large partitions because of this “share” modification_counter.

    Do you have some more informations on this subject, can you re-open it ?


  • Hello i have a severity A case with ms support for a problem with modification counter that is ever 0 despite of inserts and deletes. Thanks to this post i have tried and i see that updating a row the modification counter is always double the value of the modified rows. Actually we don’t update rows but we have this behavior.with mssql 2016cu7. Do you have this issue? Modification counter ever 0? It seems that updating a row “switch on” the modification counter .

  • I had this explaination from microsoft:

    If the row to be updated is part of a compressed rowgroup, the update operation will first mark the row as logically deleted, and then insert the updated row into the deltastore. This is the reason for modification_counter to increment by 2 per update (1 for existing row’s deletion and 1 for updated row’s insertion).

    Although if the row to be updated is currently in the deltastore (which is a traditional rowstore), then the engine will update it in-place and modification_counter will increment by only 1.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.