Index Fragmentation

Question

Index Fragmentation

Viewing 15 posts - 16 through 30 (of 51 total)

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 1

GilaMonster (11/10/2011)

derekr 43208 (11/9/2011)
So when a NC index is created, its leaf pages match the order of the data pages
No, no, no. Nothing whatsoever to do with the data pages. When an index is created SQL tries to write the index's leaf pages out in the order of the index key columns.

And this Process is the same whether the Index is Clustered or not

Fragmentation happens when the order of the index pages don't match the order of the data pages
Fragmentation happens when the leaf pages of an index don't match the key order of that index.

By Key, this is the Data in the columns specified in the index definition

Thanks

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

derekr 43208 (11/10/2011)
GilaMonster (11/10/2011)
derekr 43208 (11/9/2011)
So when a NC index is created, its leaf pages match the order of the data pages
No, no, no. Nothing whatsoever to do with the data pages. When an index is created SQL tries to write the index's leaf pages out in the order of the index key columns.
And this Process is the same whether the Index is Clustered or not
Yup.
Fragmentation happens when the order of the index pages don't match the order of the data pages
Fragmentation happens when the leaf pages of an index don't match the key order of that index.
By Key, this is the Data in the columns specified in the index definition

Yup.

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 3

GilaMonster (11/10/2011)
derekr 43208 (11/10/2011)
GilaMonster (11/10/2011)
derekr 43208 (11/9/2011)
So when a NC index is created, its leaf pages match the order of the data pages
No, no, no. Nothing whatsoever to do with the data pages. When an index is created SQL tries to write the index's leaf pages out in the order of the index key columns.
And this Process is the same whether the Index is Clustered or not
Yup.
Fragmentation happens when the order of the index pages don't match the order of the data pages
Fragmentation happens when the leaf pages of an index don't match the key order of that index.
By Key, this is the Data in the columns specified in the index definition
Yup.

Great

Thanks

I think most of my confusion comes in with the naming conventions - Key, pages, extents etc

SQLSACT SSC-Insane Points: 21838 More actions · Answer 4

GilaMonster (11/10/2011)
derekr 43208 (11/10/2011)
GilaMonster (11/10/2011)
derekr 43208 (11/9/2011)
So when a NC index is created, its leaf pages match the order of the data pages
No, no, no. Nothing whatsoever to do with the data pages. When an index is created SQL tries to write the index's leaf pages out in the order of the index key columns.
And this Process is the same whether the Index is Clustered or not
Yup.
Fragmentation happens when the order of the index pages don't match the order of the data pages
Fragmentation happens when the leaf pages of an index don't match the key order of that index.
By Key, this is the Data in the columns specified in the index definition
Yup.

One more thing, is this whole process the same as Physical Fragmentation?

Also, is this correct - Internal Fragmentation = index pages too empty(Fill Factor), causing more required reads to get to the data

External Fragmentation: Index leaf pages not in same order as Index Key data.

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

Depends. The terminology around this varies. I've seen this called physical fragmentation, I've seen that term used for file system fragmentation.

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

GilaMonster (11/11/2011)
Depends. The terminology around this varies. I've seen this called physical fragmentation, I've seen that term used for file system fragmentation.

thanks

SQLSACT SSC-Insane Points: 21838 More actions · Answer 7

Hi

A question regarding fragmentation

If SQL looks for a value and doesn't find it where it's supposed to be because of fragmentation. Does SQL then have to go through all index pages until it finds what its looking for?

Thanks

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

Fragmentation will never cause SQL to not find a value where it's supposed to be (according to the index tree). If a value is not where it's supposed to be within an index, that's a high-severity corruption error message.

Fragmentation just causes large range scans from disk to be slower than they could be.

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 9

GilaMonster (3/14/2012)
Fragmentation will never cause SQL to not find a value where it's supposed to be (according to the index tree). If a value is not where it's supposed to be within an index, that's a high-severity corruption error message.
Fragmentation just causes large range scans from disk to be slower than they could be.

I think I worded my question incorrectly

Ok

>> SQL is looking for value 'X'

>> According to the non-leaf index pages, 'X' on leaf page #10

>> SQL goes to leaf page #10 but the value is not there because of recent page splits

>> How does SQL go about finding value 'X'

Will SQL have to go through every leaf page until it finds the value?

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

