SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stairway to SQL Server Indexes: Step 3, Clustered Indexes


Stairway to SQL Server Indexes: Step 3, Clustered Indexes

Author
Message
David Durant
David Durant
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 3, Clustered Indexes
amenjonathan
amenjonathan
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 434
I have heard from more than one person that all columns in the clustered index also show up in each non-clustered index on that same table, which is why you should keep your clustered index columns to the bare minimum you can get away with.

Is this true?

-------------------------------------------------------------------------------------------------
My SQL Server Blog
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 2829
amenjonathan (6/22/2011)
I have heard from more than one person that all columns in the clustered index also show up in each non-clustered index on that same table, which is why you should keep your clustered index columns to the bare minimum you can get away with.

Is this true?


Yes and no*.

The clustered index (plus a uniqueifier if necessary) is the row pointer for non-clustered indexes (See http://msdn.microsoft.com/en-us/library/ms177484.aspx) So a smaller clustered index will mean smaller non-clustered indexes, all else being equal.

BUTif you have few non-clustered indexes, and you have a column that is almost always included in criteria for queries, you can gain performance if that's part of the clustered index at a small cost of space in the non-clustered indexes (indeed, you'd likely be moving that column from its own non-clustered index to the clustered index, so you could actually save space.)

Note: When I speak about changing the clustered index, I'm speaking of conceptually changing it preferably before any data is in the table, and definitely before moving it to production. Changing a clustered index in production can be difficult, to say the least. This is one of those areas where planning is key.

* This is ALWAYS the answer to any Yes or No question. Always.
amenjonathan
amenjonathan
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 434
Very cool. Thanks!

-------------------------------------------------------------------------------------------------
My SQL Server Blog
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 Visits: 1721
Here's two sample queries that will run against the default DotNetNuke schema. Note the only difference is in the second query in which I've added to the where clause a requirement to make the value of the primary key greater than zero. Adding this line to the second query seems to force an index seek on the table. Is this really doing what it seems to be doing? If so, it's a very valuable technique for forcing seeks instead of scans.

SELECT
up.PropertyValue
,ppd.PortalID
FROM
dbo.UserProfile AS up
INNER JOIN dbo.ProfilePropertyDefinition AS ppd
ON up.PropertyDefinitionID = ppd.PropertyDefinitionID
WHERE
up.UserID = 2345 -- Put a real UserID here

SELECT
up.PropertyValue
,ppd.PortalID
FROM
dbo.UserProfile AS up
INNER JOIN dbo.ProfilePropertyDefinition AS ppd
ON up.PropertyDefinitionID = ppd.PropertyDefinitionID
WHERE
ppd.PropertyDefinitionID > 0
AND up.UserID = 2345 -- Put a real UserID here



[For some reason the image is not displaying, but you can click on the link and download it to view in SSMS.]

Attachments
SampleQueryPlan.sqlplan (266 views, 24.00 KB)
Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)SSC Eights! (823 reputation)

Group: General Forum Members
Points: 823 Visits: 1721
Now a second question...I personally avoid using UNIQUEIDENTIFIER cols as part of a primary key. But sometimes I have no choice when I've inherited a schema from someone else. I've not found a way to avoid index scans on these cols. Any advice?

 
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 2829
Steven Willis (6/22/2011)
Here's two sample queries that will run against the default DotNetNuke schema. Note the only difference is in the second query in which I've added to the where clause a requirement to make the value of the primary key greater than zero. Adding this line to the second query seems to force an index seek on the table. Is this really doing what it seems to be doing? If so, it's a very valuable technique for forcing seeks instead of scans.


Yes and no.*

Note in the query plan that each query has exactly the same cost, and each element of the two queries has the same cost as its counterpart (even the scan/seek). This plus the actual number of rows and row sizes indicates that the data you're querying against is simple enough that there's not much difference in performance between a seek and a scan.

Here we get into the "black box" nature of the query optimizer. It does appear that adding a requirement on the primary key forced an index seek, but that is not guaranteed to happen whenever you do so. Sometimes a scan is more efficient than a seek, and in my experience the optimizer is usually, but not always, better than I am at figuring that out.

* (see my previous post on the usefulness of this answer)
tmulherin
tmulherin
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 10
I know it's trivial but the table names used in some of your SQL statements do not match the the table names reflected in the results. For instance in step3:

SELECT *
FROM SalesOrderDetail
WHERE SalesOrderID = 43671
AND SalesOrderDetailID = 120

Heap (1 row(s) affected)
Table 'SalesOrderDetail_noindex'. Scan count 1, logical reads 1495.

This is true so far in steps 2 and 3.
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 2829
Steven Willis (6/22/2011)
Now a second question...I personally avoid using UNIQUEIDENTIFIER cols as part of a primary key. But sometimes I have no choice when I've inherited a schema from someone else. I've not found a way to avoid index scans on these cols. Any advice?
 


Yes. Determine if the schema requires the primary key to be the clustered index. The two are not the same thing.
Check out http://ask.sqlservercentral.com/questions/12/should-my-primary-key-be-clustered for starters.
Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)Say Hey Kid (710 reputation)

Group: General Forum Members
Points: 710 Visits: 1266
Comparison of clustered index table with unindexed heap in this article is not fair, IMHO. Clustered index means we have both data and index. HEAP means we only have data and no index. Author should add an index to the heap to make fair comparison.
Author should also show examples of operations where heap beats clustered index table in performance.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search