Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2008 R2 : Managing Workloads with the Resource Governor - Modifying Your Resource Governor Configuration

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/23/2012 5:32:08 PM
You can modify settings for resource pools or workload groups in SQL Server Management Studio via the Resource Governor Properties page. You simply make the changes desired (for example, a Resource Pool Maximum CPU% or Workload Group Importance) and click OK to save the changes.

Alternatively, you can modify the resource pool using the ALTER RESOURCE POOL command. With this command, you can modify the minimum and maximum CPU and memory percentages for a resource pool. The syntax is as follows:

ALTER RESOURCE POOL { pool_name | "default" }
[WITH
          ( [ MIN_CPU_PERCENT = value ]
     [ [ , ] MAX_CPU_PERCENT = value ]
     [ [ , ] MIN_MEMORY_PERCENT = value ]
     [ [ , ] MAX_MEMORY_PERCENT = value ] )
]

You can modify workload group settings using the ALTER WORKLOAD GROUP command. You can change the workload group settings as well as move the workload group to another resource pool. The syntax is as follows:

ALTER WORKLOAD GROUP { group_name | "default" }
[ WITH
    ([ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
           [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
           [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
           [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ]
           [ [ , ] MAX_DOP = value ]
           [ [ , ] GROUP_MAX_REQUESTS = value ] )
]
[ USING { pool_name | "default" } ]

Note

After executing your ALTER WORKLOAD GROUP or ALTER RESOURCE POOL commands, you need to run the ALTER RESOURCE GOVERNOR RECONFIGURE command to apply the changes.


The following example moves the ReportWG1 workload group from the ReportPool resource pool to the default resource pool:

ALTER WORKLOAD GROUP ReportWG1
USING [default];
GO
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 1). 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 1. 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.

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 apply the change. The following options provide a way to work around this problem:

  • Wait until all the sessions from the affected group have disconnected and then rerun the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

  • Explicitly stop sessions in the affected group by using the KILL command and then rerun the ALTER RESOURCE GOVERNOR RECONFIGURE statement.

  • Restart SQL Server. When the restart process is complete, the deleted group will not be created, and a moved group will automatically use the new resource pool assignment.

Note

If an attempt to reconfigure Resource Governor fails after dropping a workload group because of active sessions and you change your mind about dropping the workload group, you can restore it by rerunning the CREATE WORKLOAD GROUP command for that workgroup. After re-creating the workload group, run the ALTER RESOURCE GROUP RECONFIGURE command again, and the workload group is restored.


Deleting Resource Pools

To drop a resource pool 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 Resource Pools folder to list the resource pools defined.

3.
Right-click the resource pool you want to drop and select Delete.

4.
In the Delete Object window, the resource pool 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 RESOURCE POOL command:

DROP RESOURCE POOL OLTPPOOL
ALTER RESOURCE GOVERNOR RECONFIGURE
go

You cannot drop a resource pool if any workload groups are still assigned to the resource pool. You need to drop the workload group or move it to another resource pool first.

Modifying a Classification Function

If you need to make a change to the classification function, it’s important to note that the function cannot be dropped or altered while it is marked as the classification function for the Resource Governor. Before you can modify or drop the classification function, you first need to disable Resource Governor. Alternatively, you can replace the classification function with another by running the ALTER RESOURCE GOVERNOR command and passing it a different CLASSIFIER_FUNCTION name. You can also simply disable the current classifier function by executing the following command:

ALTER RESOURCE GOVERNOR
  WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;

If you have a more complex set of rules to apply or want to be able to make changes more dynamically than having to replace the classification function each time you need to make a change, you can define your classifier function to look up the workload group names from a database table, rather than hard-coding the workload group names and matching criteria into the function. Performance should not be greatly affected when accessing the table to look up the workload group. The reason is that the table likely won’t be very large and should remain cached in the buffer pool because it’s being accessed repeatedly every time a connection is made to SQL Server.

Other -----------------
- SQL Server 2008 R2 : Managing Workloads with the Resource Governor - Monitoring Resource Usage
- Windows Server 2008 Server Core : Decompressing Files with the Expand Utility, Performing Advanced File Comparison with the FC Utility
- Windows Server 2008 Server Core : Modifying Files with the Edlin Utility, Repairing System Databases with the ESEnTUtl Utility
- Microsoft Dynamics CRM 4.0 : Silverlight - Tools and Resources
- Microsoft Dynamics CRM 4.0 : Infrastructure Design Considerations - Windows SharePoint Integration
- Connecting Dynamics GP to Microsoft Office 2010 : Improving performance by globally turning off Outlook integration
- Connecting Dynamics GP to Microsoft Office 2010 : Skipping the exports by using Prebuilt Excel Reports
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Reading Excel files
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Creating Excel files
- SQL Server 2008 R2 : Configuring Resource Governor (part 2) - Defining Workload Groups, Creating Workload Groups in T-SQL
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer