Resource Govenor enables to manage SQL Server workload and resource utilization.
Terminology:
Resource Pool – It is a collection of physical resources. SQL Server 2008 has two built in pools, default and internal. It allows to create maximum of 18 user defined pools.
Workload Group – It is a container for the requests that are similar to the classification criteria applied to each request. Like Resource pools there are two built in workload groups, default and internal.
Classifier Function – It is a user written criteria that is being applied to the requests.Each user session would be assigned to a particular workgroup load using the classifier function.
Limitations:
- Limited to only CPU bandwidth and memory management
- We can only control resource utlization for database engine. It cannot be used with SSIS, Analysis or Reporting services
- Only one instance of SQL Server can be managed
Here is just an example of creating resource pool and workload group
--Enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
USE [master]
GO
-- Create resource pool to be used
CREATE RESOURCE POOL poolAdhoc
WITH (
-- allocate at least
MIN_CPU_PERCENT = 10
-- do not let them exceed
, MAX_CPU_PERCENT = 20
-- admin queries should be small and we will allocate about
-- of the server memory for them
, MIN_MEMORY_PERCENT = 5
, MAX_MEMORY_PERCENT = 5 )
GO
-- Create adhoc workload group
CREATE WORKLOAD GROUP groupAdhoc
WITH (
-- use all defaults
IMPORTANCE = MEDIUM
, REQUEST_MAX_MEMORY_GRANT_PERCENT =25
, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0
, REQUEST_MAX_CPU_TIME_SEC = 50
, MAX_DOP = 0
, GROUP_MAX_REQUESTS = 0
)
USING
poolAdhoc
GO
-- Create admin workload group
CREATE WORKLOAD GROUP groupAdmin
WITH (
-- use all defaults
IMPORTANCE = MEDIUM
, REQUEST_MAX_MEMORY_GRANT_PERCENT =25
, REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0
, REQUEST_MAX_CPU_TIME_SEC = 30
, MAX_DOP = 0
, GROUP_MAX_REQUESTS = 0
)
USING
poolAdhoc
GO
CREATE FUNCTION [dbo].[rgClassifier_Adhoc]()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
-- Define the return sysname variable for the function
DECLARE @grp_name AS sysname;
SET @grp_name = 'default';
-- Specify the T-SQL statements for mapping session information
-- with Workload Groups defined for the Resource Governor.
IF (IS_SRVROLEMEMBER ('sysadmin') = 1)
SET @grp_name = 'groupAdmin'
IF (APP_NAME() LIKE '%Management Studio%')
SET @grp_name = 'groupAdhoc'
RETURN @grp_name;
END
GO
-- Set the classifier function for Resource Governor
ALTER RESOURCE GOVERNOR
WITH (
CLASSIFIER_FUNCTION = dbo.[rgClassifier_Adhoc]
)
GO
-- Make changes effective
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
