Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

index advantage query Expand / Collapse
Posted Wednesday, July 3, 2013 12:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 17, 2016 12:58 AM
Points: 278, Visits: 1,798
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 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;
Post #1469854
Posted Wednesday, July 3, 2013 1:18 AM



Group: General Forum Members
Last Login: Monday, December 5, 2016 9:41 PM
Points: 1,961, Visits: 2,216
Equality and inequality columns are explained in the Microsoft documentation for sys.dm_db_missing_index_details;k%28SQL11.SWB.TSQLRESULTS.F1%29;k%28SQL11.SWB.TSQLQUERY.F1%29;k%28MISCELLANEOUSFILESPROJECT%29;k%28DevLang-TSQL%29&rd=true



Comma-separated list of columns that contribute to equality predicates of the form:

table.column =constant_value



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.
Post #1469871
Posted Wednesday, July 3, 2013 1:27 AM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 5:18 AM
Points: 45,619, Visits: 44,147
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

Post #1469876
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse