March 15, 2012 at 12:19 am
Am I right in that when an Index is fragmented, its pages are out of order?
This means that more I/O operations are required to fetch whatever it needs?
What happens to the actual data when an index is fragmented?
Thanks
March 15, 2012 at 4:26 am
derekr 43208 (3/15/2012)
Am I right in that when an Index is fragmented, its pages are out of order?This means that more I/O operations are required to fetch whatever it needs?
Pretty much, yes. Though the 'more I/O operations' only applies to large range scans. Seeks are unaffected.
What happens to the actual data when an index is fragmented?
In what sense?
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
March 15, 2012 at 7:19 am
In what sense?
If an Index is fragmented, does that mean the the actual table data is out of order or does it just mean that the Index data pages are not in order?
Considering that a clustered index is the actual table, does that mean that if it's fragmented, the actual data is not in order of the clustering key.
How does it apply for a Non_clustered index?
March 15, 2012 at 7:26 am
GilaMonster (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?
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.
Maybe I should have worded my question like this:
>>SQL is looking for value 'X'
>>According to the non-leaf index pages, 'X' on leaf page #10
>>SQL goes to find leaf page #10 but because of recent page splits, page #10 is no longer between page #9 and page #11
>>How does SQL go about finding the new location of the page?
Thanks
March 15, 2012 at 7:29 am
derekr 43208 (3/15/2012)
Considering that a clustered index is the actual table, does that mean that if it's fragmented, the actual data is not in order of the clustering key.
For the clustered index, yes
How does it apply for a Non_clustered index?
If a nonclustered index is fragmented then the leaf pages of the nonclustered index are out or order.
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
March 15, 2012 at 7:31 am
derekr 43208 (3/15/2012)
Maybe I should have worded my question like this:>>SQL is looking for value 'X'
>>According to the non-leaf index pages, 'X' on leaf page #10
>>SQL goes to find leaf page #10 but because of recent page splits, page #10 is no longer between page #9 and page #11
>>How does SQL go about finding the new location of the page?
Thanks
Page 10 is by definition between 9 and 11. If it wasn't, it wouldn't be page 10, it's be page 60 or 23 or something else.
As we explained, when a page splits ALL the pointers are updated during the process of the page split, so if SQL comes along after a page split has moved half the rows from page 10 to page 76 and it looks for a row that used to be on page 10 but is now on page 76, the fixed pointer will say that the value is on page 76.
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
March 20, 2012 at 5:16 am
GilaMonster (3/15/2012)
derekr 43208 (3/15/2012)
Maybe I should have worded my question like this:>>SQL is looking for value 'X'
>>According to the non-leaf index pages, 'X' on leaf page #10
>>SQL goes to find leaf page #10 but because of recent page splits, page #10 is no longer between page #9 and page #11
>>How does SQL go about finding the new location of the page?
Thanks
.
Page 10 is by definition between 9 and 11. If it wasn't, it wouldn't be page 10, it's be page 60 or 23 or something else.
As we explained, when a page splits ALL the pointers are updated during the process of the page split, so if SQL comes along after a page split has moved half the rows from page 10 to page 76 and it looks for a row that used to be on page 10 but is now on page 76, the fixed pointer will say that the value is on page 76
Thanks
So, no extra disk reads are required when SQL tries to find a value on a page that has moved because of recent plage splits?
Thanks
March 20, 2012 at 5:22 am
GilaMonster (3/14/2012)
derekr 43208 (3/14/2012)
I'm obviously missing something hereIf 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?
With this in mind - Is it safe to say that when a Clustered Index is fragmented, the affects are much worse than when a Non-Clustered index is fragmented - Considering that the Clustered Index is the actual table data and the Non-Clustered index just contains pointers to where the data is located
Is this right?
Thanks
Derek
March 20, 2012 at 7:37 am
derekr 43208 (3/20/2012)
So, no extra disk reads are required when SQL tries to find a value on a page that has moved because of recent plage splits?
Pages don't move in a page split, some of the rows on a page will be moved to another page and all referencing pointers fixed up.
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
March 20, 2012 at 7:38 am
derekr 43208 (3/20/2012)
With this in mind - Is it safe to say that when a Clustered Index is fragmented, the affects are much worse than when a Non-Clustered index is fragmented - Considering that the Clustered Index is the actual table data and the Non-Clustered index just contains pointers to where the data is located
Generally but not necessarily. Large range scans of a nonclustered index can be badly affected by fragmentation just like large range scans of a clustered index.
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
March 20, 2012 at 7:54 am
GilaMonster (3/20/2012)
derekr 43208 (3/20/2012)
So, no extra disk reads are required when SQL tries to find a value on a page that has moved because of recent plage splits?Pages don't move in a page split, some of the rows on a page will be moved to another page and all referencing pointers fixed up.
Ok, these are the pointers in the non-leaf levels of the Index?
Will extra reads still be incurred?
Thanks
March 20, 2012 at 7:57 am
derekr 43208 (3/20/2012)
GilaMonster (3/20/2012)
derekr 43208 (3/20/2012)
So, no extra disk reads are required when SQL tries to find a value on a page that has moved because of recent plage splits?Pages don't move in a page split, some of the rows on a page will be moved to another page and all referencing pointers fixed up.
Ok, these are the pointers in the non-leaf levels of the Index?
Yes, the pointers from the non-leaf levels of the index downwards, either to a lower non-leaf level or to the leaf level.
Will extra reads still be incurred?
No, no, no. When a page split occurs the affected rows are moved to a new page and ALL pointers that say which page those rows are on are changed. How could there ever be more page reads as a result?
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
March 20, 2012 at 7:58 am
GilaMonster (3/20/2012)
derekr 43208 (3/20/2012)
With this in mind - Is it safe to say that when a Clustered Index is fragmented, the affects are much worse than when a Non-Clustered index is fragmented - Considering that the Clustered Index is the actual table data and the Non-Clustered index just contains pointers to where the data is locatedGenerally but not necessarily. Large range scans of a nonclustered index can be badly affected by fragmentation just like large range scans of a clustered index.
I don't get this because I understand that Fragmentation on an Index has no affect on SQL finding a value in an Index - Right?
I get the Clustered Index part but if the Non-Clustered Index just contains pointers to the data how does it get fragmented?
With a Non-Clustered Index, I can a case where the data that the NC Index is pointing to is fragmented but not the NC Index itself.
Hope this makes sense
Thanks
Derek
March 20, 2012 at 8:01 am
GilaMonster (3/20/2012)
derekr 43208 (3/20/2012)
GilaMonster (3/20/2012)
derekr 43208 (3/20/2012)
So, no extra disk reads are required when SQL tries to find a value on a page that has moved because of recent plage splits?Pages don't move in a page split, some of the rows on a page will be moved to another page and all referencing pointers fixed up.
Ok, these are the pointers in the non-leaf levels of the Index?
Yes, the pointers from the non-leaf levels of the index downwards, either to a lower non-leaf level or to the leaf level.
Will extra reads still be incurred?
No, no, no. When a page split occurs the affected rows are moved to a new page and ALL pointers that say which page those rows are on are changed. How could there ever be more page reads as a result?
Ok, not reads.....
Will there be more I/O operations needed to get whatever its looking for?
Thanks
Derek
March 20, 2012 at 8:03 am
If it is part of a large range scan from disk, maybe.
Emphasis Large, Scan and Disk.
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
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply