Maintenance

Invoke-sqmIndexOptimize

Reorganizes or rebuilds fragmented indexes with sensible defaults, without installing Ola Hallengren's IndexOptimize: REORGANIZE from 5 %, REBUILD from 30 % fragmentation, only indexes from 1000 pages, ONLINE where the edition allows it with a low-priority wait that never blocks the application, per partition, columnstore by deleted rows, and an optional time limit for the maintenance window.

Module: sqmSQLTool v1.9.178.0+

Requires: dbatools

ShouldProcess: Yes

Output: PSCustomObject per index / partition

Execution Flow

Why these defaults. Small indexes (below about 8 MB) are read from memory and their fragmentation does not matter. Light fragmentation is fixed by REORGANIZE, which is always online and can be interrupted; heavy fragmentation by REBUILD, which also refreshes the index statistics with a full scan. An ONLINE rebuild still needs a short schema lock at the start and end; WAIT_AT_LOW_PRIORITY with ABORT_AFTER_WAIT = SELF makes the rebuild give up instead of queueing every other session behind it. Heaps, disabled indexes (a rebuild would enable them), hypothetical and memory-optimized indexes are never touched.

Fragmentation is only read for indexes that sys.dm_db_partition_stats shows at or above -MinPageCount, so small indexes cost nothing. LIMITED mode still reads the non-leaf level of large indexes. Requires db_owner (ALTER on the tables) and VIEW DATABASE STATE. For Ola Hallengren's IndexOptimize as an Agent job see New-sqmOlaMaintenanceJobs.

Parameters

Parameter	Type	Required	Description
-SqlInstance	String	Optional	Target SQL Server instance. Default: `$env:COMPUTERNAME`.
-SqlCredential	PSCredential	Optional	SQL authentication credentials.
-Database	String	Optional	Database name or wildcard. System databases excluded unless named explicitly. Default: `*`.
-Table	String	Optional	Table name or wildcard pattern. Default: `*`.
-FragmentationLow	Int	Optional	From this fragmentation (%) an index is reorganized. Default: `5`.
-FragmentationHigh	Int	Optional	From this fragmentation (%) an index is rebuilt. Default: `30`.
-MinPageCount	Int	Optional	Minimum index size in 8 KB pages. Default: `1000`.
-Online	String	Optional	`Auto` (ONLINE where the edition allows it), `On`, `Off`. Default: `Auto`.
-AllowOfflineRebuild	Bool	Optional	Rebuild offline when ONLINE is not possible. `$false`: REORGANIZE instead. Default: `$true`.
-LowPriorityWaitMinutes	Int	Optional	MAX_DURATION of WAIT_AT_LOW_PRIORITY for ONLINE rebuilds (SQL Server 2014+). 0 = normal lock wait. Default: `1`.
-MaxDop	Int	Optional	MAXDOP for rebuilds. 0 = server setting. Default: `0`.
-SortInTempdb	Switch	Optional	SORT_IN_TEMPDB = ON for rebuilds.
-FillFactor	Int	Optional	FILLFACTOR for rebuilds of whole indexes (not partitions). 0 = keep existing. Default: `0`.
-ColumnstoreDeletedPercent	Int	Optional	Columnstore: REORGANIZE from this share of deleted rows. Default: `20`.
-PartitionLevel	Bool	Optional	Process partitioned indexes per partition. Default: `$true`.
-TimeLimitMinutes	Int	Optional	Do not start further commands after this many minutes; the rest is `NotStarted`. 0 = no limit.
-UpdateStatistics	Switch	Optional	Afterwards run Invoke-sqmUpdateStatistics (adaptive defaults) on the processed databases, within the remaining time limit.
-WhatIf	Switch	Optional	Shows the planned action and command per index.
-EnableException	Switch	Optional	Throw exceptions immediately instead of logging and continuing.

Return Value

Returns one PSCustomObject per index or partition that reached a threshold: SqlInstance, Database, Table, Index, IndexType, Partition, PageCount, FragmentationPercent (columnstore: deleted rows %), Action (REORGANIZE / REBUILD ONLINE / REBUILD OFFLINE), Status (Success / Failed / WhatIf / NotStarted / Skipped), Duration_s, Command, Message.

Examples

Example 1, Sensible defaults for all user databases

Invoke-sqmIndexOptimize -SqlInstance "SQL01"

Example 2, Maintenance window of 2 hours, statistics afterwards

Invoke-sqmIndexOptimize -SqlInstance "SQL01" -TimeLimitMinutes 120 -UpdateStatistics

Example 3, Show what would be done

Invoke-sqmIndexOptimize -SqlInstance "SQL01" -Database 'SalesDB' -WhatIf |
    Format-Table Table, Index, Partition, PageCount, FragmentationPercent, Action

Example 4, Standard edition during business hours: never lock a table offline

Invoke-sqmIndexOptimize -SqlInstance "SQL01" -AllowOfflineRebuild $false

Example 5, Typical result (lab run, partitioned table and columnstore)

Index Partition Action         Status  Duration_s Command
----- --------- ------         ------  ---------- -------
CX_P          2 REBUILD ONLINE Success        0,6 ALTER INDEX [CX_P] ON [dbo].[P] REBUILD PARTITION = 2 WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)));
IX_G          1 REBUILD ONLINE Success        0,2 ALTER INDEX [IX_G] ON [dbo].[P] REBUILD PARTITION = 1 WITH (ONLINE = ON (...));
CCI_C           REORGANIZE     Success        0,7 ALTER INDEX [CCI_C] ON [dbo].[C] REORGANIZE;

⬆ Index

All Commands