Get index fragmentation statistics

I recently attended a Pittsburgh SQL Server user group meeting where Brent Ozar gave a presentation on the silent performance killer.  This motivated me to create a stored procedure that could leverage the DMVs in SQL 2005/2008 to gather index fragmentation statistics for all databases on a given server.


The goal is very simple.  Build a query that could be scheduled to grab statistics that are helpful towards determining if an index needs to be defragged or reorganized.  I would like to throw these results into a table so I could analyze them at a later date.  I would also like to monitor the fill factor and padding to determine if I need to make changes and to analyze if the changes are really helpful.

Download Scripts

The following script uses the following DMV’s sys.dm_db_index_physical_stats, sys.objects and sys.indexes and this script is provided as is.

To download the script click here

To download the create table script for the table used click here

Table Definition

The following is an explanation of the columns.  The following descriptions come from MSDN.

Column Name



Name of database, unique within an instance of SQL Server.

objectName Object name.
indexName Name of the index. name is unique only within the object.

NULL = Heap

partitionNumber 1-based partition number within the owning object; a table, view, or index.

1 = Nonpartitioned index or heap.

fragmentation Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks.

0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL for heaps when mode = SAMPLED.

fill_factor > 0 = FILLFACTOR percentage used when the index was created or rebuilt.

0 = Default value

is_padded 1 = PADINDEX is ON.


type_desc Description of index type:


page_count Total number of index or data pages.

For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.

date this is the current date GETDATE()


   1:  ALTER PROCEDURE [dbo].[GetStatsForIndexes]
   2:      @PageCount INT = 100
   4:  AS
   5:  BEGIN
   6:      -- SET NOCOUNT ON added to prevent extra result sets from
   7:      -- interfering with SELECT statements.
   8:      SET NOCOUNT ON;
  10:      -- Declare varables
  11:      DECLARE @dbID INT, @dbName VARCHAR(128), @SQL NVARCHAR(MAX)
  13:      -- Create a temp table to store all active databases
  14:      CREATE TABLE #databaseList
  15:      (
  16:            databaseID        INT
  17:          , databaseName      VARCHAR(128)
  18:      );
  20:      -- we only want non-system databases who are currenlty online
  21:      INSERT INTO #databaseList (databaseID, databaseName)
  22:      SELECT d.database_id, FROM sys.databases d where d.[state] = 0 and d.database_id > 4
  25:      -- Loop through all databases 
  26:         WHILE (SELECT COUNT(*) FROM #databaseList) > 0  BEGIN
  28:             -- get a database id
  29:          SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
  30:          FROM #databaseList;
  32:              SET @SQL = 'INSERT INTO DBA_Tools.dbo.IDX_FRAG (databaseName, ObjectName, indexName, partitionNumber, fragmentation, fill_factor, is_padded, type_desc, page_count, [date])
  33:                  SELECT
  34:           AS databaseName
  35:                  , AS ObjectName
  36:                  , AS indexName
  37:                  , ps.partition_number AS partitionNumber
  38:                  , ps.avg_fragmentation_in_percent AS fragmentation
  39:                  ,idx.fill_factor
  40:                  ,idx.is_padded
  41:                  ,idx.type_desc
  42:                  , ps.page_count
  43:                  , GETDATE() as [date]
  44:              FROM sys.databases db
  45:                INNER JOIN sys.dm_db_index_physical_stats ('+CAST(@dbID AS VARCHAR(10))+', NULL, NULL , NULL, N''Limited'') ps
  46:                    ON db.database_id = ps.database_id
  47:                INNER JOIN '+ @dbName+'.sys.objects obj ON obj.object_id = ps.object_id
  48:                INNER JOIN '+ @dbName+'.sys.indexes idx ON idx.index_id = ps.index_id AND idx.object_id = ps.object_id
  49:              WHERE ps.index_id > 0
  50:                 AND ps.page_count > 100
  51:              ORDER BY page_count desc
  52:              OPTION (MaxDop 1);'
  54:          EXECUTE sp_executesql @SQL
  55:          -- remove the database from the databases table
  56:          DELETE FROM #databaseList WHERE databaseID = @dbID
  58:          -- get the next database in the databases table
  59:          SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
  60:          FROM #databaseList;
  62:      END
  63:      -- temp table is no longer needed, so we will kill it.
  64:      DROP TABLE #databaseList;
  65:  END

3 responses to “Get index fragmentation statistics

Leave a Reply

Your email address will not be published.

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.

This site uses Akismet to reduce spam. Learn how your comment data is processed.