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
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.
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
@Wentu I think you will need to look at each column for each table in code and check the data type.
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
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
Amazing this site!!! Thank you so much for such a pretty good job!!!
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.
How can we transfer the table from one database to another database with indexes in SQL server by (TSQL)
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
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)
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.
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))
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?
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?
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
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” ..
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.
you need to put filter to avoid those tables.
Hi Pinal,
How can I run your code with SORT_IN_TEMPDB = ON?
Thanks!