September 26, 2003 at 8:18 am
I'm in the process of diagnosing a serious performance problem in a customer's system. At this point, I'm pretty much grasping at straws, so I thought that I would go ahead and throw this out there to see if anyone had any ideas:
We have scripts to automatically create the tables on our installation of database tables. One thing that I had noticed is that our scripts have created both a clustered and a non-clustered index on all primary keys in all tables. Is it possible that the duplicated index could be causing performance problems? Is there any overhead with this? Obviously, we don't need the duplicated index, however I'm not about to start whacking the indexes until I confirm that it could be causing a major bottleneck.
September 28, 2003 at 3:18 pm
Yes, it would be a performance problem, but to what extent I can't gauge. It will be particularly inefficient if the primary key columns are volatile (ie. updated frequently).
It will be space inefficient if the primary key is long (eg. surname and first name).
In SQL Server (7.0 and 2000), the clustered index value is carried as overhead in the non-clustered indexes.
A broad brush approach might be to drop all the redundant non-clustered indexes, but if you have time to analyze in detail you might find it's better in a number of cases to drop the clustered index and retain the non-clustered.
I recommend dropping redundant indexes anyway, to eliminate this as the possible bottleneck.
Cheers,
- Mark
Cheers,
- Mark
October 2, 2003 at 8:47 am
This is bad in almost every case, however there is one situation where there might be a good reason. Are they both in the same direction?
Keith Henry
DBA/Developer/BI Manager
Keith Henry
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply