Post reply

Index tuning

  • November 11, 2013 at 1:05 pm

    #282246

    Hey all,

    I am doing an evaluation against all indices on my database, and I have a question regarding index maintenance. I could not find the answer anywhere else, so i come here and hope you guys could help me out.

    Here is my scenario:

    1. My database is very active.

    2. I have a table: 'Table1'. Table1 has 5 columns (col1, col2, col3, col4, col5).

    3. Table1 has 1 index: 'Index1'. Index1 is made of (col1, col2, col3). Col1 is the leading column.

    4. Index1 was created on Table1, and all queries against this table had col1, col2, col3 in the 'Where' clause and only requested data in col1, col2, col3. DMV view also showed that this index has 0 lookup. Life was good.

    5. 1 year later, all queries against this table had only col1, col2 in the 'Where' clause and only requested data in col1, col2. DMV view also showed that this index has 0 lookup because Index1 satisfies all request against Table1. Even though Index1 satisfies all request, col3 is a dead weight in the index.

    My questions is:

    Which DMV shows that col3 in Index1 is not being used? More specifically, how do I find the usage of each index column?

  • November 11, 2013 at 1:25 pm

    #1665565

    A nonclustered index will always have 0 lookups. Lookups only occur to the clustered index. DMVs track the index usage, nothing tracks usage to the column level.

    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

  • November 11, 2013 at 1:28 pm

    #1665567

    Is there any way to pinpoint that col3 is not used,so that i could recreate Index1 with col1, col2?

  • November 11, 2013 at 1:37 pm

    #1665570

    You could capture and analyse every single query that executes against that table and test them all out with the narrower index.

    Why are you worried about a single column?

    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

  • November 11, 2013 at 1:57 pm

    #1665578

    1 column is just an example. What if multiple columns of the index are not being use?

  • November 11, 2013 at 1:59 pm

    #1665579

    namdinh4 (11/11/2013)

    Hey all,

    I am doing an evaluation against all indices on my database, and I have a question regarding index maintenance. I could not find the answer anywhere else, so i come here and hope you guys could help me out.

    Here is my scenario:

    1. My database is very active.

    2. I have a table: 'Table1'. Table1 has 5 columns (col1, col2, col3, col4, col5).

    3. Table1 has 1 index: 'Index1'. Index1 is made of (col1, col2, col3). Col1 is the leading column.

    4. Index1 was created on Table1, and all queries against this table had col1, col2, col3 in the 'Where' clause and only requested data in col1, col2, col3. DMV view also showed that this index has 0 lookup. Life was good.

    5. 1 year later, all queries against this table had only col1, col2 in the 'Where' clause and only requested data in col1, col2. DMV view also showed that this index has 0 lookup because Index1 satisfies all request against Table1. Even though Index1 satisfies all request, col3 is a dead weight in the index.

    My questions is:

    Which DMV shows that col3 in Index1 is not being used? More specifically, how do I find the usage of each index column?

    Homework, test, or interview question?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 11, 2013 at 2:08 pm

    #1665581

    Jeff Moden (11/11/2013)

    namdinh4 (11/11/2013)

    Hey all,

    I am doing an evaluation against all indices on my database, and I have a question regarding index maintenance. I could not find the answer anywhere else, so i come here and hope you guys could help me out.

    Here is my scenario:

    1. My database is very active.

    2. I have a table: 'Table1'. Table1 has 5 columns (col1, col2, col3, col4, col5).

    3. Table1 has 1 index: 'Index1'. Index1 is made of (col1, col2, col3). Col1 is the leading column.

    4. Index1 was created on Table1, and all queries against this table had col1, col2, col3 in the 'Where' clause and only requested data in col1, col2, col3. DMV view also showed that this index has 0 lookup. Life was good.

    5. 1 year later, all queries against this table had only col1, col2 in the 'Where' clause and only requested data in col1, col2. DMV view also showed that this index has 0 lookup because Index1 satisfies all request against Table1. Even though Index1 satisfies all request, col3 is a dead weight in the index.

    My questions is:

    Which DMV shows that col3 in Index1 is not being used? More specifically, how do I find the usage of each index column?

    Homework, test, or interview question?

    This is for the production environment. There are a couple wide indices on my database, and I would like to know which column of the index being used and how many time it is being referenced.

  • November 11, 2013 at 2:15 pm

    #1665584

    namdinh4 (11/11/2013)

    I would like to know which column of the index being used and how many time it is being referenced.

    Index usage is not tracked to the column level.

    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

  • November 11, 2013 at 2:17 pm

    #1665587

    namdinh4 (11/11/2013)

    What if multiple columns of the index are not being use?

    Same answer.

    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

  • November 11, 2013 at 2:19 pm

    #1665588

    Is there another way to assess wide indices?

  • November 11, 2013 at 4:00 pm

    #1665604

    namdinh4 (11/11/2013)

    Is there another way to assess wide indices?

    There's no guarantee but you could create the less-wide indexes, and see which indexes are used the most over time (provided that parameter sniffing doesn't prevent such a thing). Just don't forget that you've created the extra indexes.

    But, I'm with Gail. One extra column isn't going to kill you (it hasn't so far) and removing the extra column may cause the index to NOT be used for certain things that it really should be used for.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 12, 2013 at 7:34 am

    #1665729

    Thanks for the input, guys.

  • November 12, 2013 at 10:36 am

    #1665811

    Jeff Moden (11/11/2013)

    namdinh4 (11/11/2013)

    Is there another way to assess wide indices?

    There's no guarantee but you could create the less-wide indexes, and see which indexes are used the most over time (provided that parameter sniffing doesn't prevent such a thing). Just don't forget that you've created the extra indexes.

    But, I'm with Gail. One extra column isn't going to kill you (it hasn't so far) and removing the extra column may cause the index to NOT be used for certain things that it really should be used for.

    namdinh4, if you decide to create some new, narrower indexes to test, remember that stored procedures will have to be recompiled before they can use the new indexes - adding a new index from which the procedure might benefit does not cause automatic recompilation because the table itself has not changed.

    Jason Wolfkill

Viewing 13 posts - 1 through 12 (of 12 total)

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