Re: Do I care about this?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 09/15/04 

Date: Wed, 15 Sep 2004 15:16:22 -0700

My guess is that because the nc index is covering is the reason it is being
used.
But I want to comment on this statement:

>> It seems logical to me that the fastest way to fetch them all would be in
> the order that the pages are arranged on the disk, which in this case is
> the order of the clustered primary key

The clustered index does not indicate the ON DISK order.

Pages for a table can be anywhere; they are only logically ordered by the
clustered key. Each page has a pointer to the next one in sequence, but page
77 could follow page 120 and page 500 could follow 77.

But you are right that SQL Server will usually want to fetch the pages in
disk order, but since that is frequently not clustered index order, you will
get a seemingly random ordering of your rows.

Even on the pages themselves, the rows are not stored in physical order.
They are stored wherever there is room on the page, and there is an offset
table at the bottom of the page to keep track which is the first row in
logical order, which is the second and which is the third.

-
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com

"Mike Labosh" <mlabosh@hotmail.com> wrote in message
news:%23IxAKV1mEHA.3684@TK2MSFTNGP10.phx.gbl...
>> Check the execution plan, sql server is not using the clustered index.
>
> Freaky. If the query is this:
>
> SELECT * FROM LastNameSuffix
>
> It seems logical to me that the fastest way to fetch them all would be in
> the order that the pages are arranged on the disk, which in this case is
> the order of the clustered primary key. If the data engine used the
> unique constraint index on the other column, isn't that more work because
> it has to hop over all those nonclustered index pages, and then hop around
> on the disk on the data pages? Although, 8 tiny records like this
> probably fit nicely on a single data page.
>
> --
> Peace & happy computing,
>
> Mike Labosh, MCSD
>
> Feed the children!
> Save the whales!
> Free the mallocs!
> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in
> message news:08E3434D-77AD-446A-89B0-C0BB9F968081@microsoft.com...
>>
>>
>> AMB
>>
>> "Mike Labosh" wrote:
>>
>>> Thanks, all, I guess I am just obsessing too much.
>>>
>>> --
>>> Peace & happy computing,
>>>
>>> Mike Labosh, MCSD
>>>
>>> Feed the children!
>>> Save the whales!
>>> Free the mallocs!
>>>
>>>
>>>
>
>

Relevant Pages

  • Re: Compound Primary Key - order not as expected
    ... the physical order on disk may be yet another one. ... data in the order of the clustered index ... This may have been true by chance for SQL Server up version 6.5. ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Checkpoint causes need for better IO subsystem?
    ... maybe it's not the IO and that maybe it's an indexing performance issue. ... Microsoft SQL Server MVP ... is this a monotonically increasing indexing scheme that I've created? ... the Clustered index is on multiple cols... ...
    (microsoft.public.sqlserver.tools)
  • Re: Indexes
    ... because we have a clustered index but I was expecting ... Only one row fit per page, so SQL Server ... You have 100000 rows in the table and 1000 rows fit per index page. ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpoint causes need for better IO subsystem?
    ... excellent articles on clustered index selection and its impact on ... clustered index sql kimberly tripp ... Microsoft SQL Server MVP ... the random write capability of the drives comes into play, ...
    (microsoft.public.sqlserver.tools)
  • Re: Checkpoint causes need for better IO subsystem?
    ... Microsoft SQL Server MVP ... is this a monotonically increasing indexing scheme that I've created? ... Not sure about how to setup a monotonically increasing key. ... the Clustered index is on multiple cols... ...
    (microsoft.public.sqlserver.tools)