Query Your Statistics: dm_db_stats_properties

We’ve always been able to look at statistics with DBCC SHOW_STATISTICS. You can even tell SHOW_STATISTICS to only give you the properties, STAT_HEADER, or histogram, HISTOGRAM. However, it’s always come back in a format that you can’t easily consume in T-SQL. From SQL Server 2012 to everything else, you can simply query sys.dm_db_stats_properties to get that same header information, but in a consumable fashion.

dm_db_stats_properties

You can pretty easily query the function dm_db_stats_properties:

SELECT ddsp.object_id,
       ddsp.stats_id,
       ddsp.last_updated,
       ddsp.rows,
       ddsp.rows_sampled,
       ddsp.steps,
       ddsp.unfiltered_rows,
       ddsp.modification_counter,
       ddsp.persisted_sample_percent
FROM sys.dm_db_stats_properties(OBJECT_ID('Production.Location'), 1) AS ddsp;

You just have to pass in the object_id value and the stats_id value and you’re off to the races.

I can hear you.

Why?

So we can do things like this. What if I want to look at statistics that have fewer than 200 steps in the histogram? Simple. Write a query:

SELECT OBJECT_NAME(s.object_id),
       s.name,
       ddsp.steps
FROM sys.stats AS s
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS ddsp
WHERE ddsp.steps < 200;

Now you see what I mean. With the ability to query statistics directly, we can do more than simply get information about them. You can put this information together in all sorts of fascinating ways.

Don’t forget about the histogram. You can query dm_db_stats_histogram in the same way.

Conclusion

This ability has been around for quite a long while. However, I just don’t see people using this much. So, I figured I’d quickly show you how dm_db_stats_properties works, and give you a little example of why it’s important. Hopefully this proves useful.