MS SQL Srver 2008 : Using Resource Governor -Exercise 3

                 Using Resource Governor

Scenario

In this exercise, you will control resource usage for two applications by using Resource Governor. Resource
Governor helps you consolidate multiple database workloads in a single SQL Server instance while ensuring  predictable performance across workloads by defining resource limits and priorities. With Resource Governor, DBAs can take control of multiple workload environments by defining workload prioritization, minimize the possibility of runaway queries, and provide mission-critical workloads with the resources they need.
Tasks Detailed Steps Complete the following task on:

1. Prepare the lab environment
a. In Windows Explorer, view the contents of the
C:\SQLHOLS\WhatsNewForDBAs\Starter\ResourceGovernor folder.
b. Run Setup.bat. This batch file creates a view and a stored procedure that you will
use in this lab.
c. Minimize Windows Explorer.

2. Create a resource pool and workload group.
a. In SQL Server Management Studio, in Object Explorer, expand the
Management node.
b. Right-click Resource Governor, and then click Enable.
c. Right-click Resource Governor, and then click New Resource Pool.
d. In the first empty line of the Resource pools grid, enter the following values:
• Name: Low Importance Pool
• Minimum CPU %: 0
• Maximum CPU %: 10
• Minimum Memory %: 0
• Maximum Memory %: 10
Note: The resource pool enables you to create limits for resource usage. These limits
are applied if other applications require the resources. If no other applications are
running, the limits are not applied.
e. In the Workload groups for resource pool grid, enter the following values, and
then click OK:
• Name: Low Importance Group
• Importance: Low
• Maximum requests: 0
• CPU Time (sec): 0
• Memory Grant %: 25
• Grant Timeout: 0
• Degree of Parallelism: 0
Note: Each resource pool can include multiple workload groups, each with different
levels of importance and resources. This architecture provides fine-grain control over
the allocation of system available resources to different workloads.

3. Create a classification.
a. Click New Query, and then type the following code to classify client applications
with application names containing the text “Low Importance Application” in the
What's New in Microsoft® SQL Server® 2008 for Database Administrators
Tasks Detailed Steps function Low Importance Group workload group:
Tip: This code is also available in the CreateFunction.sql script file in the
C:\SQLHOLS\WhatsNewForDBAs\Starter\ResourceGovernor folder.
CREATE FUNCTION dbo.fn_ClassifyApps() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @ret sysname
IF (APP_NAME() LIKE '%Low Importance Application%')
SET @ret='Low Importance Group'
RETURN @ret
END
GO
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fn_ClassifyApps)
ALTER RESOURCE GOVERNOR RECONFIGURE
Note: Classification functions map applications to workload groups so that limits can
then be applied to their resource usage.
b. Click Execute to run the code.
c. Minimize SQL Server Management Studio.

4. Monitor applications by using Performance Monitor
a. Start the Reliability and Performance Monitor console in the Administrative
Tools program group. On the User Account Control message box, click
Continue.
b. Click the Performance Monitor node, and on the toolbar, click the Delete button
to remove any default counters.
c. On the toolbar, click the Add button.
d. In the list, click SQLServer:Resource Pool Stats, then expand it.
e. In the list of counters, click CPU usage %.
f. In the list of instances, click default and CTRL+click Low Importance Pool.
g. Click Add to add both of the selected application pools, and then click OK. 

5. Run applications
a. Maximize Windows Explorer, and then navigate to the C:\SQLHOLS\Resource
Governor\Starter folder.
b. Double-click LowImportanceApp.vbs, and then click OK to start the application.
c. In the Performance console, review resource usage. Notice that CPU usage for the
Low Importance Pool instance rises and is not limited by Resource Governor
because there are no workloads from other resource pools.
d. In Windows Explorer, double-click MissionCriticalApp.vbs, and then click OK
to start the application.
e. In the Performance console, note that whenever the default workload CPU usage
increases, Resource Governor reduces the CPU usage of the Low Importance
Pool workload.
Key Point: You can use Resource Governor to limit resources for specific workloads;
this enables more important workloads to achieve optimum performance.
f. Wait a few minutes for the VBScript applications to finish, and in the message
boxes that are displayed, click OK.
g. Close Reliability and Performance Monitor and Windows Explorer.
h. Keep SQL Server Management Studio open for the next exercise.