PoshFunctions

2.2.9

Functions/Optimize-SqlStoredProcedure.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 function Optimize-SqlStoredProcedure {
    <#
.SYNOPSIS
    Optimize SQL Stored Procedures
.DESCRIPTION
    Optimize SQL Stored Procedures, on a database LIKE string. Recompiles all stored procedures the next time they are run.
.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 Interactive
    Switch whether output should be interactive and provide progress
.PARAMETER IncludeSystemDatabase
    Switch whether to include system databases
.EXAMPLE
    Optimize-SqlStoredProcedure
 
    Determine all non system active databases, and issue sp_recompile on all found stored procedures
.NOTES
    1 - User running this function must have Windows authentication to the database server
    2 - By default system databases are excluded
    3 - Requires Get-SqlStoredProcedure function
#>

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

        [string] $Database = '*',

        [switch] $Interactive,

        [switch] $IncludeSystemDatabase
    )

    begin {
        Write-Verbose -Message "Starting [$($MyInvocation.Mycommand)]"
        Write-Verbose -Message "ServerInstance [$ServerInstance]"
        Write-Verbose -Message "Database [$Database]"
        Write-Verbose -Message "Interactive [$Interactive]"
        Write-Verbose -Message "IncludeSystemDatabase [$IncludeSystemDatabase]"
        try {
            $SpParam = @{
                ServerInstance        = $ServerInstance
                Database              = $Database
                IncludeSystemDatabase = $IncludeSystemDatabase
            }
            [array] $SpList = Get-SqlStoredProcedure @SpParam
            $SpList = $SpList | Sort-Object -Property DbName, Schema, Procedure
        } catch {
            Write-Error -Message "Could not make SQL connection to [$ServerInstance], either server not up, or no permissions to connect."
            break
        }
    }

    process {
        if ($SpList) {
            Write-Verbose -Message "There are [$($SpList.count)] stored procedures to recompile"
            $CommandsToRun = New-Object -TypeName 'System.Collections.ArrayList'
            $SpList | ForEach-Object {
                $Current = $_
                switch ($Current.Schema) {
                    'dbo' {
                        $null = $CommandsToRun.Add((New-Object -TypeName psobject -Property ([ordered] @{
                            Database = $Current.DbName
                            Query = "EXECUTE sp_recompile [$($Current.Procedure)];"
                        })))
                    }
                    default {
                        $null = $CommandsToRun.Add((New-Object -TypeName psobject -Property ([ordered] @{
                            Database = $Current.DbName
                            Query = "EXECUTE sp_recompile [$($Current.Schema).$($Current.Procedure)];"
                        })))
                    }
                }
            }
        } else {
            Write-Error -Message 'No stored procedures need to be recompiled'
            break
        }
        if ($Interactive) {
            $CommandsToRun | Show-Progress -Activity 'Recompiling all stored procedures' -PassThru -Id 1 | ForEach-Object {
                Write-Verbose -Message "DB [$($_.Database)] QUERY [$($_.Query)]"
                $null = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $_.Database -Query $_.Query -QueryTimeout 300 -Verbose:$false
            }
        } else {
            $CommandsToRun | ForEach-Object {
                Write-Verbose -Message "DB [$($_.Database)] QUERY [$($_.Query)]"
                $null = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $_.Database -Query $_.Query -QueryTimeout 300 -Verbose:$false
            }
        }
    }

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