0

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)?

3
  • 1
    Is the clustered index unique? Do/will you have indexes on the other columns? Jul 18 at 5:51
  • 8 keys is quite wide, why would you need so many key columns? Perhaps you should have INCLUDE coumns instead Jul 18 at 15:51
  • Clustered index is unique Jul 19 at 5:51
0

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.

2
  • Thanks JD, very helpful. Jul 19 at 5:50
  • @AccidentalDBA_CO Sure thing, np!
    – J.D.
    Jul 19 at 11:42
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.