Technical Article

2008 Index Rebuild using DMV

,

Previous Version (http://www.sqlservercentral.com/scripts/SQL+Server+2005/61278/)

Iterates through sys.tables; performs an online index rebuild on indexes where the logical fragmentation is greater than or equal to the @LogicalFragmentationPercent parameter. The procedure can perform offline rebuild on indexes containing text, ntext, image, xml, varchar(max), varbinary(max) and nvarchar(max) or an offline rebuild by partition. This procedure is intended to reside in the msdb database and will conditionally create and populate a table called dbo.index_maintenance_history, configured via the @MaintainHistory parameter. This procedure can also produce and HTML report via the @EmailNotification paramter.

To utilize this with RedGate's SQL Script Manager, please save T-SQL above as  usp_DBA_index_maintenance_SSC.sql along with the *.rgtool code, preferably in the C:\ProgramData\Red Gate\SQL Scripts Manager directory.

In the past several readers have posted problems w/ downloading the T-SQL from the WYSWYG editor. Should you encounter any of these types of issues, I've made the scripts available via my SkyDrive

create procedure usp_DBA_index_maintenance @DBName sysname,@Tables nvarchar(max) = NULL,@MaintainHistory bit = 0,@OutputResults bit = 1,@RebuildByPartition bit = 0,@IncludeOffline bit = 0,@LogicalFragmentationPercent int = 30,@MinPages int = 1000,@MaxDop int = 0,@FillFactor int = 80,@EmailNotification bit = 0,@EmailRecipients varchar(max) = '',@EmailSubject varchar(max) = 'Index Maintenance Report',@Debug bit = 0 as  /*  Name:            usp_DBA_index_maintenance Author: Tommy Bollhofer                 tbollhofer2@gmail.com                http://www.linkedin.com/in/tommybollhoferDescription:    Iterates through sys.tables; performs an online index rebuild on indexes where the logical fragmentation is greater than or equal to the @LogicalFragmentationPercent parameter.                 The procedure can perform offline rebuild on indexes containing text, ntext, image, xml, varchar(max), varbinary(max) and nvarchar(max) or an offline rebuild by partition.                 This procedure is intended to reside in the msdb database and will conditionally create and populate a table called dbo.index_maintenance_history, configured via the @MaintainHistory parameter.                 This procedure can also produce and HTML report via the @EmailNotification paramter.                Usage:            exec dbo.usp_DBA_index_maintenance                     @DBName = 'AdventureWorks',                     @Tables = 'SomeTableName1,SomeTableName2', -- Optional                     @MaintainHistory = 1,                     @OutputResults bit = 1,                     @RebuildByPartition = 0,                     @IncludeOffline = 0,                     @LogicalFragmentationPercent = 30,                     @MinPages = 1000,                     @MaxDop = 0,                     @EmailNotification = 1,                     @EmailRecipients = 'DBA@gmail.com',                     @EmailSubject = 'Index Maintenance Report - AdventureWorks',                     @Debug = 0                                      Last Modified: 02/24/2012  */  set nocount onif @RebuildByPartition = 1 and @IncludeOffline = 1begin    set @IncludeOffline = 0enddeclare @table_guid varchar(36)set @table_guid = replace(newid(),'-','_')exec ('if exists (select name from tempdb..sysobjects where name = ''##clustered_indexes_with_offline_data_types_' + @table_guid + ''')begin    drop table ##clustered_indexes_with_offline_data_types_' + @table_guid + 'end ')exec ('if exists (select name from tempdb..sysobjects where name = ''##nonclustered_indexes_with_offline_data_types_' + @table_guid + ''')begin    drop table ##nonclustered_indexes_with_offline_data_types_' + @table_guid + 'end ')create table #fragreport( [rid] int identity (1,1), [database_id] int, [database_name] sysname,[schema_id] int,[schema_name] sysname,[table_id] int,[table_name] sysname,[index_id] int,[index_name] sysname,[partition_scheme] sysname NULL,[partition_number] int,[pre_fragmentation_in_percent] float,[post_fragmentation_in_percent] float,[page_count] int,[ddl_operation] int,[ddl_text] char(100),[insert_date] datetime default getdate())    create table #index_rebuild_by_partition( [rid] int identity (1,1),[schema_id] int, [schema_name] sysname, [table_id] int,[table_name] sysname, [index_name] sysname,[index_id] int,[partition_number] int)     create table #index_rebuild_offline( [rid] int identity (1,1),[schema_id] int, [schema_name] sysname, [table_id] int,[table_name] sysname, [index_name] sysname,[index_id] int,[partition_number] int)     create table #index_rebuild_online( [rid] int identity (1,1), [schema_id] int, [schema_name] sysname, [table_id] int,[table_name] sysname, [index_name] sysname,[index_id] int,[partition_number] int)     exec ('create table ##clustered_indexes_with_offline_data_types_' + @table_guid + '([table_id] int,[index_name] sysname,[index_id] int,[column_name] sysname)')exec ('create table ##nonclustered_indexes_with_offline_data_types_' + @table_guid + '([table_id] int,[index_name] sysname,[index_id] int,[index_column_id] int,[column_name] sysname,[is_included_column] bit,[column_position] int)')if @Debug = 1begin    print 'TEMPORARY TABLES HAVE BEEN CREATED. GLOBAL TEMPORARY TABLE GUID IS ' + @table_guid + '.'end if @Tables IS NULL or @Tables = ''begin        insert into #fragreport    (         [database_id],         [database_name],         [schema_id],         [schema_name],         [table_id],         [table_name],         [index_id],         [index_name],         [partition_scheme],         [partition_number]    )    exec (' select distinct db_id( ''' + @DBName + ''' ) as [database_id],         ''' + @DBName + ''' as [database_name],         s.schema_id as [schema_id],         s.[name] as [schema_name],         t.[object_id] as [table_id],          t.[name] as [table_name],          i.[index_id] as [index_id],         i.[name] as [index_name],         ps.name as [partition_scheme],         p.[partition_number] as [partition_number]     from [' + @DBName + '].sys.tables t inner join [' + @DBName + '].sys.indexes i         on t.[object_id] = i.[object_id]inner join [' + @DBName + '].sys.schemas s         on t.[schema_id] = s.[schema_id]left join [' + @DBName + '].sys.partitions p      on t.[object_id] = p.[object_id]      and i.[index_id] = p.[index_id]left join [' + @DBName + '].sys.partition_schemes ps         on i.data_space_id = ps.data_space_idleft join [' + @DBName + '].sys.destination_data_spaces dds         on ps.data_space_id = dds.partition_scheme_id     where i.[index_id] <> 0 ')  exec ('  insert into ##nonclustered_indexes_with_offline_data_types_' + @table_guid + '      (            [table_id],            [index_name],            [index_id],         [index_column_id],         [column_name],         [is_included_column],         [column_position]     )    select t.object_id as table_id,         i.name as index_name,         i.index_id,         ic.index_column_id,          c.name as column_name,         ic.is_included_column,         row_number() over (partition by ic.is_included_column order by ic.index_column_id) as column_position from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.indexes i        on t.object_id = i.object_idinner join [' + @DBName + '].sys.index_columns ic        on i.object_id = ic.object_id     and i.index_id = ic.index_idinner join [' + @DBName + '].sys.columns c         on ic.object_id = c.object_id and ic.column_id = c.column_id where c.system_type_id in (34,35,99,241) and i.type = 2 union  select t.object_id as table_id,         i.name as index_name,         i.index_id,         ic.index_column_id,          c.name as column_name,         ic.is_included_column,         row_number() over (partition by ic.is_included_column order by ic.index_column_id) as column_position from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.indexes i        on t.object_id = i.object_idinner join [' + @DBName + '].sys.index_columns ic        on i.object_id = ic.object_id     and i.index_id = ic.index_idinner join [' + @DBName + '].sys.columns c         on ic.object_id = c.object_id and ic.column_id = c.column_id where c.system_type_id in (136,231,165,167) and c.max_length = -1     and i.type = 2')   exec ('  insert into ##clustered_indexes_with_offline_data_types_' + @table_guid + '      (            [table_id],            [index_name],            [index_id],            [column_name]     )  select t.object_id as table_id,         i.name as index_name,         i.index_id,         c.name as column_name from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.schemas s         on t.[schema_id] = s.[schema_id]inner join [' + @DBName + '].sys.indexes i     on t.object_id = i.object_id     inner join [' + @DBName + '].sys.columns c         on i.object_id = c.object_id where c.system_type_id in (34,35,99,241) and i.type = 1           union  select t.object_id as table_id,         i.name as index_name,         i.index_id,         c.name as column_name from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.schemas s         on t.[schema_id] = s.[schema_id]inner join [' + @DBName + '].sys.indexes i     on t.object_id = i.object_id     inner join [' + @DBName + '].sys.columns c         on i.object_id = c.object_id where c.system_type_id in (136,231,165,167) and c.max_length = -1 and i.type = 1 ')   if @Debug = 1begin    select * from #fragreport    exec ('select * from ##nonclustered_indexes_with_offline_data_types_' + @table_guid)    exec ('select * from ##clustered_indexes_with_offline_data_types_' + @table_guid)end end if @Tables IS NOT NULLbegin    if @Debug = 1    begin        print 'TABLES SPECIFIED: ' + @Tables    end         select @Tables = replace(@Tables,',',''',''')        insert into #fragreport    (         [database_id],         [database_name],         [schema_id],         [schema_name],         [table_id],         [table_name],         [index_id],         [index_name],         [partition_scheme],         [partition_number]    )    exec (' select distinct db_id( ''' + @DBName + ''' ) as [database_id],         ''' + @DBName + ''' as [database_name],         s.schema_id as [schema_id],         s.[name] as [schema_name],         t.[object_id] as [table_id],          t.[name] as [table_name],          i.[index_id] as [index_id],         i.[name] as [index_name],         ps.name as [partition_scheme],         p.[partition_number] as [partition_number]     from [' + @DBName + '].sys.tables t inner join [' + @DBName + '].sys.indexes i         on t.[object_id] = i.[object_id]inner join [' + @DBName + '].sys.schemas s         on t.[schema_id] = s.[schema_id]left join [' + @DBName + '].sys.partitions p      on t.[object_id] = p.[object_id]      and i.[index_id] = p.[index_id]left join [' + @DBName + '].sys.partition_schemes ps         on i.data_space_id = ps.data_space_idleft join [' + @DBName + '].sys.destination_data_spaces dds         on ps.data_space_id = dds.partition_scheme_id     where i.[index_id] <> 0      and t.[name] in (''' + @Tables + ''')')  exec ('  insert into ##nonclustered_indexes_with_offline_data_types_' + @table_guid + '      (            [table_id],            [index_name],            [index_id],         [index_column_id],         [column_name],         [is_included_column],         [column_position]     )    select t.object_id as table_id,         i.name as index_name,         i.index_id,         ic.index_column_id,          c.name as column_name,         ic.is_included_column,         row_number() over (partition by ic.is_included_column order by ic.index_column_id) as column_position from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.indexes i        on t.object_id = i.object_idinner join [' + @DBName + '].sys.index_columns ic        on i.object_id = ic.object_id     and i.index_id = ic.index_idinner join [' + @DBName + '].sys.columns c         on ic.object_id = c.object_id and ic.column_id = c.column_id where c.system_type_id in (34,35,99,241) and i.type = 2 and t.[name] in (''' + @Tables + ''') union  select t.object_id as table_id,         i.name as index_name,         i.index_id,         ic.index_column_id,          c.name as column_name,         ic.is_included_column,         row_number() over (partition by ic.is_included_column order by ic.index_column_id) as column_position from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.indexes i        on t.object_id = i.object_idinner join [' + @DBName + '].sys.index_columns ic        on i.object_id = ic.object_id     and i.index_id = ic.index_idinner join [' + @DBName + '].sys.columns c         on ic.object_id = c.object_id and ic.column_id = c.column_id where c.system_type_id in (136,231,165,167) and c.max_length = -1     and i.type = 2     and t.[name] in (''' + @Tables + ''')')   exec ('  insert into ##clustered_indexes_with_offline_data_types_' + @table_guid + '      (            [table_id],            [index_name],            [index_id],            [column_name]     )  select t.object_id as table_id,         i.name as index_name,         i.index_id,         c.name as column_name from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.schemas s         on t.[schema_id] = s.[schema_id]inner join [' + @DBName + '].sys.indexes i     on t.object_id = i.object_id     inner join [' + @DBName + '].sys.columns c         on i.object_id = c.object_id where c.system_type_id in (34,35,99,241) and i.type = 1  and t.[name] in (''' + @Tables + ''')          union  select t.object_id as table_id,         i.name as index_name,         i.index_id,         c.name as column_name from [' + @DBName + '].sys.tables tinner join [' + @DBName + '].sys.schemas s         on t.[schema_id] = s.[schema_id]inner join [' + @DBName + '].sys.indexes i     on t.object_id = i.object_id     inner join [' + @DBName + '].sys.columns c         on i.object_id = c.object_id where c.system_type_id in (136,231,165,167) and c.max_length = -1 and i.type = 1  and t.[name] in (''' + @Tables + ''')') if @Debug = 1begin    select * from #fragreport    exec ('select * from ##nonclustered_indexes_with_offline_data_types_' + @table_guid)    exec ('select * from ##clustered_indexes_with_offline_data_types_' + @table_guid)end    end if @Debug = 1begin    print 'OBJECT INFORMATION HAS BEEN COLLECTED.'end declare @numtables int,          @number_of_indexes int,          @count_pre_index_statistics int,          @count_post_index_statistics int,         @number_of_index_rebuild_by_partition int,         @number_of_index_rebuild_offline int,         @number_of_index_rebuild_online int,          @table_count int,          @index_count int,         @recount int,         @database_id int,          @schema_id int,          @schema_name sysname,          @table_id int,          @table_name sysname,          @index_id int,          @index_name sysname,         @partition_number int,          @output_value int,          @sql nvarchar(max)          -- gather pre-fragmentation statistics     update #fragreport        set [pre_fragmentation_in_percent] = d.[avg_fragmentation_in_percent],            [page_count] = d.[page_count]     from #fragreport s cross apply (select [database_id],                 [object_id],                 [index_id],                 [partition_number],                 [index_type_desc],                 [alloc_unit_type_desc],                 [index_depth],                 [index_level],                 [avg_fragmentation_in_percent],                 [fragment_count],                 [avg_fragment_size_in_pages],                 [page_count],                 [avg_page_space_used_in_percent],                 [record_count],                 [ghost_record_count],                 [version_ghost_record_count],                 [min_record_size_in_bytes],                 [max_record_size_in_bytes],                 [avg_record_size_in_bytes],                 [forwarded_record_count]             from sys.dm_db_index_physical_stats (s.database_id, s.table_id, s.index_id, s.partition_number, NULL)) d     if @Debug = 1begin    print 'PRE-FRAGMENTATION STATISTICAL INFORMATION HAS BEEN COLLECTED.'end               -- indexes by partition     insert into #index_rebuild_by_partition     (            [schema_id],            [schema_name],            [table_id],            [table_name],            [index_id],            [index_name],            [partition_number]     )     exec ('     select distinct f.[schema_id],            f.[schema_name],             f.[table_id],            f.[table_name],             f.[index_id],            f.[index_name],            f.[partition_number]  from #fragreport f     where (f.[page_count] > ' + @MinPages + ')      and (f.[pre_fragmentation_in_percent] > ' + @LogicalFragmentationPercent + ')      ')      -- indexes by offline data types     insert into #index_rebuild_offline     (            [schema_id],            [schema_name],            [table_id],            [table_name],            [index_id],            [index_name],            [partition_number]     )     exec ('     select distinct f.[schema_id],            f.[schema_name],             f.[table_id],            f.[table_name],             f.[index_id],            f.[index_name],            f.[partition_number]  from #fragreport finner join ##clustered_indexes_with_offline_data_types_' + @table_guid + ' o         on f.[table_id] = o.[table_id]inner join ##nonclustered_indexes_with_offline_data_types_' + @table_guid + ' o2         on f.[table_id] = o2.[table_id]     where (f.[page_count] > ' + @MinPages + ')      and (f.[pre_fragmentation_in_percent] > ' + @LogicalFragmentationPercent + ')     ')   -- indexes by online data types insert into #index_rebuild_online (            [schema_id],            [schema_name],            [table_id],            [table_name],            [index_id],            [index_name],            [partition_number]      )     exec (' select distinct f.[schema_id],            f.[schema_name],             f.[table_id],            f.[table_name],             f.[index_id],            f.[index_name],            f.[partition_number]  from #fragreport fleft outer join ##clustered_indexes_with_offline_data_types_' + @table_guid + ' o         on f.[table_id] = o.[table_id]left outer join ##nonclustered_indexes_with_offline_data_types_' + @table_guid + ' o2         on f.[table_id] = o2.[table_id]     where (f.[page_count] > ' + @MinPages + ')      and (f.[pre_fragmentation_in_percent] > ' + @LogicalFragmentationPercent + ')     and coalesce(o.[table_id],o.[table_id]) is null      and coalesce(o2.[table_id],o2.[table_id]) is null      ')  exec ('  update #fragreport        set [ddl_operation] = 0,            [ddl_text] = ''REBUILD MUST BE PERFORMED OFFLINE''     from #fragreport s                                  inner join ##clustered_indexes_with_offline_data_types_' + @table_guid + ' o         on s.[table_id] = o.[table_id]inner join ##nonclustered_indexes_with_offline_data_types_' + @table_guid + ' o2         on s.[table_id] = o2.[table_id]              where (s.[page_count] > ' + @MinPages + ')      and (s.[pre_fragmentation_in_percent] > ' + @LogicalFragmentationPercent + ')     ')              update #fragreport        set [ddl_operation] = 4,            [ddl_text] = 'INDEX CONTAINS < ' + convert(char(4),@MinPages) + ' PAGES'     from #fragreport      where [page_count] <= @MinPages      if @Debug = 1begin    print 'INDEXES HAVE BEEN SPIT INTO CATEGORIES; PARTITIONED, OFFLINE, AND ONLINE.'end                                -- rebuild indexes by partition; implies offlineif @RebuildByPartition = 1 and @IncludeOffline = 0begin if (select count(*) from #index_rebuild_by_partition) > 0    begin    select @number_of_index_rebuild_by_partition = count(*) from #index_rebuild_by_partition      set @recount = 1              while @recount <= @number_of_index_rebuild_by_partition         begin              select @schema_id = [schema_id],                    @schema_name = [schema_name],                    @table_id = [table_id],                    @table_name = [table_name],                     @index_id = [index_id],                    @index_name = [index_name],                    @partition_number = [partition_number]              from #index_rebuild_by_partition              where rid = @recount                   exec ('use [' + @dbname + '] alter index ' + @index_name + ' on ' + @schema_name + '.' + @table_name + ' rebuild partition = ' + @partition_number + ' with(maxdop = ' + @MaxDop + ')')                           update #fragreport                set [ddl_operation] = 1,                    [ddl_text] = 'INDEX WAS REBUILT OFFLINE BY PARTITION'              where [schema_id] = @schema_id                and [table_id] = @table_id                and [index_id] = @index_id                and [partition_number] = @partition_number                     set @recount = @recount + 1         end     end        if @Debug = 1    begin        print 'INDEX REBUILD OPERATIONS BY PARTITION HAVE BEEN COMPLETED.'    end         end             -- rebuild online indexes; include offline indexesif @RebuildByPartition = 0 and @IncludeOffline = 1beginif (select count(*) from #index_rebuild_offline) > 0    begin         select @number_of_index_rebuild_offline = count(*) from #index_rebuild_offline          set @recount = 1                      while @recount <= @number_of_index_rebuild_offline             begin                  select @schema_id = [schema_id],                        @schema_name = [schema_name],                        @table_id = [table_id],                        @table_name = [table_name],                         @index_id = [index_id],                        @index_name = [index_name],                        @partition_number = [partition_number]                  from #index_rebuild_offline                  where rid = @recount                           exec                         (                            'use [' + @dbname + '] alter index ' + @index_name + ' on ' + @schema_name + '.' + @table_name + '                              rebuild with(online = off, fillfactor = ' + @FillFactor + ', maxdop = ' + @MaxDop + ')'                        )                                   update #fragreport                    set [ddl_operation] = 2,                        [ddl_text] = 'INDEX WAS REBUILT OFFLINE'                  where [schema_id] = @schema_id                    and [table_id] = @table_id                    and [index_id] = @index_id                    and [partition_number] = @partition_number                             set @recount = @recount + 1             end        end         if (select count(*) from #index_rebuild_online) > 0    begin                 select @number_of_index_rebuild_online = count(*) from #index_rebuild_online         set @recount = 1                      while @recount <= @number_of_index_rebuild_online             begin                  select @schema_id = [schema_id],                        @schema_name = [schema_name],                        @table_id = [table_id],                        @table_name = [table_name],                         @index_id = [index_id],                        @index_name = [index_name],                        @partition_number = [partition_number]                  from #index_rebuild_online                  where rid = @recount                           exec                         (                            'use [' + @dbname + '] alter index ' + @index_name + ' on ' + @schema_name + '.' + @table_name + '                              rebuild with(online = on, fillfactor = ' + @FillFactor + ', maxdop = ' + @MaxDop + ')'                        )                                   update #fragreport                    set [ddl_operation] = 3,                        [ddl_text] = 'INDEX WAS REBUILT ONLINE'                 where [schema_id] = @schema_id                    and [table_id] = @table_id                    and [index_id] = @index_id                    and [partition_number] = @partition_number                             set @recount = @recount + 1             end            end                if @Debug = 1        begin            print 'OFFLINE/ONLINE INDEX REBUILD OPERATIONS HAVE BEEN COMPLETED.'        end end    -- rebuild online indexesif @RebuildByPartition = 0 and @IncludeOffline = 0beginif (select count(*) from #index_rebuild_online) > 0    begin             select @number_of_index_rebuild_online = count(*) from #index_rebuild_online         set @recount = 1                      while @recount <= @number_of_index_rebuild_online             begin                  select @schema_id = [schema_id],                        @schema_name = [schema_name],                        @table_id = [table_id],                        @table_name = [table_name],                         @index_id = [index_id],                        @index_name = [index_name],                        @partition_number = [partition_number]                  from #index_rebuild_online                  where rid = @recount                           exec                         (                            'use [' + @dbname + '] alter index ' + @index_name + ' on ' + @schema_name + '.' + @table_name + '                              rebuild with(online = on, fillfactor = ' + @FillFactor + ', maxdop = ' + @MaxDop + ')'                        )                                   update #fragreport                    set [ddl_operation] = 3,                        [ddl_text] = 'INDEX WAS REBUILT ONLINE'                 where [schema_id] = @schema_id                    and [table_id] = @table_id                    and [index_id] = @index_id                    and [partition_number] = @partition_number                             set @recount = @recount + 1             end    end        if @Debug = 1    begin        print 'ONLINE INDEX REBUILD OPERATIONS HAVE BEEN COMPLETED.'    end     end        -- gather post-fragmentation statistics     update #fragreport        set [post_fragmentation_in_percent] = d.[avg_fragmentation_in_percent]     from #fragreport s cross apply (select [database_id],                 [object_id],                 [index_id],                 [partition_number],                 [index_type_desc],                 [alloc_unit_type_desc],                 [index_depth],                 [index_level],                 [avg_fragmentation_in_percent],                 [fragment_count],                 [avg_fragment_size_in_pages],                 [page_count],                 [avg_page_space_used_in_percent],                 [record_count],                 [ghost_record_count],                 [version_ghost_record_count],                 [min_record_size_in_bytes],                 [max_record_size_in_bytes],                 [avg_record_size_in_bytes],                 [forwarded_record_count]             from sys.dm_db_index_physical_stats (s.database_id, s.table_id, s.index_id, s.partition_number, NULL)) d          if @Debug = 1begin    print 'POST-FRAGMENTATION STATISTICAL INFORMATION HAS BEEN COLLECTED.'end      if @MaintainHistory = 1begin    if not exists (select * from msdb.sys.objects where name = 'index_maintenance_history')    begin             create table msdb.dbo.index_maintenance_history            (                  [rid] int identity (1,1),                  [database_name] sysname,                 [schema_name] sysname,                 [table_name] sysname,                 [index_name] sysname,                 [partition_scheme] sysname NULL,                 [partition_number] int,                 [pre_fragmentation_in_percent] float,                 [post_fragmentation_in_percent] float,                 [page_count] int,                 [ddl_operation] int,                 [ddl_text] char(100),                 [insert_date] datetime default getdate()             )                 end     insert into msdb.dbo.index_maintenance_history     (      [database_name],     [schema_name],     [table_name],     [index_name],     [partition_scheme],     [partition_number],     [pre_fragmentation_in_percent],     [post_fragmentation_in_percent],     [page_count],     [ddl_operation],     [ddl_text],     [insert_date]    )    select [database_name],         [schema_name],         [table_name],         [index_name],         isnull([partition_scheme],'Default') as [partition_scheme],         isnull([partition_number],1) as [partition_number],         isnull([pre_fragmentation_in_percent],0) as [pre_fragmentation_in_percent],         --isnull(isnull([post_fragmentation_in_percent],[pre_fragmentation_in_percent]),0) as [post_fragmentation_in_percent],         isnull([post_fragmentation_in_percent],0) as [post_fragmentation_in_percent],         isnull([page_count],0) as [page_count],         isnull([ddl_operation],9) as [ddl_operation],         isnull([ddl_text],'NO ACTION NECESSARY') as [ddl_text],         [insert_date]     from #fragreport          if @Debug = 1        begin            print 'HISTORICAL INFORMATION HAS BEEN COLLECTED.'        end      end if @EmailNotification = 1begin         declare @tableHTML nvarchar(max);        set @tableHTML =            N'<STYLE TYPE="text/css">TD{font-family: calibri; font-size: 9pt;}</STYLE>' +             N'<b><font face="calibri" size="2">Index(s) targeted for maintenance operations have logical fragmentation > than ' + convert(char(2),@LogicalFragmentationPercent) + ' percent. Indexes with < ' + convert(char(4),@MinPages) + ' pages are ignored by default.</font></b><br><br>' +            N'<table border="1" cellpadding="2" cellspacing="2" border="1">' +            N'<tr><th><font face="calibri" size="2">Database Name</font></th>' +            N'<th><font face="calibri" size="2">Table Name</font></th>' +            N'<th><font face="calibri" size="2">Index Name</font></th>' +            N'<th><font face="calibri" size="2">Partition Scheme</font></th>' +            N'<th><font face="calibri" size="2">Partition Number</font></th>' +            N'<th><font face="calibri" size="2">Operation</font></th>' +            N'<th><font face="calibri" size="2">Pre-Logical Fragmentation</font></th>' +            N'<th><font face="calibri" size="2">Post-Logical Fragmentation</font></th>' +            N'<th><font face="calibri" size="2">Index Page Count</font></th>' +            cast ( ( select td = [database_name], '',                             td = [schema_name] + '.' + [table_name], '',                             td = [index_name], '',                             td = isnull([partition_scheme],'Default'), '',                             td = isnull([partition_number],1), '',                             case                              when [ddl_operation] = 0 then '<td bgcolor="#FFFF00">REBUILD MUST BE PERFORMED OFFLINE</td>'                             when [ddl_operation] = 1 then '<td bgcolor="#FF0000">INDEX WAS REBUILT OFFLINE BY PARTITION</td>'                             when [ddl_operation] = 2 then '<td bgcolor="#FF0000">INDEX WAS REBUILT OFFLINE</td>'                             when [ddl_operation] = 3 then '<td bgcolor="#FF0000">INDEX WAS REBUILT ONLINE</td>'                             when [ddl_operation] = 4 then '<td bgcolor="#00FF00">INDEX CONTAINS < ' + convert(char(4),@MinPages) + ' PAGES</td>'                             else '<td bgcolor="#00FF00">NO ACTION NECESSARY</td>'                             end,'',                                 td = convert(char,convert(int,isnull([pre_fragmentation_in_percent],0))) + '%', '',                         --td = convert(char,convert(int,isnull(isnull([post_fragmentation_in_percent],[pre_fragmentation_in_percent]),0))) + '%', '',                         td = convert(char,convert(int,isnull([post_fragmentation_in_percent],0))) + '%', '',                             td = convert(char,isnull([page_count],0)), ''                     from #fragreport                  order by [table_name]                        for xml path('tr'), type ) as nvarchar(max) ) +            N'</table>' ;                        --Format the HTML            set @tableHTML = REPLACE( @tableHTML, '<', '<' );            set @tableHTML = REPLACE( @tableHTML, '>', '>' );            set @tableHTML = REPLACE( @tableHTML, '&', '&' );                    exec msdb.dbo.sp_send_dbmail @recipients=@EmailRecipients,@subject = @EmailSubject,@body = @tableHTML,@body_format = 'HTML';                        if @Debug = 1            begin                print 'E-MAIL NOTIFICATION HAS BEEN SENT.'            end             end        if @OutputResults = 1begin select [database_name],         [schema_name],         [table_name],         [index_name],         [partition_scheme],         [partition_number],         [pre_fragmentation_in_percent],         [post_fragmentation_in_percent],         [page_count],         [ddl_text]     from #fragreport      end-- housekeeping     drop table #fragreport drop table #index_rebuild_offline drop table #index_rebuild_onlinedrop table #index_rebuild_by_partitionexec ('if exists (select name from tempdb..sysobjects where name = ''##clustered_indexes_with_offline_data_types_' + @table_guid + ''')begin    drop table ##clustered_indexes_with_offline_data_types_' + @table_guid + 'end ')exec ('if exists (select name from tempdb..sysobjects where name = ''##nonclustered_indexes_with_offline_data_types_' + @table_guid + ''')begin    drop table ##nonclustered_indexes_with_offline_data_types_' + @table_guid + 'end ')

Rate

3 (2)

Share

Share

Rate

3 (2)