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
) ON [PRIMARY]

GO

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

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 (
   [Database_Name]
  ,[Object_Name]
  ,[Index_Name]
  ,[Index_Id]
  ,[Index_Type_Desc]
  ,[Avg_Fragmentation_In_Percent]
  ,[Fragment_Count]
  ,[Page_Count]
)

SELECT 
    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]
FROM 
    sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,NULL) AS ps
INNER JOIN sys.indexes AS i
ON 
    ps.[object_id] = i.[object_id] AND ps.[Index_Id] = i.[Index_Id]
WHERE 
    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.

select 
    [Object_Name]
  , [Index_Name]
  , Avg_Fragmentation_In_Percent
  , 'ALTER INDEX [' + [Index_Name] + '] ON [dbo].[' + [Object_Name] + ']REBUILD ;'
FROM 
    [dbo].[IndexFraglist]
WHERE 
      [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.
ORDER BY
    [Object_Name]
  , [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.

Advertisements