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

I'm not sure what the inequality column means for the index Expand / Collapse
Author
Message
Posted Tuesday, October 14, 2008 11:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Jesse,

I ran the SQL - I have one entry with a score of 119,567. It is displaying equality_columns of StrategyCode and HistoryDate on one table - no inequality columns - so I'd create an index on StrategyCode, HistoryDate?

I have another table where the sole entry is under inequality_columns - RRCode - so I'd make an index on RRCode?

I have a third table where I have TransactionAction, TransactionEntityType under equality_columns and ProcessStatusFlag under inequality_columns.

Is this telling me to make 2 indexes - or 1 index of TransactionAction, TransactionEntityType and ProcessStatusFlag?

TIA,
Doug
Post #585670
Posted Monday, November 10, 2008 8:42 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
Yes for the first two:

StrategyCode, HistoryDate
RRCode

3rd:

one index on TransactionAction, TrasactionEntityType,PrcessStatusFlag

Don't neglect the includes

The missing index views don't account for clustered index inheritance. Clustered index seek columns are appended to the end as seeks to every non-unique nonclustered index, and as includes to every unique nonclustered index.

SQL Server will never seek any further keys on and index beyond the one it has to seek on an inequality operation. So put all the equalities first, and then the inequalities. Preferabbly find the one most-used (unless some other queries seek it as an equality) and relegate the rest to includes since they'll never get used.



Post #599922
Posted Monday, November 10, 2008 8:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
Jesse,

I lost the link to this topic - do you have the URL of your script?

Thanx,
Doug
Post #599938
Posted Tuesday, November 11, 2008 10:21 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, October 21, 2013 4:37 PM
Points: 701, Visits: 211
http://www.sqlservercentral.com/scripts/Index+Management/63937/

There seems to be one other topic that has the same problem also - What to do with Multiple Similar Index Reccommendations



Post #600804
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse