Interview Question of the Week #007 – How to Reindex Every Table of the Database?

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. How do you reindex every table of the database?

Question: How to reindex every table of the database?

Answer: Well, The answer of this question can be only given in the form of the script.

Reindex Every Table in SQL Server 2014 and Later 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
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

Check Before You Rebuild Everything

Rebuilding everything is simple, but it is not free. On a big database it takes time, generates a lot of transaction log and locks each table while it works, unless you rebuild online, which is an Enterprise edition feature. Before you reindex every table, I like to check which indexes really need it.

SELECT OBJECT_NAME(ips.object_id) AS TableName, i.name AS IndexName,
       ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
INNER JOIN sys.indexes AS i
    ON i.object_id = ips.object_id AND i.index_id = ips.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;

Focus on the indexes with high fragmentation and a decent page count. Small indexes with only a handful of pages gain almost nothing from a rebuild. One bonus to remember: a rebuild also updates the statistics of that index, so you do not need to update those statistics again right after. Run the check on a quiet day, save the output, and compare it again next month.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.