SQL Server - T-SQL script for database index maintenance

A good thing about "time" is that it also helps in adding more to our knowledge and knowledge is a powerful tool which helps us in implementing new things or may be see old things in new way. I recently revisited one of my old script which was for DB index maintenance and I've realized that it had become obsolete as technology and usage of the databases has changed over the years like now we are using more of high availability options, so this script should evolve as well. Moreover on certain SQL boxes it was not behaving as I intend it to be, so one fine day I sat down and decided to update the same.

As earlier this script can choose the type of index maintenance to be performed i.e. "Rebuilding" or "Reorganising" of the index after calculating the fragmentation level of an index. However this time around I've wrapped up the script in a stored procedure as on some boxes putting the script directly in the job was throwing "Syntax error" on some ghost characters and line numbers. I've also put in several checks like for "Online" or "Read Only" databases, or databases which are in "mirroring" along with "try and catch" for any other unexpected surprises. The (smarter) script is as below. Please leave your valued suggestions and feedback on the same and share if you like.

--========================================================

USE [master]

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Db_Maintenance]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[usp_Db_Maintenance]

GO

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===============================================

-- Author: Siddharth Chauhan

-- Description: Smart Script for DB index maintenance

-- ===============================================

CREATE proc [dbo].[usp_Db_Maintenance]

@dbn varchar(255)

as

begin

set quoted_identifier on

set nocount on

declare @dbname varchar(255), @flag int, @cmd nvarchar(max), @err_number varchar(15), @err_desc varchar(max)

Begin try

select @dbname = @dbn

set @flag = 0

If (@dbname not in ('master','msdb','model','tempdb'))

begin

     

      if exists (select 1 from sys.databases where name = @dbname and recovery_model_desc = 'FULL')

      begin

            if not exists (select 1 from sys.database_mirroring where database_id = DB_ID(@dbname))

            begin

                  select @cmd = N'alter database ' + QUOTENAME(@dbname) +' set recovery bulk_logged;'

                  exec sp_executesql @cmd

                  set @flag = 1;

            end

      end

      select @cmd = N'SELECT DB_NAME(database_id) "databasename",

      sch.name "schemaname", o.name "tablename",

      i.name "index_name", index_type_desc "index_type_desc",

      avg_fragmentation_in_percent "fragmentation_perc", fragment_count "No_of_fragments",

      avg_fragment_size_in_pages, i.allow_page_locks

      FROM sys.dm_db_index_physical_stats (db_id(''' + @dbname + N'''),null,null,null,null ) s join

      [' + @dbname + N'].sys.indexes i

      on i.object_id = s.object_id and i.index_id = s.index_id

      join ['+ @dbname + N'].sys.objects o on o.object_id =  s.object_id

      join ['+ @dbname + N'].sys.schemas sch on sch.schema_id = o.schema_id

      where

      --avg_fragmentation_in_percent > 90 AND

            s.index_id > 0 -- To ignore table\index heaps

      --AND page_count > 10; -- To ignore small tables'

     

      create table #index_stats (databasename varchar(255), schemaname varchar(255), tablename varchar(255),

      index_name varchar(255), index_type_desc varchar(50), fragmentation_perc float, No_of_fragments bigint,

      avg_fragment_size_in_pages float, [allow_page_locks] bit)

      --print @cmd

      insert into #index_stats

      exec sp_executesql @cmd

     

      ------------------------------------------------------------------------------------------

      --select * from #index_stats

      declare  @id nvarchar(255), @tn nvarchar(255), @frag int, @sn nvarchar(255), @page_lock bit

      declare cur_index cursor

      for select index_name, tablename, schemaname, fragmentation_perc, allow_page_locks from #index_stats where fragmentation_perc > 0

      --order by 3 desc

      open cur_index

      fetch next from cur_index into @id, @tn, @sn, @frag, @page_lock

      while @@FETCH_STATUS = 0

      begin

     

      begin try

     

      select @cmd = N'alter index ' + QUOTENAME(@id)+ N' on '+QUOTENAME(@dbname)+'.'+QUOTENAME(@sn)+'.'+QUOTENAME(@tn)+

                              case

                              when @frag < 31 and @page_lock = 1 then N' REORGANIZE; '

                              else N' REBUILD; ' end

      print @cmd

      exec sp_executesql @cmd

     

      end try

     

      begin catch

     

            select @err_number = convert(varchar(10),ERROR_NUMBER()), @err_desc = ERROR_MESSAGE();

            print 'error occurred on database: ' + db_name() + ', on table:' + QUOTENAME(@sn)+'.'+QUOTENAME(@tn);

            print 'error description is as follows: ';

            print 'error number: ' + @err_number;

            print 'error message: ' + @err_desc;

           

      end catch

      fetch next from cur_index into @id, @tn,@sn, @frag, @page_lock

      end

      close cur_index

      deallocate cur_index

     

      drop table #index_stats;

      If @flag = 1

      begin

            select @cmd = N'alter database ' + QUOTENAME(@dbname) +' set recovery full;'

            exec sp_executesql @cmd

            set @flag = 0;

      end

     

      drop table #index_stats

end

     

End try

Begin Catch

      select @err_number = convert(varchar(10),ERROR_NUMBER()), @err_desc = ERROR_MESSAGE();

      print 'fatal error occurred on database: ' + db_name();

      print 'error description is as follows: ';

      print 'error number: ' + @err_number;

      print 'error message: ' + @err_desc;

     

     

end catch

end

GO

--========================================================