Share via


SQL Server: Handling Indexes Fragmentation

Introduction

This is a handy SQL Template to get information about the indexes fragmentation on a particular user database. It can be used to rebuild all the indexes based on a rule (ex. index fragmentation > 30% ). Or just to get a report of the current index fragmentation status.

Details

The script has below characteristics:

The variable @EXECUTE is to decide whether you want to get the current index fragmentation for all the tables or proceed with the full index rebuild

Since it is a template, you have to specify the database name and an e-mail address (to get a report) 

I never rebuild indexes on a database when the recovery model is FULL so the script checks for that

 If the SQL Edition is Enterprise, then it uses ONLINE= ON option

 It uses MAXDOP = 0

 It generates a report with the previous and current index fragmentation. This is a HTML report that you should receive by e-mail

This is how you specify the variables in the sql template

 

 

Code

Note that @EXECUTE variable is currently 0 so the script will just report the index fragmentation. Remember to change it to 1 when you really want to start the index rebuild process

USE <Database,sysname,model>
 
GO
 
SET NOCOUNT ON
 
GO
 
DECLARE @EXECUTE bit  = 0  -- // 0 = PRINT , 1 = EXECUTE
 
 
IF @EXECUTE = 1 SELECT  'REINDEX TABLES'  ELSE SELECT  'PRINTING STATEMENTS / REPORT'
 
 
SELECT @@servername,serverproperty('Edition'),getdate(),db_name() asDatabase_Name, recovery_model_desc as  [Recovery Model]
 
FROM sys.databases where name  = ''
 
 
--ALTER DATABASE SET RECOVERY SIMPLE
 
--ALTER DATABASE SET RECOVERY FULL
 
 
IF (SELECT recovery_model_desc FROM sys.databases where name  = '') ='FULL' AND @EXECUTE = 1
 
BEGIN
 
      SELECT 'The database is in FULL recovery model, Reindex cannot be executed'
 
      GOTO FINAL
 
END
 
 
DECLARE @objectid int;
 
DECLARE @indexid int;
 
DECLARE @partitioncount bigint;
 
DECLARE @schemaname nvarchar(130);
 
DECLARE @objectname nvarchar(130);
 
DECLARE @indexname nvarchar(130);
 
DECLARE @partitionnum bigint;
 
DECLARE @partitions bigint;
 
DECLARE @frag float;
 
DECLARE @rows int;
 
DECLARE @prtime datetime;
 
DECLARE @command varchar(max);
 
DECLARE @Counter int = 1;
 
DECLARE @Total int = 0;
 
DECLARE @Initial_Time datetime;
 
DECLARE @tableHTML_Fragmentation varchar(max);
 
 
select @Initial_Time = getdate()
 
 
IF (object_id( 'tempdb..#tablesrows'  ) IS  NOT NULL)  DROP  TABLE..#tablesrows ;
 
      
 
SELECT object_id,OBJECT_NAME(object_id) TableName,SUM(Rows) Rows
 
INTO #tablesrows
 
FROM sys.partitions WHERE index_id < 2 GROUP BY  object_id
 
 
IF (object_id( 'tempdb..#TMP_DBA_IDX_FRAG'  ) IS  NOT NULL)  DROP  TABLE..#TMP_DBA_IDX_FRAG ;
 
 
      CREATE TABLE  #TMP_DBA_IDX_FRAG (
 
      objectid int,
 
      indexid int,
 
      partitionnumber int,
 
      avg_fragmentation_in_percent float,
 
      new_avg_fragmentation_in_percent float,
 
      [Rows] bigint)
 
 
INSERT INTO  #TMP_DBA_IDX_FRAG
 
SELECT
 
    IDX.object_id AS  objectid,
 
    IDX.index_id AS  indexid,
 
    IDX.partition_number AS  partitionnum,
 
    IDX.avg_fragmentation_in_percent AS  frag,
 
      0 as  new_avg_fragmentation_in_percent,
 
      RC.Rows
 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL ,  NULL,'LIMITED') as  IDX
 
INNER JOIN #tablesrows RC ON  IDX.object_id=RC.object_id
 
WHERE index_id > 0 and avg_fragmentation_in_percent >10;
 
 
 
SELECT @Total = count(*) FROM  #TMP_DBA_IDX_FRAG;
 
 
IF @EXECUTE = 1 PRINT 'REINDEX TABLES' ELSE  PRINT 'PRINTING STATEMENTS'
 
PRINT '======================================================='
 
PRINT ''
 
-- Declare the cursor for the list of partitions to be processed.
 
DECLARE partitions CURSOR FOR  SELECTobjectid,indexid,partitionnumber,avg_fragmentation_in_percent,[Rows]FROM #TMP_DBA_IDX_FRAG
 
order by  rows desc,objectid,indexid;
 
-- Open the cursor.
 
OPEN partitions;
 
-- Loop through the partitions.
 
WHILE (1=1)
 
    BEGIN;
 
        FETCH NEXT
 
           FROM partitions
 
           INTO @objectid, @indexid, @partitionnum, @frag,@rows;
 
        IF @@FETCH_STATUS < 0 BREAK;
 
        SELECT @objectname = QUOTENAME(o.name), @schemaname =QUOTENAME(s.name)
 
        FROM sys.objects AS o
 
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
 
        WHERE o.object_id = @objectid;
 
        SELECT @indexname = QUOTENAME(name)
 
        FROM sys.indexes
 
        WHERE  object_id = @objectid AND index_id = @indexid;
 
        SELECT @partitioncount = count (*)
 
        FROM sys.partitions
 
        WHERE object_id = @objectid AND index_id = @indexid;
 
 
            SET @prtime = getdate()
 
            SET @command = NULL
 
        --IF @frag < 30.0 and @frag > 15.0
 
        --    SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE; --('+cast(@frag as varchar)+'%) // '+cast(@rows as varchar) +' rows';
 
        IF @frag >= 30.0
 
            BEGIN
 
            SELECT @command = N'ALTER INDEX ' + @indexname + N' ON ' +@schemaname + N'.' + @objectname + N' REBUILD WITH (MAXDOP =0'+CASEserverproperty('EngineEdition') WHEN  3 THEN  ',ONLINE =ON' END+');--('+cast(@frag as  varchar)+'%) // '+cast(@rows as  varchar) +' rows';                 
 
            END
 
        IF @partitioncount > 1
 
            SET @command = @command + N' PARTITION=' +CAST(@partitionnum AS  nvarchar(10));
 
        
 
            BEGIN TRY
 
                  IF @command is  not null IF @EXECUTE = 1 EXEC(@command) ELSE  PRINT (@command);            
 
            END TRY
 
            BEGIN CATCH
 
            PRINT cast(ERROR_NUMBER() as  varchar(10))+'//'+ERROR_MESSAGE();
 
                  IF ERROR_NUMBER() = 2725 SET  @command = N'ALTER INDEX '  + @indexname + N' ON '  + @schemaname + N'.'  + @objectname + N' REBUILD WITH (MAXDOP =0);--('+cast(@frag as  varchar)+'%) // '+cast(@rows as  varchar) +' rows (OFFLINE)'           
 
                  IF @EXECUTE = 1 EXEC (@command) ELSE PRINT (@command)
 
            END CATCH
 
                        
 
        PRINT N'Executed ('+ cast(@Counter as  varchar)+'/'+cast(@Totalas varchar)+'):' + @command;
 
            PRINT N'Execution Time: ' +CONVERT  ( varchar(30) ,getdate() ,120)+'   ('+SUBSTRING(CONVERT ( varchar(30) ,getdate()-@prtime ,120),12,1000)+')'
 
            PRINT N'-----------'
 
              
 
            SET @Counter=@Counter+1
 
    END;
 
-- Close and deallocate the cursor.
 
CLOSE partitions;
 
DEALLOCATE partitions;
 
 
UPDATE #TMP_DBA_IDX_FRAG
 
SET new_avg_fragmentation_in_percent = IDX.avg_fragmentation_in_percent
 
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL ,  NULL,'LIMITED') as  IDX
 
