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

Stairway to SQL Server Indexes: Step 3, Clustered Indexes Expand / Collapse
Author
Message
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
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: Monday, December 16, 2013 10:42 AM
Points: 96, 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
Post #1129754
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: Yesterday @ 6:45 AM
Points: 1,333, Visits: 1,701
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
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: Monday, December 16, 2013 10:42 AM
Points: 96, Visits: 434
Very cool. Thanks!

-------------------------------------------------------------------------------------------------
My SQL Server Blog
Post #1129807
Posted Wednesday, June 22, 2011 9:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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 (235 views, 24.64 KB)
Post #1129825
Posted Wednesday, June 22, 2011 10:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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
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: Yesterday @ 6:45 AM
Points: 1,333, Visits: 1,701
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
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 9, 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
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: Yesterday @ 6:45 AM
Points: 1,333, Visits: 1,701
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
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: 2 days ago @ 5:52 AM
Points: 369, Visits: 1,215
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
Post #1412119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse