I hadn’t been writing this Powershell series for a long time, and I’d like to make up for it, even though a lot of things have changed (in my work & community life) … I’d like to introduce you to two things today, once in a conventional way and how Powershell could configure SQL Server (or how I did it in my script), and a simpler and faster way with the Powershell module from dbatools.io.

Best Practices with T-SQ

As part of the SQL Server installation, you should optimize certain parameters to guarantee high-performance and stable operation. This includes a variety of configuration parameters at the instance level, such as “Max. Memory” or “Max. degree of Parallelism”. All these instance settings can be set with the same SQL Server function, so I have written a “function” for these recurring T-SQL commands to stay flexible for later extensions.

function ExecuteSQLCmd ([string]$SQLQuery) {
     Invoke-Sqlcmd -ServerInstance $ServerName -Query $SQLQuery -QueryTimeout 65535
}

With this simple function (even before, but now it is “easier”), I can call the following functions accordingly and configure my SQL Server according to Best Practices, in which I call the respective function, calculate values according to system equipment then execute it with ExecuteSQLCmd. The following function enables me to adapt it to the respective environment.

SetMaxMemory
Add_TempDBFiles
SetMaxDOP
SetNetworkPacketSize
SetOptimizeAdhocWorkload
SetBackupCompression
AddLocalSystemToSysadminGroup
enable_XPAgent

Example – Powershell Function “SetMaxDOP”

In order to set the value for MaxDOP (max degree of parallelism), I have to know how many logical CPUs the server has. I determined this value I had at the beginning of the script through auxiliary functions, only with this value I can decide… Usually, I set the Cost Threshold for Parallelism on our systems to 40, this fits at least 90% of the systems. Of course, you can still have a lot more to stick to the best practices, as you can read in the next section, but with these values, I was going quite well on our systems for the last two years.

function SetMaxDOP() {
    Try { 
        Write-Host "Setting of MaxDOP / Threshold"
        $sqlquery = "
        EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
        EXEC sys.sp_configure N'cost threshold for parallelism', N'40';
        "
        ExecuteSQLCmd $sqlquery

        if ($global:NoLogicalCPUs -le 4) {
            $sqlquery = "
            EXEC sys.sp_configure N'max degree of parallelism', N'0'
            RECONFIGURE WITH OVERRIDE
            "
            Write-Host "[INFO] Set Threshold to 40 and Set MaxDOP to 0."  -ForegroundColor Green
        } else {
            $sqlquery = "
            EXEC sys.sp_configure N'max degree of parallelism', N'"+($global:NoLogicalCPUs/2)+"'
            RECONFIGURE WITH OVERRIDE
            "
            Write-Host "[INFO] Set Threshold to 40 and Set MaxDOP to "($global:NoLogicalCPUs/2) -ForegroundColor Green
        }
        ExecuteSQLCmd $sqlquery
    }
    Catch {
        Write-Host "[ERROR] Failed to set MaxDOP." -ForegroundColor Red
    }
}

dbatools – the function Set-DbaMaxDop

I had already written several times about the Powershell module dbatools – for example when copying an agent job – here I would like to introduce you now the comparison between the conventional way and the easier way with dbatools. Dbatools provides a function for simply setting the SQL Server instance parameter for Max Degree of Parallelism, as well as other instance parameters.

The “Set-DbaMaxDop” command offers a variety of options, the simple setting of the MaxDop at the instance level (SQL Server 2008 – 2016) and the setting of the MaxDop at the database level starting with the SQL Server 2016. The function determines all the basic conditions, based on the algorithm from the KB2806535, as well as the MaxDoP Calculator from Sakthivel Chidambaram, are used to calculate the necessary values for setting the MaxDoP. Where, of course, – as with my data/notes – it must always be pointed out that these are recommendations that do not fit 100% to every environment and application, but give the first clue.

Set-DbaMaxDop -SqlServer SQL2016

If you want to specify a value yourself, this is also possible … or use the internal test function “Test-DbaMaxDop” Test-DbaMaxDop -SqlServer SQL2016

Or even decide for yourself what value is more appropriate for this environment …Set-DbaMaxDop -SqlServer SQL2016 -MaxDop 6

Further information can be found on the following pages dbatools.io: https://dbatools.io/functions/set-dbamaxdop/ and https://dbatools.io/functions/test-dbamaxdop/

dbatools – the function Set-DbaSpConfigure

Now we have changed not only the value for MaxDop but also the value for the Threshold for the Max Degree of Parallelism. Also, this value can be set with the dbatools very easy. Since both are instance parameters, you can customize both settings individually with your own values and this command, but the internal calculation makes the use of two different commands useful. So to set the value for the threshold to 40, I use “Set-DbaSpConfigure”, which should not be unknown to us.

Set-DbaSpConfigure -SqlServer SQL2016 -ConfigName CostThresholdForParallelism -Value 40

Especially with the IntelliSense feature, this function is a joy since the individually available parameters can be used quickly and you can quickly get the whole command. You will also find further help and examples on the help pages of the function => https://dbatools.io/functions/set-dbaspconfigure/

Thus, as opposed to my own script, you can now optimize both instance parameters with only 3 lines of code.

Import-Module .\dbatools\dbatools.psd1

Set-DbaSpConfigure -SqlServer SQL2016 -ConfigName CostThresholdForParallelism -Value 40
Set-DbaMaxDop -SqlServer SQL2016

Björn Peters

Björn works in Hamburg as Senior Consultant – Microsoft Data Platform and Cloud at Kramer & Crew. He regularly participates in the PASS regional group meeting in Hamburg, the events of the PASS such as SQLSaturday and DataGrillen and organises the Azure Meetup in Hamburg. He is interested in topics such as SQL Server, Powershell and Azure for science fiction, snowboarding, baking and cycling.