WHERE objectid = IDX.object_id and indexid=IDX.index_id
 
 
select
 
      t.name
 
      ,i.name
 
      ,round(avg_fragmentation_in_percent,2) as   avg_fragmentation_in_percent
 
      ,round(new_avg_fragmentation_in_percent,2) asnew_avg_fragmentation_in_percent
 
      ,[Rows]
 
from #TMP_DBA_IDX_FRAG M (nolock)
 
inner join sys.tables t  on  M.objectid =t.object_id
 
inner join sys.indexes i on  M.indexid =i.index_id and  M.objectid=i.object_id
 
order by  1
 
 
select @@servername as Server_Name,getdate() as[Now],avg(avg_fragmentation_in_percent) asPrevious_avg_frag,avg(new_avg_fragmentation_in_percent)  asNew_avg_frag
 
from #TMP_DBA_IDX_FRAG (nolock)
 
where rows  > 4000
 
 
DECLARE @avg_fragmentation_in_percent float
 
DECLARE @new_avg_fragmentation_in_percent float
 
 
select  @avg_fragmentation_in_percent=avg(avg_fragmentation_in_percent),
 
            @new_avg_fragmentation_in_percent=avg(new_avg_fragmentation_in_percent)
 
      from #TMP_DBA_IDX_FRAG (nolock)
 
      where rows  > 4000
 
 
 
 
SET @tableHTML_Fragmentation =     
 
  N'
 
Previous AVG Fragmentation ('+cast(@avg_fragmentation_in_percent as  varchar)+') - New AVG Fragmentation ('+cast(@new_avg_fragmentation_in_percent as  varchar)+')
 
 
 
'+      
 
  N'
 
'+     
 
  N'
 
'+     
 
  N'
 
'+  
 
  CAST ( ( SELECT td = t.name,       '',      
 
     td = i.name, '',      
 
    td = cast(round(avg_fragmentation_in_percent,2) as  varchar(10)), '',     
 
     td = cast(round(new_avg_fragmentation_in_percent,2) as  varchar(10)), '',      
 
     td = cast([Rows] as  varchar(10))   
 
    FROM #TMP_DBA_IDX_FRAG M
 
      inner join sys.tables t  on  M.objectid =t.object_id
 
      inner join sys.indexes i on  M.indexid =i.index_id and  M.objectid =i.object_id
 
      ORDER BY  1    
 
     FOR XML PATH('tr'), TYPE      
 
  ) AS  NVARCHAR(MAX) ) +     
 
  N'
 
Table Name
 
Index Name
 
AVG Fragmentation
 
NEW AVG Fragmentation
 
Rows
 
' ;  
 
   
 
 
DECLARE @MailText varchar(max);
 
DECLARE @Subj varchar(250)
 
Set @Subj='DBREINDEX on ('+rtrim(@@servername)+') Database () HAS FINISHED'
 
 
SELECT @MailText = 'FULL DATABASE REINDEX ON '+rtrim(@@servername)+' for STARTED AT '+CONVERT(varchar(30), @MailText, 120)+char(13)+' ,FINISHED AT ' +CONVERT(varchar(30), getdate(), 120)
 
 
 
IF @avg_fragmentation_in_percent IS  NOT NULL AND @EXECUTE = 1
 
BEGIN
 
      EXEC msdb.dbo.sp_send_dbmail
 
                  @subject                      =@Subj
 
                  ,@body                       =@tableHTML_Fragmentation
 
                  , @recipients                 ='' 
 
                  , @execute_query_database = ''
 
                  , @body_format = 'HTML' ;
 
END
 
 
----------------------------------
 
 
-- Drop the temporary table.
 
IF (object_id( 'tempdb..#tablesrows'  ) IS  NOT NULL)  DROP  TABLE..#tablesrows ;
 
IF (object_id( 'tempdb..#TMP_DBA_IDX_FRAG'  ) IS  NOT NULL)  DROP  TABLE..#TMP_DBA_IDX_FRAG ;
 
 
 
 
FINAL:
 
SELECT name,compatibility_level,recovery_model_desc from  sys.databases
 
GO

Original version: http://sql-javier-villegas.blogspot.com/2012/01/handling-indexes-fragmentation.html