Skip to content

Latest commit

 

History

History
63 lines (42 loc) · 4.06 KB

rebuild-index-task.md

File metadata and controls

63 lines (42 loc) · 4.06 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords
Rebuild Index Task
Rebuild Index Task
chugugrace
chugu
03/14/2017
sql
integration-services
conceptual
sql13.dts.designer.rebuildindextask.f1
rebuilding indexes
indexes [Integration Services]
Rebuild Index task

Rebuild Index Task

[!INCLUDEsqlserver-ssis]

The Rebuild Index task rebuilds indexes in [!INCLUDEssNoVersion] database tables and views. For more information about managing indexes, see Reorganize and Rebuild Indexes.

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 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 PAD_INDEX = ON to allocate the free space specified by FILLFACTOR to the intermediate-level pages of the index.

  • Set IGNORE_DUP_KEY = ON to allow a multirow 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.

    [!NOTE]
    Online index operations are not available in every edition of [!INCLUDEmsCoName] [!INCLUDEssNoVersion]. For a list of features that are supported by the editions of [!INCLUDEssNoVersion], see Features Supported by the Editions of SQL Server 2016.

  • Specify a value for MAXDOP to limit the number of processors used in a parallel plan execution.

  • Specify WAIT_AT_LOW_PRIORITY, MAX_DURATION, and ABORT_AFTER_WAIT to control how long the index operation waits for low priority locks.

For more information about the ALTER INDEX statement and index rebuild options, see ALTER INDEX (Transact-SQL).

Important

The time the task takes to create the [!INCLUDEtsql] statement that the task runs is proportionate to the number of indexes the task rebuilds. If the task is configured to rebuild indexes in all the tables and views in a database with a large number of indexes, or to rebuild indexes in multiple databases, the task can take a considerable amount of time to generate the Transact-SQL statement.

Configuration of the Rebuild Index Task

You can set properties through [!INCLUDEssIS] Designer. This task is in the Maintenance Plan Tasks section of the Toolbox in [!INCLUDEssIS] Designer.

For more information about the properties that you can set in [!INCLUDEssIS] Designer, click the following topic:

Rebuild Index Task (Maintenance Plan)

Related Tasks

For more about how to set these properties in [!INCLUDEssIS] Designer, see Set the Properties of a Task or Container.

See Also

Integration Services Tasks
Control Flow