How to eliminate 'duplicate' index? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to eliminate 'duplicate' index?

Author

Topic

erichan
Starting Member

3 Posts

Posted - 2009-07-16 : 05:31:26

I have a problem when apply index to sql server tables.
Say I have three tables tbA, tbB and tbC.
tbA has some columns: aId, a1, a2, ...
tbB has columns: bId, aId, b1, b2, ...
tbC has columns: cId, aId, c1, c2, ...

I am building a searching tool on these tables, where query conditions are based on creteria, thus dynamic. The query could be

select tbA.*, tbB.*, tbC.* from tbA
inner join tbB on tbA.aId=tbB.aId
inner join tbC on tbA.aId=tbC.aId
where tbA.a1 = 100 and tbC.c2=200

or may be

select tbA.*, tbB.* from tbA
inner join tbB on tbA.aId=tbB.aId
where tbA.a1 = 100 and tbB.b2=200

or may be any composition of joined tables and columns in where section based on the creteria selected by users.

When I run Database Engine Tuning Advisor, many indexes are recommended. However, some of them are very similar (with same columns and different order in index). For example,

Index 1 on tbA
{
a1,
a2
}

Index 2 on tbA
{
a2,
a1
}

and some like this,

Index 3 on tbB
{
b1,
b2
}

Index 4 on tbB
{
b1,
b2,
b3
}

I know too many indexes will increase the database size and affect insert, update and delete operations, so I want to eliminate 'duplicate' indexes as above and minimise the number of index used. I have searched the internet but have not got a clue to solve the problem.

For the first scenario, my question is:
what is the order of columns in an index for selected columns, joined table columns, and where condition columns?
Is it possible to only keep Index 1 and optimise the queries to only use this index.

For the second scenario,
can I just keep the Index 4 as Index 3 seems included in Index 4?

Please advise or give me a clue for further research on index.

Thanks.

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-16 : 05:39:20

hi
This query will show you duplicate index..

SELECT OBJECT_NAME(i1.id) AS 'Table',
i1.name AS 'Index',
i2.name AS 'Duplicate Index'
FROM sysindexes i1,
sysindexes i2
WHERE i1.indid NOT IN (0,255)
AND i2.indid NOT IN (0,255)
AND INDEXPROPERTY(i1.id, i1.name, 'IsStatistics') = 0
AND INDEXPROPERTY(i2.id, i2.name, 'IsStatistics') = 0
AND i1.id = i2.id
AND i1.indid < i2.indid
AND NOT EXISTS (SELECT '1'
FROM sysindexkeys ik1,
sysindexkeys ik2
WHERE ik1.id = i1.id
AND ik1.id = ik2.id
AND ik1.indid = i1.indid
AND ik2.indid = i2.indid
AND ik1.keyno = ik2.keyno
AND ik1.colid != ik2.colid)
AND 0 = (SELECT MAX(ik1.keyno) - MAX(ik2.keyno)
FROM sysindexkeys ik1,
sysindexkeys ik2
WHERE ik1.id = i1.id
AND ik1.id = ik2.id
AND ik1.indid = i1.indid
AND ik2.indid = i2.indid)

-------------------------
R..

erichan
Starting Member

3 Posts

Posted - 2009-07-16 : 06:02:26

quote:
Originally posted by rajdaksha

hi
This query will show you duplicate index..

SELECT OBJECT_NAME(i1.id) AS 'Table',
i1.name AS 'Index',
i2.name AS 'Duplicate Index'
FROM sysindexes i1,
sysindexes i2
WHERE i1.indid NOT IN (0,255)
AND i2.indid NOT IN (0,255)
AND INDEXPROPERTY(i1.id, i1.name, 'IsStatistics') = 0
AND INDEXPROPERTY(i2.id, i2.name, 'IsStatistics') = 0
AND i1.id = i2.id
AND i1.indid < i2.indid
AND NOT EXISTS (SELECT '1'
FROM sysindexkeys ik1,
sysindexkeys ik2
WHERE ik1.id = i1.id
AND ik1.id = ik2.id
AND ik1.indid = i1.indid
AND ik2.indid = i2.indid
AND ik1.keyno = ik2.keyno
AND ik1.colid != ik2.colid)
AND 0 = (SELECT MAX(ik1.keyno) - MAX(ik2.keyno)
FROM sysindexkeys ik1,
sysindexkeys ik2
WHERE ik1.id = i1.id
AND ik1.id = ik2.id
AND ik1.indid = i1.indid
AND ik2.indid = i2.indid)

-------------------------
R..

Thanks for quick reply, but I might not make my questions clear.

It is very easy to find out those indexes using sql server management studio, thus not a problem. Actually, I want to know how to write queries to reuse a minimun number of indexes.

For example, for query A, Database Engine Tuning Advisor recommends Index 1 on tbA
{
a1,
a2
}

for query B, it recommends
Index 2 on tbA
{
a2,
a1
}

Sorry,but I cannot give a real example as the real environment is very complecated.

What I want to know is the possible reason for the above indexes. Then I can rewrite queries to only use the first index and remove the second index.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 12:34:15

the usage of index in a query depends on selectivity, columns retrieved, filter columns,...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources