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.

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

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)

, ,
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

  • A similar script by me for SQL Server 2000 which keeps current fillfactors:

    DECLARE @TableName varchar(255)

    DECLARE TableCursor CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_type = ‘base table’

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DBCC DBREINDEX (@TableName,’ ‘,0)
    FETCH NEXT FROM TableCursor INTO @TableName
    END

    CLOSE TableCursor

    DEALLOCATE TableCursor
    GO

    Reply
  • Cursors are helpful when you don;t Know SQL ;)

    Create Table #IndexTable
    (
    ID int Identity(1,1) not null,
    Table_Name varchar(100) not null
    )

    Insert into #IndexTable
    Select name from sys.tables
    Where type_desc = ‘USER_TABLE’

    Declare @TableName Varchar(100)
    Declare @sql Varchar(1000)
    Declare @fillfactor int
    Declare @Loop int
    Declare @cnt int
    Select @cnt = COUNT(*) from #IndexTable
    Set @Loop = 0
    set @fillfactor = 80
    While (@Loop < @cnt )
    BEGIN
    Select Top 1 @TableName = Table_Name from #IndexTable
    Where ID not in (Select Top(@Loop)ID from #IndexTable)

    Set @sql = ‘ALTER INDEX ALL ON ‘ + @TableName + ‘ REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
    Exec(@sql)
    Set @Loop += 1

    END

    DROP Table #IndexTable

    Reply
  • Brandon Reno
    May 30, 2009 2:57 am

    sp_msforeachtable ‘ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)’

    Reply
    • I like yours because of its simplicity…

      I modified it a bit… but kept it simple:

      USE DBName
      GO
      DECLARE @SQL AS VARCHAR(100)
      SET @SQL=’ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON)’
      EXEC sp_msforeachtable @command1=@SQL
      GO;

      Reply
  • an improvement on the rebuild script above.

    DECLARE @start varchar(20)
    DECLARE @startprint varchar(60)
    select @start = getdate()
    select @startprint = ‘Started batch at ‘ + @start
    print ‘ ‘
    print ‘ ‘
    print @startprint
    print ‘ ‘
    print ‘ ‘
    go
    — =========================================== Get list of tables into temp table
    Create Table #IndexTable
    (
    ID int Identity(1,1) not null,
    Table_Name varchar(100) not null
    )

    Insert into #IndexTable
    Select name from sys.tables
    Where type_desc = ‘USER_TABLE’ AND SUBSTRING([Name], 1, 3) ‘tmp’
    ORDER BY [Name]
    — =========================================== Declare Variables
    Declare @TableName Varchar(100)
    Declare @sql Varchar(1000)
    Declare @fillfactor int
    Declare @Loop int
    Declare @cnt int
    Declare @Message varchar(100)
    — =========================================== Get Count of tables
    Select @cnt = MAX([ID]) from #IndexTable
    SET @Message = convert(varchar(4), @cnt) + ‘ tables found’
    PRINT @Message

    Set @Loop = 0
    set @fillfactor = 80
    — =========================================== Loop thru tables
    While (@Loop <= @cnt )
    BEGIN
    — ————————————— Get next table
    Select Top 1 @TableName = Table_Name from #IndexTable
    Where ID not in (Select Top(@Loop)ID from #IndexTable)
    — ————————————— display status
    SET @Message = 'Working on table: ' + @TableName + ' ' + convert(varchar(4), @Loop) + ' of ' +
    convert(varchar(4), @cnt)
    print @Message
    — ————————————— Build command and execute it
    Set @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
    Exec(@sql)
    Set @Loop = @Loop + 1

    END
    — =========================================== Drop Temp Table
    DROP Table #IndexTable
    /* =========================================== Completed */
    DECLARE @end varchar(20)
    DECLARE @endprint varchar(60)
    print ' '
    print ' '
    select @end = getdate()
    select @endprint = 'Ended batch at ' + @end
    PRINT @endprint
    GO

    Reply
  • jernas solomon
    July 13, 2010 7:41 pm

    Thanks for u help .. this script very usefule for me..

    Reply
  • OBJECT_SCHEMA_NAME() appears to be a 2008 function and not available in 2005.
    Hence the script fails with ‘OBJECT_SCHEMA_NAME’ is not a recognised built-in function name.’

    Reply
  • Perhaps I am mistaken, but I was led to believe that rebuilding the Clustered Index of a table automatically led to the rebuilding of any NonClustered indexes within.

    That being the case, wouldn’t it be a bad idea to rebuild nonC’s after a Cluster rebuild?

    Reply
  • Pinal, I exactly copied your code and executed the code but I got the following error. I am using SQL 2008 standard.

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘*’

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘FILLFACTOR’.

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘REBUILD’.

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘WITH’.

    Msg 319, Level 15, State 1, Line 1
    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.

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘Of’.

    I could not figure out by myself. Any advice will be appreciated.

    Thanks
    Sar

    Reply
    • Jynesh, check to make sure your browser did not copy additional characters.

      I am running on SQL 2008 R2 with no problems. Also there is no asterisk (*) in his code, which is why I believe your copy and paste may be incorrect.

      Reply
  • Change this line:

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

    to:

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

    So you can use the script for tables w/ spaces, etc…

    Thanks!

    Reply
  • Dear Jynesh,
    The problem is with the inverted single commas. Replace them by again typing single inverted commas and the script should work fine.

    Thanks,

    Reply
  • “…answer is I just think having it 80 will do the job…”
    WOW Very convincing!
    Thanks!

    Reply
  • Dave,

    You’re awesome. When I google-search any SQL Server-related topic and I see sqlauthority in the results, I know my answer lies in a very concisely-worded article that gets the job done. I also look forward to seeing your picture at the top of the browser window squinting at me as if to say, “You’re here again? Why do you even still have your job!?!?”

    Please keep up the great work.

    Rick

    Reply
  • Chris

    Good call. Pulled my hair out until I saw your response. Spaces in table names was my issue.

    Thanks

    Reply
  • How often should indexes rebuilded?

    Reply
  • If you want to rebuild all indexes… this is by far the easiest way to do it:

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

    Reply
  • Thanks Pinal!

    Reply
  • Hi Pinal Dave

    Daily I use this Script in my Project to rebulid index. Is it good?

    Reply
  • Hi Dave,

    Could you please give the same script with ONLINE option should ON

    Reply
  • Hello. When I run this script via t-sql in 2008 R2 – I get the following error.

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword ‘WITH’.
    Msg 319, Level 15, State 1, Line 1
    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.

    With Debug, the process seems to step through the logic properly, prodcuing this error at the end. I’ve added semicolon’s to the statements and still get the same error. Any ideas?

    Thank you in advance. I come to your site often as I am new at SQL Server and you information is always very helpful!

    Reply
  • Mike,
    This script worked fine for me. The only place the word, “with” shows up is within the rebuild command string as it’s being constructed. Surely you replaced the tick marks being used as string delimiters in these posts with a single quote character required by SSMS.

    i.e ` for ‘ ?

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version