Change Workload Group Settings
Applies To: SQL Server 2016
You can change workload group settings by using SQL Server Management Studio.
You can change the settings of the default workload group and user-defined workload groups.
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.
Changing workload group settings requires CONTROL SERVER permission.
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
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.
The following example changes the max memory grant percent setting for the workload group named
ALTER WORKLOAD GROUP groupAdhoc WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 30); GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO