SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor

I just wrote down following script very quickly for one of the project which I am working on. The requirement of the project was that every index existed in database should be rebuilt with fillfactor of  80. One common question I receive why fillfactor 80, answer is I just think having it 80 will do the job.Fillfactor determines how much percentage of the space on each leaf-level page are filled with data.

The space which is left empty on leaf-level page is not at end of the page but the empty space is reserved between rows of data. This ensures that rearrangement of the data does not happen every time new data rows are inserted.

Following is incorrect image of leaf-level page of fillfactor.

SQL SERVER - 2008 - 2005 - Rebuild Every Index of All Tables of Database - Rebuild Index with FillFactor fillfactor1

Following is correct image of leaf-level page of fillfactor.

SQL SERVER - 2008 - 2005 - Rebuild Every Index of All Tables of Database - Rebuild Index with FillFactor fillfactor2

Let us see the T-SQL script which will rebuild each index of all tables of any particular database. Following script will work with SQL Server 2005 (SP2) and SQL Server 2008. It is simple cursor going over each table and rebuilding every index of database.

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET
@fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT
OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET
@sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE
TableCursor
DEALLOCATE TableCursor
GO

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Cursor, SQL Index, SQL Scripts
Previous Post
SQLAuthority News – Microsoft Certification Exam – Discount Code – Free Second Chance – MCTS, MCITP, MCPD
Next Post
SQL SERVER – FIX : ERROR : Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration

Related Posts

45 Comments. Leave new

  • Ok,
    obviously we have an html issue going on here, just do a find and replace. Just replace all occurances of ` with the single quote character your keyboard will produce.

    Reply
  • I added “ONLINE = ON” in the REBUILD WITH clause and I am notified one of the indexes cannot be rebuilt online because it refers a TEXT. is there a way to handle these cases authomatically?
    Thankx
    Wentu

    Reply
  • @Wentu I think you will need to look at each column for each table in code and check the data type.

    Reply
  • Hi Pinal,
    When I run your script through SSMS IT is sucessfully running. When I run as a job I am getting error :
    ALTER INDEX failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct. Can you help me with this.

    Thanks

    Padma

    Reply
  • pinal, This script does not work if the object is owned by a schema name like NT user name or if the table is having special charectors or blank spaces.

    Please test your script

    Reply
  • Emerson P. Bertolo
    February 2, 2013 1:31 am

    Amazing this site!!! Thank you so much for such a pretty good job!!!

    Reply
  • Hi Pinal, i ve been facing the same issue with my web appln timing out after migrating from sql 05 to 08. Executing an sp that returns for eg (11k rows) manually in mgmt studio runs in less than 10 seconds. But the functionality associated with this SP times out when accessing from UI. Do u think rebuilding indexes is a fix for my case? Any help is greatly appreciated.

    Reply
  • How can we transfer the table from one database to another database with indexes in SQL server by (TSQL)

    Reply
  • Hi, my problem is the compatibility mode.
    I created this script, and this is very usefull fo me.
    Enjoy

    DECLARE @DB INT
    DECLARE @OBJECT INT
    DECLARE @INDEX INT
    DECLARE @FRAG FLOAT
    DECLARE @ACTION VARCHAR(50) = NULL

    SELECT
    ROW_NUMBER() OVER(ORDER BY database_id, object_id, index_id) AS ROW_ID,
    database_id DATABASEID,
    object_id OBJECTID,
    index_id INDEXID,
    avg_fragmentation_in_percent FRAG
    INTO #IDX_DFGR
    FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0

    DECLARE @OBJECTS INT = (SELECT COUNT(OBJECTID) FROM #IDX_DFGR)

    WHILE @OBJECTS > 0
    BEGIN
    SET @DB = (SELECT DATABASEID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @OBJECT = (SELECT OBJECTID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @INDEX = (SELECT INDEXID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @FRAG = (SELECT FRAG FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)

    IF(@FRAG >= 30)
    SET @ACTION = ‘ REBUILD’
    ELSE
    SET @ACTION = ‘ REORGANIZE’

    DECLARE @DBNAME VARCHAR(128) = (SELECT DB_NAME(@DB))

    EXEC(‘USE [‘+@DBNAME+’];

    DECLARE @schemaname nvarchar(130);
    DECLARE @objectname nvarchar(130);
    DECLARE @indexname nvarchar(130);
    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    JOIN sys.schemas as s ON s.schema_id = o.schema_id
    WHERE o.object_id = ‘+@OBJECT+’;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = ‘+@OBJECT+’ AND index_id = ‘+@INDEX+’;
    EXEC (”ALTER INDEX ”+@indexname + ” ON ” +@schemaname+”.”+@objectname+”’+@ACTION+”’)
    PRINT(”DB: ‘+@DBNAME+’ IDX: ”+@indexname + ” TABLE: ” +@schemaname+”.”+@objectname+” ACTION: ‘+@ACTION+”’)
    ‘)

    SET @OBJECTS = @OBJECTS – 1
    END

    DROP TABLE #IDX_DFGR

    Reply
  • Initial script with no cursor
    declare @sql varchar(max) = ”
    select
    @sql = @sql + ‘
    DBCC DBREINDEX (”[‘ + TABLE_SCHEMA + ‘].[‘ + TABLE_NAME + ‘]”,” ”,0)’
    from INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = ‘BASE TABLE’

    exec (@sql)

    Reply
  • This is simple Script for same question.

    select ‘alter index ‘ +i.name+ ‘ on ‘ +o.name+ ‘ disable ‘ + CHAR(13)+Char(10)+’;’
    From sys.indexes i Inner Join sys.objects o
    On o.object_id = i.object_id
    Where o.is_ms_shipped = 0
    And i.index_id >= 1

    To enable – REBUILD the indexes.

    Reply
  • Zachary “ZBow” Bowles
    January 29, 2014 3:28 am

    what are the benefits of using the ssis task vs running something like this:

    TRUNCATE TABLE TRACKING..Ck_Idx_Frag

    DECLARE @DB INT
    DECLARE @OBJECT INT
    DECLARE @INDEX INT
    DECLARE @FRAG FLOAT
    DECLARE @ACTION VARCHAR(50) = NULL

    IF object_id(‘tempdb..#RPMemps’) IS NOT NULL
    BEGIN
    DROP TABLE #IDX_DFGR
    END

    SELECT /*looks at all db and tables, can be filtered to one db, to find what the frag % and select the ones larger than 10%*/
    ROW_NUMBER() OVER(ORDER BY database_id, object_id, index_id) AS ROW_ID,
    database_id DATABASEID,
    object_id OBJECTID,
    index_id INDEXID,
    avg_fragmentation_in_percent FRAG
    INTO #IDX_DFGR
    FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
    WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0

    DECLARE @OBJECTS INT = (SELECT COUNT(OBJECTID) FROM #IDX_DFGR)

    WHILE @OBJECTS > 0
    BEGIN
    SET @DB = (SELECT DATABASEID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @OBJECT = (SELECT OBJECTID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @INDEX = (SELECT INDEXID FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)
    SET @FRAG = (SELECT FRAG FROM #IDX_DFGR WHERE ROW_ID = @OBJECTS)

    IF(@FRAG >= 30) /*determine if a rebuild or reorganize is needed*/
    SET @ACTION = ‘REBUILD’
    ELSE
    SET @ACTION = ‘REORGANIZE’

    DECLARE @DBNAME VARCHAR(128) = (SELECT DB_NAME(@DB))

    EXEC(‘USE [‘+@DBNAME+’];

    DECLARE @schemaname nvarchar(130);
    DECLARE @objectname nvarchar(130);
    DECLARE @indexname nvarchar(130);
    SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    JOIN sys.schemas as s ON s.schema_id = o.schema_id
    WHERE o.object_id = ‘+@OBJECT+’;

    BEGIN TRY
    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = ‘+@OBJECT+’ AND index_id = ‘+@INDEX+’;
    EXEC (”ALTER INDEX ”+@indexname + ” ON ” +@schemaname+”.”+@objectname+”’+@ACTION+”’)
    /*PRINT(”DB: ‘+ @DBNAME +’ IDX: ”+ @indexname + ” TABLE: ” + @schemaname + ”.”+@objectname+” ACTION: ‘+@ACTION+”’)*//*was used during testing*/
    END TRY
    BEGIN CATCH
    Insert TRACKING..Ck_Idx_Frag
    Select ”’+ @DBNAME +”’ as DBName, object_name(‘+@OBJECT+’) as Tablename, ERROR_MESSAGE() AS ErrorMessage
    END CATCH
    ‘)

    SET @OBJECTS = @OBJECTS – 1
    END

    DROP TABLE #IDX_DFGR

    Select *
    From TRACKING..Ck_Idx_Frag /*error trapping*/

    –Create TABLE TRACKING..Ck_Idx_Frag (DBName NVARCHAR(200), Tablename NVARCHAR(200), ErrorMessage NVARCHAR(MAX))

    Reply
  • If I am using the following script found in this post:

    EXEC sp_MSForEachTable ‘Print “Rebuild index on: ?”; ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90);’

    1. I would like to specify only one table or a range of tables. how do I modify the following script?
    2. How do I modify the script to display each rebuild as it is done?
    3. How to specify the Rebuild in OffLINE or ONLINE mode?

    Reply
  • I copied Pinal script and keep trying to run it. Keep getting an error “Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.” I am using SQL 2008 R2. I have double checked and made sure everything copied correctly. It appears that it did. Any ideas on why it won’t run?

    Reply
    • you must have a table with space in the name. Try below

      DECLARE @TableName VARCHAR(255)
      DECLARE @sql NVARCHAR(500)
      DECLARE @fillfactor INT
      SET @fillfactor = 10
      DECLARE TableCursor CURSOR FOR
      SELECT ‘[‘+OBJECT_SCHEMA_NAME([object_id])+’].[‘+name +’]’ AS TableName
      FROM sys.tables
      OPEN TableCursor
      FETCH NEXT FROM TableCursor INTO @TableName
      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
      print @sql
      EXEC (@sql)
      FETCH NEXT FROM TableCursor INTO @TableName
      END
      CLOSE TableCursor
      DEALLOCATE TableCursor
      GO

      Reply
  • It should be:

    SELECT OBJECT_SCHEMA_NAME([object_id])+’.[‘+name+’]’ AS TableName

    as the version in article doesn’t work properly if you have a table which is named eg. “Order” ..

    Reply
  • Hi Pinal,

    I have 200 tables and i want to let 6 tables out of the rebuilding process.

    How can i accomplished that with your code.

    Reply
  • Alan Shepherd
    April 12, 2017 3:27 am

    Hi Pinal,

    How can I run your code with SORT_IN_TEMPDB = ON?

    Thanks!

    Reply

Leave a Reply