We have provided seven scenarios as examples. Scenarios 1 through 5 are cumulative; each one builds on the preceding scenario. Scenario 6 is unrelated to the preceding scenarios, and scenario 7 builds on scenario 6.
Scenario 1
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.
-
Create workload groups for your workloads.
-
Create a classification function.
-
Register the classification function with Resource Governor.
-
Enable Resource Governor.
-
Monitor the Resource Governor performance counters and query the DMVs that will return information about resource usage for a workload group.
Example
.gif) Note: |
|---|
|
The following configuration does not specify a resource pool that the workload group should use. By default, the workload groups will use the default pool.
|
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
Scenario 2
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.
-
Set a limit on CPU usage for the ad hoc group.
-
Monitor SQL Trace events (Resource Governor management class event).
-
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
Scenario 3
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.
-
Create a new resource pool and configure CPU limits.
-
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
Scenario 4
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.
-
Create a new resource pool and set resource limits.
-
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
Scenario 5
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.
| Note: |
|---|
|
You can change the settings for the default group, but not the default pool. If you think that a change is needed for the default pool, this is a strong indicator that you need to create a new resource pool.
|
Follow the step below to change the current Resource Governor configuration. A configuration example is provided for you.
-
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
Scenario 6
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.
-
Create a new resource pool with the default settings.
-
Create a new workload group that is in an existing pool.
-
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
Scenario 7
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.
-
Create and register new classifier function that will classify requests into the remaining workload group.
-
Drop the workload group.
-
Drop the resource pool.
-
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