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

Clustered Indexes Expand / Collapse
Author
Message
Posted Tuesday, October 5, 2010 3:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 1:05 PM
Points: 1,913, Visits: 794
Thanks Hugo. Nice explanation.

I have used the below DBCC to see how page allocation happening and how it logically arranged using NextpageID and PrevpageID.

DBCC IND(Demo, Demo, -1);
GO


Regards,
Rals
.
Post #998171
Posted Tuesday, October 5, 2010 3:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
Christian Buettner-167247 (10/5/2010)
Hi Hugo,

In your explanation you have switched from order of rows accross pages to order of pages.

I have not meant that a physical order is imposed on the pages. I have meant that a physical order is imposed on the rows accros pages. This physical ordering of rows accross pages does not result in physical ordering of the pages, and neither does it result in ordered data on the hard disk itself.

Hi Christian,

I must have misunderstood you - my apologies. I was convinced you were refering to both (order of rows in a page and order of pages on disk). And I tried to make clear that for both, the physical order can be different from the logical order. The logical order follows the clustered index, and is implemented using the pointer chain (for the order of pages), and the slot array (for the order of rows within a page).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #998179
Posted Tuesday, October 5, 2010 4:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:45 AM
Points: 338, Visits: 1,431
Would have been nice to have a definition of what physically ordered and logically ordered meant?
Post #998204
Posted Tuesday, October 5, 2010 4:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
No Problem Hugo. I was not that clear in my first post, so I can see where you were coming from. And that just emphasizes how important it is to be very precise and clear when allegeing facts

Best Regards,
Chris Büttner
Post #998218
Posted Tuesday, October 5, 2010 6:31 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 11:01 AM
Points: 703, Visits: 327
It's not surprising that so many people get this wrong. Not much effort is given by authors to fully explain this, perhaps a few don't know either. I used to misunderstand it as well, but the QoD have enlightened me... As I understand it, both the pages and rows are physically sorted ONLY after an index rebuild (or the table has had no page splits yet). From there, new pages and page splits can be placed anywhere and logically ordered through double linkage. This is where fragmentation starts to occur. Rows remain physically sorted within the pages. If a single row takes up more than 1/2 of the free space in the page (> 4030 of the 8060 bytes), you will have a lot of unused space within pages and fragmentation would become very high, as every row requires it's own page.

The following excerpts are not wrong, but don't give the full picture either.

From Wikipedia: "Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them."
http://en.wikipedia.org/wiki/Index_(database)#Clustered

MSDN: "A clustered index determines the physical order of data in a table."
http://msdn.microsoft.com/en-us/library/aa933131(SQL.80).aspx
Post #998274
Posted Tuesday, October 5, 2010 6:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:36 PM
Points: 2,818, Visits: 2,565
Good question, good lively discussion. Thanks.
Post #998292
Posted Tuesday, October 5, 2010 7:20 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:12 AM
Points: 702, Visits: 3,012
Hugo, thanks VERY much for your script and, even more, your explanation. Simple and to the point, thorough.

Posts like this one are why I sometimes spend more time than I should at this website!

Rich
Post #998325
Posted Tuesday, October 5, 2010 7:34 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:45 PM
Points: 649, Visits: 474
Good questions..and thanks to the comments a much more detailed explaination was provided!!


Post #998346
Posted Tuesday, October 5, 2010 7:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:59 PM
Points: 262, Visits: 918
I answered this question using the common misconception as a guide. Usually second-guessing the QotD is guaranteed to trap us.

The discussion of physical vs. logical ordering is certainly interesting in an academic sense. If I was encountering problems with the edge case where physical order was actual impacting performance, I would appreciate finding this distinction quickly rather than delving into deeply esoteric notes.

However, for the day-to-day operation of a developer and database steward [not quite as lofty as DBA but still responsible for data health] - is there a compelling reason to be pedantic about the "sometimes physical disorder" of a clustered index? Doesn't the DB engine have enough optimization intelligence to ensure that the clustered index performs as well as possible across the largest number of use-cases? Armed with this new information about clustered indexes, am I going to do anything differently?

I'm not asking this to be a griefer. I am curious if I am being negligent of my responsibility for data if I don't really care that I don't know this level of minutiae. I am grateful that others have thought about (and managed) this for me. I build framework code so other developers don't need to worry about the heavy-lifting details; I am content to accept the same from grown-up DBAs and DBMS developers. Standing on the shoulders of giants, etc. :)
Post #998352
Posted Tuesday, October 5, 2010 8:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 2,917, Visits: 2,528
Very good question - actually gets the blood flowing.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Post #998433
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse