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 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.