Edit

Share via

Change workload group settings

  • Article

Applies to: SQL Server Azure SQL Managed Instance

You can change workload group settings by using SQL Server Management Studio or Transact-SQL.

Permissions

Changing workload group settings requires the CONTROL SERVER permission.

Change workload group settings using SQL Server Management Studio

To change workload group settings using SQL Server Management Studio:

  1. In Object Explorer, expand the Management node down to and including the Workload Groups folder that contains the workload group to be modified.
  2. Use the context menu for the workload group to be modified, and select Properties.
  3. In the Resource Governor Properties page, select the row for the workload group in the Workload groups for resource pool grid.
  4. Select the cells in the row to be changed, and enter new values.
  5. To save the changes, select OK.

Change workload group settings using Transact-SQL

To change workload group settings using Transact-SQL:

  1. Execute the ALTER WORKLOAD GROUP statement specifying the values to be changed.
  2. Execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement for the changes to take effect.

Example

The following example changes the max memory grant percent setting for the workload group named groupAdhoc and makes the new configuration effective.

ALTER WORKLOAD GROUP groupAdhoc WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 30);

ALTER RESOURCE GOVERNOR RECONFIGURE;

Related content