Rebuild Index Task

The Rebuild Index task rebuilds indexes in SQL Server database tables and views. By using the Rebuild Index task, a package can rebuild indexes in a single database or multiple databases. If the task rebuilds only the indexes in a single database, you can choose the views and tables whose indexes the task rebuilds.

This task encapsulates an ALTER INDEX REBUILD statement with the following index rebuild options:

Ø  Specify a FILLFACTOR percentage or use the original FILLFACTOR amount.

Ø  Set PAD_INDEX = ON to allocate the free space specified by FILLFACTOR to the intermediate-level pages of the index.

Ø  Set SORT_IN_TEMPDB = ON to store the intermediate sort result used to rebuild the index in tempdb. When the intermediate sort result is set to OFF, the result is stored in the same database as the index.

Ø  Set IGNORE_DUP_KEY = ON to allow a multi row insert operation that includes records that violate unique constraints to insert the records that do not violate the unique constraints.

Ø  Set ONLINE = ON to not hold table locks so that queries or updates to the underlying table can proceed during re-indexing.

When and how often should you Rebuild Indexes?

The performance of your indexes, and therefore your database queries, will degrade as you indexes become fragmented. The Rebuild Index task does a very good job of rebuilding indexes to remove logical fragmentation and empty space, and updating statistics. As such, it is very important that you schedule this task to run regularly.

Ø  Nightly, if required. If your indexes fragment rapidly, and you have a nightly maintenance window that allows you to run the Rebuild Index task, along with all the other maintenance tasks; then do so. Index fragmentation will degrade the performance of your indexes. Assuming that you have a maintenance window, rebuilding every night can't do any harm, and can very well boost the performance of your server.

Ø  Weekly, at minimum. If you can't perform this task nightly, then, at a minimum, it should be run once a week, during a maintenance window. If you wait much longer than a week, you risk hurting your SQL Server's performance due to the negative impact of wasted empty space and logical fragmentation.

Ø  Consider alternatives, otherwise. If you don't have a maintenance window long enough to run this task at least once a week, then you need to consider the following alternatives:

Ø  Use the online version of the Rebuild Index task – available only with the Enterprise Edition of SQL Server.

Ø  Use the Reorganize Index task followed by the Update Statistics task – if you're using the Standard Edition of SQL Server. This is your only real alternative when using the Maintenance Plan Wizard if want to avoid the Rebuide Index task.

Ø  Avoid the Maintenance Plan Wizard – T-SQL or Power Shell scripts offer greater control and flexibility over the exact nature and duration of this task.

Step by step creating Rebuild Index Task

       Take Rebuild Index task

Double click on Rebuild Index task.

Create new connection and write the connection name, server name and select windows authentication.

Click OK.

Select the database.

Click OK.

Select Object Type.

View T-sql

Click OK.

Now execute the package.

Package executed successfully.