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