How To Find Missing Indexes in SQL Server

dbtut April 21, 2019 MSSQL

In the database, especially during the new development phase, indexes are the most important performance providers for us. You should be familiar with the index concept before detecting missing indexes. The article “Index Concept and Performance Effect on SQL Server” will help you.

The following query will be a start for you to detect missing indexes in your database. But running all the index create scripts returned in the query will not be the right solution. The result set is sorted by Impact. So you should start reviewing from the first record.

SELECT
mid.statement AS [Query Text]
  ,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS [Impact],
  'CREATE INDEX [IX_'+LEFT (PARSENAME(mid.statement, 1), 32)+'_'
  +ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(mid.equality_columns, '[',''),',','_'),']',''),' ',''),'')+
  +ISNULL('_'+REPLACE(REPLACE(REPLACE(REPLACE(mid.inequality_columns, '[',''),',','_'),']',''),' ',''),'')+']'
  + ' 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 + ')', '') AS [Index Create Script]
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) > 10 and mid.database_id=DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

SELECT

mid.statement AS [Query Text]

,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS [Impact],

'CREATE INDEX [IX_'+LEFT (PARSENAME(mid.statement, 1), 32)+'_'

+ISNULL(REPLACE(REPLACE(REPLACE(REPLACE(mid.equality_columns, '[',''),',','_'),']',''),' ',''),'')+

+ISNULL('_'+REPLACE(REPLACE(REPLACE(REPLACE(mid.inequality_columns, '[',''),',','_'),']',''),' ',''),'')+']'

+ ' 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 + ')', '') AS [Index Create Script]

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) > 10 and mid.database_id=DB_ID()

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

When I looked at the first index create script in my environment, it suggested an index that has 17 included columns for a table that has 20 column. Of course I didn’t create this index. Because creating this index means almost the same thing with recreating table. In the article I mentioned above, I gave detailed information about which index we should create and which index we should not create.

If you have performance problem in your SQL Server, you may want to read the following articles.

“How To Find Most Expensive Queries in SQL Server”

“How To Identify Long Running Queries Using Extended Events“,

“How To Identify Long Running Queries Using SQL Server Profiler“,

“Running SQL Server With High Performance(High Performance)“,

“Index Concept and Performance Effect on SQL Server“,

“Statistics Concept and Performance Effect on SQL Server”