Wednesday, June 10, 2009

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

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:


Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

2 comments:

Suresh Koritala said...

thank you very much and i am new for DBA so i need to rebuil the indes for whole database so which is the better way means offline or online ???

Artemakis Artemiou [MVP] said...

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