Explore statistics histogram using SQL Server 2017

Prior to SQL Server 2016 SP1 CU2, we need to use DBCC SHOW_STATISTICS WITH HISTOGRAM to read Statistics histogram of a specific statistics. Since it is a DBCC command in order to filter any information or if you want to join with another table, we first need to save the out to a table.

Below is the output of DBCC SHOW_STATISTICS when you read the histogram.

Statistics1

Now sys.dm_db_stats_histogram DMF is available in SQL Server 2016 SP1 CU2 and in SQL Server 2017.

Syntax: sys.dm_db_stats_histogram (object_id, stats_id)

The first parameter is the id of the object, for which properties of one of its statistics is requested. Second parameter is the id of the statistics object. You can get the id of ststiatsics using sys.stats DMV.

It returns information similar to DBCC SHOW_STATISTICS WITH HISTOGRAM.

Statistics2

The range_high_key column is of type sql_variant data type so you need to cast or convert if predicate is compared with a non-string constant.

Now you can join the histogram result with other tables and apply filter on it for further analysis.

 

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published. Required fields are marked *