Consolidating Indexes in SQL Server

by Dec 1, 2014

  • #2052

    Avatar of SandiW
    SandiW

    Keymaster

    Webcast topic for April 22, 2015 is “Consolidating Indexes in SQL Server”. Our speaker, Kendra Little, Microsoft Certified Master,
    talks about 3 different index consolidation scenarios and how you can measure the options pros and cons.
    Ask questions and chat further after the webcast on this topic.


    ? Sandi Warren

    Idera Staff / Director, Product Marketing

    #2077

    Avatar of SandiW
    SandiW

    Keymaster

    A few questions came in to get the ball rolling.

    Q: What is the limit on the size of keys in a clustered index?
    A: 900 bytes

    Q: Can we create a clustered index on a non-primary key column?
    A: Yes


    ? Sandi Warren

    Idera Staff / Director, Product Marketing

    #2078

    Avatar of Kendra Little
    Kendra Little

    Participant

    Hi folks! I’d love to know what you think of the webcast and the triumphs and challenges you’ve had consolidating indexes.

    #2079

    Avatar of Kevin Kelso
    Kevin Kelso

    Participant

    Kendra, I enjoyed the webcast. It helped me clear up a few questions I’ve had about index consolidation.

    One of my main challenges in consolidating queries is similar indexes where the key columns are the same (or mostly the same) but in different orders. Are there any tips on what to look for with these types of indexes?

    #2080

    Avatar of Kendra Little
    Kendra Little

    Participant

    Ooo, great question, Kevin!

    It’s definitely harder to consolidate indexes with key columns in different orders, because the read/write metrics in sys.dm_db_index_usage_stats don’t necessarily tell me all of the information that I might need to know.

    Let’s say I have an index with key (a,b,c) and sys.dm_db_index_usage_stats shows it has 1 million seeks. Are those seeks a query that has “where a=1 and b=2 and c=3″? If so, then changing the order and making the index (b, c, a) might be somewhat uncontroversial. (It could certainly change things, but the index is probably still pretty useful.)

    But could the seeks be from a query that’s ONLY using “where a=1″? If that’s the case then changing the order could dramatically reduce the usefulness of the index for that query, and it might even be the only thing currently using the index.

    Because of this, I approach key column reordering with caution. If the usage counts are pretty low, then in some environments it can be OK to make a gamble and test some indexing changes based on assumptions. In other environments, it’s worth the extra time to snoop around and try to identify if the most frequent queries running against the SQL Server are using the indexes. That means using a monitoring tool to look at top queries, or slicing and dicing the execution plan cache to look at queries (the names of the indexes they’re using are captured in the plans).

    But yes, it’s definitely a tough problem to crack sometimes!

    #2081

    Avatar of Kevin Kelso
    Kevin Kelso

    Participant

    Thanks for the reply Kendra. That’s been my main fear when looking to combine similar indexes. I’m glad to know I’m not alone and that I wasn’t missing some big secret on how to figure that out.

    #2089

    Avatar of Kevin Fries
    Kevin Fries

    Participant

    It’s another Kevin with a question about the above.
    Assuming an index with a key of a, b and c. Barring some goofy datatype, I don’t understand why it would make a difference to the query how either the field order in the index or the where clause would make a difference if the key is fully specified and especially if it’s unique.
    From the above:
    “Let’s say I have an index with key (a,b,c) and sys.dm_db_index_usage_stats shows it has 1 million seeks. Are those seeks a query that has “where a=1 and b=2 and c=3″? If so, then changing the order and making the index (b, c, a) might be somewhat uncontroversial. (It could certainly change things, but the index is probably still pretty useful.)”

    Or am I reading too much into this? I’m aware that when using predicates that are something similar to
    ‘where c like ‘trash%’ and a = 1 and b = 2 ‘ would probably be better written as ‘a = 1 and b = 2 and c like ‘%trash%’ and something like a = like ‘%trash%’ and b = 2 and c =3′ would probably benefit more if the order of the index was b, c and then a or c, b and then a.
    PS: Thanks again to your group for the class in Philly.

    #2090

    Avatar of Kendra Little
    Kendra Little

    Participant

    Kevin Fries! Hello again!

    If we change an index with key (a,b,c) to (b,c,a), then we’ve changed the leading column on the multi-column statistic related to that index. The leading column in the index is what gets the histogram on that index. So depending on how that histogram is being used, it *might* change estimates / query plans that you’re getting.

    Kendra