2005 Online Index Rebuild using DMV

IF OBJECT_ID('dbo.usp_OnlineIndexRebuild') IS NOT NULL
BEGIN
 DROP PROCEDURE dbo.usp_OnlineIndexRebuild
 IF OBJECT_ID('dbo.usp_OnlineIndexRebuild') IS NOT NULL
 PRINT '<<< FAILED DROPPING PROCEDURE dbo.usp_OnlineIndexRebuild >>>'
 ELSE
 PRINT '<<< DROPPED PROCEDURE dbo.usp_OnlineIndexRebuild >>>'
END
go
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER OFF
go

CREATE PROCEDURE dbo.usp_OnlineIndexRebuild
 @Emailrecipients varchar(MAX) = 'tbollhofer2@gmail.com',
 @EmailSubject varchar(MAX) = 'Index Maintenance Report'
AS

/* 

Name:          dbo.usp_OnlineIndexRebuild
Contact:          Tommy Bollhofer (tbollhofer2@gmail.com)
Purpose:          Identifies indexes with average logical fragmentation >= 30% using the DM available in MSSQL 2005; performs an online rebuild for 
      indexes that do not contain text,ntext,image or xml datatypes; performs an offline rebuild for indexes that do contain
 these datatypes.
Last Modified:          08/17/2007

*/
DECLARE @dbId INT
SET @dbId = db_id()

CREATE TABLE #fragReport
(
 RowID int identity (1,1),
 Object_Id int,
 Index_Id int,
 Index_Name sysname,
 LogicalFragmentation float
)

CREATE TABLE #reindex_online
(
 RowID int identity (1,1),
 Object_Name sysname,
 Index_Name sysname
)

CREATE TABLE #reindex_offline
(
 RowID int identity (1,1),
 Object_Name sysname,
 Index_Name sysname
)

CREATE TABLE #contains_text
(
 RowID int identity (1,1),
 Index_Name sysname
)

 INSERT INTO #fragReport([Object_Id], [Index_Id],[Index_Name],[LogicalFragmentation])
 SELECT A.object_id, 
 A.index_id, 
 [name], 
 Avg_Fragmentation_In_Percent 
 FROM sys.dm_db_index_physical_stats (@dbId, NULL,NULL, NULL, NULL) AS A 
 JOIN sys.indexes B WITH(NOLOCK)
 ON A.Object_id = B.Object_id 
 AND A.Index_id = B.Index_id 
 WHERE Avg_Fragmentation_In_Percent >= 30
 AND B.[name] IS NOT NULL


 INSERT INTO #contains_text
 SELECT A.Index_Name 
 FROM #fragReport A
 JOIN sys.columns B WITH(NOLOCK)
 ON A.Object_Id = B.Object_id
 AND B.system_type_id IN (34,35,99,241)
 GROUP BY A.Index_Name


 INSERT INTO #reindex_online([Object_Name],[Index_Name])
 SELECT C.name + '.' + B.name AS [Object_Name], 
 A.[Index_Name]
 FROM #fragReport A WITH(NOLOCK)
 JOIN sys.tables B WITH(NOLOCK)
 ON A.Object_Id = B.Object_Id
 JOIN sys.schemas C WITH(NOLOCK)
 ON B.schema_id = C.schema_id
 WHERE A.Index_Name NOT IN (SELECT Index_Name
 FROM #contains_text WITH(NOLOCK))
                                                                 
 INSERT INTO #reindex_offline([Object_Name],[Index_Name])
 SELECT C.name + '.' + B.name AS [Object_Name], 
 A.[Index_Name]
 FROM #fragReport A WITH(NOLOCK)
 JOIN sys.tables B WITH(NOLOCK)
 ON A.Object_Id = B.Object_Id
 JOIN sys.schemas C WITH(NOLOCK)
 ON B.schema_id = C.schema_id
 WHERE A.Index_Name IN (SELECT Index_Name
 FROM #contains_text WITH(NOLOCK))                                                                 


        DECLARE @numtables int,
                @numindexes int,
                @numreindexes int,
                @tabcount int,
                @indcount int,
                @recount int,
                @currtable int,
                @tabname varchar(255),
                @currind int,
                @indname varchar(255)

         SELECT @numreindexes = count(*) FROM #reindex_online WITH(NOLOCK)

             SET @recount = 1

            WHILE @recount <= @numreindexes
            BEGIN
                SELECT @tabname = [Object_Name],
                 @indname = [Index_Name]
                 FROM #reindex_online
                 WHERE RowId = @recount
                
                 EXEC ('ALTER INDEX ' + @indname + ' ON ' + @tabname + ' REBUILD WITH(PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)')
            
                SET @recount = @recount + 1
            END
            
         SELECT @numreindexes = count(*) FROM #reindex_offline WITH(NOLOCK)
            
            SET @recount = 1
            
            WHILE @recount <= @numreindexes
            BEGIN
                SELECT @tabname = [Object_Name],
                 @indname = [Index_Name]
                 FROM #reindex_offline
                 WHERE RowId = @recount
                
                EXEC ('ALTER INDEX ' + @indname + ' ON ' + @tabname + ' REBUILD WITH(PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF)')
            
                SET @recount = @recount + 1
            END
            
            
DECLARE @tableHTML nvarchar(MAX) ;
SET @tableHTML =
    N'<STYLE TYPE="text/css">TD{font-family: calibri; font-size: 10pt;}</STYLE>' + 
 N'<b><font face="calibri" size="2">This report represents index(s) which have an average fragmentation > than 30 percent. These index(s) have been targeted for maintenance.</font></b><br><br>' +
 N'<table border="1" cellpadding="2" cellspacing="2" border="1">' +
 N'<tr><th><font face="calibri" size="2">Table Name</font></th><th><font face="calibri" size="2">Index Name</font></th><th><font face="calibri" size="2">LogicalFragmentation</font></th>' +
 CAST ( ( SELECT td = C.name + '.' + B.name, '',
 td = A.Index_Name, '',
 td = convert(char(2),convert(int,A.LogicalFragmentation)) + '%', ''
 FROM #fragReport A 
 JOIN sys.tables B WITH(NOLOCK)
 ON A.Object_Id = B.Object_Id
 JOIN sys.schemas C WITH(NOLOCK)
 ON B.schema_id = C.schema_id
 FOR XML PATH('tr'), TYPE 
 ) AS NVARCHAR(MAX) ) +
 N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients = @Emailrecipients,
 @subject = @EmailSubject,
 @body = @tableHTML,
 @body_format = 'HTML' ;

DROP TABLE #fragreport
DROP TABLE #contains_text
DROP TABLE #reindex_online
DROP TABLE #reindex_offline

go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.usp_OnlineIndexRebuild') IS NOT NULL
 PRINT '<<< CREATED PROCEDURE dbo.usp_OnlineIndexRebuild >>>'
ELSE
 PRINT '<<< FAILED CREATING PROCEDURE dbo.usp_OnlineIndexRebuild >>>'
go

Resources

• usp_OnlineIndexRebuild.sql