Skip to content

change-workload-group-settings.md

Latest commit

 

History

History
68 lines (48 loc) · 2.79 KB

change-workload-group-settings.md

File metadata and controls

68 lines (48 loc) · 2.79 KB
title Change Workload Group Settings
description Learn how to change workload group settings of the default and user-defined workload groups using SQL Server Management Studio or Transact-SQL.
author WilliamDAssafMSFT
ms.author wiassaf
ms.reviewer dfurman
ms.date 01/02/2025
ms.service sql
ms.subservice performance
ms.topic how-to
helpviewer_keywords
workload groups [SQL Server], alter
Resource Governor, workload group alter
monikerRange >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current

Change workload group settings

[!INCLUDE SQL Server SQL MI]

You can change workload group settings by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

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 (SSMS):

  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 [!INCLUDEtsql]:

  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