-
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 WarrenIdera Staff / Director, Product Marketing
KeymasterA 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 bytesQ: Can we create a clustered index on a non-primary key column?
A: Yes
? Sandi WarrenIdera Staff / Director, Product Marketing
ParticipantHi folks! I’d love to know what you think of the webcast and the triumphs and challenges you’ve had consolidating indexes.
ParticipantKendra, 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?
ParticipantOoo, 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!
ParticipantThanks 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.
ParticipantIt’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.ParticipantKevin 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