Microsoft SQL Server 2008 R2 Unleashed- P158:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1514 CHAPTER 40 Managing Workloads with the Resource Governor ALTER RESOURCE GOVERNOR RECONFIGURE GO You can also move a workload group to another resource pool in SSMS using the Resource Governor Properties page. Click the Resource Pool name in the Resource Pools grid then right-click on Workload Group in the Workload Groups grid and select Move To see Figure . This brings up the Move Workload Group dialog which lists the available resource pools the workload group can be moved to. Select the desired resource pool and click OK. FIGURE Moving a workload group in SSMS. Why move a workload group to a different resource pool You might decide that a workload group should be in a resource pool that has different configuration settings or you might want to move workload groups out of a resource pool so that you can drop the resource pool. Deleting Workload Groups You can delete a workload group or resource pool by using SQL Server Management Studio or T-SQL. To drop a workload group in SSMS follow these steps 1. Expand the Management node in Object Explorer and expand the Resource Governor node to display the Resource Pools folder. 2. Expand the node of the resource pool where the workload group is defined to display the Workload Groups folder. Modifying Your Resource Governor Configuration 1515 3. Expand the Workload Groups folder to list the workload groups. 4. Right-click the workload group you want to drop and select Delete. 5. In the Delete Object window the Workload Group is listed in the Object to Be Deleted list. Click OK to confirm the deletion. To drop a workload group using T-SQL use the DROP WORKLOAD GROUP command DROP WORKLOAD GROUP OLTPWG1 ALTER RESOURCE GOVERNOR RECONFIGURE go You cannot drop a workload group if there are any active sessions assigned to it. If a workload group contains active sessions deleting the workload group or moving it to a different resource pool will fail when the ALTER RESOURCE GOVERNOR RECONFIGURE statement is called to
