index advantage query

Question

index advantage query

IT researcher

SSCertifiable

Points: 7567
More actions
July 3, 2013 at 12:14 am

#274638

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;

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

Equality and inequality columns are explained in the Microsoft documentation for sys.dm_db_missing_index_details

http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k%28SYS.DM_DB_MISSING_INDEX_DETAILS_TSQL%29;k%28SQL11.SWB.TSQLRESULTS.F1%29;k%28SQL11.SWB.TSQLQUERY.F1%29;k%28MISCELLANEOUSFILESPROJECT%29;k%28DevLang-TSQL%29&rd=true

equality_columns
nvarchar(4000)
Comma-separated list of columns that contribute to equality predicates of the form:
table.column =constant_value
inequality_columns
nvarchar(4000)
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.

http://www.brentozar.com/blitzindex/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Don't create missing indexes off the missing index DMV unless you've tested and ensured they really are useful.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass