Rebuilding database indexes

Rebuilding database indexes and updating the statistics is mandatory for BigFix Inventory to operate smoothly. If you experience a delay in UI response or importing data, make sure to defragment your hard disk and that statistics are up to date.

About this task

From version 10.0.4, for new installations and upgrades, a job is included in MS SQL database named “<InventoryDB> Full database Index Reorganization.” This job conducts required index maintenance.

The job follows the below criteria:

Procedure

The script runs everyday at 4 AM local time.
If a data import is running (scheduled import or manual import), the job waits for the import to finish, and then attempts to rebuild the index. The job attempts to run 16 times after 30 minute intervals. The total time spent on waiting is 8 hours. If the import does not complete in 8 hours, the job exits the queue with an error message in the status log. In that case, the index is rebuilt the next day at the scheduled time.
If an index rebuilding job already exists with the name, "<InventoryDB> Full database Index Reorganization," then the scripts do not overwrite the data and the existing configuration is retained.
For the BigFix Inventory index rebuilding job to execute, the SQL Server Agent must be configured and running. The agent is turned off in some cases. You need to manually turn it on. To learn about how to configure and how to start SQL Server Agent, refer to Configure SQL Server Agent and Start, Stop, or Pause the SQL Server Agent Service respectively.
It is recommended to configure SQL Server Agent to restart automatically in case of failure. For more information, see Auto Restart SQL Server Agent.

What to do next

For versions lower than 10.0.4, run the operation once a week to rebuild database.

The following example shows a simple approach to rebuild indexes and update statistics. The job rebuilds all indexes independent of their fragmentation. The script uses the standard database name as TEMADB.

      USE TEMADB
      GO
      IF EXISTS (SELECT
      *
      FROM dbo.imports
      WHERE success IS NULL)
      BEGIN
      PRINT N'CANNOT RUN index rebuild. BFI import is running!'
      PRINT N'Wait until BFI import finishes'
      END
      ELSE
      BEGIN
      DECLARE table_cursor CURSOR FOR
      SELECT
      table_schema,
      table_name
      FROM INFORMATION_SCHEMA.TABLES
      WHERE table_type = 'BASE TABLE'
      OPEN table_cursor
      DECLARE @tableName sysname
      DECLARE @tableSchema sysname
      FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
      WHILE @@fetch_status != -1
      BEGIN
      PRINT N'START alter index all on ' + @tableSchema
      + N'.' + @tableName + N' rebuild';
      EXECUTE (N'alter index all on ' + @tableSchema + N'.'
      + @tableName
      +
      N' rebuild')
      PRINT N'END alter index all on ' + @tableSchema
      + N'.' + @tableName + N' rebuild';
      FETCH NEXT FROM table_cursor INTO @tableSchema,
      @tableName
      END
      CLOSE table_cursor
      DEALLOCATE table_cursor
      PRINT N'START sp_updatestats';
      EXECUTE sp_updatestats
      PRINT N'END sp_updatestats';
      END
      GO

Creating Full Database Index Reorganization job

About this task

You can create a Full Database Index Reorganization job if required.

Below are the steps to create a Full Database Index Reorganization job in two different scenarios:

Delete the existing Full Database Index Reorganization job
1. Connect to the DB.
2. Run the following SQL query.
  1. Replace 'DATABASE_NAME_TO_REPLACE' with your actual BigFix Inventory database name.
  2. Execute the query.
    EXEC msdb.dbo.sp_delete_job @job_name=N'DATABASE_NAME_TO_REPLACE Full Database Index Reorganization', @delete_unused_schedule=1
3. Go to SQL Server Agent, expand the Jobs section and verify if Database Name Full Database Index Reorganization job does not exist.

Create the 'Full Database Index Reorganization' job if the job does not exist

Connect to the DB.

Run the following SQL query.

Replace 'DATABASE_NAME_TO_REPLACE' with your actual BigFix Inventory database name.

Execute the query.

BEGIN TRANSACTION
			DECLARE @ReturnCode INT
			SELECT @ReturnCode = 0
			
			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'DATABASE_NAME_TO_REPLACE Full Database Index Reorganization', 
					@enabled=1, 
					@notify_level_eventlog=2, 
					@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)]', 
					@job_id = @jobId OUTPUT
			IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check the BFI Import', 
	    @step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=3, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=16, 
		@retry_interval=30,
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'          
          IF EXISTS (SELECT top(1) 1 FROM dbo.imports WHERE success IS NULL)
          BEGIN
            RAISERROR(''BFI Data import is in progress, aborting query'',11,1);
          END
          ELSE PRINT(''Import has completed, moving to next step'')
          GO', 
		@database_name=N'DATABASE_NAME_TO_REPLACE', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

			EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reorganize all indexes', 
					@step_id=2, 
					@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'
			----------Common procedure to print Error details------
			create procedure #printErrorDetails as
			begin
				Declare @ErrorMessage nvarchar(4000)
				Declare @ErrorSeverity int
				Declare @ErrorState int
				
				SELECT
					@ErrorMessage = ERROR_MESSAGE(),
					@ErrorSeverity = ERROR_SEVERITY(),
					@ErrorState = ERROR_STATE()
				PRINT ''--------Error state: '' + CONVERT( VARCHAR(12), @ErrorState, 121) + ''. Error severity: '' + CONVERT( VARCHAR(12), @ErrorSeverity, 121) + ''. Error message: '' + @ErrorMessage
			end
			GO
			set nocount on
			-----------Global variable declarations--------------------
			Declare @MIN_REORG_FRAGMENTATION_LEVEL INT = 5
			Declare @MIN_REBUILD_FRAGMENTATION_LEVEL INT = 30
			Declare @PAGE_COUNT_THRESHOLD INT = 9
			Declare @PAGE_COUNT_REORG_THRESHOLD INT = 99
			Declare @REBUILD_FILL_FACTOR INT = 90
			Declare @REBUILD_MAX_DOP INT = 1
	
			PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  + '' Begin reindexing script for '' + DB_NAME()
			----------Table to Hold Fragmented Objects----
			create table #Reorganize (SchemaName varchar(50),tablename varchar(100),Indexname varchar(150),Fragmentation float,Pages integer)
			create table #RebuildOffline (SchemaName varchar(50), tablename varchar(100),Indexname varchar(150),Fragmentation float,Pages integer)
			create table #RebuildOnline (SchemaName varchar(50), tablename varchar(100),Indexname varchar(150),Fragmentation float,Pages integer)
	
			----------Table to hold the table names where AllowPageLocks is disabled----
			create table #Allow (tablen varchar(100))
	
			-----------For SQL Server Enterprise Edition online indexing is supported---------------------------------------------------------------
			if CHARINDEX(''Enterprise'', convert(nvarchar(64),(select serverproperty(''Edition''))) ) > 0
			begin
			-----------Inserting All fragmented table where fragmentation level is between @MIN_REORG_FRAGMENTATION_LEVEL to @MIN_REBUILD_FRAGMENTATION_LEVEL and having ALLOW_PAGE_LOCKS = ON in temptable----
			insert into #Reorganize(SchemaName,tablename,Indexname,Fragmentation,Pages)
	
			select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on
			o.schema_id= s.schema_id  left outer join sys.indexes i on
			o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips
			on i.object_id=ips.object_id and i.index_id=ips.index_id
			where o.type=''U'' and i.index_id > 0 and allow_page_locks = 1 and avg_fragmentation_in_percent between @MIN_REORG_FRAGMENTATION_LEVEL and @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD
	
			-----------Inserting All fragmented table where fragmentation level is greater than @MIN_REBUILD_FRAGMENTATION_LEVEL or (is greater than @MIN_REORG_FRAGMENTATION_LEVEL and ALLOW_PAGE_LOCKS = OFF) in temptable (online for non clustered indexes)-----
			insert into #RebuildOnline(SchemaName,tablename,Indexname,Fragmentation,Pages)
	
			select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on
			o.schema_id= s.schema_id  left outer join sys.indexes i on
			o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips
			on i.object_id=ips.object_id and i.index_id=ips.index_id
			where o.type=''U'' and i.index_id > 1 and ( ( avg_fragmentation_in_percent > @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_THRESHOLD ) or ( avg_fragmentation_in_percent > @MIN_REORG_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD and allow_page_locks = 0 ) )
	
			-----------Inserting All fragmented table where fragmentation level is greater than @MIN_REBUILD_FRAGMENTATION_LEVEL or (is greater than @MIN_REORG_FRAGMENTATION_LEVEL and ALLOW_PAGE_LOCKS = OFF) in temptable (online for clustered indexes NOT containing text, ntext or image fields)---
			insert into #RebuildOnline(SchemaName,tablename,Indexname,Fragmentation,Pages)
	
			select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on
			o.schema_id= s.schema_id  left outer join sys.indexes i on
			o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips
			on i.object_id=ips.object_id and i.index_id=ips.index_id
			where o.type=''U'' and i.index_id = 1 and o.name not in ( select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where data_type in (''TEXT'', ''NTEXT'',''IMAGE'') ) 
			and ( ( avg_fragmentation_in_percent > @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_THRESHOLD ) or ( avg_fragmentation_in_percent > @MIN_REORG_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD and allow_page_locks = 0 ) )
	
			-----------Inserting All fragmented table where fragmentation level is greater than @MIN_REBUILD_FRAGMENTATION_LEVEL or (is greater than @MIN_REORG_FRAGMENTATION_LEVEL and ALLOW_PAGE_LOCKS = OFF) in temptable (offline for clustered indexes containing text, ntext or image fields)--------
			insert into #RebuildOffline(SchemaName,tablename,Indexname,Fragmentation,Pages)
	
			select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on
			o.schema_id= s.schema_id  left outer join sys.indexes i on
			o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips
			on i.object_id=ips.object_id and i.index_id=ips.index_id
			where o.type=''U'' and i.index_id = 1 and o.name in ( select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where data_type in (''TEXT'', ''NTEXT'',''IMAGE'') ) 
			and ( ( avg_fragmentation_in_percent > @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_THRESHOLD ) or ( avg_fragmentation_in_percent > @MIN_REORG_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD and allow_page_locks = 0 ) )
	
			end
			else
			begin
			-----------For SQL Server Enterprise editions other than Enterprise the online indexing is not supported, so rebuild all indexes offline-------------
			----------Inserting into Reorganize temp table-----------
			insert into #Reorganize(SchemaName,tablename,Indexname,Fragmentation,Pages)
	
			select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on
			o.schema_id= s.schema_id  left outer join sys.indexes i on
			o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips
			on i.object_id=ips.object_id and i.index_id=ips.index_id
			where o.type=''U'' and i.index_id > 0 and avg_fragmentation_in_percent between @MIN_REORG_FRAGMENTATION_LEVEL and @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_REORG_THRESHOLD
	
			-----------Inserting into Rebuildoffline temp table-----------
			insert into #RebuildOffline(SchemaName,tablename,Indexname,Fragmentation,Pages)
	
			select s.name,o.name,i.name,ips.avg_fragmentation_in_percent,ips.page_count from sys.objects o left outer join sys.schemas s on
			o.schema_id= s.schema_id  left outer join sys.indexes i on
			o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ips
			on i.object_id=ips.object_id and i.index_id=ips.index_id
			where o.type=''U'' and avg_fragmentation_in_percent > @MIN_REBUILD_FRAGMENTATION_LEVEL and page_count > @PAGE_COUNT_THRESHOLD
	
			------------Determining the tablenames where AllowPageLocks is disabled---------
			insert into  #Allow(tablen)
			select distinct t.name from sys.tables t join sys.indexes i on t.object_id = i.object_id where allow_page_locks =0
	
			end
	
			-----------Variable declarations--------------------
			Declare @cmd varchar(1000)
			Declare @Iname varchar(250)
			Declare @Jname varchar(250)
			Declare @sname varchar(150)
			Declare @tname varchar(150)
			Declare @frag float
			Declare @pgs integer
			Declare @index varchar(1000)
			Declare @managePageLocks bit
			Declare @bypassReorg bit
	
			Declare @EXIT_CODE bit = 0
	
			-----------Manage Locking-----------
			set LOCK_TIMEOUT 5000
			set DEADLOCK_PRIORITY LOW
			-----------Cursor for reorganize---------------------
			Declare db_reindex CURSOR for
			select Indexname,SchemaName,tablename, Fragmentation, Pages from #Reorganize
			OPEN db_reindex
			FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs
			WHILE @@FETCH_STATUS = 0
			BEGIN
				set @managePageLocks = 0
				set @bypassReorg = 0
				set @Jname= @sname + ''.''+  @tname
				if exists (select 1 from #Allow where tablen = @tname)
				begin
					set @managePageLocks = 1
					set @index = @Jname + ''.'' + @Iname
					PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Allowing page locks for '' + @index
					set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' SET (ALLOW_PAGE_LOCKS = ON)''
					BEGIN TRY
						execute (@cmd)
					END TRY
					BEGIN CATCH
						set @bypassReorg = 1
						set @EXIT_CODE = 1
						PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Exception during ALTER INDEX for allowing page locks. By-passing reorg for '' + @index
						EXEC #printErrorDetails
					END CATCH
				end
				if ( @bypassReorg = 0 )
				begin
					set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' reorganize with (LOB_COMPACTION = ON)''
					PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  + '' Reorganize index '' +  @Iname + '' on ''+ @Jname + '' having fragmentation ''+ CONVERT( VARCHAR(24), @frag, 121) + '' and '' + CONVERT( VARCHAR(12), @pgs, 121) + '' pages''
					BEGIN TRY
						execute (@cmd)
					END TRY
					BEGIN CATCH
						set @EXIT_CODE = 1
						PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  +  '' - Exception during reorganize. Continuing.''		
						EXEC #printErrorDetails
					END CATCH
					if ( @managePageLocks = 1 )
					begin
						PRINT CONVERT( VARCHAR(24), GETDATE(), 121) + '' - Disallowing page locks for '' + @Jname + ''.'' + @Iname
						set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' SET (ALLOW_PAGE_LOCKS = OFF)''
						BEGIN TRY
							execute (@cmd)
						END TRY
						BEGIN CATCH
							set @EXIT_CODE = 1
							PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  +  '' - Exception during ALTER INDEX for disallowing page locks. Continuing.''
							EXEC #printErrorDetails
						END CATCH
					end
				end
				FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs
			END
	
			CLOSE db_reindex
			DEALLOCATE db_reindex
	
			------------Cursor For Rebuild Online------------
			Declare db_reindex CURSOR for
			select  Indexname,SchemaName,tablename, Fragmentation, Pages from #RebuildOnline
			OPEN db_reindex
			FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs
			WHILE @@FETCH_STATUS = 0
			BEGIN
				set @Jname= @sname + ''.''+  @tname
				set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' rebuild with (ONLINE = ON, MAXDOP = '' + CONVERT( VARCHAR(4), @REBUILD_MAX_DOP, 121) + '', PAD_INDEX = ON, FILLFACTOR = '' + CONVERT( VARCHAR(4), @REBUILD_FILL_FACTOR, 121) + '')''
				PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  + '' Rebuild index online (with MAXDOP = '' + CONVERT( VARCHAR(4), @REBUILD_MAX_DOP, 121) + '') '' +  @Iname + '' on ''+ @Jname + '' having fragmentation ''+ CONVERT( VARCHAR(24), @frag, 121) + '' and '' + CONVERT( VARCHAR(12), @pgs, 121) + '' pages''
				BEGIN TRY
					execute (@cmd)
				END TRY
				BEGIN CATCH
					set @EXIT_CODE = 1
					PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  + '' - Exception during rebuild.  Continuing.''
					EXEC #printErrorDetails
				END CATCH
				FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs
			END
	
			CLOSE db_reindex
			DEALLOCATE db_reindex
	
			------------Cursor For Rebuild Offline------------
			Declare db_reindex CURSOR for
			select  Indexname,SchemaName,tablename, Fragmentation, Pages from #RebuildOffline
			OPEN db_reindex
			FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs
			WHILE @@FETCH_STATUS = 0
			BEGIN
				set @Jname= @sname + ''.''+  @tname
				set @cmd= ''Alter Index '' + @Iname + '' on ''+ @Jname + '' rebuild with (PAD_INDEX = ON, FILLFACTOR = '' + CONVERT( VARCHAR(4), @REBUILD_FILL_FACTOR, 121) + '')''
				PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  + '' Rebuild index offline '' +  @Iname + '' on ''+ @Jname + '' having fragmentation ''+ CONVERT( VARCHAR(24), @frag, 121) + '' and '' + CONVERT( VARCHAR(12), @pgs, 121) + '' pages''
				BEGIN TRY
					execute (@cmd)
				END TRY
				BEGIN CATCH
					set @EXIT_CODE = 1
					PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  + '' - Exception during rebuild. Continuing.''
					EXEC #printErrorDetails
				END CATCH
				FETCH NEXT from db_reindex into @Iname,@sname,@tname,@frag,@pgs
			END
			CLOSE db_reindex
			DEALLOCATE db_reindex
			PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  + '' End reindexing script for '' + DB_NAME()
	
			if ( @EXIT_CODE = 1 )
			begin
				PRINT CONVERT( VARCHAR(24), GETDATE(), 121)  + '' Reindexing script completed but an error occurred and one of the steps failed!''
			end
			', 
					@database_name=N'DATABASE_NAME_TO_REPLACE', 
					@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'Runs daily at 04:00 AM', 
					@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=20210601, 
					@active_end_date=99991231, 
					@active_start_time=40000, 
					@active_end_time=235959, 
					@schedule_uid=N'4e4d7b59-fd7f-4772-a7c5-a5565443f9d4'
			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 to SQL Server Agent, expand the Jobs section and verify if Database Name Full Database Index Reorganization job exist.