How fragmented is the data with MSSQL 2005

  • With Microsoft SQL Server 2000, Microsoft SQL Server 2005 you can check how fragmented is your data with the DBCC SHOWCONTIG see: How fragmented is the data with MSSQL.
  • Microsoft SQL Server 2005 introduced the dynamic management view: SYS.DM_DB_INDEX_PHYSICAL_STATS.
  • SYS.DM_DB_INDEX_PHYSICAL_STATS shows the fragmentation for all the tables in the database being queried.

Applies to:

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Database and tables fragmentation

  1. USE sql911;
  2. GO
  3. SELECT CAST(DB_NAME(DATABASE_ID) AS VARCHAR(20)) AS 'DatabaseName',
  4.        CAST(OBJECT_NAME([OBJECT_ID]) AS VARCHAR(20)) AS 'TableName',
  5.        CAST(INDEX_TYPE_DESC AS VARCHAR(20)) AS INDEX_TYPE_DESC,
  6.        AVG_FRAGMENTATION_IN_PERCENT
  7. FROM   SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID('sql911'),NULL,NULL,NULL,NULL );
  8. GO
[Get Code]
DatabaseName         TableName            INDEX_TYPE_DESC      AVG_FRAGMENTATION_IN_PERCENT
-------------------- -------------------- -------------------- ----------------------------
sql911               EMP_WORK             HEAP                 0
sql911               DEPARTMENT           HEAP                 0
sql911               ORG                  CLUSTERED INDEX      0
sql911               Nums                 CLUSTERED INDEX      0.415183867141162
sql911               sales02              CLUSTERED INDEX      66.6666666666667
sql911               trans01              HEAP                 0
sql911               trans02              HEAP                 0
sql911               HttpLogs             HEAP                 0
sql911               logs                 HEAP                 0
sql911               sales                HEAP                 0
sql911               projects             CLUSTERED INDEX      0
sql911               Emps                 HEAP                 0

(12 row(s) affected)
  • The table Nums has almost no fragmentation.
  • The table sales02 is extremely fragmented.
  • SYS.DM_DB_INDEX_PHYSICAL_STATS will be replacing DBCC SHOWCONTIG. Which is too bad, because DBCC SHOWCONTIG does show a lot more information. See: How fragmented is the data with MSSQL 
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%