New-DbaRgWorkloadGroup

Synopsis

Creates a Resource Governor workload group within a specified resource pool to control SQL Server resource allocation.

Description

Creates a Resource Governor workload group within a specified resource pool, allowing you to define specific resource limits and priorities for different types of SQL Server workloads. Workload groups act as containers that classify incoming requests and apply resource policies like CPU time limits, memory grant percentages, and maximum degree of parallelism.

This is essential for DBAs managing multi-tenant environments, mixed workloads, or systems where you need to prevent resource-intensive queries from impacting critical applications. You can create separate workload groups for reporting queries, ETL processes, application traffic, or administrative tasks, each with tailored resource constraints.

The function supports both internal and external resource pools, handles existing workload group conflicts with optional force recreation, and automatically reconfigures Resource Governor to apply the changes immediately.

Syntax

New-DbaRgWorkloadGroup
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-WorkloadGroup] <String[]>]
    [[-ResourcePool] <String>]
    [[-ResourcePoolType] <String>]
    [[-Importance] <String>]
    [[-RequestMaximumMemoryGrantPercentage] <Int32>]
    [[-RequestMaximumCpuTimeInSeconds] <Int32>]
    [[-RequestMemoryGrantTimeoutInSeconds] <Int32>]
    [[-MaximumDegreeOfParallelism] <Int32>]
    [[-GroupMaximumRequests] <Int32>]
    [-SkipReconfigure]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> New-DbaRgWorkloadGroup -SqlInstance sql2016 -WorkloadGroup "groupAdmin" -ResourcePool "poolAdmin"

Creates a workload group “groupAdmin” in the resource pool named “poolAdmin” for the instance sql2016.

Example: 2

PS C:\> New-DbaRgWorkloadGroup -SqlInstance sql2016 -WorkloadGroup "groupAdmin" -Force

If “groupAdmin” exists, it is dropped and re-created in the default resource pool for the instance sql2016.

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-SqlCredential

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

-WorkloadGroup

Specifies the name of the workload group to create within the resource pool. Use descriptive names that reflect the workload type, like ‘ReportingQueries’, ‘ETLProcesses’, or ‘AdminTasks’.
Each workload group acts as a container for classifying requests and applying specific resource limits and priorities.

-ResourcePool

Specifies which resource pool will contain the new workload group. Defaults to ‘default’ if not specified.
Use this to organize workload groups within custom resource pools that have specific CPU and memory allocations.

-ResourcePoolType

Determines whether to create the workload group in an Internal or External resource pool. Defaults to Internal.
Use External for R/Python workloads or machine learning services; use Internal for standard SQL Server workloads like queries and stored procedures.

-Importance

Sets the relative priority for requests in this workload group when competing for CPU resources. Defaults to MEDIUM.
Use HIGH for critical application queries, MEDIUM for normal operations, and LOW for background tasks like maintenance or reporting.

-RequestMaximumMemoryGrantPercentage

Limits how much memory any single query in this workload group can consume from the resource pool. Defaults to 25%.
Lower this for concurrent workloads to prevent memory hogging, or increase it for data warehouse queries that need large memory grants for sorting and hashing.

-RequestMaximumCpuTimeInSeconds

Sets the maximum CPU time in seconds that any single request can consume before being terminated. Default of 0 means unlimited.
Use this to prevent runaway queries from consuming excessive CPU, typically setting values between 300-3600 seconds depending on your workload requirements.

-RequestMemoryGrantTimeoutInSeconds

Defines how long a query will wait for memory grants before timing out. Default of 0 means unlimited wait time.
Set this to prevent queries from waiting indefinitely during memory pressure, typically using values like 60-300 seconds for interactive workloads.

-MaximumDegreeOfParallelism

Controls the maximum number of processors that queries in this workload group can use for parallel execution. Default of 0 uses the server’s MAXDOP setting.
Lower values prevent queries from consuming too many CPU cores, while higher values can improve performance for analytical workloads on servers with many cores.

-GroupMaximumRequests

Limits the total number of concurrent requests that can execute simultaneously within this workload group. Default of 0 means unlimited.
Use this to control concurrency for resource-intensive workloads, preventing too many expensive queries from running at once and overwhelming the system.

-SkipReconfigure

Prevents automatic reconfiguration of Resource Governor after creating the workload group. Changes won’t take effect until you manually run ALTER RESOURCE GOVERNOR RECONFIGURE.
Use this when creating multiple workload groups in a batch to avoid repeated reconfigurations, but remember to reconfigure manually afterward.

-Force

Drops and recreates the workload group if it already exists, applying new configuration settings.
Use this when you need to modify an existing workload group’s properties, as Resource Governor workload groups cannot be altered once created.

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

-WhatIf

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

-Confirm

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