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


Clustered Indexes


Clustered Indexes

Author
Message
rals
rals
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2263 Visits: 843
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
.

Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18429 Visits: 12426
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
Jonathan AC Roberts
Jonathan AC Roberts
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1792 Visits: 1931
Would have been nice to have a definition of what physically ordered and logically ordered meant?
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5201 Visits: 3889
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
rtelgenhoff
rtelgenhoff
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 366
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
Daniel Bowlin
Daniel Bowlin
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7742 Visits: 2629
Good question, good lively discussion. Thanks.
Rich Mechaber
Rich Mechaber
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2667 Visits: 3671
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
daveb87
daveb87
Right there with Babe
Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)Right there with Babe (733 reputation)

Group: General Forum Members
Points: 733 Visits: 537
Good questions..and thanks to the comments a much more detailed explaination was provided!!



Mike Dougherty-384281
Mike Dougherty-384281
SSC Eights!
SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)SSC Eights! (828 reputation)

Group: General Forum Members
Points: 828 Visits: 944
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. Smile
sjimmo
sjimmo
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4744 Visits: 2907
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
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