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)
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
Is there a script to keep the current fill factor for SQL2005 and 2008? can you also use 0 as in sql 2000?
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
sp_msforeachtable ‘ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)’
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;
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
Did you tested your script? or not. Do not submit your script without testing please.
Thanks for u help .. this script very usefule for me..
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.’
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?
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.
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!
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,
“…answer is I just think having it 80 will do the job…”
WOW Very convincing!
Thanks!
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
Chris
Good call. Pulled my hair out until I saw your response. Spaces in table names was my issue.
Thanks
How often should indexes rebuilded?
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);’
works a treat
Beautiful! Thanks!
Thanks Pinal!
Hi Pinal Dave
Daily I use this Script in my Project to rebulid index. Is it good?
Hi Dave,
Could you please give the same script with ONLINE option should ON
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!
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 ‘ ?