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 «««910111213»»»

Introduction to Indexes: Part 2 – The clustered index Expand / Collapse
Author
Message
Posted Friday, January 14, 2011 9:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 7:17 AM
Points: 13, Visits: 42
Nice
Post #1048042
Posted Friday, January 14, 2011 11:15 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
Excellent article Gail! Very simple and easy to understand with the fundamentals that add up to a good understanding of the subject.

These articles should be required reading for both newbies and old hands alike.
Todd Fifield
Post #1048090
Posted Monday, January 17, 2011 7:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 6:53 AM
Points: 2,701, Visits: 1,562
Hi Gail,
When you're performing an update on a non-clustered index column on a table with clustered index (CI), the execution plan indicated that CI is being Updated.

Do you know why this behaviour happens, and is CI is really being updated although you're not updating the CI column?

Thanks,
Simon


Simon Liew
Microsoft Certified Master: SQL Server 2008
Post #1049012
Posted Monday, January 17, 2011 10:56 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: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
The clustered index is been updated. While you may not be updating the clustered index key, as I mentioned in the article "A clustered index is an index where the leaf level of the index contains the actual data rows of the table".

Since the actual data rows are at the leaf of the clustered index, the cluster will be affected by all updates against that table. In essence, it is the table.



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 #1049065
Posted Thursday, January 27, 2011 10:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:56 AM
Points: 12, Visits: 131
FYI,

3 columns with an index on each one separately produced and index intersection plan on my 2008, unlike what the article said! - this is in reference to part 3.



Kindest Regards,

R
Post #1054711
Posted Thursday, January 27, 2011 10:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
Sure, you can get index intersections. Doesn't mean you always will nor that they are the most optimal approach

p.s. Yes, part 3 needs an edit in that area, but it did not say that SQL would never seek on all three and intersect.



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 #1054717
Posted Monday, October 24, 2011 10:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
"A scan of the clustered index is equivalent to a table scan. It’s a read of all of the data pages in the table...
This is a full scan of all the data pages, i.e. a table scan..."

So if a table has 4.5 million rows and the Clustered Index Scan operator properties sheet displays "Actual number of rows 720939" for a query against that table, it's scanning the whole table but only returning 720,939 rows? The sheet subtitle says "Scanning a clustered index, entirely or only a range".


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1195342
Posted Monday, October 24, 2011 10:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
ChrisM@Work (10/24/2011)
So if a table has 4.5 million rows and the Clustered Index Scan operator properties sheet displays "Actual number of rows 720939" for a query against that table, it's scanning the whole table but only returning 720,939 rows?


Quite likely, yes. There are some cases where a clustered index (or nonclustered index) scan can abort early, that's usually due to TOP or some constructs of Row_number.

The Actual rows is always the rows returned, not the rows read, so if there's a predicate on that scan, it's the number of rows that qualified for the predicate, not the number read from the table.

The sheet subtitle says "Scanning a clustered index, entirely or only a range".


A bit incorrect. A range scan (<clustered key> between @SomeValue and @SomeOtherValue) appears as a seek.



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 #1195362
Posted Wednesday, October 26, 2011 2:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:52 AM
Points: 6,872, Visits: 14,185
GilaMonster (10/24/2011)
ChrisM@Work (10/24/2011)
So if a table has 4.5 million rows and the Clustered Index Scan operator properties sheet displays "Actual number of rows 720939" for a query against that table, it's scanning the whole table but only returning 720,939 rows?


Quite likely, yes. There are some cases where a clustered index (or nonclustered index) scan can abort early, that's usually due to TOP or some constructs of Row_number.

The Actual rows is always the rows returned, not the rows read, so if there's a predicate on that scan, it's the number of rows that qualified for the predicate, not the number read from the table.

The sheet subtitle says "Scanning a clustered index, entirely or only a range".


A bit incorrect. A range scan (<clustered key> between @SomeValue and @SomeOtherValue) appears as a seek.


Awesome. Many thanks Gail.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1196111
Posted Friday, July 6, 2012 8:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 2,268, Visits: 3,429
An integer column defined as identity (a very good and popular choice for clustered index) is 4 bytes.



It's certainly popular, but most often not good. The no-thought identity as clustered key likely causes more performance problems than any other single thing.

Most "guidelines" for the clus key affect mostly the INSERT of new rows, which is done only once. Keep in mind that rows will typically be read dozens or hundreds of times after being INSERTed. In fact, even very likely thousands of times if you force too many table scans by having the wrong clustered key.

Nonclustered indexes should not be thought of as the main way to access data. You want the primary access to be through the clustered index.

Often a (date) OR (parent_key, child_key) (such as order_number, item_number) makes a vastly better clustering key than identity.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1326132
« Prev Topic | Next Topic »

Add to briefcase «««910111213»»»

Permissions Expand / Collapse