Database Engine Tuning Advisor
-
Hi,
When I ran Database Engine Tuning Advisor, it suggests to create a non-clustered index on the column where a clustered index is already present.
Can somebody give me some explanation why it is saying like that?
Regards,
Basavaraj Yn
-
basavarajyn (6/13/2013)
Hi,When I ran Database Engine Tuning Advisor, it suggests to create a non-clustered index on the column where a clustered index is already present.
Can somebody give me some explanation why it is saying like that?
Regards,
Basavaraj Yn
Is that column the only column in the Clustered index, if not, is it the leading column?
-
basavarajyn (6/13/2013)
When I ran Database Engine Tuning Advisor, it suggests to create a non-clustered index on the column where a clustered index is already present.Can somebody give me some explanation why it is saying like that?
Because the database tuning advisor always suggests the absolute best index for the query, that's why it's so dangerous to blanket accept its recommendations.
The clustered index is the largest index on the table, so the most IO to seek and read. Hence if the query wants just the clustered index column, the clustered index, while a very good index to use is not the absolute best and hence DTA will recommend a nonclustered index on tha same column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply