SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Find Missing Indexes For All SQL Server Databases

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 .

SQL Geek

Dharmendra is a SQL Server/Microsoft Data Platform professional with over eight years of experience. He enjoys helping others in the SQL Server community and does this by contributing on blogs, speaking at several SQL events. His passion and focus is to explore and share more and more on SQL Server.

Comments

Leave a comment on the original post [www.dharmendrakeshari.com, opens in a new window]

Loading comments...