DMV (Database Management View) and DMO (Database Management Objects) were added in 2005. There have been huge improvements in each new edition of SQL Server. It provides us a lot of useful information about SQL Server like – expensive queries, wait type, missing index…., and the list goes on and on…

In this blog, I am going to discuss Missing Indexes for all databases. It can help you find missing indexes that are going to have a very high impact on your workload.

But before you create the missing indexes on the tables, I would strongly request you to consider the below mentioned essential points.

• Try to figure out which all kind of queries (SELECT, INSERT, UPDATE, or DELETE) are using the table. If the table got more and more DML operation, I want to you analyze the missing index impact more thoroughly before you create it on the table.

• You need to make sure that you are not going to end up with a duplicate index on the table by creating the missing index. The duplicate or unwanted indexes can kill your database performance. For more details, you can refer the blog over-indexing can hurt your SQL Server performance.

• If you find there is an existing index’s columns are pretty much matching with the suggested missing index’s columns. I would request you to leverage the existing index by making it a wider index instead of creating one more index on the table. Just as a warning – When I am saying to have a wider index, it doesn’t mean that I am saying you to add all column in the current index to make it wider.

Script to find Missing Indexes for all databases in SQL Server

SELECT 
migs.user_seeks as [Estimated Index Uses],
migs.avg_user_impact [Estimated Index Impact %],
migs.avg_total_user_cost[Estimated Avg Query Cost], 
db_name(mid.database_id) AS DatabaseID,
OBJECT_SCHEMA_NAME (mid.OBJECT_ID,mid.database_id) AS [SchemaName],
OBJECT_NAME(mid.OBJECT_ID,mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID,mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL 
THEN '_'
ELSE ''
  END
+ REPLACE(REPLACE(REPLACE(ISNULL(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 + ') WITH (MAXDOP =?, FILLFACTOR=?, ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [Create TSQL],
mid.equality_columns, 
mid.inequality_columns, 
mid.included_columns,
migs.unique_compiles,
migs.last_user_seek
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
ORDER BY [Estimated Index Uses] DESC OPTION (RECOMPILE);

I hope the script help you to align your indexes. Please let me know your thought on the script by  leaving a comment.

Thanks!

The post Find Missing Indexes For All SQL Server Databases appeared first on .