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

Statistics Update Clarification

By default statistics are created automatically within SQL Server. And, by default, these stats are updated automatically based on a set of triggers. The triggers are defined as If 0 rows - Any data added leads to a statistics update If < 500 rows - 500 rows added causes a stats update If > 500 rows - 500 rows + 20% of the number of rows causes a stats update (unless you enable a traceflag in 2012 in which case you get a proportional value instead of 20%). There are some exceptions for temporary tables and some variations for filtered statistics and filtered indexes, but you get the idea. I was writing an article on statistics in preparation for another Oracle/SQL Server discussion (on, you guessed it, statistics) and I…
Read More