dm_db_stats_histogram missing row when column/histogram step contains NULL
When statistics on a nullable column contain a NULL sys.dmdbstatshistogram will not return a row for the corresponding histogram step (when RANGEHIKEY is a NULL). DBCC SHOWSTATISTICS returns all steps including the aforementioned missing one. Without all the histogram levels dmdbstats_histogram is not usable.
I can confirm this behaviour with INT and VARCHAR columns, I haven't tested other data types). I have attached the reproduction code and a screenshot. Any questions please let me know. Thank you.
SQL Version: 14.0.3025
Fixed in SQL Server 2017 CU21 and SQL Server 2019 CU7
Paul White commented
Fixed in SQL Server 2017 CU 21
This makes automation and analysis using the DMOs impossible. Back to dynamic SQL.
Discovered this for a column where the vast majority of data was NULL. Very confusing!
(Microsoft SQL Server 2017 (RTM-CU11) (KB4462262) - 14.0.3038.14 (X64) )
Gary Harding commented
Agree, the missing histogram step for NULL values contains useful information in the equal_rows column.
Without it, the DMO is not a complete replacement for DBCC SHOW_STATISTICS, which I imagine was the intention.