Thursday, December 18, 2014

Finding Missing Index

The first thing I usually do when optimizing queries is to check if there is at least one index on all tables referred in the query. However, having the right index is important as well. I found this query on the net that finds what index is missing on most frequently used queries. I would thank them but I can't remember

SELECT  
Avg_Estimated_Impact = dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans),
TableName = OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id),
Create_Statement = 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
   + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
   CASE
      WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
      ELSE ''
   END
   + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
   + ']'
   + ' ON ' + dm_mid.statement
   + ' (' + ISNULL (dm_mid.equality_columns,'')
   + CASE 
         WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' 
         ELSE ''
      END
   + ISNULL (dm_mid.inequality_columns, '')
   + ')'
   + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '')
FROM sys.dm_db_missing_index_groups dm_mig
   INNER JOIN sys.dm_db_missing_index_group_stats dm_migs ON dm_migs.group_handle = dm_mig.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details dm_mid ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY last_user_seek DESC
Here's the result set:
Avg_Estimated_Impact        TableName    Create_Statement
-------------------------   ------------ ------------------------------------------------
2052.6                      MyTable      CREATE INDEX [IX_MyTable_Col1_Col2] ON [DBName].[dbo].[MyTable] ([Col1],[Col2]) INCLUDE ([Col3], [Col4])

2066.24                     MyOtherTable CREATE INDEX [IX_MyOtherTable_Col3_Col5] ON [DBName].[dbo].[MyOtherTable] ([Col3],[Col5]) INCLUDE ([Col4])

Comment?


~~ CK

No comments:

Post a Comment