Clustered Index and Non Clustered Index

Question

Clustered Index and Non Clustered Index

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
April 10, 2012 at 9:16 am

#258916

Hi,
I was running a select query and against the primary key as we know there was a clustered index. But while analyzing the query using DTA, DTA recommended to create a Non Clustered create on the same column.
I have 2 questions on this -
1. How DTA can suggest a Non Clustered index to be created on a column which is already under Cluster?
2. Any benefit out of it we create a non cluster index?
3. Any -ve aspect?
Regards,
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Without seeing the DDL (CREATE TABLE) statement for the table(s), including all existing indexes; the code you ran through DTA there really is no way to answer your questions.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

1) Quite easily. DTA is looking for the absolute best and does not consider duplicate indexes to be a problem

2) Yes, in some edge cases, but it's far from a common case

3) Yes, it's another index, so write overhead, additional storage, additional maintenance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

Thanks Gail.

Hi Lynn

You are asking for the table structures.

Well, can you guide me please, if there has to be something specific I need to look at ?

against both the tables, the below options are available...And in one table one id column is a foreign key to the other table.

one table (A) has - 17 columns

The other table (B) has - 22 columns

For Table B, one column is a foreign key to the other table(A).

PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

I'm sorry, but you aren't new to ssc. Please post the DDL (CREATE TABLE statement) for the table(s) including all defined indexes and your code you ran through DTA.