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 (http://blog.SQLAuthority.com)

About these ads

42 thoughts on “SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor

  1. 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

  2. Pingback: 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 Journey to SQL Authority with Pinal Dave

  3. 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

    • 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;

  4. 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

  5. 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.’

  6. 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?

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

    • 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.

  8. 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!

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

  10. 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

  11. 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);’

  12. Pingback: SQL SERVER – Correct Value for Fillfactor – Quiz – Puzzle – 24 of 31 « SQL Server Journey with SQL Authority

  13. 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!

  14. 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 ‘ ?

  15. 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.

  16. 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

  17. 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

  18. 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

  19. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

  20. 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.

  21. 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

  22. 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)

  23. 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.

  24. 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))

  25. Pingback: SQL SERVER – Change Fill Factor – SQL in Sixty Seconds #069 | Journey to SQL Authority with Pinal Dave

  26. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s