Determining if indexes are redundant

Question

I have a table with the following structure:

Id (int identity PK)
FooId (int FK)
BarId (int FK)
QuxId (int FK)
Other fields

The following indexes have been defined:

PK (Id), clustered
Index1 (FooId)
Index2 (FooId, BarId)
Index3 (FooId, BarId, Id, QuxId)

Is it redundant to keep both Index1 and Index2?

Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

Would it make more sense to have just one index, (FooId, BarId, QuxId)? Would there be any benefit in including Id?

(as a sidenote, I'm a developer, not a DBA)

Answer 1

Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

It depends, and the reason behind that is because the nonclustered index leaf pages contain the clustered index key (Id). Take a look at the following example:

use TestDB;
go

create table FooBar
(
    Id int identity(1, 1) not null
        constraint PK_FooBar_Id primary key clustered,
    FooId int not null,
    BarId int not null,
    QuxId int not null
);
go

create index Index1
on FooBar(FooId);
go

create index Index2
on FooBar(FooId, BarId);
go

create index Index3
on FooBar(FooId, BarId, QuxId);
go

insert into FooBar(FooId, BarId, QuxId)
values
    (1, 2, 3),
    (2, 3, 4),
    (4, 5, 6),
    (5, 6, 7),
    (6, 7, 8),
    (7, 8, 9);
go


select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar
where FooId = 4;

By capturing the execution plan for the last query (the SELECT), you can see that it is an index seek on [FooBar].[Index3], and notice my CREATE INDEX doesn't include the Id column.

Martin brings up a good point in his comment (see below for quote):

But specifying Id in a particular position in the index rather than just accepting the defaults can avoid a sort if the requirement is to ORDER BY that column order. Also allows a straight forward lookup on FooId, BarId, Id which an index on FooId, BarId, QuxId, Id wouldn't do (whether or not Id is included in the NCI key or leaf depends if the index is declared as unique or not)

Here is what he's talking about. With a query like this (forced index table hint to show the Sort operation):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with (index(Index3))
where FooId = 4
order by Id;

This would generate the below plan:

But by changing the structure of the index, like so:

create index Index3
on FooBar(FooId, Id, BarId, QuxId)
with (drop_existing = on);
go

And re-executing the above query:

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar -- with (index(Index3))
where FooId = 4
order by Id;

You now don't have the expensive Sort operation:

Now take a look at what happens if your query does in fact look like the following (again, table hint there just to force Index3):

select
    Id,
    FooId,
    BarId,
    QuxId
from FooBar with(index(Index3))
where FooId = 4
and BarId = 5
order by Id;

You will again have that Sort operation. Now if your index looked like the following:

create index Index3
on FooBar(FooId, BarId, Id, QuxId)
with(drop_existing = on);
go

By having Id as the third key column in the index structure, you will see the benefit of having it already ordered by Id when the Seek Predicates are on FooId and BarId. Here is proof with that altered index:

To get a visual of that, let's look at the leaf page of Index3. You can do this with the following:

dbcc ind('TestDB', 'FooBar', -1);
go
-- Index3 (index_id = 4) leaf page PID = 288
dbcc traceon(3604);
go
dbcc page('TestDB', 1, 288, 3);
go

You will get similar output like the following:

Answer 2

Question2: Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

This is your index, now:

Index3 (FooId, BarId, Id, QuxId)

Because Id is unique (as it is the primary key), there can be no two rows with same Id and different QuxId. Therefore, your index is equivalent to this one (which will use slightly less space):

Index3b (FooId, BarId, Id) INCLUDES (QuxId)

You can even remove the Id column and have the:

Index3c (FooId, BarId) INCLUDES (QuxId)

An query like the following can use equally well Index 3, 3b or 3c:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 ;

If however you have a query with a range scan or a sorting on QuxID:

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
  AND QuxID BETWEEN 123 AND 314 ;

SELECT QuxId
FROM table
WHERE FooId = 5
  AND BarId = 7 
ORDER BY QuxID ;

the above (3, 3b or 3c) indexes can be used but not as efficiently as this one that has the QuxID values in the needed order:

Index3d (FooId, BarId, QuxId)

In short, the indexes (FooId, BarId, Id, QuxId) and (FooId, BarId, QuxId) can be used for many queries equally well but they are not exactly equivalent and you may have queries that will use more efficiently the one but not the other.

Answer 3

There is no point including leading column of clustered index into non-clustered index; non-clustered indexes contain clustered key unless your table is heap.

Having 3 different indexes with the same leading column[s] is redundant - even though the engine in some cases may decide use to different indexes (say you are are querying WHERE FooId = 1, so Index1 may be the best choice because the index itself is smaller then Index2 or Index3 in terms of IO operations , but I doubt it can be a huge benefit compared to overhead needed to maintain 3 indexes). You need to decide what are the most frequent queries and create an index for their max benefit. Also, in SQLServer you can INCLUDE column[s] in the index (http://msdn.microsoft.com/en-us/library/ms190806.aspx) , thus reducing overhead in maintaining non-leading columns.