Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

index advantage query Expand / Collapse
Author
Message
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: Sunday, July 20, 2014 10:15 PM
Points: 265, Visits: 1,713
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;
Post #1469854
Posted Wednesday, July 3, 2013 1:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 1,151, Visits: 1,588
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/
Post #1469871
Posted Wednesday, July 3, 2013 1:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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