Clustered DB Server: Clustered Indexes and physical order.

I've got a question about how clustered indexes map to the physical order of
the records in a database. I've always been under the impression that a
clustered index orders the records physically in the order of the clustered
key. As an MCT, I've taught this idea many times.
However, on a whim I just did a page dump of a table, and noticed that the
records were not in the order of the clustered key (I used the authors table
in the pubs database). An an aside, a "select *" does display them in
clustered key order; Also the column order in a "select *" does not match
the column order in the page dump.
From a practical point of view, I suppose this makes sense, since the unit
of IO is the page, and once we have the page in the buffer, reading from
anywhere on the page is probably going to "cost" the same.
But then what does it mean to say that a clustered index orders the data
physically in the order of the index? It can't mean that the pages are
contiguous. Otherwise, fragmentation of a clustered index would be
impossible (and its not). Does it simply mean that the pages are in the
order of the index? For example, if the index is on id number, does it mean
that on page 1, if the highest id number is 5, then on page 2, the lowest id
number is greater than 5, but it may not be at the beginning of the page?
If that's so, then Microsoft's documentation and courseware is seriously
misleading in some places. I'd be grateful for any insight into this issue.It means that the pages are in the order of the index, but data with a page
are in no specified order. I don't find the documentation misleading, becaus
e
the smallest I/O is a page, and as you said, once a page is in memory, there
is no performance difference between retrieving the first or the last row in
the page.
Vern
"MAS" wrote:

> I've got a question about how clustered indexes map to the physical order
of
> the records in a database. I've always been under the impression that a
> clustered index orders the records physically in the order of the clustere
d
> key. As an MCT, I've taught this idea many times.
> However, on a whim I just did a page dump of a table, and noticed that the
> records were not in the order of the clustered key (I used the authors tab
le
> in the pubs database). An an aside, a "select *" does display them in
> clustered key order; Also the column order in a "select *" does not match
> the column order in the page dump.
> From a practical point of view, I suppose this makes sense, since the unit
> of IO is the page, and once we have the page in the buffer, reading from
> anywhere on the page is probably going to "cost" the same.
> But then what does it mean to say that a clustered index orders the data
> physically in the order of the index? It can't mean that the pages are
> contiguous. Otherwise, fragmentation of a clustered index would be
> impossible (and its not). Does it simply mean that the pages are in the
> order of the index? For example, if the index is on id number, does it me
an
> that on page 1, if the highest id number is 5, then on page 2, the lowest
id
> number is greater than 5, but it may not be at the beginning of the page?
> If that's so, then Microsoft's documentation and courseware is seriously
> misleading in some places. I'd be grateful for any insight into this issu
e.
>
>|||MAS
There was one of the longest threads in the history of this newsgroup on
this very topic, last w

.
Please see the thread entitled "insert into tbl1 select * from tbl2order by
field1 doesnt work!"
It was initially posted on May 23, by someone called CoolTech77. However,
once he posting the original question, he never came back. But someone else
jumped in and swore that because all the docs said 'physical order', it must
be true, in spite of lots of evidence to the contrary.
If you are SQL Server MCT, teaching SQL Server classes, I strongly recommend
you read my book, Inside SQL Server 2000 (2005 is coming soon) so you can
make sure you have the details correct. I actually use the authors table as
an example when talking about page structures.
Also, take a look at this blog entry by David Portas (who was one of the
people on the thread last w

) who tried to clear things up.
http://blogs.conchango.com/davidpor...05/30/4007.aspx
But, as an MCT, it would really be worthwhile to also read the whole thread.
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:%23u6SVzlhGHA.1272@.TK2MSFTNGP03.phx.gbl...
> I've got a question about how clustered indexes map to the physical order
> of the records in a database. I've always been under the impression that
> a clustered index orders the records physically in the order of the
> clustered key. As an MCT, I've taught this idea many times.
> However, on a whim I just did a page dump of a table, and noticed that the
> records were not in the order of the clustered key (I used the authors
> table in the pubs database). An an aside, a "select *" does display them
> in clustered key order; Also the column order in a "select *" does not
> match the column order in the page dump.
> From a practical point of view, I suppose this makes sense, since the unit
> of IO is the page, and once we have the page in the buffer, reading from
> anywhere on the page is probably going to "cost" the same.
> But then what does it mean to say that a clustered index orders the data
> physically in the order of the index? It can't mean that the pages are
> contiguous. Otherwise, fragmentation of a clustered index would be
> impossible (and its not). Does it simply mean that the pages are in the
> order of the index? For example, if the index is on id number, does it
> mean that on page 1, if the highest id number is 5, then on page 2, the
> lowest id number is greater than 5, but it may not be at the beginning of
> the page?
> If that's so, then Microsoft's documentation and courseware is seriously
> misleading in some places. I'd be grateful for any insight into this
> issue.
>|||Thanks,
I'll check those resources out.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23zVzeBmhGHA.1856@.TK2MSFTNGP03.phx.gbl...
> MAS
> There was one of the longest threads in the history of this newsgroup on
> this very topic, last w

