Clustered Index Internals

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 Brandon Forest » Clustered Index Internals

41 posts, Page 2 of 5

««1 2 3 4 5 »»»

Clustered Index Internals

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Wednesday, December 09, 2009 7:13 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 10:01 AM
Points: 31,423, Visits: 13,735

If you think something is wrong, please provide a reason and reference. We have not reason to take your work for it over anyone else's.

The third answer has been edited and we are looking for another reference to support the partitioning item.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #831384

honza.mf

Posted Wednesday, December 09, 2009 7:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:14 AM
Points: 1,102, Visits: 1,197

Sorry, I wasn't able to understand the last option. Even after few attempts to translate to some other language.

See, understand, learn, try, use efficient
© Dr.Plch

Post #831386

adrian.buzila

Posted Wednesday, December 09, 2009 7:22 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, April 05, 2013 8:40 AM
Points: 907, Visits: 173

This is wrong:
"A clustered index physically rearanges data on disk."
because it is out of context
and because: "Once created, a clustered index is maintained logically rather than physically."

Post #831394

george sibbald

Posted Wednesday, December 09, 2009 8:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 5,269, Visits: 11,204

jts_2003 (12/9/2009)

This reminded me of something that has been puzzling me recently about clustered indexes. If the leaf level of a clustered index actually is the table's data itself, does this mean the table data only exists in the index, or is there another copy somewhere else?
e.g. if a table without a clustered index has one created, does all the table data get copied into the leaf level of the clustered index, or does it get 'moved'.

as you say the the leaf level of the clustered index is the table data itself so by definition there is only one copy of it. The clustered 'index' consists of the root node, a variable no of intermediate nodes, the last of which points to the actual data as the clustered index controls the logical ordering of the data. Its more accurate to think of the leaf node of the clustered index existing as the table data.

As to the question itself the explanation contradicts the answer as posed:

Explanation: "... all the columns of every row in the table are in the leaf level of a clustered index. At the time a clustered index is created, data in the table is copied and ordered by the clustering key. Once created, a clustered index is maintained logically rather than physically.

therefore the clustered index only dictates physical order at creation or rebuild time, so the options missed an important word, rebuild, as in
A clustered index rebuild physically rearranges data on disk.

so long as the question generates discussion it has fulfilled its purpose. Smile

---------------------------------------------------------------------

Post #831484

Oleg Netchaev

Posted Wednesday, December 09, 2009 9:10 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:53 AM
Points: 1,662, Visits: 1,709

Not that there is anything wrong, but there is no explanation in the answer about the "Dropping and rebuilding a clustered index on a partition scheme may move data to other drives" statement.

The clustered index on a partition scheme has one row in the sys.partitions per partition, each with index_id = 1. Instead of having one, it has as many b-tree structures as the number of partitions. Dropping and rebuilding index on a partition will rearrange the data, but where the moving of the data to other drives come into play? I am not saying that it is not happening, but the QoD does not provide an explanation to this point. In a mean time it would be nice to find it out.

Oleg

Post #831524

adrian.buzila

Posted Wednesday, December 09, 2009 9:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, April 05, 2013 8:40 AM
Points: 907, Visits: 173

I agree that is not wrong, but is false as a general statement. It is out of context.
It is true where you create/rebuilt an index, but false when you insert/update/delete on a table. When you insert/update/delete the order for the existing data is not modified on the disk. The order in this case is mantained logically.

Post #831538

Steve Jones - SSC Editor

Posted Wednesday, December 09, 2009 9:29 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 10:01 AM
Points: 31,423, Visits: 13,735

Actually I'm not sure it's wrong. When you insert/update data you can cause a page split. In that case, the data is rearranged on disk.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #831545

adrian.buzila

Posted Wednesday, December 09, 2009 9:34 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, April 05, 2013 8:40 AM
Points: 907, Visits: 173

If you cause a page split. But if not? It is still false as a general statement.

Post #831553

Source-NH

Posted Wednesday, December 09, 2009 9:39 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 9:07 AM
Points: 1,619, Visits: 473

I am very interested in how the rebuilding of a clustered index would result in data being moved to another partition. The partition of a particular record in a table is determined through a calculation based on data in the record itself. Unless either the data or the partition function are changed, than a given record will always be placed into the same partition that it was previously placed in. If this is an accurate statement, than an index rebuild should NEVER result in data being moved into a different partition.

Also, the doubly linked list would imply that there are pointers on every page to the previous and next pages in the table. I have NEVER read anywhere in any documentation that this exists. As far as I knew, the IAM pages contained the bit maps providing the allocation of pages to a table. Can anyone point me to documentation that describes this linked list?

Post #831559

SQLRNNR

Posted Wednesday, December 09, 2009 10:10 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

There have been a few concerns regarding the moving of data to a different drive if you rebuild an index in a partition scheme.

I want to provide a scenario to support the "may" in this option.

When partitioning a table, you create a filegroup for each partition. Once created files may be created for each filegroup. It may be necessary for each file to be assigned to a different drive. Let's say the table is 300GB and spread across 2 partitions and filegroups. Each of these filegroups has 10 files spread across 10 drives - that means each file is roughly 15GB, if we have partitioned the data down the middle (it's easier for this scenario).

Each partition data will remain in that partition when a rebuild occurs, however, due to the rebuild we have a scenario where the data could now potentially move from one file to another in the filegroup and thus be on a different drive.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #831591

« Prev Topic | Next Topic »

41 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions