Problem solve Get help with specific problems with your technologies, process and projects.

Identifying external or internal table fragmentation

This article describes how to identify tables that are fragmented internally (ie, fragmentation inside data pages) or externally (ie, extents are fragmented).

This article describes how to identify tables that are fragmented internally (i.e., fragmentation inside data pages) or externally (i.e., extents are fragmented).

The process of doing a DBCC re-index of a large database can be a time-consuming task. In most cases the fragmentation occurs in just a part of the database and is concentrated in just a few tables. Re-indexing just those tables should be sufficient and thus precious time and computer resources can be saved.

Here I describe the way to identify the fragmented tables. I use the DBCC SHOWCONTIG T-SQL statement as a basis to gather information about all user tables in the database that have a CLUSTERED index. I assume here that fragmentation in the clustered index would lead to fragmentation in all other indexes since the cluster index is used as the "cluster key" with all other non-clustered indexes that are built upon it.

I use DBCC SHOWCONTIG with the TABLESRESULT clause in order to query the resulting results table to list all the table names that have either internal fragmentation that falls below a given acceptable scan density percentage or external fragmentation that is greater than a given extent fragmentation percentage. The resulting tables should be defragmented using re-index or index defrag.

Here is the procedure code for identifying fragmented tables. The procedure should be compiled inside the database to be re-indexed.

Create procedure sp_list_fragmented_tables 
  ( @internalFragmentationPctAllowed int,
    @externalFragmentationPctAllowed int )
  Declare @showContigStmt varchar(100)
  Declare @CurrTable         varchar(50)

/* Cursor declared on USER tables that have A clustered index */

  DECLARE c_examined_tables
        cursor for select a.table_name from 
        information_schema.tables a,sysindexes b 
        where             a.table_type = 'BASE TABLE' and 
                a.table_name = object_name ( and 
                b.indid = 1 
  set noCount on 

/* create Result table as a temporary table */   

  Create table #showContigResults 
        (ObjectName sysname,
         Objectid bigint,
         IndexName sysname,
         indexid int,
         [level] int,
         pages int ,
         [rows] bigint,
         minRecsize int,
         maxRecsize int,
         avgRecSize real ,
         ForwardRecs int,
         Extents int,
         ExtentSwitches int,
         AvgFreeBytes real,
         AvgPageDensity real,
         ScanDensity decimal(5,2), 
         BestCount int,
         ActCount int,
         LogicalFrag decimal (5,2), 
         ExtentFragmentation decimal (5,2)) 
  /* loop over all tables and exec DBCC SHOWCONTIG with TABLERESULTS format */

  OPEN c_examined_tables
  FETCH NEXT FROM c_examined_tables INTO @CurrTable
     set @showContigStmt = 'DBCC SHOWCONTIG ([' + @currTable + '])' + 
                ' with tableresults'
     Insert  #showContigResults exec (@showContigStmt)
     FETCH NEXT FROM c_examined_tables INTO @CurrTable

  close c_examined_tables
  deallocate c_examined_tables

 /* output fragmented objects that fall within the criteria */
  select ObjectName ,  ScanDensity , ExtentFragmentation 
  from #showContigResults
  where ScanDensity < @internalFragmentationPctAllowed or 
        ExtentFragmentation > @externalFragmentationPctAllowed

Here is an example of a call to the function:

/* list only  tables that have less than 50% SCAN density (internal
   fragmentation) and more than 10% Extent fragmentation (external
   fragmentation) */

exec sp_list_fragmented_tables 50,10

The process I showed here can be used as a tool for identifying fragmented tables and focusing de-fragmentation or re-indexing maintenance tasks just to those objects and thus reduce the time- and resource-intensive job of re-indexing all the tables in a large database.

About the author

Eli Leiba works at the Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. His email is

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.