Edit

Share via

Move a workload group

  • Article

Applies to: SQL Server Azure SQL Managed Instance

You can move a resource governor workload group to a different resource pool by using either SQL Server Management Studio or Transact-SQL.

You can't move a workload group if there's a pending resource governor configuration operation.

Limitations

  • You can't move a workload group if there's a pending resource governor configuration operation. You can determine whether there's a configuration pending by querying the sys.dm_resource_governor_configuration dynamic management view to get the current value of the is_configuration_pending column.
  • If a workload group contains active sessions, moving it to a different resource pool fails when the ALTER RESOURCE GOVERNOR RECONFIGURE statement is executed to apply the change. To avoid this problem, you can take one of the following actions:
    • Wait until all sessions in the affected group disconnect, and then execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
    • Explicitly stop sessions in the affected group by using the KILL T-SQL command, and then execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement. If you decide that you don't want to explicitly stop sessions, move the group to the original resource pool.
    • Restart the server. When the server restarts, a moved group uses the new resource pool assignment.

Permissions

Moving a workload group requires the CONTROL SERVER permission.

Move a workload group using SQL Server Management Studio

To move a workload group by using SQL Server Management Studio:

  1. In Object Explorer, expand the Management node down to Resource Governor.
  2. Open the Resource Governor context menu and select Properties. This opens the Resource Governor Properties page.
  3. In the Resource Pools grid, select the resource pool containing the workload group to be moved. The Workload Groups grid now lists the workload groups in that resource pool.
  4. In the Workload Groups grid, open the context menu for the workload group to be moved, and select Move to. This opens a Move Workload Group window.
  5. Available resource pools are displayed in the window. Select the resource pool that you want to move the workload group to, and select OK.
  6. Select OK to execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
  7. If the create or reconfigure operation fails for the resource pool or workload group, a summary error message appears below the title of the property page. To see a detailed error message, select the down arrow on the error message.

Move a workload group using Transact-SQL

To move a workload group by using Transact-SQL:

  1. Execute the ALTER WORKLOAD GROUP statement specifying the name of the workload group to be moved and the resource pool to which it should be moved.
  2. Execute the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

Example

The following example moves a workload group named groupAdhoc to the default resource pool.

ALTER WORKLOAD GROUP groupAdhoc USING [default];

ALTER RESOURCE GOVERNOR RECONFIGURE;

Related content