Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by David Durant » Stairway to SQL Server Indexes: Step 3,...
Add to briefcase

Stairway to SQL Server Indexes: Step 3, Clustered Indexes Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
David Durant
Posted Monday, February 14, 2011 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 23, 2012 10:54 AM
Points: 8, Visits: 23
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 3, Clustered Indexes
Post #1063682
amenjonathan
Posted Wednesday, June 22, 2011 9:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 20, 2012 5:21 PM
Points: 84, Visits: 391
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
Post #1129754
sknox
Posted Wednesday, June 22, 2011 9:33 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:31 AM
Points: 1,041, Visits: 1,356
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.
Post #1129787
amenjonathan
Posted Wednesday, June 22, 2011 9:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 20, 2012 5:21 PM
Points: 84, Visits: 391
Very cool. Thanks!

My SQL Server Blog
Post #1129807
Steven Willis
Posted Wednesday, June 22, 2011 9:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:48 PM
Points: 284, Visits: 1,249
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.]

  Post Attachments 
SampleQueryPlan.sqlplan (228 views, 24.64 KB)
Post #1129825
Steven Willis
Posted Wednesday, June 22, 2011 10:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:48 PM
Points: 284, Visits: 1,249
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?

 
Post #1129840
sknox
Posted Wednesday, June 22, 2011 10:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:31 AM
Points: 1,041, Visits: 1,356
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)
Post #1129863
tmulherin
Posted Wednesday, June 22, 2011 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 09, 2012 11:34 AM
Points: 7, 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.
Post #1129878
sknox
Posted Wednesday, June 22, 2011 10:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:31 AM
Points: 1,041, Visits: 1,356
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.
Post #1129880
Vedran Kesegic
Posted Sunday, January 27, 2013 6:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 343, Visits: 1,077
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.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!
Post #1412119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.