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

Effect of Clustered Index on Non-Clustered Index Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 8:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 12:03 PM
Points: 39, Visits: 69
Not remotely a DBA so forgive me if this is a novice question, but will having a clustered index in a database improve the performance of non-clustered indexes?

Sean
Post #1469578
Posted Tuesday, July 2, 2013 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,062, Visits: 11,891
Sean Grebey (7/2/2013)
Not remotely a DBA so forgive me if this is a novice question, but will having a clustered index in a database improve the performance of non-clustered indexes?

Sean


Indexes are at the table level not at the database level. A clustered index will not make some other index faster. Indexes are there to help queries perform faster. An index by itself does not have a performance measure. I think you should do some reading on indexes to get a basic understanding of what they do. Over on the left is a link to Stairways, there is a whole series of article on indexes that starts with the very basic and progresses more advanced with each article. Find an hour or so and dive in.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1469580
Posted Tuesday, July 2, 2013 8:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 12:03 PM
Points: 39, Visits: 69
Sure I understand that indexes are against tables. And I understand the difference between clustered and non-clustered indexes. My question is will non-clustered indexes perform better if there is a clustered index on the table. That is will they used the clustered key perform better if one exists?
Post #1469582
Posted Tuesday, July 2, 2013 9:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 12:03 PM
Points: 39, Visits: 69
I was asking because from what I understand if there is a clustered index non-clustered indexes use the clustered key, otherwise it uses the rowID (file number, page number and slot number), so it would seem that having a clustered index would improve performance of the non-clustered indexes but I haven't actually found anywhere that said so.
Post #1469586
Posted Friday, July 5, 2013 7:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:52 PM
Points: 7,079, Visits: 12,569
Sean Grebey (7/2/2013)
I was asking because from what I understand if there is a clustered index non-clustered indexes use the clustered key, otherwise it uses the rowID (file number, page number and slot number), so it would seem that having a clustered index would improve performance of the non-clustered indexes but I haven't actually found anywhere that said so.

It's not possible to make that declaration without more information, e.g. the number of columns in the clustering key, its data types and whether it is declared as unique or whether it is actually unique, the data access patterns of the table. Focus on picking a good clustering key, one that is narrow, unique, preferably ever-increasing and most importantly one that supports your most common data access patterns.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1470891
Posted Saturday, July 6, 2013 10:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
Sean Grebey (7/2/2013)
Sure I understand that indexes are against tables. And I understand the difference between clustered and non-clustered indexes. My question is will non-clustered indexes perform better if there is a clustered index on the table. That is will they used the clustered key perform better if one exists?


They absolutely can (it always depends) especially if the clustered index is unique. The columns of a clustered index are added to every non-clustered index. If there is no unique clustered index, then an 8 byte "uniquefier" is also added to the index. Same holds true for when a non-unique clustered index is used except it's worse. In such cases, the columns of the clustered index are added to the columns of the non-clustered index and then the 8 byte uniquifier is added to that which could make a single column clustered index quite wide and comparatively slow.

One of the best presentations I've found on the subject can be found in the "movie" at the following URL.
http://technet.microsoft.com/en-US/sqlserver/gg508879.aspx


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470925
Posted Saturday, July 6, 2013 1:06 PM


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 @ 4:54 PM
Points: 42,434, Visits: 35,488
Jeff Moden (7/6/2013)
If there is no unique clustered index, then an 8 byte "uniquefier" is also added to the index.


Not quite

If there's no clustered index, the 8-byte RID is added to all nonclustered indexes as the row location. That's all that's added and it's only when there's no clustered index at all. It's not a uniquifier, it's the physical RID, the combination of File ID, Page Number, Slot index.

Same holds true for when a non-unique clustered index is used except it's worse. In such cases, the columns of the clustered index are added to the columns of the non-clustered index and then the 8 byte uniquifier is added to that which could make a single column clustered index quite wide and comparatively slow.


When there is a clustered index (of any form) then the clustered index key (the entire of it) is added to all nonclustered indexes as the row location. When a clustered index is not unique, the clustered index key gets an additional nullable 4-byte uniquifier added to the clustered index key (only actually has a value on duplicate rows). Since it's part of the clustered index key, the uniquifier is added to all nonclustered indexes as well.



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 #1470934
Posted Saturday, July 6, 2013 3:00 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 786, Visits: 690
Sean Grebey (7/2/2013)
I was asking because from what I understand if there is a clustered index non-clustered indexes use the clustered key, otherwise it uses the rowID (file number, page number and slot number), so it would seem that having a clustered index would improve performance of the non-clustered indexes but I haven't actually found anywhere that said so.


You could argue that adding a clustered index, makes the non-clustered indexes slower. As long as the table is a heap, lookup is by RID, that is you go straight to the data. With a clustered index, there are a few more logical reads to traverse the tree of the clustered index.

Then again, with the clustered key as part of the non-clustered index, their may be queries where the index becomes covering, which is a huge performance boost.

As it stands, in SQL Server, clustered index is the norm, and you should only use heaps if you know exactly what you are doing. (In other products, the reverse may apply.) You may gain some performance with heaps, but you lose a lot in manageability, which in the end will affect your performance negatively.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1470945
Posted Saturday, July 6, 2013 11:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
GilaMonster (7/6/2013)
Jeff Moden (7/6/2013)
If there is no unique clustered index, then an 8 byte "uniquefier" is also added to the index.


Not quite

If there's no clustered index, the 8-byte RID is added to all nonclustered indexes as the row location. That's all that's added and it's only when there's no clustered index at all. It's not a uniquifier, it's the physical RID, the combination of File ID, Page Number, Slot index.

Same holds true for when a non-unique clustered index is used except it's worse. In such cases, the columns of the clustered index are added to the columns of the non-clustered index and then the 8 byte uniquifier is added to that which could make a single column clustered index quite wide and comparatively slow.


When there is a clustered index (of any form) then the clustered index key (the entire of it) is added to all nonclustered indexes as the row location. When a clustered index is not unique, the clustered index key gets an additional nullable 4-byte uniquifier added to the clustered index key (only actually has a value on duplicate rows). Since it's part of the clustered index key, the uniquifier is added to all nonclustered indexes as well.


I certainly blew that one out of my nose. Thank you VERY much for the education, Gail.

Back to the books.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470964
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse