SQL 2008 : Resource Governor

Resource Governor enables dynamic (on the fly) allocation of SQL Server resources.

Resource Governor consists of 3 factors -

1. Resource Pools

A resource pool is a set configuration settings.
You can adjust 4 factors per pool -

• Maximum Memory
• Minimum Memory
• Maximum CPU
• Minimum CPU

NB : CPU is controlled per Scheduler, not accross all schedulers.

There are 2 standard resource pools, 'internal' and 'default'.

'Internal' controls system resources used by SQL Server.
It is not affected by the configuration of other user pools.

'Default' is the first user pool, initially defined to use all available resources.

Further resource pools are user defined. The effect of adding further pools is shown below -

Eff Max% = Max% - SUM (Min% of other pools)
Shared% = Eff Max% - Min%

Resource Pool	Min %	Max %	Effective Max %	Shared %
Internal	0	100	100	100
Default	0	100	80	20
SQL_App	10	40	20	10
SQL_Reports	10	40	20	10
SQL_Admin	0	25	5	5

The same calculations apply equally to memory or cpu....

Tsql for creation of a resource pool -

CREATE RESOURCE POOL SQL_Reports
WITH
(
MAX_CPU_PERCENT = 25,
MAX_MEMORY_PERCENT = 50
);
GO

2. Workload Groups

Workload Groups are containers for sql server sessions.
They get applied to resource pools and can be moved freely between them.

CREATE WORKLOAD GROUP Adhoc_Reports
USING SQL_Reports
GO

CREATE WORKLOAD GROUP Application_Reports
USING SQL_Reports
GO

To put sql sessions into groups, the sessions need to be classified.

3. Classification Functions

To allocate sql sessions to workgroups, they need to be classified.
To do this, write a Classifier Function.
This is really a SQL scaler UDF (user defined function) which looks at connection/session properties to determine session properties.

You could advocate different behaviour based on -
What is running the session - APP_NAME()
What the time is - GETDATE()
Who is executing the SQL - SUSER_SNAME()

CREATE FUNCTION ResourceGovClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
 DECLARE @classification VARCHAR(32)

 IF SUSER_SNAME() = 'ReportingUser'
    SET @classification = ‘Application_Reports’

 IF (APP_NAME() LIKE '%REPORT SERVER%')
    SET @classification = ‘Adhoc_Reports’

 RETURN @classification
END
GO

Setting up Resource Governor to use the function -

ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.ResourceGovClassifier)
GO

Enable Resource Governor -

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

When defining the IMPORTANCE of a sesion, be aware it only applies to active workers of groups assigned to the same pool.

Not sure if all this is working?

Query Resource Governor sessions like this -

SELECT    session_id,
          [host_name],
          [program_name],
          nt_user_name,
          groups.name as 'ResGov_Group',
          pools.name as 'ResGov_Pool'
FROM sys.dm_exec_sessions usersessions
    INNER JOIN sys.dm_resource_governor_workload_groups groups
            ON usersessions.group_id = groups.group_id
    INNER JOIN sys.dm_resource_governor_resource_pools pools
            ON groups.pool_id = pools.pool_id