Thursday, January 26, 2012

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 = 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

4 comments:

  1. Great Post and Code! Thank You

    ReplyDelete
  2. Is not better to use parallelism for the rebuild process either via usage of maxdop or let the sql to decide the parallelism? I am asking because in the script the maxdop 0 is used.

    Thanks,
    Xenia

    ReplyDelete
  3. Hello, I always use MAXDOP=0 when rebuilding indexes. This is something that I do mostly off working hours so I can use all the CPU. I is also faster

    ReplyDelete