07Feb 2022 by Grant Fritchey 1 Comment

Query Your Statistics: dm_db_stats_properties

SQL Server
We've always been able to look at statistics with DBCC SHOW_STATISTICS. You can even tell SHOW_STATISTICS to only give you the properties, STAT_HEADER, or histogram, HISTOGRAM. However, it's always come back in a format that you can't easily consume in T-SQL. From SQL Server 2012 to everything else, you can simply query sys.dm_db_stats_properties to get that same header information, but in a consumable fashion. dm_db_stats_properties You can pretty easily query the function dm_db_stats_properties: SELECT ddsp.object_id, ddsp.stats_id, ddsp.last_updated, ddsp.rows, ddsp.rows_sampled, ddsp.steps, ddsp.unfiltered_rows, ddsp.modification_counter, ddsp.persisted_sample_percent FROM sys.dm_db_stats_properties(OBJECT_ID('Production.Location'), 1) AS ddsp; You just have to pass in the object_id value and the stats_id value and you're off to the races. I can hear you. Why? So we can do things like this. What if I want to look at statistics that have fewer…
Read More
29May 2018 by Grant Fritchey 2 Comments

Query To Retrieve Statistics Data: dm_db_stats_histogram

SQL Server 2017, T-SQL
Starting with SQL Server 2016 Sp1 CU2, a new way of directly querying statistics, specifically the histogram, has been introduced: dm_db_stats_histogram. We've always been able to query the statistics using DBCC SHOW_STATISTICS. However, the output, three different result sets with three different structures, made automating access to statistics information a pain. Now, we have more capability through dm_db_stats_histogram. dm_db_stats_histogram To access the information in dm_db_stats_histogram, you just have to pass in the object_id and the statistics_id values for the statistics you're interested in like this: SELECT * FROM sys.dm_db_stats_histogram(OBJECT_ID('HumanResources.Employee'), 1) AS ddsh; It's very straight forward to use. The results look like this: Handy right? Now you can query the histogram directly. Yeah, I hear a few of you saying... and this helps me... how? Here's an example. This query…
Read More