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.

Goal

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

Description

databaseName

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.

0 = PADINDEX is OFF.

type_desc Description of index type:

HEAP
CLUSTERED
NONCLUSTERED
XML
SPATIAL

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()

Script

   1:  ALTER PROCEDURE [dbo].[GetStatsForIndexes]
   2:      @PageCount INT = 100
   3:  
   4:  AS
   5:  BEGIN
   6:      -- SET NOCOUNT ON added to prevent extra result sets from
   7:      -- interfering with SELECT statements.
   8:      SET NOCOUNT ON;
   9:  
  10:      -- Declare varables
  11:      DECLARE @dbID INT, @dbName VARCHAR(128), @SQL NVARCHAR(MAX)
  12:  
  13:      -- Create a temp table to store all active databases
  14:      CREATE TABLE #databaseList
  15:      (
  16:            databaseID        INT
  17:          , databaseName      VARCHAR(128)
  18:      );
  19:  
  20:      -- we only want non-system databases who are currenlty online
  21:      INSERT INTO #databaseList (databaseID, databaseName)
  22:      SELECT d.database_id, d.name FROM sys.databases d where d.[state] = 0 and d.database_id > 4
  23:  
  24:  
  25:      -- Loop through all databases 
  26:         WHILE (SELECT COUNT(*) FROM #databaseList) > 0  BEGIN
  27:  
  28:             -- get a database id
  29:          SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
  30:          FROM #databaseList;
  31:  
  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:                    db.name AS databaseName
  35:                  , obj.name AS ObjectName
  36:                  , idx.name 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);'
  53:  
  54:          EXECUTE sp_executesql @SQL
  55:          -- remove the database from the databases table
  56:          DELETE FROM #databaseList WHERE databaseID = @dbID
  57:  
  58:          -- get the next database in the databases table
  59:          SELECT TOP 1 @dbID = databaseID, @dbName = databaseName
  60:          FROM #databaseList;
  61:  
  62:      END
  63:      -- temp table is no longer needed, so we will kill it.
  64:      DROP TABLE #databaseList;
  65:  END

  • Cameron

    John,
    Enjoyed reading your post. Does the SP have to be created in the master database or can it be created in a different database say in a database that you use to gather the statistics?
    Cameron

  • Cameron that is a good question. You can keep the stored procedure on the database of your choice. It doesn’t have to be on the master database.

    For example, I usually have a DBA database where I will consolidate my sql server tools/scripts like this one.

  • PolishPaul

    Hi John,
    I tried this on SQL2012 but my tables show up empty. When i tried it on SQL2008R2 i only get results for one database. Here’s what I did:
    Created an empty DB “DBA_Tools” and ran the script to create the table. I then created the stored procedure in that database. After calling it, i ran a select * on the IDX_FRAG table and only see results for one of the databases. Any suggestions on how to properly implement this?