functions/set-axparametersniffingsetting.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
100
101
102

<#
    .SYNOPSIS
        Set the parameter sniffing configuration
         
    .DESCRIPTION
        Set the parameter sniffing value in the database based on the released hotfix from Microsoft for AX 2012
         
    .PARAMETER DatabaseServer
        Server name of the database server
         
        Default value is: "localhost"
         
    .PARAMETER DatabaseName
        Name of the database
         
        Default value is: "MicrosoftDynamicsAx"
         
    .PARAMETER SqlUser
        User name of the SQL Server credential that you want to use when working against the database
         
    .PARAMETER SqlPwd
        Password of the SQL Server credential that you want to use when working against the database
         
    .PARAMETER OutputCommandOnly
        When provided the SQL is returned and not executed
         
        Note: This is useful for troubleshooting or providing the script to a DBA with access to the server
         
    .EXAMPLE
        PS C:\> Set-AxParameterSniffingSetting
         
        This will configure the correct parameter sniffing settings.
         
    .NOTES
        Author: Mötz Jensen (@Splaxi)
         
    .LINK
        https://community.dynamics.com/365/financeandoperations/b/axsupport/posts/how-to-proactively-avoid-parameter-sniffing-step-by-step
#>

function Set-AxParameterSniffingSetting {
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseShouldProcessForStateChangingFunctions', '')]
    [CmdletBinding()]
    [OutputType([System.String], ParameterSetName="Generate")]
    param (
        [Parameter(ValueFromPipelineByPropertyName = $true)]
        [string] $DatabaseServer = $Script:ActiveAosDatabaseserver,

        [Parameter(ValueFromPipelineByPropertyName = $true)]
        [string] $DatabaseName = $Script:ActiveAosDatabase,

        [string] $SqlUser,

        [string] $SqlPwd,

        [Parameter(ParameterSetName = "Generate")]
        [Switch] $OutputCommandOnly
    )

    Invoke-TimeSignal -Start

    $baseParams = Get-DeepClone $PSBoundParameters
    $baseParams.Add("TrustedConnection", $true)

    $UseTrustedConnection = Test-TrustedConnection $baseParams

    $SqlParams = @{ DatabaseServer = $DatabaseServer; DatabaseName = $DatabaseName;
        SqlUser = $SqlUser; SqlPwd = $SqlPwd
    }

    $sqlCommand = Get-SqlCommand @SqlParams -TrustedConnection $UseTrustedConnection

    $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\set-axparametersniffingsetting.sql") -join [Environment]::NewLine

    if ($OutputCommandOnly) {
        (Get-SqlString $sqlCommand)
    }
    else {
        try {
            Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $sqlCommand)

            $sqlCommand.Connection.Open()
            $null = $sqlCommand.ExecuteNonQuery()

            Write-PSFMessage -Level Verbose -Message "SQL query executed."
        }
        catch {
            Write-PSFMessage -Level Host -Message "Something went wrong while working against the database" -Exception $PSItem.Exception
            Stop-PSFFunction -Message "Stopping because of errors"
            return
        }
        finally {
            if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) {
                $sqlCommand.Connection.Close()
            }
    
            $sqlCommand.Dispose()
        }
    }

    Invoke-TimeSignal -End
}