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.

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

SELECTOBJECT_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!

The post Read Statistics Histogram using DMV appeared first on .