Some questions are extremely popular questions and they never get old. Here is one such question which I see very often asked to DBAs in their early career.
Question: How to re-index every table of the database?
Answer: Well, The answer of this question can be only given in the form of the script.
For SQL Server 2014 and later version
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 WHERE is_memory_optimized = 0 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
(Remember that alter index will fail on in-memory table, hence they needs to be excluded)
For SQL Server 2005, 2008 and 2012 versions
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
For SQL Server 2000 version
DECLARE @MyTable VARCHAR(255) DECLARE myCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' OPEN myCursor FETCH NEXT FROM myCursor INTO @MyTable WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Reindexing Table: ' + @MyTable DBCC DBREINDEX(@MyTable, '', 80) FETCH NEXT FROM myCursor INTO @MyTable END CLOSE myCursor DEALLOCATE myCursor EXEC sp_updatestats GO
Well, there are many different methods and many different variations out there for this script, however, above script has always worked for me and I trust them.
Here are few related blog posts one should refer for further information.
- SQL SERVER – Online Index Rebuilding Index Improvement in SQL Server 2012
- SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor
- SQL SERVER – ReIndexing Database Tables and Update Statistics on Tables
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
You could really hurt your overall performance by resetting the fillfactor on all indexes to only 80%, particularly on large+ tables. Why not just leave the fillfactor out, leaving it unchanged, rather than forcing a single value across all indexes?
I agree Scott. The script can be modified based on business requirement. There is no one-size-fit-all script which I can make.
Hello Sir ,
Can i use dbreindex dbcc command
alter proc usp_defrag as
begin
CREATE TABLE #TMPFRAG (
ObjectName VARCHAR (255),
ObjectId INT,
IndexName VARCHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity INT,
BestCount INT,
ActualCount INT,
LogicalFrag INT,
ExtentFrag INT)
INSERT INTO #TMPFRAG
EXECUTE (‘DBCC SHOWCONTIG WITH TABLERESULTS’)
DECLARE @OBJid INT,@OBJNAME VARCHAR(30),@scanden int
declare c1 CURSOR
for select ObjectId,OBJECTNAME,SCANDENSITY FROM #TMPFRAG where ScanDensity <=50
OPEN C1
FETCH NEXT FROM C1 INTO @OBJID,@OBJNAME,@SCANDEN
WHILE(@@FETCH_STATUS=0)
BEGIN
dbcc dbreindex(@OBJNAME);
FETCH NEXT FROM C1 INTO @OBJID,@OBJNAME,@SCANDEN
END
CLOSE C1
DEALLOCATE C1
end
exec usp_defrag
Following script works fine for me:
DECLARE @sql VARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
SET @sql = ‘ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = ‘ + CONVERT(VARCHAR(3),@fillfactor) + ‘)’
exec sp_MSforeachtable @command1=@sql
This will not work if your database contains in-memory tables.
The sp_msforeachtable method also works.
Examples:
exec sp_msforeachtable “set quoted_identifier on alter index all on ? rebuild”
exec sp_msforeachtable “set quoted_identifier on alter index all on ? reorganize”
exec sp_msforeachtable “set quoted_identifier on alter index all on ? rebuild with ( fillfactor = 100 )”
Yes. that would also work.
I use an application that has a lot of data. The application is doing heavy calculations that on a big database can take hours sometimes. Index defragmentation has a major impact on calculations performance, so a index maintenance is periodically required. I used many index defragmentation scripts, but this one had the biggest impact on performance for my application. After running this script, my calculation time was shortened to less than half of how long usually took to complete. So many thanks for this wonder script.
You should reindex the table which had a lot of changes. If no data is changed, don’t run reindex.