July 2, 2013 at 8:50 am
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
July 2, 2013 at 8:56 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2013 at 8:59 am
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?
July 2, 2013 at 9:04 am
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.
July 5, 2013 at 7:44 pm
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
July 6, 2013 at 10:28 am
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
Change is inevitable... Change for the better is not.
July 6, 2013 at 1:06 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 6, 2013 at 3:00 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 6, 2013 at 11:12 pm
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. :blush: Thank you VERY much for the education, Gail.
Back to the books.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply