sql server 05 primary key index

[SQL_Hell]

Hiya,


We are having some problems with a reporting services report performing badly today and on one of the key tables I have noticed that there are 2 indexes on the same column (the primary key), one of the indexes is clustered the other isn't.

Does anyone know what the net effects of having 2 indexes on the same column are?

 

[namliam]

Other than taking up space, nothing. If you have 2 identical indexes the database will choose one and use that without problems as far as I know.

 

[SQL_Hell]

Yeah I think you're right, after looking through some execution plans it does pick one of the 2 indexes to use, usually the clustered one.

Going to delete the non clustered index as it's not needed

 

[namliam]

Be carefull! Make sure that all the constraints and such are not using it by chance....

I wouldnt just delete an Index just like that, the space consumed is not that much (now a days) unless you have a real storage problem.
Even then it will not help much.

 

[SQL_Hell]

It's fine there are no contraints on this table apart from the primary key, there are no table hints in queries and even if there was the optimiser would choose the other index anyway.

This is a reporting server not live a production server and I have tested all the reports and there are no problems.

It's not the space I am concerned about it's more time spent over the weekend rebuilding 2 indexes on the same column on a table that has 15 million rows. We have a small maintenance window.

 

[namliam]

OK, just making sure you are not overlooking something...

 

[SQL_Hell]

Ok thanks