A few days ago I got a question about the ideal value for “Extent Scan Fragmentation”. That is why I decided to write a post on it. First of all for the ones that have no idea what “Extent Scan Fragmentation” is, it is one of the values returned when running DBCC Showcontig(http://msdn.microsoft.com/en-us/library/aa258803(v=sql.80).aspx ). The answer to the question is given by Microsoft (http://technet.microsoft.com/en-us/library/ms175008.aspx) “the value should be as close to zero as possible, although a value from 0 to 10 percent may be acceptable”. Somethings that are very important to keep in mind are:
1- when the index spans multiple files this value is meaningless.
2- and it also does not apply to heaps.
If you still using DBCC Showcontig, I will advise you start using “sys.dm_db_index_physical_stats”( http://technet.microsoft.com/en-us/library/ms188917.aspx). For further reading a will recommend the following links: