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:

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-sysdm_db_index_physical_stats.aspx

http://www.simple-talk.com/sql/database-administration/defragmenting-indexes-in-sql-server-2005-and-2008/

http://mssqlserverindia.blogspot.nl/2008/08/understanding-sql-servers-dbcc.html

http://www.mssqltips.com/sqlservertip/1708/index-fragmentation-report-in-sql-server-2005-and-2008/

http://www.simple-talk.com/blogs/2012/07/03/quick-look-at-dm_db_index_physical_stats/

http://selfhandle.com/post/2011/03/12/Analyzing-Index-with-sysdm_db_index_physical_stats.aspx