DMV: sys.dm_db_stats_histogram (object_id, stats_id)
For a long time, the DBCC SHOW_STATISTICS command was the only way to get information about statistics. It displays current query optimization statistics for a table or indexed view. It was always a painful task, if you wanted to capture output of the DBCC SHOW_STATISTICS for all tables in one go.
With the release of SQL Server vNext CTP1.3, Microsoft has introduced a new DMV sys.dm_db_stats_histogram. This DMV returns the statistics histogram for the specified database object (table or indexed view) in the current SQL Server database. It is equivalent to DBCC SHOW_STATISTICS WITH HISTOGRAM.
Note: As per Microsoft, the DMV will also become available in an upcoming SQL Server 2016 SP1 CU?. The CU number has to be confirmed by Microsoft.
Demonstration
I am going to use “WideWorldImporters” sample database to demonstrate it. Let’s say we need to look at statistics information of the table “PurchaseOrderLines” in the database. Let’s first retrieve the table id (object_id) and stats_id.
SELECT OBJECT_NAME(OBJECT_ID) as tblName, OBJECT_ID, name, stats_id FROM sys.stats WHERE OBJECT_NAME(OBJECT_ID) = 'PurchaseOrderLines'
Older way (DBCC) to retrieve Histogram information
- Run the DBCC SHOW_STATISTICS WITH HISTOGRAM to fetch the histogram details of the statistics “FK_Purchasing_PurchaseOrderLines_PackageTypeID”
DBCC SHOW_STATISTICS ('Purchasing.PurchaseOrderLines','FK_Purchasing_PurchaseOrderLines_PackageTypeID')
WITH HISTOGRAM
Newer way (DMV) to retrieve Histogram information
- To fetch the HISTOGRAM details, you can use the below DMV where I passed object_id and stats_id to get the data.
SELECT OBJECT_NAME(OBJECT_ID) as tblName, * FROM sys.dm_db_stats_histogram(1554104577, 4)
- Here is a way to fetch out all the table’s histogram information in a database in one shot.
SELECT OBJECT_NAME(hist.object_id) tblName, hist.stats_id, step_number, range_high_key, equal_rows, distinct_range_rows, average_range_rows FROM sys.stats st CROSS APPLY sys.dm_db_stats_histogram(st.object_id, st.stats_id) hist --WHERE st.object_id = 1554104577 AND st.stats_id = 4
I hope you enjoyed learning!
4 thoughts on “Read Statistics Histogram using DMV”
I loved this dmv… Thanks for sharing awesome stuff!
WHY do you love it? What do you actually do with the information that it provides?
This is nice and thanks for taking the time to post the information but it doesn’t answer some important questions… WHY would you need such information? What can you do with that information?
Thanks Jeff for your feedback! When we are troubleshooting query performance issues such as parameter sniffing issue, stale statistics issue..etc, It can help to see all the statistics information in one shot… The purpose of the post to show how you can leverage the DMV to see all the statistics in one go..