Invoke-DbaDbIndexRebuild

Synopsis

Rebuilds or reorganizes indexes and heaps across one or more databases.

Description

Performs index maintenance against a collection of databases, tables, indexed views and indexes, exposing the full ALTER INDEX option set through SMO. Runs in three modes: Rebuild, Reorganize, or Auto, where Auto picks the operation per index from measured fragmentation the way Ola Hallengren’s IndexOptimize does.

Fragmentation is measured once per database with a LIMITED scan of sys.dm_db_index_physical_stats, averaged across partitions by page count and read from the IN_ROW_DATA allocation units only. That scan drives the Auto decision, the MinimumFragmentation and MinimumPageCount filters, and the reported before and after numbers.

Columnstore indexes are the exception: that DMV only sees their rowstore parts, so their fragmentation and page counts are reported as null and they are skipped in Auto mode or under either filter. Maintain them with an explicit Mode instead.

Heaps are included with IncludeHeap. Heaps cannot be reorganized, and the popular maintenance solutions leave them alone entirely, so this is the only way to defragment one without hand-writing ALTER TABLE … REBUILD. Rebuilding a heap also rebuilds the table’s nonclustered indexes, so those are not rebuilt a second time in the same run.

Objects that cannot be processed are skipped with a warning rather than failing the run, so a single unsupported index does not stop maintenance across the rest of the instance.

Syntax

Invoke-DbaDbIndexRebuild
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <String[]>]
    [[-ExcludeDatabase] <String[]>]
    [-AllDatabases]
    [-AllUserDatabases]
    [[-Table] <String[]>]
    [[-Index] <String[]>]
    [[-Mode] <String>]
    [[-ReorganizeThreshold] <Double>]
    [[-RebuildThreshold] <Double>]
    [[-MinimumFragmentation] <Double>]
    [[-MinimumPageCount] <Int32>]
    [-Online]
    [[-MaxDop] <Int32>]
    [[-FillFactor] <Int32>]
    [-PadIndex]
    [-SortInTempdb]
    [-Resumable]
    [[-ResumableMaxDuration] <Int32>]
    [-WaitAtLowPriority]
    [[-MaxDurationMinutes] <Int32>]
    [[-AbortAfterWait] <String>]
    [-IncludeHeap]
    [[-StatementTimeout] <Int32>]
    [[-InputObject] <Object[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example 1: Rebuilds every index on every table and indexed view in Northwind

PS C:\> Invoke-DbaDbIndexRebuild -SqlInstance sql2017 -Database Northwind

Example 2: Runs fragmentation driven maintenance across all user databases, leaving indexes under 1000 pages alone...

PS C:\> Invoke-DbaDbIndexRebuild -SqlInstance sql2017 -AllUserDatabases -Mode Auto -MinimumPageCount 1000

Runs fragmentation driven maintenance across all user databases, leaving indexes under 1000 pages alone, reorganizing those between 5 and 30 percent fragmented and rebuilding anything worse.

Example 3: Rebuilds one named index online, limited to four processors

PS C:\> Invoke-DbaDbIndexRebuild -SqlInstance sql2019 -Database Sales -Table dbo.Orders -Index IX_Orders_CustomerID -Online -MaxDop 4

Example 4: Rebuilds the indexes in Sales as a resumable online operation that pauses itself after an hour, so...

PS C:\> Invoke-DbaDbIndexRebuild -SqlInstance sql2019 -Database Sales -Online -Resumable -ResumableMaxDuration 60

Rebuilds the indexes in Sales as a resumable online operation that pauses itself after an hour, so maintenance fits inside a fixed window.

Example 5: Rebuilds the indexes in Staging and also rebuilds its heaps, which most maintenance solutions skip entirely

PS C:\> Invoke-DbaDbIndexRebuild -SqlInstance sql2019 -Database Staging -IncludeHeap

Example 6: Reorganizes the indexes on a table piped in from Get-DbaDbTable

PS C:\> Get-DbaDbTable -SqlInstance sql2019 -Database Sales -Table dbo.Orders | Invoke-DbaDbIndexRebuild -Mode Reorganize

Example 7: Runs Auto mode against the databases whose names start with prod

PS C:\> Get-DbaDatabase -SqlInstance sql2019 | Where-Object Name -match "^prod" | Invoke-DbaDbIndexRebuild -Mode Auto

Example 8: Rebuilds online, waiting at low priority for five minutes for the locks it needs, then killing the blocking...

PS C:\> Invoke-DbaDbIndexRebuild -SqlInstance sql2022 -Database Sales -Online -WaitAtLowPriority -MaxDurationMinutes 5 -AbortAfterWait Blockers

Rebuilds online, waiting at low priority for five minutes for the locks it needs, then killing the blocking sessions if they have not cleared.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. Requires SQL Server 2005 or later, because ALTER INDEX and sys.dm_db_index_physical_stats do not exist on SQL Server 2000.

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Database

Specifies which databases to maintain on the target instance. Accepts multiple database names.
Use this when you want index maintenance on a known set of databases rather than the whole instance.

-ExcludeDatabase

Excludes specific databases from index maintenance, including databases that arrived through the pipeline.
Useful when maintaining every user database but skipping ones that have their own maintenance window.

-AllDatabases

Targets every accessible database on the instance, including the system databases master, model and msdb.
Requiring this switch means an unqualified call never silently rebuilds every index on the instance.

-AllUserDatabases

Targets all user databases on the instance, excluding the system databases.
Use this for routine maintenance across an instance while leaving system databases alone.

-Table

Limits maintenance to specific tables. Accepts schema-qualified names such as dbo.Orders, and bracketed names for objects with special characters.
When omitted, every non-system table in the selected databases is processed along with any indexed views.

-Index

Limits maintenance to specific index names within the selected tables.
Use this to rebuild one known problem index rather than everything on the table. Heaps have no index name, so no heap is returned when this is specified.

-Mode

Controls which operation is performed. Rebuild issues ALTER INDEX … REBUILD, Reorganize issues ALTER INDEX … REORGANIZE, and Auto decides per index from measured fragmentation. Defaults to
Rebuild.
Reorganize is skipped with a warning for heaps, disabled indexes, and indexes with ALLOW_PAGE_LOCKS = OFF, none of which SQL Server will reorganize.
Auto is the Ola Hallengren approach: below ReorganizeThreshold the index is left alone, between the two thresholds it is reorganized, and at or above RebuildThreshold it is rebuilt.
Reorganize takes none of the rebuild options, so under Reorganize they are listed in a warning and skipped rather than refused. Auto still refuses an invalid rebuild combination, because Auto can
decide to rebuild.

-ReorganizeThreshold

Sets the fragmentation percentage at which Auto mode starts reorganizing an index. Defaults to 5.
Indexes below this level are skipped entirely because the cost of maintenance outweighs the benefit.

-RebuildThreshold

Sets the fragmentation percentage at which Auto mode switches from reorganize to rebuild. Defaults to 30.
Only applies when Mode is Auto.

-MinimumFragmentation

Skips any index whose measured fragmentation is below this percentage, in every mode.
Use this to make an explicit Rebuild or Reorganize run skip indexes that are already healthy. Columnstore indexes cannot be measured this way and are skipped with a warning when this is specified.

-MinimumPageCount

Skips any index or heap smaller than this many pages. Defaults to 0, which processes everything.
A common cutoff is 1000 pages, below which fragmentation rarely costs enough to be worth acting on. Columnstore indexes cannot be measured this way and are skipped with a warning when this is above 0.

-Online

Performs the rebuild online so the table stays available to other sessions for the duration.
Requires Enterprise or Developer edition, or Azure SQL Database. Anything that cannot be rebuilt online is skipped with a warning rather than being rebuilt offline behind your back: XML and spatial
indexes, which SQL Server never rebuilds online on any version, edition or platform, columnstore indexes before SQL Server 2019, heaps before SQL Server 2014, and disabled clustered indexes and
indexed views.

-MaxDop

Limits the number of processors used for the operation by adding MAXDOP, from 0 to 64.
Use this to stop a large rebuild from consuming every core on a busy instance.

-FillFactor

Sets the fill factor percentage applied during the rebuild, from 1 to 100.
Leaving free space on each page reduces page splits on indexes that see frequent inserts into the middle of the key range. Omit it to keep whatever fill factor the index already has.

-PadIndex

Applies the fill factor to the intermediate index pages as well as the leaf level.
Only meaningful alongside FillFactor, and ignored by REORGANIZE.

-SortInTempdb

Performs the intermediate sort for the rebuild in tempdb instead of the destination filegroup.
Speeds up rebuilds when tempdb is on separate storage, at the cost of extra tempdb space. Cannot be combined with Resumable.

-Resumable

Makes the rebuild resumable so it can be paused and continued rather than rolled back.
Requires SQL Server 2017 or later, or Azure SQL Database, and must be combined with Online. It is dropped, and the reason recorded in Notes, for everything SQL Server documents as unsupported: heaps,
columnstore indexes, disabled indexes, filtered indexes, a computed or rowversion key column, and a computed or LOB included column. Those objects are rebuilt without it rather than being skipped.

-ResumableMaxDuration

Sets how many minutes a resumable rebuild runs before it pauses on its own, from 1 to 10080 (7 days).
Use this to fit index maintenance into a fixed maintenance window without leaving a long rollback behind.

-WaitAtLowPriority

Makes the online rebuild wait at low priority for the schema modification locks it needs, so it does not block short queries behind it.
Requires SQL Server 2014 or later, or Azure SQL Database, and must be combined with Online.

-MaxDurationMinutes

Sets how many minutes the low priority wait lasts before AbortAfterWait takes effect, from 0 to 71582.
Only applies when WaitAtLowPriority is specified, and must be at least 1 when AbortAfterWait is not None.

-AbortAfterWait

Specifies what happens when the low priority wait expires. None lets the operation keep waiting normally, Self aborts the rebuild, and Blockers kills the sessions holding the blocking locks. Defaults
to None.
Anything other than None needs both WaitAtLowPriority and MaxDurationMinutes, because there is otherwise no wait to abort after.

-IncludeHeap

Includes heaps, meaning tables with no clustered index, rebuilt with ALTER TABLE … REBUILD.
Heaps fragment through forwarded records and deleted rows but are ignored by most maintenance solutions, so they are opt-in here rather than silently rebuilt. A heap rebuild also rebuilds the table’s
nonclustered indexes, so the rest of that table is skipped for the run: their measured fragmentation is stale from that point on.

-StatementTimeout

Sets the command timeout in minutes for each operation, from 0 to 35791394. Defaults to 0 (infinite timeout).
Large rebuilds can run for hours, so the default lets them finish rather than failing partway through.

-InputObject

Accepts SMO objects from the pipeline: databases from Get-DbaDatabase, tables from Get-DbaDbTable, or views from Get-DbaDbView.
Use this to filter the target objects with the full power of those commands before handing them over for maintenance.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.

-WhatIf

Shows what would happen if the command were to run.

-Confirm

Prompts for confirmation of every step. For example:
Are you sure you want to perform this action?
Performing the operation “Rebuild ClusteredIndex PK_Orders” on target “dbo.Orders in Northwind on SQL2016”.
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is “Y”):

Outputs

PSCustomObject

Returns one object per index or heap that was actually processed. Objects filtered out by a threshold, or skipped because they are incompatible with the requested operation, produce verbose messages or warnings instead of output. Nothing is returned when WhatIf is used.

Default display properties (via Select-DefaultView):

• SqlInstance: The full SQL Server instance name (computer\instance)
• Database: Name of the database containing the object
• Schema: Schema of the table or view
• Table: Name of the table or view
• IndexName: Name of the index, or the table name when the object is a heap
• IndexType: The SMO index type such as ClusteredIndex or NonClusteredIndex, or “Heap” for a heap
• Operation: The operation performed, either Rebuild or Reorganize
• PageCount: Number of pages in the index or heap as measured before the operation (long); null for a columnstore index
• FragmentationBefore: Average fragmentation percentage before the operation (double, 0-100); null for a columnstore index
• FragmentationAfter: Average fragmentation percentage after the operation (double, 0-100); null when the operation failed or the object is a columnstore index
• Duration: Elapsed time of the operation as hours:minutes:seconds, where hours is a running total rather than a clock reading
• Success: Boolean indicating whether the operation completed without error

Additional properties available:

• ComputerName: The name of the computer hosting the SQL Server instance
• InstanceName: The SQL Server instance name
• Online: Boolean indicating whether the operation actually ran online; always true for a reorganize, which never takes the object offline
• Resumable: Boolean indicating whether the operation actually ran as resumable
• Start: DateTime when the operation began
• End: DateTime when the operation completed
• Notes: Error text when the operation failed, or an explanation of why requested options were suppressed; null otherwise