Missing Stats DMV?

Useful statistics are critical for great performance in SQL Server. So much so, that by default, SQL Server creates statistics for columns it deems worthy without any help or interjection from anyone.1 However, SQL Server isn’t yet quite smart enough to automatically create multi-column stats objects that might be helpful. It would be great to have a built-in missing stats DMV. For those wondering if multi-column stats are actually used by SQL Server’s cardinality estimator, this Microsoft blog post shows SQL Server 2016 compatibility level 130 making use of multi-column stats to provide better row estimates.

Lies, damn lies, and missing stats dmv

    Lies, damn lies, and statistics

SQL Server does have a fairly useful dynamic management view, or DMV, which provides insight that can be leveraged in this area. The DMV I’m talking about is the set of DMVs around missing indexes, consisting of sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details, etc. I’m not saying the missing indexes DMVs are a panacea that will enable you to fix every performance situation you run into, but they can be useful if you know where to look. This post doesn’t go into a lot of depth about how to use those DMVs for the purpose of actually creating indexes, however I will show you how you can create multi-column stats objects as an interim performance booster while evaluating the need for those indexes.

The script below compares the details provided by the missing index DMVs against the existing sys.stats and sys.stats_columns views to provide a list of potential new stats.

The results show two columns, ObjectName and Columns. These columns can be used in a CREATE STATISTICS statement. To save a small amount of time, the third column provides a template CREATE STATISTICS statement. Ensure you don’t just blindly create all statistics presented here; do a quick sanity check to ensure they make sense for your environment.

Let me know if this “missing stats DMV” is useful, or if you think I’m crazy for doing this.

See the rest of our series on performance.

1 Assuming the database has auto_create_stats enabled. See https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-2017#AutoUpdateStats for more details.