August 15, 2005 at 6:26 am
Greetings,
As part of a daily monitoring job, I report on fragmentation. I ignore most of these results except on Monday's when the bulk of the optimisation jobs have run the previous day. But I get indifferent results with regards the "Extent Scan Fragmentation".
For example:
DBCC SHOWCONTIG ('AssessedBenefit')
PRINT '-------------------------------------------------------------'
PRINT '-------------------------------------------------------------'
DBCC DBREINDEX(AssessedBenefit)
--DBCC INDEXDEFRAG(Abacus, AssessedBenefit,AssessmentID)
PRINT '-------------------------------------------------------------'
PRINT '-------------------------------------------------------------'
DBCC SHOWCONTIG ('AssessedBenefit')
The output then fluctuates, for example the first output may be ~15%, then the second ~80%. Re-executing the script then reports ~80%, then ~13%??
It bascially ossilates with one value tending to 100%, the other 0%????
Any explanation would be gratefully appreciated!
Kind Regards,
Dan
August 16, 2005 at 1:45 pm
It has been my experience that the Extent Scan Fragmentation is less relevant and less reliable than Logical Scan Fragmentation. The statistics I look at are Scan Density and Average Page Density. I've seen cases where a recently rebuilt index shows a high Extent Scan Fragmentation but everything else looks fine.
From BOL:
| Logical Scan Fragmentation | Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page. |
| Extent Scan Fragmentation | Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent containing the current page for an index is not physically the next extent after the extent containing the previous page for an index. |
HTH
Dylan Peters
SQL Server DBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply