Rebuilding database indexes

Rebuilding database indexes and updating the statistics is mandatory to allow BigFix Inventory operate smoothly. In case you experience delay in UI response or importing data, make sure the defragmentation is done and statistics are up to date.

Note: It is recommended to run the operation once in a week to rebuild database.

The below example shows the simple approach to rebuild indexes and update statistics. It rebuilds all indexes independent of their fragmentation. The script assumes 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