Indexing Question...

  • Are these the basic rules to follow when building indexes:

    Field in the join clause should be the first item in the index,

    followed by any field that shows up in the where clause,

    include fields should be everything else that is in the "select" portion that isn't already included.

    I realize this is very generic, and the include part probably wouldn't be correct for most indexes, but for my example, only one other field in the table shows up in the select statement, so I thought to add it in the include statement...

    Does this sound logical?

    Thanks

  • Order of the columns in the key may or may not break down the way you're talking about. It depends on what the optimizer does. As a starting point, yeah, it sounds good. You should get a covering index from what you describe.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • However you wouldn't build indexes for every query. I would think you would start with the columns in PKs and FKs first, if you haven't explicitly declared those or set indexes. Those often cover the join conditions, then look at common WHERE clauses amongst most of your queries.

  • Yep, there are already a few indexes on this table that cover the PK and other "ID's" in the table, but one specific SP always comes back via Quest Perfomance Analysis as the "heavy hitter" that I would like to optimize.

  • http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    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
  • gregory.anderson (7/30/2009)


    Yep, there are already a few indexes on this table that cover the PK and other "ID's" in the table, but one specific SP always comes back via Quest Perfomance Analysis as the "heavy hitter" that I would like to optimize.

    Post it here, we can give you index recommendations and reasons why we're making those recommendations

    Have a look at this to see the best way to post a performance question. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    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
  • Here is the proc:

    SELECTCSH.Non_Cash_Act_IDAS [NonCashActivityID],

    Pmt.Pmt_IDAS [PmtID],

    Pmt.Statement_IDAS [StatementID],

    CSH.AmountAS [Amount]

    FROMNon_Cash_Act CSH

    INNER JOINCD_Session SES ON SES.CD_Session_ID = CSH.CD_Session_ID

    INNER JOINPmt ON CSH.Pmt_ID = Pmt.Pmt_ID

    INNER JOINStatement ST ON ST.Statement_ID = Pmt.Statement_ID

    WHERESES.Cash_Drawer_ID = @Cash_Drawer_ID

    ANDST.StatementVoidIndicator = @False

    ANDCSH.Active = @True

    Want the index on Non_Cash_Act. Primary keys for tables are [TableName_ID] so it's not really utilized on the Non_Cash_Act table

    As I stated above, I was going to write something like this:

    Create NonClusteredIndex [IndexName] On Non_Cash_Act

    (

    [CD_Session_ID] Asc,

    [Pmt_ID] Asc,

    [Active] Asc

    ) Include ([Amount])

  • could you upload the actual execution plan for us as well?

    edit:

    Please also give us the definitions of the current indexes on those tables.

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

Viewing 8 posts - 1 through 7 (of 7 total)

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