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

on December 6, 2016

Whether 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?

Query for 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:

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

Query 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:

]

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.