DBCC Showcontig and the ideal Extent Scan Fragmentation Value

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:










Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s