Change Workload Group Settings
Applies to:
SQL Server
Azure SQL Managed Instance
You can change workload group settings by using SQL Server Management Studio.
Before you begin: Limitations and Restrictions, Permissions
To change the settings for a workload group, using: SQL Server Management Studio, Transact-SQL
Before You Begin
Limitations and Restrictions
You can change the settings of the default workload group and user-defined workload groups.
REQUEST_MAX_MEMORY_GRANT_PERCENT
The memory consumed by index creation on a non-aligned partitioned table is proportional to the number of partitions involved. If the total required memory exceeds the per-query limit, (REQUEST_MAX_MEMORY_GRANT_PERCENT) imposed by the workload group setting, this index creation may fail. Because the default workload group allows a query to exceed the per-query limit with the minimum required memory to start for SQL Server 2005 compatibility, the user may be able to run the same index creation in the default workload group, if the default resource pool has enough total memory configured to run such a query.
Index creation is allowed to use more memory workspace than initially granted for performance. This special handling is supported by Resource Governor, however, the initial grant and any additional memory grant are limited by the workload group and resource pool settings.
Permissions
Changing workload group settings requires CONTROL SERVER permission.
Change Workload Group Settings Using SQL Server Management Studio
To change workload group settings by using SQL Server Management Studio
In Object Explorer, recursively expand the Management node down to and including the Workload Groups folder that contains the workload group to be modified.
Right-click the workload group to be modified, and then click Properties.
In the Resource Governor Properties page, select the row for the workload group in the Workload groups for resource pool grid if it is not automatically selected.
Click or double-click the cells in the row to be changed, and enter the new values.
To save the changes, click OK
Change Workload Group Settings Using Transact-SQL
To change workload group settings by using Transact-SQL
Run the ALTER WORKLOAD GROUP statement specifying the property values to be changed.
Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
Example (Transact-SQL)
The following example changes the max memory grant percent setting for the workload group named groupAdhoc
.
ALTER WORKLOAD GROUP groupAdhoc
WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 30);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
See Also
Resource Governor
Create a Workload Group
Create a Resource Pool
Change Resource Pool Settings
ALTER WORKLOAD GROUP (Transact-SQL)
ALTER RESOURCE POOL (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)