Clustered Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Robert Davis » Clustered Indexes

39 posts, Page 2 of 4

««1 2 3 4 »»»

Clustered Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

rals

Posted Tuesday, October 05, 2010 3:22 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 19, 2013 1:13 AM
Points: 1,720, Visits: 746

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

Hugo Kornelis

Posted Tuesday, October 05, 2010 3:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 5,244, Visits: 7,063

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

Jonathan AC Roberts

Posted Tuesday, October 05, 2010 4:19 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 4:50 AM
Points: 316, Visits: 1,186

Would have been nice to have a definition of what physically ordered and logically ordered meant?

Post #998204

Christian Buettner-167247

Posted Tuesday, October 05, 2010 4:57 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:12 AM
Points: 2,526, Visits: 3,620

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 Wink

Best Regards,
Chris Büttner

Post #998218

rtelgenhoff

Posted Tuesday, October 05, 2010 6:31 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, May 03, 2013 6:06 AM
Points: 699, Visits: 272

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

Daniel Bowlin

Posted Tuesday, October 05, 2010 6:52 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:10 PM
Points: 2,673, Visits: 2,418

Good question, good lively discussion. Thanks.

Post #998292

rmechaber

Posted Tuesday, October 05, 2010 7:20 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921

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! Smile

Rich

Post #998325

daveb87

Posted Tuesday, October 05, 2010 7:34 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 2:32 PM
Points: 586, Visits: 357

Good questions..and thanks to the comments a much more detailed explaination was provided!!

Post #998346

Mike Dougherty-384281

Posted Tuesday, October 05, 2010 7:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:18 AM
Points: 198, Visits: 682

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

sjimmo

Posted Tuesday, October 05, 2010 8:53 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:15 AM
Points: 2,865, Visits: 2,467

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 »

39 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions