If you’re a graduate of my free How to Think Like the SQL Server Engine course – and you’d better be, dear reader – then you’re vaguely familiar with DBCC SHOW_STATISTICS. It’s a command that shows you the contents of a statistics histogram.
When I’m doing the first two parts of the D.E.A.T.H. Method – (D)eduplicating identical indexes and (E)liminating unused indexes – I often wanna check the selectivity of the first field in the index. However, running DBCC SHOW_STATISTICS was painful because it’s not set-based – I couldn’t easily dump out the contents of lots of indexes at once.
That’s where sys.dm_db_stats_histogram comes in really handy. I’ve added a new section to the bottom of sp_BlitzIndex to show its contents when you’re examining a single table with the @TableName parameter:
In that screenshot, I’m looking at the indexes on the Stack Overflow Users table, in particular, an index on DisplayName. The bottom section shows the histogram for that index, and if you keep scrolling down – in the real output, not the screenshot, silly – you can see the histograms for the rest of the indexes.
Now, this is a brand spankin’ new feature – it’s only in the dev branch of the First Responder Kit, and sys.dm_db_stats_histogram is only available on SQL Server 2016 SP1 CU2, 2017, and newer.
Right now, the stats on user-created indexes show up first, followed by the user-created stats, followed by the auto-created stats, all in alphabetical order. This feature also only works when you’re looking at a specific table with the @TableName parameter – it doesn’t really make sense to dump out all histograms across an entire database.
My question for you, dear reader, is: are there any changes you would want to this feature before I roll it out as part of the main branch? If you want to show other data, don’t just give me the wish list – give me the problem you’re trying to solve with the additional data, because there might be a better way to render it than what I’m showing.
You can leave a comment with any changes you’d like to see, or, if you’d like to edit the code and contribute changes yourself, check out the contribution guide.