When Did SQL Server Last Update That Statistic? How Much Has Been Modified Since? And What Columns are in the Stat?

manual-stats-updateWhether I’m working as a DBA, a consultant, a teacher, or just answering questions in my inbox, I always end up needing a script to inspect statistics one way or another.

Here are some freshly written scripts for a classic DBA question: what’s going on in my stats?

How to get statistics details on SQL Server 2008 R2 and higher

For most modern versions of SQL Server, I like to join to sys.dm_db_stats_properties() — you can get a LOT of detail in a single query! (This works with SQL Server 2008 R2 SP2+ / SQL Server 2012 SP1+ / All higher versions)

Here’s the query, looking at a sample table in the WideWorldImporters database:

SELECT 
	stat.auto_created,
	stat.name as stats_name,
	STUFF((SELECT ', ' + cols.name
		FROM sys.stats_columns AS statcols
		JOIN sys.columns AS cols ON
			statcols.column_id=cols.column_id
			AND statcols.object_id=cols.object_id
		WHERE statcols.stats_id = stat.stats_id and
			statcols.object_id=stat.object_id
		ORDER BY statcols.stats_column_id
		FOR XML PATH(''), TYPE
	).value('.', 'NVARCHAR(MAX)'), 1, 2, '')  as stat_cols,
	stat.filter_definition,
	stat.is_temporary,
	stat.no_recompute,
	sp.last_updated,
	sp.modification_counter,
	sp.rows,
	sp.rows_sampled
FROM sys.stats as stat
CROSS APPLY sys.dm_db_stats_properties (stat.object_id, stat.stats_id) AS sp
JOIN sys.objects as so on 
	stat.object_id=so.object_id
JOIN sys.schemas as sc on
	so.schema_id=sc.schema_id
WHERE 
	sc.name= 'Warehouse'
	and so.name='StockItemTransactions'
ORDER BY 1, 2;
GO

The output looks like this:

Click to see a larger image

Click to see a larger image

 

Can you guess why the top row has NULL values for last_updated, modification_counter, rows, and rows_sampled? (Once you have your guess, the answer is here.)

What if my SQL Server is a dinosaur? Here’s a script for SQL Server 2005 and 2008.

If you’re using SQL Server 2008 or prior, you don’t  have the luxury of sys.dm_db_stats_properties().

For these instances, it’s not a big deal to get the date statistics were last updated – we can call to the STATS_DATE() function. But we’re really guessing when it comes to how many rows have been modified, we have to use a column called rowmodctr in sys.sysindexes which is a guess at how many rows have changed. It also was wildly inaccurate in some versions of SQL Server 2005.

But a guessed estimate is better than no information, as long as you know it’s a guess!

Here’s the query:

SELECT 
    stat.auto_created,
    stat.name as stats_name,
    STUFF((SELECT ', ' + cols.name
        FROM sys.stats_columns AS statcols
        JOIN sys.columns AS cols ON
            statcols.column_id=cols.column_id
            AND statcols.object_id=cols.object_id
        WHERE statcols.stats_id = stat.stats_id and
            statcols.object_id=stat.object_id
        ORDER BY statcols.stats_column_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')  as stat_cols,
    stat.filter_definition,
    stat.is_temporary,
    stat.no_recompute,
    STATS_DATE(stat.object_id, stat.stats_id) as last_updated,
    ISNULL(
    /* Index stats */
    (SELECT rowmodctr
        FROM sys.sysindexes as sysind
        JOIN sys.indexes as ind on 
            sysind.id=ind.object_id
            and sysind.indid=ind.index_id
        where sysind.id=stat.object_id
        and ind.name=stat.name 
    ),
    /* Column stats */
    (SELECT rowmodctr
        FROM sys.sysindexes as sysind
        where sysind.id=stat.object_id
        and sysind.indid in (0,1)
    )) 
        AS estimated_modification_counter
FROM sys.stats as stat
JOIN sys.objects as so on 
    stat.object_id=so.object_id
JOIN sys.schemas as sc on
    so.schema_id=sc.schema_id
WHERE 
    sc.name= 'Warehouse'
    and so.name='StockItemTransactions'
ORDER BY 1, 2;
GO

And here’s how the output looks:

Click to see a larger image

Click to see a larger image

 

See how the estimated modification counter is totally off in the top row, and for the column statistic? That’s because this table has a clustered columnstore index — and ye olde rowmodctr column totally doesn’t understand what’s going on with that!

This script is going to be wrong about things like Columnstore indexes. But if you have Columnstore indexes, you’re on a version of SQL Server that lets you use the non-Dinosaur script above, which is more accurate.

TLDR;

Make sure you use the script for the correct version of SQL Server that you’re running. The “dinosaur” script is less accurate, but better than nothing when you need this.

, , , , ,

8 Responses to When Did SQL Server Last Update That Statistic? How Much Has Been Modified Since? And What Columns are in the Stat?

  1. James Youkhanis December 6, 2016 at 8:31 am #

    Would you recommend enabling traceflag 2371 on SQL Server 2012 for a OLTP system or mayabe use Ola Hallengren’s scripts to update stats nightly. Maybe both?

  2. Dennis January 7, 2017 at 3:30 pm #

    Two points,

    First, I guess your script could benefit by “[Ratio] = format(sp.modification_counter*1./sp.rows,’P’)” for a better readability.

    Second, I came across a values for modification_counter > 2* rows – is this to be expected?
    For a smaller tables I would assume SQL Server simply doesn’t care.
    For tables with 2mio+ rows I’m starting to find this odd.

    • Dennis January 7, 2017 at 3:39 pm #

      Sorry, forgot to mention. Trace flag 2371 is been set and settings for auto_update_stats, auto_create_stats and auto_update_async_stats are switched on.

    • Kendra Little January 9, 2017 at 8:27 am #

      Question – what @@version of SQL Server are you looking at?

      It is expected that if you modify the same column in the same row twice, that will increment the modification counter more than once. So yes, it’s allowed that on a frequently modified table, you might have the modification counter be higher than the number of rows — and it could be twice as much depending on how the table is used.

      However, in putting together a quick demo of this, I noticed that the modification counter is increasing at a rate higher than the number of modifications I’m doing! I’m testing this against Microsoft SQL Server 2016 (SP1) (KB3182545) – 13.0.4001.0 (X64). It looks like a bug to me, but I’m going to check my work a bit more before filing it (haven’t had any coffee yet).

      • Dennis January 9, 2017 at 8:51 am #

        Hi, here ther version number:

        Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) – 11.0.5388.0 (X64)
        Sep 23 2016 16:56:29
        Copyright (c) Microsoft Corporation
        Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

        • Kendra Little January 16, 2017 at 9:49 am #

          Hi Dennis,

          I did more research, and the issue I’ve found is specific to cases where there’s a clustered columnstore index on the table. It behaves normally in all other cases for me. I’m going to file a bug on the issue I found.

          Kendra

Trackbacks/Pingbacks

  1. Bug: Incorrect modification_counter for Column Stats on Tables with a Clustered Columnstore Index - by Kendra Little - January 17, 2017

    […] 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. […]

Leave a Reply