Consolidating Indexes in SQL Server

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

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!

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.