Database Reference
In-Depth Information
Figure 18-12.
Checking fragmentation with
DBCC ShowContig()
The resulting fragmentation report indicates that the number of pages the Orders table is currently using is
20. The current number of extents used is five.
For all the data in the table to be accessed, SQL Server would have to switch four times between the various
extents. This is indicated by the Extent Switches value of 4.
The Avg. Pages per Extent value is therefore 4, derived by taking the number of pages (20) and dividing by the
extents (5).
In a perfect world, the pages would be contiguous and held within just three extents, but the actual count is
currently five. This is noted in the report as Scan Density Best Count: Actual Count.
When administrators examine a report similar to the one shown in Figure
18-12
, they would know that the
table is fragmented at the extent level and would run a SQL command similar to the one shown in Figure
18-13
.
This command rebuilds tables and indexes internally, attempting to fit the data into as few data pages and extents
as it can.
