Using Db Maint, Working with…
Fragmentation report
To open this dialog:
- Right-click on a database in the “Databases” folder and select “Display”, “Tables” and “Sort by name” or “Sort by rows”.
- Right-click on the desired table and select “Show indexes”.
- Right-click the desired index and select “Show fragmentation”.
If you want to see fragmentation level for several indexes at the same time,
use the View fragmentation information dialog.
Note that on SQL Server 7.0 and 2000, the first 8 pages for each index are
placed on mixed extents. A mixed extent can contain pages from several indexes.
Hence, it is not meaningful to talk about fragmentation for small indexes. Also,
if the database (filegroup) is placed over several files, use the “Logical scan fragmentation” instead of “Scan Density” (since jumps between files are considered an extent switch).
This dialog presents the fragmentation level for the selected index. Db Maint
executes the DBCC SHOWCONTIG statement to get this information. A general
overview of how full each page is in average and how many times SQL Server needs to
jump between extents to perform a scan along the leaf level of the index is
presented.
This dialog also presents the full output from the DBCC SHOWCONTIG statement.
See SQL Server’s Books Online for information on how to interpret this.