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

In one of my previous articles, I talked about Index Fragmentation in SQL Server in terms of how to track it and how to resolve it. In this post, we are going to see how to rebuild all the indexes of a database in SQL Server.

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.


Learn more tips like this! Check our online course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime access, Q&A, certificate of completion).

Below 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 or later:

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

 

Check our YouTube Video on SQL Server Index Maintenance

 

Read Also:

 

Featured Online Courses:

 

Other SQL Server Articles:

 

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

 

Check our other SQL Server Performance articles.

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub



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

 

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

  1. 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

Comments are closed.