Monitoring Databases  «Prev 

dbcc showcontig output

DBCC SHOWCONTIG scanning 'Customers' table...

Table: 'Customers' (213575799); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 3

- Extents Scanned..............................: 2

- Extent Switches..............................: 1

- Avg. Pages per Extent........................: 1.5

- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 50.00%

- Avg. Bytes Free per Page.....................: 244.7

- Avg. Page Density (full).....................: 96.98%

  1. Pages Scanned – Number of pages in the table or index that contain data.
  2. Extents Scanned – Number of extents in the table or index (an extent is 8 pages).
  3. Extent Switches – Number of times the dbcc statement switch extents.
  4. Avg. Pages per Extent – Number of pages per extent in the page chain.
  5. Scan Density [Best Count: Actual Count] – Best count is the ideal number of extent changes for a completely contiguous table. Actual count is the actual number of extent changes. The number in scan density is 100 if everything is contiguous; if it is less than 100, some fragmentation exists. Scan density is a percentage.
  6. Logical Scan Fragmentation – The percentage of out-of-order pages returned from scanning the leaf pages of an index.
  7. Extent Scan Fragmentation – The percentage of out-of-order extents in scanning the leaf pages of an index.
  8. Avg. Bytes free per page – Average number of free bytes on the pages scanned.
  9. Avg. Page density (full) – Average page density (as a percentage).

Note: ALTER INDEX REORGANIZE and ALTER INDEX REBUILD commands are equivalent to DBCC INDEXDEFRAG and DBCC DBREINDEX respectively. The sys.dm_db_index_physical_stats dynamic management function replaces DBCC SHOWCONTIG. It is recommended to stop using DBCC INDEXDEFRAG, DBCC DBREINDEX, and DBCC SHOWCONTIG because they will be removed in a future version of Microsoft SQL Server.
The following commands defrag both indexes: 
USE tempdb;
ALTER INDEX PK_Frag ON Frag REORGANIZE;
ALTER INDEX ix_col ON Frag REORGANIZE;
A sys.dm_db_index_physical_stats dynamic management function examines the index structure after defragmenting the index. Both the logical-fragmentation and pagedensity problems created by the insertion of one-half million rows are resolved: 
USE tempdb;
GO
SELECT * FROM sys.dm_db_index_physical_stats ( db_id('tempdb'),
object_id('Frag'), NULL, NULL, 'DETAILED');
GO
Result: 
index_id: 1
index_type_desc: CLUSTERED INDEX
avg_fragmentation_in_percent: 0.559173738569831
page count: 15201
avg_page_space_used_in_percent: 99.538930071658

index_id: 2
index_type_desc: NONCLUSTERED INDEX
avg_fragmentation_in_percent: 1.23915737298637
page count: 1614
avg_page_space_used_in_percent: 99.487558685446

High Performance SQL Server