Finding Missing indexes

months ago by Danny Riebeek

Situation

SQL Server registers every missing index. Here is how you retrieve that information from the missing index DMV's
select '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], 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 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.

REPLIES:

Chris Harshman 2018-07-09 19:31:20

You may want to add the typical disclaimers for looking at the Missing Index DMVs, such as comparing the suggested indexes to already existing indexes because it will recommend duplicates and column order of the suggested index key is fairly random instead of a useful order. Here's a Microsoft article on the problems: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms345485(v=sql.105)

LEAVE A REPLY