SQL server: Create missing indexes with unmessing names

If you do any performance tuning, and everyone does, then you’ll come to a point where you want to know if your tables have sufficient indexes to serve the queries fast.

Identifying Missing Indexes with DMVs

Starting with SQL Server 2005, the engine tracks index usage through the query optimizer and can now determine if a query can benefit from adding indexes.

How the Engine Tracks Your Needs

This information can be identified using a few Dynamic Management Views (DMV), including:

• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_columns

A Quick Database Inventory

You can determine an estimate of the number of missing indexes per database by running the following query:

SQL

SELECT DB_NAME(database_id) Database_name , count(*) No_Missing_indexes FROM sys.dm_db_missing_index_details GROUP BY DB_NAME(database_id) ORDER BY count(*) DESC; 

Note: One important fact is that missing Index DMVs can track a maximum of 500 indexes. If you have many databases with many active tables, there’s a chance not all information will be present.

Standardizing Index Names for Deployment

I’ve seen plenty of useful queries about creating statements to create these missing indexes, but they often give vague names based on sys.dm_db_missing_index_groups fields. Since it’s a very good practice to standardize your objects’ names, the following approach is preferred.

The Proposed Naming Convention

The following query will give names to indexes in this format: [IX_misssing_Tablename_col1-col2-coln_INC_col1-col2-coln]

The word “missing” here is a reference to imply that the index definition was suggested by DMVs. INC is trailed by the index included columns. Please note that a DASH is used in the name here, so the index name will have to be square bracketed ([]) whenever being referenced in code (Rebuild, defrag, drop, disable, enable, etc.).

The Automated Index Generation Script

Here’s the code. A default Filegroup (FG) is also appended to the statement in case you are using FGs, which is a very good practice.

Declare @default_FG sysname Set @default_FG ='Secondary' ;With Missing_indexes AS ( SELECT '--AVG User impact : '+ CAST(migs.avg_user_impact as varchar(20)) +' '+ 'CREATE INDEX ' + Cast ( '[IX_missing_' + LEFT (PARSENAME(mid.statement, 1), 32) + replace(replace(replace(ISNULL ('_' + mid.equality_columns,''),']',''),'[',''),', ','-') + replace(replace(replace(ISNULL ('-' + mid.inequality_columns,''),']',''),'[',''),', ','-') + replace(replace(replace(ISNULL ('_INC_' + mid.included_columns,''),']',''),'[',''),', ','-') + ']' as sysname ) +']' + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns,'') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '') + ') ' + ISNULL ('INCLUDE (' + mid.included_columns + ')', '') + 'ON ' + QUOTENAME(@default_FG) + ' GO ' as SQL_Statement ,migs.avg_user_impact FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 15 ) select Replace(SQL_Statement,']]',']') From Missing_indexes order by avg_user_impact desc 

It’s recommended to run this with the result output set to text. Make sure that the MAX number of characters per column is larger than the default value of 256 via: SSMS > Tools > Options > Query Results > SQL Server > Results to Text.

Crucial Considerations and Limitations

There are some important notes about this script and missing index DMVs in general that you should keep in mind before deploying.

Why You Shouldn't Trust DMVs Blindly

These index recommendations are NOT to be taken as absolute trustworthy. You should always look at the code and the benefit in terms of consumed resources, how frequent your table is read vs. write, and which columns are already touched by queries.

The DMVs are a good hint to point to your busy tables, but that doesn’t always mean they provide a “silver bullet.” Some recommended indexes can be quite large with many columns. While they may help some queries, they can also hinder write operations (Insert, Update, Delete), increase backup and maintenance time, and occupy significant disk space.

Key Technical Limitations

1. Scope of Recommendations: The DMVs don’t suggest filtered indexes, clustered indexes, indexed views, or partitioning. For these, the Database Engine Tuning Advisor excels.
2. Persistence: DMV data is stored for the duration of instance uptime. All information is lost if the instance is rebooted.
3. Execution Plans: If you are looking to tune queries, you should always look at execution plans to understand costly operations.
4. Object Names: Since object names have a MAX of 128 character length, the query trims index names to fit.

Some more information is also logged in the official documentation regarding the limitations of the missing indexes feature.

Enjoy!

SQL Server Consulting Services

Ready to future-proof your SQL Server investment?