Missing Indexes

06 Jun 2022 30430 views 0 minutes to read Contributors

Situation

SQL Server registers every missing index. Here is how you retrieve that information from the missing index DMV's

		
			
T-SQL
			
Transact-SQL
			

			
select db_name(t1.database_id) as database_name, t1.inequality_columns,t1.included_columns,t1.statement as table_name,(select max(rowcnt) from sysindexes where id=t1.object_id and status in (0, 2066) ) as [rowcount] , t2.user_seeks,t2.last_user_seek,t2.last_user_scan,t2.avg_total_user_cost,t2.avg_user_impact, 'CREATE INDEX idx_DBO_'+REPLACE(REPLACE(REPLACE(statement,'[',''),']',''),'.','_')+CONVERT(VARCHAR,t1.index_handle)+'E ON '+statement+'('+equality_columns+') '+ CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE( '+ISNULL(included_columns,'')+') ' ELSE '' END as [Equality] , 'CREATE INDEX idx_DBO_'+REPLACE(REPLACE(REPLACE(statement,'[',''),']',''),'.','_')+CONVERT(VARCHAR,t1.index_handle)+'I ON '+statement+'('+inequality_columns+') '+ CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE( '+ISNULL(included_columns,'')+') ' ELSE '' END as [InEquality] from sys.dm_db_missing_index_details t1 WITH (NOLOCK) inner join sys.dm_db_missing_index_groups t0 WITH (NOLOCK) on t1.index_handle=t0.index_handle INNER JOIN sys.dm_db_missing_index_group_stats t2 WITH (NOLOCK) ON t0.index_group_handle=t2.group_handle where database_id = Db_id() and user_seeks > 10 order by avg_user_impact desc, user_seeks desc, user_scans desc, last_user_seek desc
			

				

					

				

					
1
				
						
select db_name(t1.database_id) as database_name, t1.inequality_columns,t1.included_columns,t1.statement as table_name,(select max(rowcnt) from sysindexes where id=t1.object_id and status in (0, 2066) ) as [rowcount] , t2.user_seeks,t2.last_user_seek,t2.last_user_scan,t2.avg_total_user_cost,t2.avg_user_impact, 'CREATE INDEX idx_DBO_'+REPLACE(REPLACE(REPLACE(statement,'[',''),']',''),'.','_')+CONVERT(VARCHAR,t1.index_handle)+'E ON '+statement+'('+equality_columns+') '+ CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE( '+ISNULL(included_columns,'')+') ' ELSE '' END as [Equality] , 'CREATE INDEX idx_DBO_'+REPLACE(REPLACE(REPLACE(statement,'[',''),']',''),'.','_')+CONVERT(VARCHAR,t1.index_handle)+'I ON '+statement+'('+inequality_columns+') '+ CASE WHEN included_columns IS NOT NULL THEN 'INCLUDE( '+ISNULL(included_columns,'')+') ' ELSE '' END as [InEquality] from sys.dm_db_missing_index_details t1 WITH (NOLOCK) inner join sys.dm_db_missing_index_groups t0 WITH (NOLOCK) on t1.index_handle=t0.index_handle INNER JOIN sys.dm_db_missing_index_group_stats t2 WITH (NOLOCK) ON t0.index_group_handle=t2.group_handle where database_id = Db_id() and user_seeks > 10 order by avg_user_impact desc, user_seeks desc, user_scans desc, last_user_seek desc
					
				
			
		

The WHERE clause contains a filter that only shows the missing indexes for the current databases and only for indexes that are missing for more then 10 times. You can change that how ever you want.

Result

The result is a list of CREATE statements that you can copy and execute in your database. The names of the indexes are somehow prefabricated, you can adjust that formula.  

We especially pay attention to the data in the yellow marked area. For example If the amount of rows is very high and the user seeks is very low and the last user seek was a week ago, we wouldn't create that index of course. But if the rowcount was for example 200000 and the user seeks above 1000 and last user seek actual with current date then yes.

About us

Over the years, SQLTreeo gained precious know-how which we transformed into customer support, managed database services and database solutions. In order to express our knowledge and experiences, we tend to work with anything less than the best-trained staff and that is the key to success and the biggest asset of our company. Thus, all our database management services are controlled by high level skilled and Senior database administrators. We can help you in every aspect of SQL Server.