Handling Indexes Fragmentation

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
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) (see the screenshot)
• 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

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() as Database_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 SELECT objectid,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'+CASE serverproperty('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(@Total as 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) as new_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) as Previous_avg_frag,avg(new_avg_fragmentation_in_percent)  as New_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