Index Drop for Partial Overlap?

All we need is an easy explanation of the problem, so here it is.

I’m working with a 50mil row table with the following:

  • Clustered Index keyed on [Col1]
  • Nonclustered Index keyed on [Col1], [Col2] (no includes)

It seems like the right call here would be to drop #2 and rebuild #1 keyed on [Col1], [Col2]. Sound right? Would the tuning logic be the same if the NCI still had [Col1] as its first key and then 7 other keys after that (rebuild the CX with 8 keys)?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As hinted at by Andrew Sayer, one thing to consider is how many other indexes do you have?…as all the fields of your clustered index will automatically be included on any nonclustered index for that table as well. This is how nonclustered indexes are able to facilitate a key lookup operation when additional fields not stored in that nonclustered index are required to satisfy the query. Therefore it is typically recommended to be mindful of not overly-bloating your clustered index.

With that in mind, to your question regarding "It seems like the right call here would be to drop #2 and rebuild #1 keyed on [Col1], [Col2]", yes that’s probably going to be a fine idea most times (depending on the data type and size of Col2) since the nonclustered index on (Col1, Col2) is a covering index of Col1.

But to your second question "Would the tuning logic be the same if the NCI still had [Col1] as its first key and then 7 other keys after that?", while the situation is the same with the nonclustered index covering the same field as the clustering index, 8 fields on the clustered index could be potentially heavy (again depending on their data types and size) for a clustered index, especially if you maintain a good amount of other nonclustered indexes on that same table as well. You very well may end up consuming more space than you’re saving by doing so.

In either situation, it’s going to depend on the data types and size of the columns, the number of indexes on the table, and the use cases you have for the table with how it’s queried. Given that, the best way to find out for each case is to test your use cases with the index architected both ways, and compare the performance outcomes.

A general rule of thumb I follow for all indexing is the five by five rule. I like to not have more than five indexes on any given entity, and no more than five columns defined per index. While this is not a hard and fast rule, and there are definitely exceptions, it’s just a general idea I personally like to follow.

Method 2

If [Col1] is unique or is the primary key, leave the clustered index as is.

You’ll need to do some investigating to find out if that NCI is providing any benefit. If it’s the only NCI it might get used, even if it’s not really optimal, but you won’t know without doing some research.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply