SQLServer : Monitoring and Correcting Index Fragmentation

by bitznbitez

Monitoring index fragmentation is important. There are tools to do this for you, but in the end they all just use the SQL Server interfaces. You can do this yourself for less money for the company.

First I keep a table in my “Monitoring” database. I usually build one of these per server I administer. It could just as well be centrally located on a single server if you prefer. I build a table like this. The table and process is built to be able to store fragmentation history, though typically I truncate the table before each run, which is why my later queries do not self join on max(Snapshot_Timestamp) etc.

CREATE TABLE [dbo].[IndexFragList](
   [Database_Name] [nvarchar](128) ,
   [Object_Name] [nvarchar](128) NULL,
   [Index_Name] [sysname] NULL,
   [Index_Id] [int] NULL,
   [Index_Type_Desc] [nvarchar](60) NULL,
   [Avg_Fragmentation_In_Percent] [float] NULL,
   [Fragment_Count] [bigint] NULL,
   [Page_Count] [bigint] NULL,
   [Snapshot_Timestamp] [datetime] NOT NULL


ALTER TABLE [dbo].[IndexFragList] ADD DEFAULT (getdate()) FOR [Snapshot_Timestamp]

Then at some interval, whatever makes sense for you, run the following SQL. This can be run via sp_foreachdb or in some other fashion, like a single step for each db you want to monitor as a sqlagent job.

INSERT INTO Monitoring.dbo.IndexFragList (

    DB_NAME([Database_Id]) AS [Database_Name]
  , OBJECT_NAME(ps.OBJECT_ID) AS [Object_Name]
  , i.[Name] AS [Index_Name]
  , [ps.Index_Id]
  , [Index_Type_Desc]
  , [Avg_Fragmentation_In_Percent]
  , [Fragment_Count]
  , [Page_Count]
    sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,NULL) AS ps
INNER JOIN sys.indexes AS i
    ps.[object_id] = i.[object_id] AND ps.[Index_Id] = i.[Index_Id]
    database_id = DB_ID()

Then after all the stats are gathered you can query the table to find what indexes need to be reorged or rebuilt. You can use a query similar to this one.

  , [Index_Name]
  , Avg_Fragmentation_In_Percent
  , 'ALTER INDEX [' + [Index_Name] + '] ON [dbo].[' + [Object_Name] + ']REBUILD ;'
      [Avg_Fragmentation_In_Percent] >= 50.0
  and [Database_Name] = 'MyDbName'
  and [Index_Name] is not null -- This excludes Heaps

-- Order by Index_id so that the cluster indexes are reorged first.
  , [Index_Id] DESC;

The last column of this query can be pasted to wherever you want to run your rebuilds.   You could alternately script it further so the output of that column is iteratively executed.