docker compose
Docker compose : Install from ec2 userdata
March 26, 2018
How to run `make` command from another directory
March 26, 2018

SetMaxDop value for MS SQL database using PowerShell

When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution. SQL Server considers parallel execution plans for queries, index data definition language (DDL) operations, parallel insert, online alter column, parallel stats collection, and static and keyset-driven cursor population.

This script shall be run from a remote computer to a server where SQL server is installed or on a local server where SQL DB is present. It uses AD credentials to remote access and execution.

If you want to run this on local computer you can remove the Invoke-Comand part and run the snippet from SetupMaxDOPPs part only.

[CmdletBinding()]
 param(

[Parameter(Mandatory=$true)]
 [string]
 $NetBIOSName,

[Parameter(Mandatory=$true)]
 [string]
 $DomainNetBIOSName,

[Parameter(Mandatory=$true)]
 [string]
 $DomainAdminUser,

[Parameter(Mandatory=$true)]
 [string]
 $DomainAdminPassword,

[Parameter(Mandatory=$false)]
 [string]
 $dop="2"

)

try {
 Start-Transcript -Path C:\log\SetMaxDOP.log -Append

$DomainAdminFullUser = $DomainNetBIOSName + '\' + $DomainAdminUser
 $DomainAdminSecurePassword = ConvertTo-SecureString $DomainAdminPassword -AsPlainText -Force
 $DomainAdminCreds = New-Object System.Management.Automation.PSCredential($DomainAdminFullUser, $DomainAdminSecurePassword)

$SetupMaxDOPPs={
 $sql = "EXEC sp_configure 'show advanced options', 1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'max degree of parallelism', " + $Using:dop + "; RECONFIGURE WITH OVERRIDE; "
 Invoke-Sqlcmd -AbortOnError -ErrorAction Stop -Query $sql
 }

Invoke-Command -Authentication Credssp -Scriptblock $SetupMaxDOPPs -ComputerName $NetBIOSName -Credential $DomainAdminCreds

}
 catch {
        $error[0]|format-list -force #print more detail reason for failure
 }
Bitnami