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)
pinaldave
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
[...] January 31, 2009 by pinaldave Yesterday I came across following error while enabling fill factor for my database server, when I was trying to write article SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFa…. [...]
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)’
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