asked on

SQL Server Almost Duplicate Indexes

MS SQL Server 2008 R2

I am helping a friend with some database issues and one of the issues is duplicate indexes. Many of the tables have indexes like this...

PK_Index = ID (CLUSTERED)
IDX_1 = ID with City, State as included fields
IDX_2 = ID with City,State,Zip as included fields

I say you can delete IDX_1 since IDX_2 is the same with an added field in the included list. He says no, he needs to keep all three.

What is the best practice when you have indexes that are very similar but vary by the included fields?

Thanks!

Jim

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

You should be able to delete IDX_1 in most cases. If it is gone and you have a query that only uses ID with City, State it should be able to perform an index range scan of IDX_2 and still use an index.

I'm not sure the same query would perform any better using IDX_1 over IDX_2 but I'm sure there is a test case or two that might.

As a quick test, find the (ID, City, State) with the most values:
select ID with City, State,count(*) from some_table group by ID with City, State

Then perform a query using those values:
select * from some_table where ID=1 and city='some_city' and state='XX';

drop IDX_1 and perform the same query. See if the difference is noticeable.

Aneesh

>I'm not sure the same query would perform any better using IDX_1 over IDX_2 but I'm sure there is a test case or two that might.
there will be slight difference, IDX_2 will be larger as it contains one more column, because of that more logical reads when using this index. But there wont be any notable performance decreases

slightwv (䄆 Netminder)

>>there will be slight difference ...

Since the index exists, one has to assume there is at least one query where ALL columns are used. So dropping IDX_2 on a query that uses ID, City,State and Zip may suffer more than the extra block reads for the query that only uses ID, City and State.