To find out the existing indexes, Give the command sp_helpindex
Once a user query is run engine will look for relevant indexes .
If we query the DMV sys.dm_db_missing_index_details , we will be able to get the details of the missing index. This query will return equality_columns, inequality_columns, included_columns and statement.
equality_columns – This will be the columns used in the where condition with an equal operator
inequality_columns – This will give the columns used in the where condition with a not equal to (<>)operator. Also this will include all the columns used with a non equal to operator (eg. <)
Included_columns – This is the columns that needed to put in ‘include’ part of the index, That would improve query performance very much
Statement is the table in which query was executed.
This is a DMV that will give another set of information – sys.dm_db_missing_index_group_stats
The query will return unique_compiles, user_seeks, last_user_seek, avg_total_user_cost and avg_user_impact
unique_compiles- No of compiles taken for the user seek, 1 means only one compilation after the sql server started even if there was many number of user queries
user_seeks – No of user queries after SQL Server start
last_user_seek – Last query date and time
avg_total_user_cost – self explanatory
avg_user_impact – Teh percentage value will say, the amount of improvement you can get if we create index on it
sys.dm_db_missing_index_groups is another DMV which is explained below
This will give index_group_handle and index_handle. Usage is explained in an example later
This DMV will give the column name and column usage(based on operator used)
SAMPLE QUERY FOR FINDING INDEX REQUIREMENT
SELECT avg_user_impact AS avg_imprvmnt_perc, avg_total_user_cost AS avg_cost_of_query_before_index, ‘CREATE INDEX ix_’ + [statement] + ISNULL(equality_columns, ‘_’) + ISNULL(inequality_columns, ‘_’) + ‘ ON ‘ + [statement] + ‘ (‘ + ISNULL(equality_columns, ‘ ‘) + ISNULL(inequality_columns, ‘ ‘) + ‘)’ + ISNULL(‘ INCLUDE (‘ + included_columns + ‘)’, ”) AS missing_index_cmd FROM sys.dm_db_missing_index_details a INNER JOIN sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c ON b.index_group_handle
LIMITATIONS OF DMV
A DMV can store information from a maximum of 500 missing indexes.
Unable to provide recommendations for clustered, indexed views and partitioning.
Once the SQL Server is restarted all the information related to missing indexes is lost. To keep the information for later use, the DBA needs to backup all the data available within all the missing index DMV prior to the restart of SQL Server
– Ref :SQL Server Performance