May 17, 2010 at 12:21 pm
Ok, so I throw together some scripts to track down duplicate indexes.
I found a few tables with duplicates but when I started investigating I found that in each case there was one primary key unique non-clustered index and then a seperate clustered index on the same primary key column.
Is there any benefit to doing this or should I assume whatever former Dev/DBA did this was just doing something crazy and toss out the clustered indexes?
for example there is a table called "tests" with a primary key on the "ID" bigint column.
If I view indexes on that table I have
IX_Tests_ID (clustered) - only column is ID
PK_Tests (unique, non-clustered) - only column is ID
Thanks!
May 17, 2010 at 12:44 pm
I wouldn't toss the Clustered Indexes. If anything you may want to recreate the PKs as Clustered.
Speculation - The PK was created without a clustered index and then later they discovered performance problems and then created a Clustered Index to help alleviate the problem. Another possibility is that a Clustered index existed first and then they backtracked to add the PK after and could not add a second clustered index.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 17, 2010 at 5:27 pm
Or worse still, they didn't know what they were doing and created all the PKs as non-clustered and then created clustered indexes.
I go with Jason, drop and re-create the PKs as cluster and drop the extra index.
Leo
Striving to provide a better service.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 18, 2010 at 10:33 am
Kimberly Tripp started great series Spring cleaning your indexes if you want to find out more about indexes cleanup.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply