Move a Workload Group
Applies To: SQL Server 2016
You can move a Resource Governor workload group to a different resource pool by using either SQL Server Management Studio or Transact-SQL.
You cannot move a workload group if there is a pending Resource Governor configuration operation.
You cannot move a workload group if there is a pending Resource Governor configuration operation. You can determine whether there is a configuration pending by querying the sys.dm_resource_governor_configuration (Transact-SQL) dynamic management view to get the current status of is_configuration_pending.
Moving a workload group requires CONTROL SERVER permission.
To move a workload group by using Management Studio
In Object Explorer, recursively expand the Management node down to Resource Governor.
Right-click Resource Governor and then click Properties, this opens the Resource Governor Properties page.
In the Resource Pools window, click the resource pool containing the workload group to be moved. The Workload Groups window now lists the workload groups in that resource pool.
In the Workload Groups window, right-click the right arrow to the left of the workload group to be moved, and click Move to. This displays a Move Workload Group window.
Available resource pools are displayed in the window. Click the name of the resource pool that you want to move the workload group to, and then click OK to carry out this action.
This action is not completed until after you click OK. When you click OK, the ALTER RESOURCE GOVERNOR RECONFIGURE statement is executed.
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, click the down arrow on the error message.
To move a workload group by using Transact-SQL
Run 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.
Run the ALTER RESOURCE GOVERNOR RECONFIGURE statement.
The following example moves a workload group named
groupAdhoc to the default resource pool.
ALTER WORKLOAD GROUP groupAdhoc USING [default]; GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO