Index Key Re-Ordering

Question

Index Key Re-Ordering

Arsh

SSCertifiable

Points: 6354
More actions
November 17, 2017 at 3:21 am

#329612

Dear Experts,
Please advise when does the re-ordering of a index pages occur when a key value is updated. Does it happen immediately after or when the index is rebuilt ? Say , if the index had on column storing numbers as 1,2,3,4...10 . If 4 is updated to store 11, when does the reordering occur ? Is it immediately after the update statement commits or with a rebuild ? Thank you...

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Arsh - Friday, November 17, 2017 3:21 AM
Dear Experts,
Please advise when does the re-ordering of a index pages occur when a key value is updated. Does it happen immediately after or when the index is rebuilt ? Say , if the index had on column storing numbers as 1,2,3,4...10 . If 4 is updated to store 11, when does the reordering occur ? Is it immediately after the update statement commits or with a rebuild ? Thank you...

Check this Does a Clustered Index really physically store the rows in key order?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Arsh - Friday, November 17, 2017 3:21 AM
Dear Experts,
Please advise when does the re-ordering of a index pages occur when a key value is updated.

Never.
If a row has to move within an index, the update is split into a delete/insert pair and executed as such.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arsh SSCertifiable Points: 6354 More actions · Answer 3

GilaMonster - Friday, November 17, 2017 5:31 AM
Never.
If a row has to move within an index, the update is split into a delete/insert pair and executed as such.

Thanks Gail. So , it implies that after the delete is done, that location becomes part of fragmentation and the 'insert' will happen at the end .Please correct if wrong.Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Arsh - Friday, November 17, 2017 7:44 AM
GilaMonster - Friday, November 17, 2017 5:31 AM
Never.
If a row has to move within an index, the update is split into a delete/insert pair and executed as such.
Thanks Gail. So , it implies that after the delete is done, that location becomes part of fragmentation and the 'insert' will happen at the end .Please correct if wrong.Thanks.

That's not how a delete and insert are done in an index, so no. Nothing different here than a normal delete and normal insert, except that it's done internally as part of the update, and it's not visible to the user whether the update is in-place or split

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101117 More actions · Answer 5

The key thing to remember is this:
Data does not have to stored in physical sequence in order to be able to read in key sequence.

That is, the data must logically be in sequence, but not necessarily physically. All that is required is that SQL has the proper hgh-level index values and record chains to read the data in sequence if/as needed.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Arsh SSCertifiable Points: 6354 More actions · Answer 6

Thanks Scott and Gail. I think I got it .So It's not the actual data but the pointers to the data (cluster key contents ) that are in sequence in the index pages.

Makes sense .. otherwise a physical re-ordering would take a month while rebuilding an index that's takes an hour with the pointers' reordering , I guess ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

An index rebuild *is* a physical reordering. It creates a new index and drops the old one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arsh SSCertifiable Points: 6354 More actions · Answer 8

GilaMonster - Friday, November 17, 2017 1:49 PM
An index rebuild *is* a physical reordering. It creates a new index and drops the old one.

Thanks Gail. So it means until a rebuild is done , the data in the pages in not necessarily in the physical order and the data is much like unordered data ? thank u.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

No. The data is ordered by the index key. Logically ordered, but still ordered.
The data doesn't have to be physically in key order on each page, and the pages don't have to be in physical index order, but the index is still ordered by the index key.

I suggest you get a copy of SQL Server 2012 Internals and read the chapter on index architecture.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Arsh SSCertifiable Points: 6354 More actions · Answer 10

GilaMonster - Monday, November 20, 2017 5:32 AM
No. The data is ordered by the index key. Logically ordered, but still ordered.
The data doesn't have to be physically in key order on each page, and the pages don't have to be in physical index order, but the index is still ordered by the index key.
I suggest you get a copy of SQL Server 2012 Internals and read the chapter on index architecture.

Thank you Gail for sharing knowledge.