Functions/Optimize-SqlIndexFragmentation.ps1

function Optimize-SqlIndexFragmentation {
    <#
.SYNOPSIS
    Optimize SQL Index Fragmentation
.DESCRIPTION
    Optimize SQL Index Fragmentation, on a database or a table LIKE string. Type can either be 'Rebuild', or 'Reorganize'
.PARAMETER ServerInstance
    Database instance that you wish to connect to. Defaults to $env:COMPUTERNAME
.PARAMETER Database
    String containing text for database name to be LIKE. Wildcards can be used. Defaults to '*'
.PARAMETER Table
    String containing text for table name to be LIKE. Wildcards can be used. Defaults to '*'
.PARAMETER Type
    String value, either 'Rebuild', or 'Reorganize'. Defaults to 'Reorganize'
.PARAMETER Interactive
    Switch whether output should be interactive and provide progress
.PARAMETER IncludeSystemDatabase
    Switch whether to include system databases
.PARAMETER MinFragmentation
    Integer representing the minimum fragmentation percentage level you want to address. Valid range 1-99, defaults to 10
.PARAMETER MinPageCount
    Integer representing the minimum number of pages that must be in index before processing. Must be greater than 0. Default 200.
.EXAMPLE
    Optimize-SqlIndexFragmentation
 
    Returns index fragmentation on the default database instance on the local computer
.NOTES
    1 - User running this function must have Windows authentication to the database server
    2 - By default system databases are excluded
    3 - outputs a psobject containing:
        DatabaseName, Schema, Table, Index, Avg_Fragmentation_In_Percent, Page_Count
    4 - If edition of SQL is 'Enterprise' it can rebuild index in online mode
    5 - Requires Get-SqlIndexFragmentation function
    6 - Put in error checking to get around lack of INDEX PAGE LEVEL LOCKING
#>


    [CmdletBinding()]
    [OutputType('psobject')]
    param (
        [string] $ServerInstance = $env:COMPUTERNAME,

        [string] $Database = '*',

        [string] $Table = '*',

        [ValidateSet('Rebuild', 'Reorganize')]
        [string] $Type = 'Reorganize',

        [switch] $Interactive,

        [switch] $IncludeSystemDatabase,

        [ValidateRange(1, 99)]
        [int] $MinFragmentation = 10,

        [ValidateScript( { ($_ -gt 0) })]
        [int] $MinPageCount = 200
    )

    begin {
        Write-Verbose -Message "Starting [$($MyInvocation.Mycommand)]"
        Write-Verbose -Message "ServerInstance [$ServerInstance]"
        Write-Verbose -Message "Database [$Database]"
        Write-Verbose -Message "Table [$Table]"
        Write-Verbose -Message "Type [$Type]"
        Write-Verbose -Message "Interactive [$Interactive]"
        Write-Verbose -Message "IncludeSystemDatabase [$IncludeSystemDatabase]"
        Write-Verbose -Message "MinFragmentation [$MinFragmentation]"
        Write-Verbose -Message "MinPageCount [$MinPageCount]"

        try {
            $SqlDbParam = @{
                ServerInstance = $ServerInstance
                Database       = 'master'
                Query          = "SELECT SERVERPROPERTY('Edition') as 'Edition';"
            }
            $SqlEdition = Invoke-Sqlcmd @SqlDbParam
            if ($SqlEdition.Edition -match 'Enterprise') {
                $Online = ' WITH (ONLINE = ON)'
            } else {
                $Online = ''
            }
        } catch {
            Write-Error -Message "Could not make SQL connection to [$ServerInstance], either server not up, or no permissions to connect."
            break
        }
        $FragParam = @{
            ServerInstance        = $ServerInstance
            Database              = $Database
            Table                 = $Table
            IncludeSystemDatabase = $IncludeSystemDatabase
            MinFragmentation      = $MinFragmentation
            MinPageCount          = $MinPageCount
        }
        Write-Verbose -Message 'Getting SQL Index Fragmentation...'
        [array] $TableList = Get-SqlIndexFragmentation @FragParam
        $TableList = $TableList | Sort-Object -Property DbName, Schema, Table, Index
    }

    process {
        if ($TableList) {
            Write-Verbose -Message "There are [$($TableList.count)] indexes to optimize"
            Write-Verbose -Message 'Creating command list...'
            $CommandsToRun = New-Object -TypeName 'System.Collections.ArrayList'
            #$Type = 'Reorganize'
            $TableProcessing = ''
            $TableList | ForEach-Object {
                $Current = $_
                switch ($Current.Schema) {
                    'dbo' {
                        switch ($Type) {
                            'Rebuild' {
                                $CommandsToRun.Add((New-Object -TypeName psobject -Property ([ordered] @{
                                    Database     = $Current.Dbname
                                    Table        = $Current.Table
                                    Stats        = "UPDATE STATISTICS [$($Current.Table)];"
                                    Optimize     = "ALTER INDEX [$($Current.Index)] ON [$($Current.Table)] REBUILD $Online;"
                                    TableLockOn  = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] SET (ALLOW_PAGE_LOCKS = ON);"
                                    TableLockOff = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] SET (ALLOW_PAGE_LOCKS = OFF);"
                                }))) | Out-Null
                            }
                            'Reorganize' {
                                $CommandsToRun.Add( (New-Object -TypeName psobject -Property ([ordered] @{
                                    Database     = $Current.Dbname
                                    Table        = $Current.Table
                                    Stats        = "UPDATE STATISTICS [$($Current.Table)];"
                                    Optimize     = "ALTER INDEX [$($Current.Index)] ON [$($Current.Table)] REORGANIZE;"
                                    TableLockOn  = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] SET (ALLOW_PAGE_LOCKS = ON);"
                                    TableLockOff = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] SET (ALLOW_PAGE_LOCKS = OFF);"
                                }))) | Out-Null
                            }
                        }
                    }
                    default {
                        switch ($Type) {
                            'Rebuild' {
                                $CommandsToRun.Add( (New-Object -TypeName psobject -Property ([ordered] @{
                                    Database     = $Current.Dbname
                                    Table        = $Current.Table
                                    Stats        = "UPDATE STATISTICS [$($Current.Schema)].[$($Current.Table)];"
                                    Optimize     = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] REBUILD $Online;"
                                    TableLockOn  = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] SET (ALLOW_PAGE_LOCKS = ON);"
                                    TableLockOff = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] SET (ALLOW_PAGE_LOCKS = OFF);"
                                }))) | Out-Null
                            }
                            'Reorganize' {
                                $CommandsToRun.Add( (New-Object -TypeName psobject -Property ([ordered] @{
                                    Database     = $Current.Dbname
                                    Table        = $Current.Table
                                    Stats        = "UPDATE STATISTICS [$($Current.Schema)].[$($Current.Table)];"
                                    Optimize     = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] REORGANIZE;"
                                    TableLockOn  = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] SET (ALLOW_PAGE_LOCKS = ON);"
                                    TableLockOff = "ALTER INDEX [$($Current.Index)] ON [$($Current.Schema)].[$($Current.Table)] SET (ALLOW_PAGE_LOCKS = OFF);"
                                }))) | Out-Null
                            }
                        }
                    }
                }
            }
        } else {
            Write-Error -Message 'No indexes need to be optimized'
            break
        }

        if ($Interactive) {
            $CommandsToRun | Show-Progress -Activity 'Optimizing database table indexes' -PassThru -Id 1 | ForEach-Object {
                $Current = $_

                Write-Verbose -Message "DB [$($Current.Database)] QUERY [$($Current.Optimize)]"
                try {
                    write-verbose -Message 'Running Query'
                    if ($Current.Table -ne $TableProcessing) {
                        Write-Verbose -Message 'Updating statistics'
                        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.Stats -QueryTimeout 900 -Verbose:$false -EA Stop | Out-Null
                        $TableProcessing = $Current.Table
                    }
                    Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.Optimize -QueryTimeout 900 -Verbose:$false -EA Stop | Out-Null
                } catch {
                    #if ($error[0].Exception -match 'page level locking is disabled') {
                        write-verbose -Message "Running [$($Current.TableLockOn)]"
                        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.TableLockOn   -QueryTimeout 900 -Verbose:$false | Out-Null
                        write-verbose -Message "Running [$($Current.Optimize)]"
                        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.Optimize         -QueryTimeout 900 -Verbose:$false | Out-Null
                        write-verbose -Message "Running [$($Current.TableLockOff)]"
                        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.TableLockOff  -QueryTimeout 900 -Verbose:$false | Out-Null
                    #}
                }
            }
        } else {
            $CommandsToRun | ForEach-Object {
                $Current = $_
                Write-Verbose -Message "DB [$($Current.Database)] QUERY [$($Current.Optimize)]"
                try {
                    write-verbose -Message 'Running Query'
                    if ($Current.Table -ne $TableProcessing) {
                        Write-Verbose -Message 'Updating statistics'
                        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.Stats -QueryTimeout 900 -Verbose:$false -EA Stop | Out-Null
                        $TableProcessing = $Current.Table
                    }
                    Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.Optimize -QueryTimeout 900 -Verbose:$false -EA Stop | Out-Null
                } catch {
                    #if ($error[0].Exception -match 'page level locking is disabled') {
                        write-verbose -Message "Running [$($Current.TableLockOn)]"
                        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.TableLockOn   -QueryTimeout 900 -Verbose:$false | Out-Null
                        write-verbose -Message "Running [$($Current.Optimize)]"
                        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.Optimize         -QueryTimeout 900 -Verbose:$false | Out-Null
                        write-verbose -Message "Running [$($Current.TableLockOff)]"
                        Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Current.Database -Query $Current.TableLockOff  -QueryTimeout 900 -Verbose:$false | Out-Null
                    #}
                }
            }
        }
    }

    end {
        Write-Verbose -Message "Ending [$($MyInvocation.Mycommand)]"
    }
}