Thomas Williams Tech Blog

Re-indexing Tables with Original Fillfactor

As part of a weekly database job that runs out-of-hours, I re-index all the tables in a particular reporting database. After playing around with index fillfactor I arrived at a fill factor of 90 as the tables are mostly being read and only being updated a couple of times a month. For my key tables, I made sure I set up clustered indexes on the fields that were being used most commonly in my queries (which of course speeds data access up considerably)

I discovered a simple script that would rebuild indexes on all tables in a database to have a fill factor of 90 (see http://www.sql-server-performance.com/rebuilding_indexes.asp), but I wanted more customisation so wrote a script to rebuild all indexes with their original fill factor (some of may tables are set higher as the table is never updated, and some are set lower as the table is updated more often).

So, here's the script, which doesn't actually do the re-indexing, just PRINTs the relevant statements to the Messages window (if using Query Analyzer):

  --table name variable
  DECLARE @TableName VARCHAR(255)
  --index name variable
  DECLARE @IndexName VARCHAR(255)
  --original fill afctor variable
  DECLARE @OriginalFillFactor TINYINT
  --override fill factor (can be NULL)
  DECLARE @OverrideFillFactor TINYINT
  --set override fill factor for indexes here, if required. This WILL overwrite the original value
  --for each index! (If commented out, original fill factor will be used)
  --SET @OverrideFillFactor = 90

  --temporary table for holding all indexes
  CREATE TABLE #tempIndexes 
  (
      index_name VARCHAR(255), 
      index_description VARCHAR(210), 
      index_keys NVARCHAR(2048)
  )
  --temporary table for holding table name, index name, index id and fillfactor
  DECLARE @temp TABLE (
      TableName VARCHAR(255), 
      IndexName VARCHAR(255), 
      IndexId INT, 
      OriginalFillFactor TINYINT
  )

  --set NOCOUNT ON
  SET NOCOUNT ON

  --get all the tables using INFORMATION_SCHEMA.TABLES into a cursor
  DECLARE TableCursor CURSOR FOR
  SELECT  TABLE_NAME 
  FROM    INFORMATION_SCHEMA.TABLES 
  WHERE   TABLE_TYPE = 'BASE TABLE'

  --open the cursor
  OPEN TableCursor
  --get the first row
  FETCH NEXT FROM TableCursor INTO @TableName
  --if we got a valid row, continue
  WHILE @@FETCH_STATUS = 0 BEGIN 
      --clear out the #temp table, containing indexes for the passed table
      DELETE FROM #tempIndexes
      --get indexes into #tempIndexes. This may print a warning message "The object does not 
      --have any indexes.", which can be ignored
      INSERT INTO #tempIndexes EXEC sp_helpindex @TableName

      --insert into outer table, the table name, the index name
      INSERT INTO @temp ([TableName], [IndexName])
      SELECT  @TableName, index_name
      FROM    #tempIndexes

      FETCH NEXT FROM TableCursor INTO @TableName
  END

  CLOSE TableCursor
  DEALLOCATE TableCursor

  --drop the temp index table
  DROP TABLE #tempIndexes

  --now get the index id and original fill factors
  UPDATE  @temp
  SET     IndexId = i.[indid], OriginalFillFactor = i.[OrigFillFactor]
  FROM    sysindexes i, sysobjects o, @temp T
  WHERE   i.[id] = o.[id] AND o.[name] = T.TableName AND T.IndexName = i.[name]

  --lastly loop through the @temp table and re-index
  DECLARE DBREINDEXCursor CURSOR FOR
  SELECT  TableName, IndexName, OriginalFillFactor 
  FROM    @temp
  --open cursor
  OPEN DBREINDEXCursor
  --get first row into local variables
  FETCH NEXT 
  FROM    DBREINDEXCursor 
  INTO    @TableName, @IndexName, @OriginalFillFactor
  --loop through cursor while there are rows remaining
  WHILE @@FETCH_STATUS = 0
  BEGIN
      --if we've been given an override fill factor, apply it here
      IF NOT (@OverrideFillFactor IS NULL) SET @OriginalFillFactor = @OverrideFillFactor 
      --do the re-index operation here
      PRINT 'DBCC DBREINDEX(' + @TableName + ',' + @IndexName + 
          ',' + CONVERT(VARCHAR(3), @OriginalFillFactor) + ')'
      --get the next row
      FETCH NEXT 
      FROM    DBREINDEXCursor 
      INTO    @TableName, @IndexName, @OriginalFillFactor
  END

  --close and deallocate the cursor
  CLOSE DBREINDEXCursor
  DEALLOCATE DBREINDEXCursor

  GO

Some indexing resources I found helpful are at http://www.c-sharpcorner.com/Code/2004/March/SQLPerformanceChecklist06.asp, http://www.extremeexperts.com/sql/articles/BestPractices.aspx and http://www.sql-server-performance.com/rebuilding_indexes.asp.

Share this post:  Email | Digg This! | Stumble This! | Save to del.icio.us | Share on Facebook | Add to Live Favorites |