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'm reviewing our database and noticed that on a particular table we have the following indexes:

Table:

Col1    INT IDENTITY(1,1) Primary Key
Col2    INT
...
about 15 more columns
....
ColN   VARCHAR(50)
....
another 10 more columns

In addition to the standard primary key clustered index on Col1, we also have the following index:

create nonclustered index [iTable-Col1] ON [dbo].[Table1]
(
    Col1
)
include ( ColN )

We regularly search by Col1 and want to only retrieve ColN. This index does get used as it is the smallest index that contains all of the data required to satisfy the query.

My question is, does this index add any benefit to SQL Server? Would we be better off dropping it and just doing a search on the clustered index?

My only thought is that this index is much smaller than the clustered index (25mb vs 300mb) which may make searching and/or caching faster.

Server is SQL Server 2012 if that makes any difference.

share|improve this question
1  
Your question includes the answer: ("My only thought is that this index is much smaller than the clustered index (25mb vs 300mb) which may make searching and/or caching faster.") –  ypercube May 7 at 8:28
    
@JonSeigel can you explain further? Why is it useful for scans but not seeks? –  Greg May 7 at 21:00
    
Thanks @JonSeigel. If you wanted to add that as an answer I'd accept it. –  Greg May 8 at 22:49

2 Answers 2

up vote 4 down vote accepted

Most of the time, this type of index is only useful for index scans against those columns.

The index tree structure above the leaf level will usually be very similar in terms of tree levels, so if that's the case, there's little or no performance difference in navigating the tree for seek operations (that don't also scan). If you want to learn the basics of index internals, I have a video here that will help you.

Anyways, back to the scans. The reasons why this index would be useful are much less about the index itself, and more about why not to scan the clustered index (or keep those data pages in memory). If the other columns make the table relatively wide (based on the sizes you gave, I would say they do, although it's unclear how large this table is in the context of the entire database), the narrow index may be of benefit in some cases. Frequent scans (or seeks on many different key values) will tend to keep the entire index in memory, so the larger the index, the less memory space there will be for other objects, and of course this will incur more I/O if the buffer pool is cold.

Although it's somewhat of a rare use case (I've done it once or maybe twice that I can remember), the biggest benefit of this type of index is it will greatly help queries that want to use a merge join to join this table. (Note: optimize the query first if an index scan isn't appropriate!) In the query plan, a scan of the clustered index, or a scan and sort of a nonclustered index before a merge join can give an indication of when this might be a good idea. But again, this is rare. The query should be executed frequently enough, or the base table expensive enough to keep stuck in memory (i.e., you don't need it there all the time), to justify the extra storage and maintenance for the index.

If you're unsure how this index is being used right now, check the DMV sys.dm_db_index_usage_stats. Bear in mind that the counts are reset on server restart, so if you're considering dropping the index, make sure it isn't useful during the entire business cycle.

share|improve this answer

Let SQL Server answer your question, if the index is useful the Query Optimizer will use it, otherwise it won't.

share|improve this answer
    
SQL will always use the smallest index that satisfies a query. If Index 1 and Index 2 both satisfy the query but index one is smaller then it will use that. This doesn't necessarily mean we should have both? –  Greg May 7 at 20:55

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.