SQL Server Tips by Robin Schumacher

Diagnosing Object Fragmentation

If there is an environment that will benefit from reducing fragmentation, how does the DBA go about rectifying the matter? There are a number of metrics and factors to consider before one can intelligently start creating reorganization plans.

Prior to SQL Server 2005, Microsoft’s main diagnostic weapon to help uncover object fragmentation was the DBCC SHOWCONTIG command. In SQL Server 7.0, the command is not easy to view for large numbers of objects because the output is very text-based. In SQL Server 2000 and higher, the command is easier to use in the DBCC. In SQL Server 2005, Microsoft has introduced a new function that makes it even simpler to query for fragmentation issues.

When using DBCC SHOWCONTIG to diagnose fragmentation in SQL Server 7 – 2000, particular attention should be paid to the following metrics:

• Extent Scan Fragmentation: This highlights any gaps or lack of contiguous order in extents and indicates the presence of external fragmentation. Percentages of 25-50% or more are not a favorable reading.

• Logical Scan Fragmentation: This metric represents the percentage of pages that are out of physical order. Values greater than 25% for this metric may mean that index scan performance is not what it could be. This statistic is meaningless for tables without clustered indexes (heap tables).

• Average Page Density: This metric represents the fullness of the data or index pages, and is an indicator of internal fragmentation. The more full a data or index page is, the less I/O needed by SQL Server when performing scans of tables or indexes. High values are good here, with anything below 50% being suspect. Low values for this metric often indicate the presence of page splitting. Internal fragmentation is not necessarily bad in OLTP environments where large fillfactors are often specified. Also, small objects, those will little or no data, will likely show low readings because they simply do not consume enough space in a database page. These objects can be ignored.

• Extent Switches: In a perfect world, this will equal the number of object extents minus one. Higher values, many times higher than an object’s extent count, can indicate external fragmentation.

• Scan Density: This is computed by dividing the optimum number of extent switches by the actual number of extent switches. This percentage can be misleading, however, if the object spans more than one database file and should not be considered in such an event. Values close to 100% are best.
  In SQL Server 2005, a new function called sys.dm_db_index_physical_stats is available for use in diagnosing index fragmentation. More information regarding how this new function call is used later, but the following are the output columns from the function on which focus should be placed:

• avgfragmentation: This is the logical fragmentation percentage, which takes into account multiple files. Microsoft’s rule of thumb is that any index with a score over 30 is a candidate for a rebuild/reorganization.

• fragments: This is the number of physically consecutive leaf pages in the index.

• pages: This is the number of data pages.

• avgpagefullness: This describes how full the pages in the table/index are.

• avgfragmentsize: This is the average number of pages in one fragment of the index. Larger numbers are better here.

As seen above, the DBA cannot just blindly diagnose fragmentation in the SQL Server, but instead needs to evaluate a number of individual metrics.

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm