Resource Governor Workload Management Scenarios

Your challenge is to determine the workload group and resource pool configuration that you want to use for your SQL Server environment. To this, you have to know how SQL Server resources are currently consumed by an application.

The solution is to create a workload group that is in the default resource pool. Run the application for several days, and use the information that you collect to answer the following questions.

• What is the CPU requirement?
  Determine a CPU bandwidth estimate by multiplying the average total CPU usage per request by the average number of requests per second.

  The maximum, average, and standard deviation of CPU usage can be used to determine whether a maximum CPU limit is necessary.

• What is the number of concurrent requests?
  Use the maximum and average number of concurrent requests statistics to determine the minimum concurrency factor.

• What is the total memory that is needed?
  Use the maximum and average total memory used to determine the memory requirement.

• How much memory is needed for a single query?
  Use the maximum and average memory per query statistic to determine how much memory is needed.

• Is any query not running due to lack of memory?
  Use the average time waiting on memory queue statistic to determine whether any queries are blocked because of memory availability.

Q: I just installed a new version of SQL Server and would like to use Resource Governor. How can I use it in my environment?

A: Consider using Resource Governor for monitoring resource consumption by workloads. Follow the steps below to set up a Resource Governor environment. A configuration example is provided for you.

1. Create workload groups for your workloads.

2. Create a classification function.

3. Register the classification function with Resource Governor.

4. Enable Resource Governor.

5. Monitor the Resource Governor performance counters and query the DMVs that will return information about resource usage for a workload group.

Example

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Q: Based on the monitoring results from Scenario 1, I would like to see an event any time that a query in the ad-hoc group (GroupAdhoc) runs longer than 30 seconds.

A: Follow the steps below to change the current Resource Governor configuration. A configuration example is provided for you.

1. Set a limit on CPU usage for the ad hoc group.

2. Monitor SQL Trace events (Resource Governor management class event).

3. Perform an action on the event. For example, ignore the event, send an e-mail, send a page, or execute the KILL command on the request.

Example

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Q: I want to further restrict the ad-hoc group so that it does not exceed 50 percent of CPU usage when all the requests are cumulated.

A: Because the previous scenarios used the default pool, you must create a new resource pool. Follow the steps below to change the current Resource Governor configuration. A configuration example is provided for you.

1. Create a new resource pool and configure CPU limits.

2. Configure the ad hoc workload group to use the new resource pool.

Example

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Q: Now I want to guarantee that the admin group always has resources to run diagnostic queries, but these queries should not take more than 10 percent of the server memory resources.

A: In order to do this you must create a new resource pool. Follow the steps below to change the current Resource Governor configuration. A configuration example is provided for you.

1. Create a new resource pool and set resource limits.

2. Configure the admin workload group to use the new pool.

Example

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Q: Since my reports are currently in the default resource pool, but using a separate workload group (GroupReports), I would like to ensure that anything in the default resource group has a lower resource usage priority than reports.

A: To achieve this you must configure the IMPORTANCE setting in the default group.

Follow the step below to change the current Resource Governor configuration. A configuration example is provided for you.

1. Change the setting for the default group.

Example

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Q: I want to create a new workload group that uses an existing resource pool. I then want to change the classifier function so it moves the group to a new resource pool.

A: Use the following steps to set up this new Resource Governor environment. A configuration example is provided for you.

1. Create a new resource pool with the default settings.

2. Create a new workload group that is in an existing pool.

3. Create and register a new classifier function for handling requests.

Example

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Q: I decide that I no longer require a workload group (created in Scenario 6), so I want to drop the workload group and the resource pool that it is in.

A: Use the following steps to change the Resource Governor environment. A configuration example is provided for you.

1. Create and register new classifier function that will classify requests into the remaining workload group.

2. Drop the workload group.

3. Drop the resource pool.

4. Apply the configuration changes.

Example

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO