Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following index created on a table in my database:

CREATE INDEX [idx_index1]
on [table1]
(col1, col2, col3)

The server is suggesting the following 'missing' index:

CREATE INDEX [idx_index2]
on [table1]
(col1, col2)
INCLUDE (col3, col4, col5, col6....)

It seems logical to me to amend the existing index definition to include the suggested columns, rather than creating a new index that needs to be maintained. A query that selects on col1 and col2 could use index1 just as effectively as index2. Am I correct or am I maybe missing something?

share|improve this question
add comment

3 Answers 3

up vote 11 down vote accepted

And so enters the art of performance tuning and indexing strategies...

It seems logical to me to amend the existing index definition to include the suggested columns

I'm going to take your quote and write a third index definition:

create index [idx_index3]
on [table1] (col1, col2, col3)
include (col4, col5, col6....);

That should be the CREATE INDEX statement that corresponds to your quoted statement.

That very well may be a prudent solution, but it depends. Here are a couple of examples when I say that it depends.

If you have a common workload that mostly consists of queries like this:

select col1, col2, col3
from table1
where col1 = 1
and col2 = 2
and col3 = 3;

Then your idx_index1 index would be solid. Perfectly narrow, it's an index that satisfies that query with no extraneous data in it (not taking into account the clustered index definition, if one at all).

But if you have workload that consists of queries mainly like the following:

select co11, col2, col3, col4, col5
from table1
where col1 = 1
and col2 = 2;

Then idx_index2 would be wise, as it is what's called a covering index preventing the need for a key lookup back to the clustered index (or a RID lookup back to the heap). That nonclustered index definition would solely encompass all of the data that query needs.

With your recommendation, it would be well suited for a query like the following:

select co11, col2, col3, col4, col5
from table1
where col1 = 1
and col2 = 2
and col3 = 3;

Your idx_index3 recommendation would be a covering index that satisfies the search criteria for the above query.

The point I'm trying to get at, is in an isolated question like this we can't answer this definitively. It all depends on what the common and frequent workload is. Of course you could always define all three of these indexes to handle each sample query type, but then comes into question the maintenance that'll be required to keep these indexes updated (think: INSERTs, UPDATEs, DELETEs). That's the overhead of indexes.

You need to dissect and evaluate the workload, and determine where the advantages will be best in place. If the first sample query is the most common by far being executed dozens of times a second, and there is a very infrequent query like the third sample query, then it wouldn't make sense to bloat the leaf level pages of the index with the INCLUDE nonkey columns. It all depends on your workload.

If you understand prudent indexing strategies, and you understand your common workload, then by applying both of those you will be able to come up with what is the best route to take.

share|improve this answer
    
I'm going to have to digest that for a while but it seems like a good answer. I assume it was a typo that the 'index3' that you defined has col3 as an equality column AND an included column ? –  paulH Nov 27 '13 at 18:43
    
Yep :-) Good catch. I've edited that out. –  Thomas Stringer Nov 27 '13 at 18:47
    
Not to mention that if the table only has cols 1-6 it's pretty silly to index 1 & 2 and include 3-5. –  Kenneth Fisher Nov 27 '13 at 22:56
1  
@KennethFisher - why would that be silly? It seems a reasonable enough thing to do if your database structure and your workload warrant it. E.g. if you have a query that selects columns 1-5 based on the values of columns 1 and 2, and maybe column 6 is an nvarchar(max) column that you don't want to bloat your index with. –  paulH Nov 27 '13 at 23:23
    
@paulH It is probably just my opinion but at the point you have added enough columns to the include that your index has 90+% of your columns in the table you have bloated your index to the point that the extra read to go to the table itself isn't all that important. Now there are certainly exceptions .. if cols 1-5 are all int's and col6 is a varchar(max) then I might do it. But in general I would look at those VERY carefully. –  Kenneth Fisher Nov 29 '13 at 14:22
add comment

You are indeed correct and have discovered why it's important for a DBA to always review the "suggestions" put forward by the missing index DMVs etc.

Consider that the suggestions offered by the missing index DMVs are put forward in isolation, meaning that SQL Server decided that an index of the recommended structure would benefit the query, irrespective of what other index structures may already exist.

share|improve this answer
add comment

A little more, on one of the implications of Thomas' answer:

He said:

Of course you could always define all three of these indexes to handle each sample query type, but then comes into question the maintenance that'll be required to keep these indexes updated (think: INSERTs, UPDATEs, DELETEs). That's the overhead of indexes.

So, another big question becomes: how often is the table updated?

Consider first an example of a table that is constantly updated, like for example, a retail ORDERS table reflecting website consumer activity... there, you want to be conscientious about having multiple indexes, because they increase the work done by constant updates, and therefore constantly affect the database's performance.

On the other hand, consider a table that is only updated as part of website setup -- the table being updated ONCE for most values, and values infrequently added -- there, update slowdowns are pretty much not a consideration. Multiple indexes could slowdown database index rebuilds & reorgs, but as long as they are fast enough, FEEL FREE: if multiple indices speeds up the reads, go for it.

A middle case could be a table that is normally only updated in a batch process overnight. There, update slowdowns from multiple indexes would not affect daytime performance -- they would only affect (1) the time taken, to run that nightly batch maintenance, (2) the performance of any concurrent processes, and (3) the time taken for database maintenance tasks like index reorganization. So, as long as processes in those 3 arenas are running fast enough for you... create the indexes that speed up queries.

HTH...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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