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.

Starting with SQL server 2005 , engine tracks indexes usage , through query optimizer, and can now determine if a query can benefit from adding indexes; this information can be identified using 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

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

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;

One important fact is that missing Indexes DMVs can track a maximum of 500 indexes so if you have many databases with many active tables then there’s a chance not all of the info will be present.

I’ve seen plenty are useful queries about creating statements to create these missing indexes but they just give vague names to indexes ,based on sys.dm_db_missing_index_groups fields.

Since it’s a very good practice to standardize your objects’ names , the following query will give names to indexes in following format

[IX_misssing_Tablename_col1-col2-coln_INC_col1-col2-coln]

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

Here’s the code, a default 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 and make sure that the MAX number of characters per column are larger than the default value of 256.

SSMS>options>Tools>Query results>SQL server>Results to text

There are some important notes about this script and missing indexes DMVs in general

1- These indexes’ recommendations are NOT to be taken as **absolute** trustworthy.

You should always look at the code and look at the benefit in terms of consumed resources, how frequent your table is read vs. write, which columns are already touched by queries …etc.

The reason is that the DMVs here is a good hint to point to your busy tables but that doesn’t always mean it will give a “silver bullet” of indexes to run and voila! Some of the recommended indexes can be pretty big with so many columns that may help some queries but can also hinder your write operations (Insert, Update, Delete), increase your backup and maintenance time and occupy more disk space.

Instead, a good understanding of design and which queries run frequently , coupled with this information should lead you to a good balance.

2- If you are looking to tune queries then you should look at execution plans to understand the costly operations and better tune the query in Database engine tuning Advisor , it’s smarter and will suggest better indexes and ALSO statistics (can also suggest partitioning in case of Enterprise Edition).

3- Since object names have a MAX of 128 Character length , the query trims indexes names to that.

4- The DMVs don’t suggest filtered indexes,clustered indexes, indexed views or partitioning ; as stated ,database engine tuning Advisor excels here.

5- DMVs data are stored for the duration of instance uptime. Low period may NOT generate enough information and all info is lost if instance is rebooted.

Some more information is also logged in Limitations of the Missing Indexes Feature

Enjoy
M