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 two indexes on a table that will contain roughly 10-20 million records at a given moment.

Index 1

CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC] ON [dbo].[LogWindow]
(
    [AccountNumber] ASC,
    [UTC] ASC
)
INCLUDE (   [Site],
    [Application],
    [Version],
    [Machine],
    [Context],
    [Severity],
    [Message],
    [Details]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Index 2

CREATE NONCLUSTERED INDEX [IX_NC_AccountNumber_UTC_Site_Context_Machine] ON [dbo].[LogWindow]
(
    [AccountNumber] ASC,
    [UTC] ASC,
    [Site] ASC,
    [Context] ASC,
    [Machine] ASC
)
INCLUDE (   [Application],
    [Version],
    [Severity],
    [Message],
    [Details],
    [Type]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The reason why I am currently using two indexes is because I am issuing two types of queries. The first query only filters records based upon AccountNumber and UTC. The second query filters records based upon those same columns, plus the additional Site, Context and Machine columns.

The second index is very similar to the first; it's just covering less columns because now they are part of the key indexed columns.

Does it make sense for me to have the first index when the second one is so similar with additional key columns? Will SQL Server determine there is a benefit to using the first index over the second?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

In this case, yes, they appear to be redundant. The second index will satisfy any queries that would benefit from the first one. Also, both indexes appear to contain the same columns, so rows within the index leaf pages should be about the same size in either. Your non-leaf index pages will be a bit larger with more key columns, but that probably won't amount to a significant size increase, unless the index rows are quite large and only fit a few per page.

It's important to consider the size of the index rows when deciding if an index is truly redundant. If you have an index on a couple of int columns, a decimal, and a datetime, the rows will be much smaller than if you have another index on those same columns plus a character column that averages a couple hundred bytes. Any analytical queries that would aggregate a large number of rows would benefit from the index that has rows much more densely packed on the data pages, even though either index could technically satisfy the query.

So, make sure you consider the difference in average row size, and whether or not that has any performance implications before you go dropping any redundant-looking indexes.

share|improve this answer
    
Great answer, thank you! You mention that aggregate queries would benefit from the index that has the rows more densely packed. Why is that true only for aggregate queries? If I am just doing a simply pull of the rows, wouldn't that index also be beneficial in cases that like? –  Ek0nomik Oct 4 '13 at 18:03
    
Aggregate queries tend to look at a lot of rows. Really, any query that is fetching a lot of rows should benefit from a more densely packed index (assuming it's still covering). If you're just doing a simple seek and pulling a few dozen rows, you shouldn't see a very big performance difference. This is one of those tricky parts of index tuning that can require some careful analysis, since the answers won't all be right on the surface. For your specific index, though, it looks pretty clear-cut. –  db2 Oct 4 '13 at 19:08
    
I actually will be pulling much more than a few dozen rows; likely in the tens of thousands. The performance looks completely fine right now though. The queries are finishing in under a second so I don't have any complaints. I was initially asking the question because my loading of the table was taking quite a bit of time and I was hoping to make that a little quicker. Thanks again for the good answer. –  Ek0nomik Oct 4 '13 at 19:15
add comment

They are redundant. Second index should be usable by any query that would be using the first one.

share|improve this answer
    
By having a column be part of the key index column(s), the data is just as easily accessible (or more) by SQL Server if it were part of the included column? –  Ek0nomik Oct 4 '13 at 15:25
2  
The key index columns are good for searching starting from left to right. At the point where the column is not being searched, everything else is (for all practical puroses) an included column. E.g. Your search criteria using Index 2 is AccountNumber, UTC, Machine, Application would mean a search for the AccountNumber and UTC, but then Machine and Application are treated as included columns. –  RLF Oct 4 '13 at 15:38
    
@RLF - Thank you for the reply. I'll remove my first index which will help my INSERT's run a little faster! –  Ek0nomik Oct 4 '13 at 16:02
add comment

As other readers have already answered your original question, Yes, they are redundant.

What else I wanted to add is, reminding you that column you choose for Non-Cluster index is as important as when you choose for Clustered Index. Unlike Clustered Index, initial Set of Data set to be search for By index is solely depends on first columns of you NON-Clustered index.

So in your case [AccountNumber] is the first column.

What is the Data-type of that column?

What is the cardinality of that column in table ?

What type of query this will be?

Is it involves JOIN or just pure single table query with some Where filter ? If your query includes JOIN than what index is on another table is also important.

If you haven’t get your code to expected level provide more information about the query and tale definition to suggest possible improvements.

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.