January 5, 2010 at 8:19 am
Hello everyone,
I'm in charge of a database where I've numerous duplicate indexes (too many people working on it since 5 years).
During the last development it has been introduced a new bit field (archived) to mark if a record is archived or not (from a business point of view). since then, on many table I've
1) an index on the PK
2) an other index on the PK + the archive bit field.
As far as I understand it, it's redundant to have these 2 indexes. I'm not really sure how to proceed ?
- Should I drop the the indexes based on the PK + the archived field. Then create a index with only the archived field ?
or
- Should I drop both indexes and recreate the index on the PK with the archived field ?
or
- is there something else that I've not foreseen ?:-)
thanks a lot in advanced for your advises
January 5, 2010 at 8:31 am
Technically those two indexes are not duplicates. One enforces the primary key, one allows filter on two columns. Unless you have a good reason, I'd actually leave them.
Widening the pk is not a good idea, unless it is allowed for two identical records to exist, one archived one not.
If the pk is a clustered index, you could remove the composite index, if you really want to be rid of one, since the PK is unique it shouldn't make queries less efficient. Shouldn't. Test carefully if you decide to go that route.
An index with just the archived column will likely be useless. As a single column index it won't be selective enough to be used in queries. SQL seldom uses more than one index per table when evaluating queries, so you can't have the pk and a separate archived index and expect them both to be used.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2010 at 8:40 am
Execute below SQL to actually see more duplicate indexes on your database. I would remove the duplicate indexes, if any, but as Gila said yours doesnot seems to be case of 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)
SQL DBA.
January 6, 2010 at 8:27 am
Thanks for this quick response. I think I understand it.
But, I still have some more questions to be sure. I'm really not an index expert 😉
a) I've a table where the PK column (type: UniqueIdentifier) is indexed twice with non-clustered index. I guess this is a real duplicate where I can delete one of them right ?
b) I've other tables where the PK (still UniqueIdentifier) is a non-clustered indexed and the second index is a clustered on the PK + a bit field. Should I keep the second one as is, or modify it to be non-clustered ? I know that having Clustered GUID on UniqueIdentifier is not the best thing to have right ?
Thanks again for your adivses, I'm learning 😉
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply