Sql-server – Are these indexes redundant

indexsql server

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?

Best Answer

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.

Related Solutions

MySQL – Find and Remove Redundant Indexes

You can use pt-duplicate-key-checker from Percona Toolkit. You can also use the common_schema by Shlomi Noach.

SQL Server – How to Determine if Indexes Are Redundant

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;

enter image description here

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:

enter image description here

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:

enter image description here

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:

enter image description here

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:

enter image description here

Best Answer

Related Solutions

MySQL – Find and Remove Redundant Indexes

SQL Server – How to Determine if Indexes Are Redundant

Related Question