commands

^

Set-DbaRgResourcePool

Author John McCall (@lowlydba), https://www.lowlydba.com/
Availability Windows, Linux, macOS

 

Synopsis

Sets a resource pool for use by the Resource Governor on the specified SQL Server.

Description

Sets a resource pool for use by the Resource Governor on the specified SQL Server. A resource pool represents a subset of the physical resources (memory, CPUs and IO) of an instance of the Database Engine.

Syntax

Set-DbaRgResourcePool
    [-SqlInstance <DbaInstanceParameter[]>]
    [-SqlCredential <PSCredential>]
    [-ResourcePool <String[]>]
    [-Type <String>]
    [-MinimumCpuPercentage <Int32>]
    [-MaximumCpuPercentage <Int32>]
    [-CapCpuPercentage <Int32>]
    [-MinimumMemoryPercentage <Int32>]
    [-MaximumMemoryPercentage <Int32>]
    [-MinimumIOPSPerVolume <Int32>]
    [-MaximumIOPSPerVolume <Int32>]
    [-SkipReconfigure]
    [-InputObject <Object[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Set-DbaRgResourcePool
    [-SqlInstance <DbaInstanceParameter[]>]
    [-SqlCredential <PSCredential>]
    [-ResourcePool <String[]>]
    [-Type <String>]
    [-MinimumCpuPercentage <Int32>]
    [-MaximumCpuPercentage <Int32>]
    [-CapCpuPercentage <Int32>]
    [-MinimumMemoryPercentage <Int32>]
    [-MaximumMemoryPercentage <Int32>]
    [-MinimumIOPSPerVolume <Int32>]
    [-MaximumIOPSPerVolume <Int32>]
    [-SkipReconfigure]
    [-InputObject <Object[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Set-DbaRgResourcePool
    [-SqlInstance <DbaInstanceParameter[]>]
    [-SqlCredential <PSCredential>]
    [-ResourcePool <String[]>]
    [-Type <String>]
    [-MinimumCpuPercentage <Int32>]
    [-MaximumCpuPercentage <Int32>]
    [-CapCpuPercentage <Int32>]
    [-MinimumMemoryPercentage <Int32>]
    [-MaximumMemoryPercentage <Int32>]
    [-MinimumIOPSPerVolume <Int32>]
    [-MaximumIOPSPerVolume <Int32>]
    [-MaximumProcesses <Int32>]
    [-SkipReconfigure]
    [-InputObject <Object[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Set-DbaRgResourcePool-SqlInstance sql2016 -ResourcePool "poolAdmin" -MaximumCpuPercentage 5

Configures a resource pool named "poolAdmin" for the instance sql2016 with a Maximum CPU Percent of 5.

Example: 2
PS C:\> Set-DbaRgResourcePool-SqlInstance sql2012\dev1 -ResourcePool "poolDeveloper" -SkipReconfigure

Configures a resource pool named "poolDeveloper" for the instance dev1 on sq2012.
Reconfiguration is skipped and the Resource Governor will not be able to use the new resource pool
until it is reconfigured.

Example: 3
PS C:\> Get-DbaRgResourcePool -SqlInstance sql2016 -Type "Internal" | Where-Object { $_.IsSystemObject -eq $false } | Set-DbaRgResourcePool -MinMemoryPercent 10

Configures all user internal resource pools to have a minimum memory percent of 10
for the instance sql2016 by piping output from Get-DbaRgResourcePool.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

Alias
Required False
Pipeline true (ByValue, ByPropertyName)
Default Value
-SqlCredential

Credential object used to connect to the Windows server as a different user

Alias
Required False
Pipeline false
Default Value
-ResourcePool

Name of the resource pool to be created.

Alias
Required False
Pipeline false
Default Value
-Type

Internal or External.

Alias
Required False
Pipeline false
Default Value Internal
Accepted Values Internal,External
-MinimumCpuPercentage

Specifies the guaranteed average CPU bandwidth for all requests in the resource pool when there is CPU contention.

Alias
Required False
Pipeline true (ByPropertyName)
Default Value 0
-MaximumCpuPercentage

Specifies the maximum average CPU bandwidth that all requests in resource pool will receive when there is CPU contention.

Alias
Required False
Pipeline true (ByPropertyName)
Default Value 0
-CapCpuPercentage

Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. Limits the maximum CPU bandwidth level to be the same as the specified value. Only for SQL Server 2012+

Alias
Required False
Pipeline true (ByPropertyName)
Default Value 0
-MinimumMemoryPercentage

Specifies the minimum amount of memory reserved for this resource pool that can not be shared with other resource pools.

Alias
Required False
Pipeline true (ByPropertyName)
Default Value 0
-MaximumMemoryPercentage

Specifies the total server memory that can be used by requests in this resource pool. value is an integer with a default setting of 100.

Alias
Required False
Pipeline true (ByPropertyName)
Default Value 0
-MinimumIOPSPerVolume

Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the resource pool.

Alias
Required False
Pipeline true (ByPropertyName)
Default Value 0
-MaximumIOPSPerVolume

Specifies the maximum I/O operations per second (IOPS) per disk volume to allow for the resource pool.

Alias
Required False
Pipeline true (ByPropertyName)
Default Value 0
-MaximumProcesses

Specifies the maximum number of processes allowed for the external resource pool. Specify 0 to set an unlimited threshold for the pool, which is thereafter bound only by computer resources.

Alias
Required False
Pipeline false
Default Value 0
-SkipReconfigure

Resource Governor requires a reconfiguriation for resource pool changes to take effect. Use this switch to skip issuing a reconfigure for the Resource Governor.

Alias
Required False
Pipeline false
Default Value False
-InputObject

Allows input to be piped from Get-DbaRgResourcePool.

Alias
Required False
Pipeline true (ByValue)
Default Value
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Alias cf
Required False
Pipeline false
Default Value

 

Want to see the source code for this command? Check out Set-DbaRgResourcePool on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaRgResourcePool.