How to rebuild all the indexes of a database in SQL Server

How to rebuild all the indexes of a database in SQL Server

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
In one of my previous posts, I talked about Index Fragmentation in SQL Server in terms of how to track it and how to resolve it.

The techniques explained in that post provided ways of reorganizing/rebuilding specific indexes or all the indexes within a given table. But what about when the DBA needs to rebuild all the indexes within a database? How can he achieve this?

It is a fact that in some cases where a large amount of indexes in a database on SQL Server has a large percentage of fragmentation, then the recommended approach is to rebuild those indexes. To this end, in the worst scenario, the DBA will need to rebuild the indexes in all the tables of the database.

My previous post on the topic explained ways of rebuilding specific or all the indexes within a table.

Under normal circumstances there is not a direct way allowing to rebuild all the indexes of a database with a single command. A workaround is to run different rebuild statements for each table.

Though, I know that workarounds are not very desirable in many cases as they might demand a large amount of time 🙂

There is not need to worry 🙂 In SQL Server there is the undocumented stored procedure sp_MSforeachtable which allows for recursively executing a T-SQL statement (or more) for all the tables within a database with the use of a single line of code.

Hereunder I propose the syntax on how to rebuild all the indexes of a given database by utilizing the sp_MSforeachtable stored procedure.

SQL Server 2000
=============

–Rebuild all indexes with keeping the default fill factor for each index
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”

–Rebuild all indexes with specifying the fill factor
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, [FILL_FACTOR_PERC])”

SQL Server 2005/2008
=================

You can either use the syntax provided above for SQL Server 2000 or:

–Rebuild all indexes online with keeping the default fill factor for each index
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON)”

–Rebuild all indexes offline with keeping the default fill factor for each index
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)”
–Rebuild all indexes online with specifying the fill factor
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=ON)”

–Rebuild all indexes offline with specifying the fill factor
USE [DATABASE_NAME]

EXEC sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR=[FILL_FACTOR_PERC],ONLINE=OFF)”

Considerations
==========
DBCC DBREINDEX is always an offline operation.
Online index rebuild fails for the following cases:

  • XML index
  • Spatial index
  • Large object data type columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml

I hope you found this post useful. Drop me a line if you have any comments or questions!


My Latest Projects:



Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.

2 thoughts on “How to rebuild all the indexes of a database in SQL Server

  1. Artemakis Artemiou [MVP]

    Hi Suresh,

    When you rebuild indexes Online, the underlying tables and associated indexes are available for queries and data modification during the index operation. However, please note that there are some requirements/restrictions when using this feature. For example, the Online Indexing feature is only available in the Enterprise edition of SQL server. Also, up to SQL Server 2008 R2, you could not perform online rebuild on Large Object (LOB) data: varchar(max), nvarchar(max), varbinary(max), or XML. From SQL Server 2012 Enteprise and later there is not such restriction.

    When you rebuild indexes Offline, the operation acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation.

    Online index rebuild is a more "convenient" option but before planning to do so you need to check all the prerequisites and guidelines (links provided below) in order to ensure that you can use this feature.

    In the opposite case (offline index rebuild), you will have to take into consideration that there will be downtime for each table during the offline rebuild process of its indexes.

    For more info you can check out the following MSDN library articles:

    Guidelines for Online Index Operations
    How Online Index Operations Work
    Features Supported by the Editions of SQL Server 2014