could anny one please explain me the meaning of equality_columns, inequality_columns, included_columns,
unique_compiles, user_seeks,avg_total_user_cost, avg_user_impact i the below...in which column should i use the index
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek,
mid.statement as 'Database.Schema.Table',
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
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 index_advantage DESC;
Equality and inequality columns are explained in the Microsoft documentation for sys.dm_db_missing_index_details
Comma-separated list of columns that contribute to equality predicates of the form:
Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form:
table.column > constant_value
Any comparison operator other than "=" expresses inequality.
Using Missing Index Information in CREATE INDEX Statements
To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement, equality columns should be put before the inequality columns, and together they should make the key of the index. Included columns should be added to the CREATE INDEX statement using the INCLUDE clause. To determine an effective order for the equality columns, order them based on their selectivity: list the most selective columns first (leftmost in the column list).
So you should create the index based on equality columns, then inequality (if any) and add an INCLUDE statement for columns that can be returned but do not participate in the seek operation.
You should have a look at Brent Ozar's implementation of the same thing, but it generates the T-SQL based on the a similar query to yours.
Don't create missing indexes off the missing index DMV unless you've tested and ensured they really are useful.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)