derekr 43208 (3/14/2012)
GilaMonster (3/14/2012)
Fragmentation will never cause SQL to not find a value where it's supposed to be (according to the index tree). If a value is not where it's supposed to be within an index, that's a high-severity corruption error message.
Fragmentation just causes large range scans from disk to be slower than they could be.
I think I worded my question incorrectly
Ok
>> SQL is looking for value 'X'
>> According to the non-leaf index pages, 'X' on leaf page #10
>> SQL goes to leaf page #10 but the value is not there because of recent page splits
>> How does SQL go about finding value 'X'
Will SQL have to go through every leaf page until it finds the value?
Thanks

Same answer Gail provided before. If a page split occurs at the leaf page, this has to be propagated back up the index tree.

SQLSACT SSC-Insane Points: 21838 More actions · Answer 11

Lynn Pettis (3/14/2012)
derekr 43208 (3/14/2012)
GilaMonster (3/14/2012)
Fragmentation will never cause SQL to not find a value where it's supposed to be (according to the index tree). If a value is not where it's supposed to be within an index, that's a high-severity corruption error message.
Fragmentation just causes large range scans from disk to be slower than they could be.
I think I worded my question incorrectly
Ok
>> SQL is looking for value 'X'
>> According to the non-leaf index pages, 'X' on leaf page #10
>> SQL goes to leaf page #10 but the value is not there because of recent page splits
>> How does SQL go about finding value 'X'
Will SQL have to go through every leaf page until it finds the value?
Thanks
Same answer Gail provided before. If a page split occurs at the leaf page, this has to be propagated back up the index tree.

Ok

So if a plage split occurs, in basic terms the non-leaf pages knows about it?

SQLSACT SSC-Insane Points: 21838 More actions · Answer 12

Lynn Pettis (3/14/2012)
derekr 43208 (3/14/2012)
GilaMonster (3/14/2012)
Fragmentation will never cause SQL to not find a value where it's supposed to be (according to the index tree). If a value is not where it's supposed to be within an index, that's a high-severity corruption error message.
Fragmentation just causes large range scans from disk to be slower than they could be.
I think I worded my question incorrectly
Ok
>> SQL is looking for value 'X'
>> According to the non-leaf index pages, 'X' on leaf page #10
>> SQL goes to leaf page #10 but the value is not there because of recent page splits
>> How does SQL go about finding value 'X'
Will SQL have to go through every leaf page until it finds the value?
Thanks
Same answer Gail provided before. If a page split occurs at the leaf page, this has to be propagated back up the index tree.

I'm obviously missing something here

If the non-leaf pages are aware of the page split at the leaf pages then it knows the new location of the Value it's looking for. So then why would this cause performance degradation if the non-leaf pages is still able to tell SQL where the value is.

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

derekr 43208 (3/14/2012)
GilaMonster (3/14/2012)
Fragmentation will never cause SQL to not find a value where it's supposed to be (according to the index tree). If a value is not where it's supposed to be within an index, that's a high-severity corruption error message.
Fragmentation just causes large range scans from disk to be slower than they could be.
I think I worded my question incorrectly
Ok
>> SQL is looking for value 'X'
>> According to the non-leaf index pages, 'X' on leaf page #10
>> SQL goes to leaf page #10 but the value is not there because of recent page splits
>> How does SQL go about finding value 'X'
Will SQL have to go through every leaf page until it finds the value?

Same answer applies

If SQL is looking for X and the non-leaf levels say that it is on page 10, then it is on page 10 or it does not exist at all. If it's anywhere else that is a critical database corruption, severity 24, connection terminated immediately.

When a page splits SQL fixes all the pointers before it releases any locks or latches, so just like you can't see an insert at the point that half of the columns are written, you can't see a page split half way through.

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

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

derekr 43208 (3/14/2012)
I'm obviously missing something here
If the non-leaf pages are aware of the page split at the leaf pages then it knows the new location of the Value it's looking for. So then why would this cause performance degradation if the non-leaf pages is still able to tell SQL where the value is.

The performance degradation from fragmentation has nothing whatsoever to do with the ability of SQL to find a value within an index. It is solely about the efficiency of large range scans from disk. Emphasis Large and Disk

If SQL has to scan a table (let's say it has 1000 pages, 8MB in total) and no portion of that table is in memory it has to first be read from disk. If the table is not fragmented that can be done as around 8 IO operations that fetch 1MB each (around, this varies per version and edition). If the table is badly fragmented that read from disk could take up to 1000 separate IO requests.

Which do you think is faster, 8 IO operations that fetch 1MB each or 1000 IO operations that fetch 8kb each?

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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 15

The performance degradation from fragmentation has nothing whatsoever to do with the ability of SQL to find a value within an index. It is solely about the efficiency of large range scans from disk. Emphasis Large and Disk

Thanks

That's the fundamental part that I missed....