Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Engine Tuning Advisor Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:56 AM
Points: 4, Visits: 109
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
Post #1462961
Posted Thursday, June 13, 2013 4:38 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 969, Visits: 3,006
How wide is your clustered index.

Can you supply DDL for the clustered index and for the suggestion.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1462974
Posted Thursday, June 13, 2013 6:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:08 AM
Points: 1,380, Visits: 2,703
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?
Post #1463013
Posted Thursday, June 13, 2013 6:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 40,390, Visits: 36,823
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1463020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse