Aha effect when setting SQL instance parameters with dbatools

In the last week, I installed several SQL Servers for one customer and had to install and configure them all identically. What is closer here than to do this with a Powershell script, so I made the “effort” and the individual steps of the SQL Server configuration with dbatools to realize. I do not want to go into all the steps of the configuration in this post, but show only a part of it, which brought me a specific “aha effect”.

As part of the SQL Server configuration, the default values of “MaxDop” and “Cost Threshold for Parallelism” should be adjusted. Setting MaxDoP using Powershell or dbatools is relatively easy as there is a separate command for this, but for the “Cost Threshold for Parallelism” dbatools has a “work-around”. Unfortunately, there is not (yet) direct command for this.

Set-DbaMaxDop -SqlInstance sql2008, sql2012

This command-line sets the value of Max DOP (Maximum Degree of Parallelism) to the recommended value for SQL Server instances SQL2008 and SQL2012. Always in connection with this configuration parameter is the “Cost Threshold”, which still set to 5 by default.

All SQL Server instances at once …

To configure all SQL Server instances relatively quickly and easily, I have the command “Get-DbaRegisteredServer” (as an alias of Get-DbaRegServer) made. As preparation for this, I have created the necessary servers (here 2 servers with 3 instances each) as “Registered Server” on all servers in the SQL Server Management Studio and was then able to access it with Powershell aka dbatools.

According to dbatools documentation, this command retrieves a list of SQL Server objects stored in locally registered groups in both the Azure Data Studio and the central management server.

With this command and the possibility to pass the objects from the result object as a pipeline, you can do beautiful things like configuring the value for “MaxDoP” on all servers or instances in a command-line…

Get-DbaRegisteredServer | Set-DbaMaxDop

However, now to my aha effect with another command line 😉

Cost Threshold For Parallelism

As indicated above, the adjustment is only a work-around with dbatools and not with a dbatools command, this I use now “Set-DbaSpConfigure”. Of course, I could also configure the “MaxDoP” with this command, but then I have to provide for the previous determination and calculation of the respective value for MaxDoP, so determine the existing cores, these match the Best Practice and the value of one Pass variable to the set command. I set these values to 40 in 98% of all instances (unless the customer or the application wants something else), so I do not need any logic here.

Following my above logic or the documentation, I tried it with the following command line:

Get-DbaRegisteredServer | Set-DbaSpConfigure -Name 'CostThresholdForParallelism' -Value 40

This command brought me to an (at first sight) incomprehensible error (even my attempt to pass the value as a string was not successful):

WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server1\Instanz1 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server2\Instanz1 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server1\Instanz2 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server2\Instanz2 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server1\Instanz3 ( <-> )
WARNING: [13:02:23][Set-DbaSpConfigure] Value out of range for Server2\Instanz3 ( <-> )

Unfortunately, I have not found the reason for this, and maybe someone else can bring me closer to the phenomenon… maybe this is by design or even a “bug”…

However, I’ve been so successful with pipelining before that I’ve used that too… so let’s first find out all the SQL instances, then find the current parameter for the “Cost Threshold For Parallelism” on those instances, and then set it to the new value 40.

Get-DbaRegisteredServer | Get-DbaSpConfigure -Name 'CostThresholdForParallelism' | Set-DbaSpConfigure -Value 40

ComputerName  : Server1
InstanceName  : Instanz1
SqlInstance   : Server1\Instanz1
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server2
InstanceName  : Instanz1
SqlInstance   : Server2\Instanz1
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server1
InstanceName  : Instanz2
SqlInstance   : Server1\Instanz2
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server2
InstanceName  : Instanz2
SqlInstance   : Server2\Instanz2
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server1
InstanceName  : Instanz3
SqlInstance   : Server1\Instanz3
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

ComputerName  : Server2
InstanceName  : Instanz3
SqlInstance   : Server2\Instanz3
ConfigName    : CostThresholdForParallelism
PreviousValue : 5
NewValue      : 40

Also, already I have found out something great for myself and am more enriched by experience in dealing with dbatools!

I love this Powershell module, which allows me to customize, optimize, and automate many (almost anything!) Things around and around SQL Server. I like to use it (as you can see my other blog posts) and meanwhile with all my clients. THANKS to @Chrissy and the many other contributors who are taking the trouble to make this community tool!

Photo by Ben White on Unsplash

Björn Peters

Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.