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