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 .