How fragmented is the data with MSSQL

  • One of the biggest problem related to performance is data fragmentation.
  • But how fragmented is your data?
  • Microsoft SQL Server has a tool: DBCC SHOWCONTIG, to tell you that.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005

Data fragmentation

  1. USE sql911;
  2. go
  3. DBCC showcontig("sales02") WITH NO_INFOMSGS ;
  4. go
  5. PRINT ' ';
  6. PRINT '***************************************';
  7. DBCC showcontig("nums") WITH NO_INFOMSGS ;
  8. go
DBCC SHOWCONTIG scanning 'sales02' table...
Table: 'sales02' (309576141); index ID: 1, database ID: 10
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 ..................: 66.67%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 7616.0
- Avg. Page Density (full).....................: 5.91%

***************************************
DBCC SHOWCONTIG scanning 'Nums' table...
Table: 'Nums' (117575457); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 1686
- Extents Scanned..............................: 213
- Extent Switches..............................: 212
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 99.06% [211:213]
- Logical Scan Fragmentation ..................: 0.42%
- Extent Scan Fragmentation ...................: 0.47%
- Avg. Bytes Free per Page.....................: 10.9
- Avg. Page Density (full).....................: 99.87%
  • I am running on 2 tables to explain the output of dbcc showcontig
  • The sales02 table is very fragmented.
    1. Avg. Pages per Extent: 1.5 out of 8. An extent is made of 8 pages, only 1.5 is used.
    2. Avg. Page Density (full): 5.91% Each page has only 6% of data or index, meaning that each page is 94% empty.
  • But this is OK!, it's very fragmented because the table is so small.
  • The nums table has no fragmentation.
    1. Avg. Pages per Extent: 7.9 out of 8. An extent is made of 8 pages, 7.9 are used.
    2. Avg. Page Density (full): 99.8%, meaning that the data is very compact, SQL Server will do a lot less read to access that data.