.
> Please see the thread entitled "insert into tbl1 select * from tbl2order
> by field1 doesnt work!"
> It was initially posted on May 23, by someone called CoolTech77. However,
> once he posting the original question, he never came back. But someone
> else jumped in and swore that because all the docs said 'physical order',
> it must be true, in spite of lots of evidence to the contrary.
> If you are SQL Server MCT, teaching SQL Server classes, I strongly
> recommend you read my book, Inside SQL Server 2000 (2005 is coming soon)
> so you can make sure you have the details correct. I actually use the
> authors table as an example when talking about page structures.
> Also, take a look at this blog entry by David Portas (who was one of the
> people on the thread last w

) who tried to clear things up.
> http://blogs.conchango.com/davidpor...05/30/4007.aspx
> But, as an MCT, it would really be worthwhile to also read the whole
> thread.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:%23u6SVzlhGHA.1272@.TK2MSFTNGP03.phx.gbl...
>|||Good point. The SQL courses 2733 and 2734 are particularly egregious
examples of inaccurate courseware.
And BTW, even the trainers that just read slides wouldn't admit it -- it's
usually the people hiring the trainers that think all there is to the job is
reading slides.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:enlhvYLiGHA.1000@.TK2MSFTNGP04.phx.gbl...
> Yes, the courseware has many inaccuracies. It is up to the trainer to know
> the product well enough to recognize when the courseware is incomplete and
> to pursue deeper. (I know that's just my opinion, and not everyone shares
> it. Some people think all a trainer has to be able to do is read the
> slides.)
> I provide tools in Inside SQL Server that you can use to see for yourself
> how the data is physically organized.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "MAS" <mark_stricker@.hotmail.com> wrote in message
> news:%23M11jCLiGHA.4276@.TK2MSFTNGP03.phx.gbl...
>|||It's all still logical ordering. Page to page ordering is with pointers. On
the pages themselves, there is a set of bytes at the bottom of the page that
gives the offsets of the rows in logical order. I describe these row-offset
bytes in detail in my book and in my course.
It's all for performance reasons. If the rows were stored physically in
order on pages, inserts would mean shuffling rows around on the pages. This
way, a new row can go wherever there is room, and only a few bytes at the
end of the page need to be adjusted.

> your book; I've been meaning to for years -- but, I am an MCT and I also
> have a full time job as a SQL Engineer AND four kids with soccer and
> baseball games to watch -- so I don't have a lot of time. :))
Excuses, excuses ...
I've got 4 kids with classroom meetings, choir concerts and drama
productions to watch, a day job and a night job as assistant instructor at a
martial arts dojo, and MVP responsibilities to answer questions on the
newsgroups, so if I can WRITE a book, you can certainly READ it. ;-)
(Actually, this is just MY excuse for why Inside SQL Server 2005 isn't done
yet. :-) )
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:Oy0P0aMiGHA.3296@.TK2MSFTNGP05.phx.gbl...
> Now, that I've looked at it, I remember reading that thread. I'm not
> under the misunderstanding that the data is ordered physically (what a
> long thread!). I understand file fragmentation both exterior and
> interior. I was just surprised on looking at a page dump that even WITHIN
> a page the data didn't seem to be in physical order.
> On seeing that, the question in my mind was, OK, what could be meant by
> logical ordering, if, even within a page, a clustered index doesn't order
> the rows? It seems to me that within a page, the rows aren't even
> logically ordered. The IAM pages can define the logical "order" of the
> data pages, I suppose. So I'm left with the surmise that logically
> ordering doesn't mean logical ordering of the rows within a page, but the
> logical ordering of rows between pages.
> For example, we have a clustered index on letter. On page 1, we have ABCD
> but they are in the order CADB. On page 2 (which may be anywhere in the
> file physically) we have FGHI, but they are in the order GFIH. If the
> pages are full and I insert E, the page 2 splits and has EGF on page 2 and
> I on page 3.
> Is this a correct understanding? (And Kalen, I will make an attempt to
> read your book; I've been meaning to for years -- but, I am an MCT and I
> also have a full time job as a SQL Engineer AND four kids with soccer and
> baseball games to watch -- so I don't have a lot of time. :))
> Thanks again.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23zVzeBmhGHA.1856@.TK2MSFTNGP03.phx.gbl...
>|||I completely understand about the training centers. It's a very rare one
that really works hard to make sure the best possible instructor is in the
room for each course. I know of a few like that, but not many.
I assume you are aware that there is a private MCT forum (with a group
dedicated to SQL Server), where you can discuss the inaccuracies in the
courseware with other SQL MCTs. Several of the most active MVPs here are
also MCTs and you can get lots of very specific MOC-related info there.
HTH
Kalen Delaney, SQL Server MVP
"MAS" <mark_stricker@.hotmail.com> wrote in message
news:e0xF44MiGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Good point. The SQL courses 2733 and 2734 are particularly egregious
> examples of inaccurate courseware.
> And BTW, even the trainers that just read slides wouldn't admit it -- it's
> usually the people hiring the trainers that think all there is to the job
> is reading slides.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:enlhvYLiGHA.1000@.TK2MSFTNGP04.phx.gbl...
>

Tuesday, February 14, 2012

Clustered Indexes and physical order.

No comments:

Post a Comment

Clustered DB Server

Blog Archive

About Me