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


Clustered Index Internals


Clustered Index Internals

Author
Message
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4369 Visits: 18732
I thought that SQL Server maintained a "logical file", but the OS maintained the "physical file". If you start with a small mdf file that grows in small chunks on a heavily used disk, each time the file grows, it would grab a piece of disk that was available, but that new piece might not be physically contiguous with the last piece, and might actually be before any of the other parts of the file or intermixed with others (I'm thinking about disk fragmentation here). SQL Server would see one long continuous "logical file", but at the physical disk level, it would actually be spread all over the disk. Because of this, a clustered index would be logically ordered, but might not be physically ordered on the disk. I'm using the terms logical and physical file loosely here (not really sure what term is most appropriate to describe what I'm thinking).

If it didn't work this way, every time your mdf file grew, it might have to rebuild some of your clustered indexes to make sure they were physically ordered on disk, right?

I know my technical jargon might be a little off, but am I correct on the concept? I always thought that saying clustered indexes were "ordered on disk" was a good way to think about and understand the concept, but was not always completely technically true when you got down to the nuts-n-bolts.

Thanks,
Chad
macrostarrphish
macrostarrphish
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 155
OK, so I didn't think the second statement was true, and apparantly it is...

That isn't my problem with the question, my problem is (as stated before) authors don't know how to write good questions and don't know how to create the right answer sets. This should have been a radio button choice set with all of the above as the last answer. You should NEVER give multi-select answer set where all are the correct choice. The should be two qualifiers for multi-select:

1.) there should be at least one false answer
2.) there should be a qualifier, such as "choose three" (of four or five answers).

Obviously choose three would give away the answer here, since there was only three choices. I am assuming the point of these excercises isn't to fool the reader but rather to educate, thus you are not trying to confuse or trick the user by not giving them a clue as to how many answers are you expecting them to select.

Other things to keep in mind:

3.) if you are going to use radios and two choices are "all of the above" and "none of the above," make sure "all of the above" is placed above "none of the above", as the reverse would make "all of the above" false by default.

CORRECT WAY OF WRITING THIS ANSWER SET
a.) true statement
b.) true statement
c.) true statement
d.) all of the above <-- still true
e.) none of the above <-- could be true, doesn't negate answer d.

INCORRECT WAY OF WRITING THIS ANSWER SET
a.) true statement
b.) true statement
c.) true statement
d.) none of the above <-- could be true, however negates answer e.
e.) all of the above <-- this can NEVER be true

4.) True/false statements should try and avoid ALWAYS and NEVER, as there is most likely an exception to the rule. If using ALWAYS or NEVER, make sure it is a 100% of the time case, or the statement is bad/false. [NOTE: Generally speaking, when taking a test and you see ALWAYS or NEVER on a T/F question, you should pick false, unless you can make a 100% case qualifying the statement...]

5.) Don't use multi-select if only one answer is to be selected, change answer set to radio buttons.

Please, try and use this as a guide to writing better questions...this isn't everything we used to enforce our authors to create a good set of learning materials, but it is a start.

I worked for an internal learning network for three years and we had very strict guidelines on how/what/when a properly worded question could go into our system. "Fluff" answer (answers that are obviously wrong) like "Donald Duck" when asking presidents of the US, are frowned upon as well, for education purposes. [Note: unless your point is to lighten the mood by making an obvious joke - we all need some humor in our lives]

Thanks for listening...
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4369 Visits: 18732
macrostarrphish (12/18/2009)
5.) Don't use multi-select if only one answer is to be selected, change answer set to radio buttons.


Actually... this is how it works by default. The person putting the question in indicates which answers are correct. If there is only one, it uses radio buttons. If there are more than one, it uses checkboxes... I've used this small tidbit of information to my advantage more than once. :-D

Chad
macrostarrphish
macrostarrphish
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 Visits: 155
Glad to hear the system thinks for its authors...LOL
BudaCli
BudaCli
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 598
ChiragNS (12/9/2009)
"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." - Very confusing.


I think you misunduerstood what the statement said but if you were to look at it again...
"Dropping a rebuilding a clustered index on a partition scheme MAY move disk to other drives."

This is not going to be something that will happen always but sometimes...

What you don't know won't hurt you but what you know will make you plan to know better
RichB
RichB
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3020 Visits: 1065
duda (12/21/2009)
ChiragNS (12/9/2009)
"Dropping a rebuilding a clustered index on a partition scheme will move disk to other drives." - Very confusing.


I think you misunduerstood what the statement said but if you were to look at it again...
"Dropping a rebuilding a clustered index on a partition scheme MAY move disk to other drives."

This is not going to be something that will happen always but sometimes...


This was how the question was originally phrased - hence most of the thread.



Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35594 Visits: 11361
Awful. Just awful.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1064 Visits: 655
"Dropping and rebuilding a clustered index on a partition scheme may move data to other drives. " ?

Really? How can that be? The idea of the partition is to locate data in a particular filegroup based on the data in a particular column, right? And the purpose of using the filegroups is to allow the partitioned data to be on different drives, for performance or organizational reasons, correct? So if the data has not changed, why would it be moved to another drive?

If this is true, it really damages what I thought I understood about partition schemes... but that is why I come here, to learn these nuances :-)

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35594 Visits: 11361
Peter Trast (3/20/2010)
"Dropping and rebuilding a clustered index on a partition scheme may move data to other drives. " ?

Really? How can that be? The idea of the partition is to locate data in a particular filegroup based on the data in a particular column, right? And the purpose of using the filegroups is to allow the partitioned data to be on different drives, for performance or organizational reasons, correct? So if the data has not changed, why would it be moved to another drive?

If this is true, it really damages what I thought I understood about partition schemes... but that is why I come here, to learn these nuances :-)

I suppose it is just possible that the clustered index was not partitioned to begin with. Depends how you read it, I guess ;-)
Terrible article. Did I mention that already? :-D



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1064 Visits: 655
Paul White NZ (3/20/2010)
Peter Trast (3/20/2010)
"Dropping and rebuilding a clustered index on a partition scheme may move data to other drives. " ?

Really? How can that be? The idea of the partition is to locate data in a particular filegroup based on the data in a particular column, right? And the purpose of using the filegroups is to allow the partitioned data to be on different drives, for performance or organizational reasons, correct? So if the data has not changed, why would it be moved to another drive?

If this is true, it really damages what I thought I understood about partition schemes... but that is why I come here, to learn these nuances :-)

I suppose it is just possible that the clustered index was not partitioned to begin with. Depends how you read it, I guess ;-)
Terrible article. Did I mention that already? :-D


Maybe if the data were not previously PARTITIONED, yes, but an assumption not in the question Cool
The clustered index existed before as stated in the question so if both the clustered index and partition scheme were in place previously, data would not move.... anyone say different, please explain... please! Smile

And, yes, you said that before but I have found it is best just to participate in the discussion without being terribly critical Smile Point out specific errors, yes, but never generalize, as in using terms like "terrible", just hurts feelings. Everyone gets a chance to be incorrect now and then. After all, "it depends" is the number one phrase that I associate with SQL...

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
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