Rebuilding Indexes

Index and data pages can experience internal fragmentation. Index and table structures can experience logical scan and extent scan fragmentation.

To create a daily job that rebuilds indexes and runs automatically outside business hours and within your maintenance window, run the following script.

USE [msdb]

GO

/****** Object:  Job [Index Maintenance]    Script Date: 2/8/2017 9:56:08 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 2/8/2017 9:56:08 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Index Maintenance', 

		@enabled=1, 

		@notify_level_eventlog=0, 

		@notify_level_email=0, 

		@notify_level_netsend=0, 

		@notify_level_page=0, 

		@delete_level=0, 

		@description=N'No description available.', 

		@category_name=N'[Uncategorized (Local)]', 

		@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Index Maintenance]    Script Date: 2/8/2017 9:56:08 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Index Maintenance', 

		@step_id=1, 

		@cmdexec_success_code=0, 

		@on_success_action=1, 

		@on_success_step_id=0, 

		@on_fail_action=2, 

		@on_fail_step_id=0, 

		@retry_attempts=0, 

		@retry_interval=0, 

		@os_run_priority=0, @subsystem=N'TSQL', 

		@command=N'DECLARE @Database VARCHAR(255)   

DECLARE @Table VARCHAR(255)  

DECLARE @cmd NVARCHAR(500)  

DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  

SELECT name FROM master.dbo.sysdatabases   

WHERE name NOT IN (''master'',''msdb'',''tempdb'',''model'',''distribution'')   

ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

WHILE @@FETCH_STATUS = 0  

BEGIN  

   SET @cmd = ''DECLARE TableCursor CURSOR FOR SELECT ''''['''' + table_catalog + ''''].['''' + table_schema + ''''].['''' + 

  table_name + '''']'''' as tableName FROM ['' + @Database + ''].INFORMATION_SCHEMA.TABLES 

  WHERE table_type = ''''BASE TABLE''''''   

   -- create table cursor  

   EXEC (@cmd)  

   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   

   WHILE @@FETCH_STATUS = 0   

   BEGIN   

       IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

       BEGIN

           -- SQL 2005 or higher command 

           SET @cmd = ''ALTER INDEX ALL ON '' + @Table + '' REBUILD WITH (FILLFACTOR = '' + CONVERT(VARCHAR(3),@fillfactor) + '')'' 

           EXEC (@cmd) 

       END

       ELSE

       BEGIN

          -- SQL 2000 command 

          DBCC DBREINDEX(@Table,'' '',@fillfactor)  

       END

       FETCH NEXT FROM TableCursor INTO @Table   

   END   

   CLOSE TableCursor   

   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  

END  

CLOSE DatabaseCursor', 

		@database_name=N'master', 

		@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily', 

		@enabled=1, 

		@freq_type=4, 

		@freq_interval=1, 

		@freq_subday_type=1, 

		@freq_subday_interval=0, 

		@freq_relative_interval=0, 

		@freq_recurrence_factor=0, 

		@active_start_date=20160805, 

		@active_end_date=99991231, 

		@active_start_time=20000, 

		@active_end_time=235959, 

		@schedule_uid=N'12200535-49e8-4951-9a89-071640f649e3'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO