GentleRebuild is Powershell script for online index rebuild in high-load clustered MSSQL databases working 24/7 - Enterprise Edition is preferred, Standard Edition is supported with limitations.

Detailed description:

Script executes ALTER INDEX ... REBUILD/REORGANIZE in a safe controlled mode using 2 threads: one thread is executing the command while second one is checking the environment for several conditions. What is controlled:

SQL server script

Powershell Script parameters

When you run powershell script, supply a configuration file name as first parameter, for example:

powershell .\GentleRebuild.ps1 param

where param.ps1 - is powershell files setting parameter variables inside. Check config_example.ps1

All parameters in detail:

Interactive menu

By pressing Ctrl/C while script is progressing (not throttling) you enter an interactive menu (might take up to 15 seconds for the menu to appear):

For non-resumable index rebuilds, option S - STOP is not available

For REORGANIZE, there is an extra option - R - stops and recalculates new stats, because on REORGANIZE already done, then process goes to the nest index in a list.

It displays the current rebuild command and waits for a single character command. Don't leave it waiting for a command - it such state throttling conditions are not controlled. After command 'S' you can change the parameters and restart the script, it would continue from the same index. However MAXDOP can't be changed once rebuild is started.

Additional comments:

Standard Edition: tips and tricks

INDEX REORGANIZE: tips and tricks

Rebuild Log

Is saved into a table FRG_Log

Two server mode (taking benefits of the readable replica)

Index analysis creates huge read stress on a server, reading all tables. If possible, it makes sense to move this process to a secondary replica. Simply provide replica server name in replicaserver parameter. Note: it is assumed that there is a DBA database there, and that database is read-write, so, it is not part of the AlwaysOn (or for that group replica server is primary).

You should run stress-full FRG_FillFragmentation there. After finishing each index rebuild, Powershell script will also run FRG_FillFragmentationOne on the secondary server to update fragmentation statistics.

Note: before running FRG_FillFragmentationOne it waits for the current LSN on the primary database to be replicated to the replica server.

Change Log:






v1.2 v1.23



Check other projects: