||SQL Server Tips by Robin Schumacher
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
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
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%
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
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
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