Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Clustered Index Internals Expand / Collapse
Author
Message
Posted Wednesday, December 16, 2009 10:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 04, 2014 8:25 AM
Points: 2,602, Visits: 17,845
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
Post #835302
Posted Friday, December 18, 2009 3:06 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:01 PM
Points: 96, Visits: 85
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...
Post #836677
Posted Friday, December 18, 2009 3:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 04, 2014 8:25 AM
Points: 2,602, Visits: 17,845
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.

Chad
Post #836681
Posted Friday, December 18, 2009 3:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 24, 2014 4:01 PM
Points: 96, Visits: 85
Glad to hear the system thinks for its authors...LOL
Post #836682
Posted Monday, December 21, 2009 1:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 6:08 AM
Points: 1,076, Visits: 591
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
Post #837002
Posted Monday, December 21, 2009 6:23 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:36 AM
Points: 1,049, Visits: 861
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.



Post #837679
Posted Monday, February 01, 2010 5:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:32 AM
Points: 11,168, Visits: 10,926
Awful. Just awful.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #857081
Posted Saturday, March 20, 2010 2:45 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 15, 2013 10:35 AM
Points: 594, Visits: 654
"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
Post #886969
Posted Saturday, March 20, 2010 3:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:32 AM
Points: 11,168, Visits: 10,926
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?




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #886974
Posted Saturday, March 20, 2010 4:05 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, March 15, 2013 10:35 AM
Points: 594, Visits: 654
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?


Maybe if the data were not previously PARTITIONED, yes, but an assumption not in the question
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! :)

And, yes, you said that before but I have found it is best just to participate in the discussion without being terribly critical :) 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
Post #886994
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse