Resource Governor 控制 Memory 和 CPU 的使用

Resource Governor管理的 Resource 是 CPU 和 Memory，由三部分组成：Resource Pool，Workload Group 和 Classifier Function。

Resource Pool 限制WorkLoad Groups使用Resource的 max value 和min value，Workload group 是指共享一个Resource pool的request set，classifier function将一个request 划分到一个workload group中。这三个component是协同工作的：一个session 执行一个batch，产生一个request，这个request 被classifier function划分到一个workload group中，而一个workload group只能使用指定的Resource Pool中规定数量的resource（CPU 和 Memory）。

使用示例

1， 创建Resource Pool

CREATE RESOURCE POOL rpLowReource_Percent_20
WITH 
(
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 20,
     CAP_CPU_PERCENT = 20,
     AFFINITY SCHEDULER = auto,
     MIN_MEMORY_PERCENT = 0,
     MAX_MEMORY_PERCENT = 20
);

Syntax

CREATE RESOURCE POOL pool_name
[ WITH
(   [ MIN_CPU_PERCENT = value ]
    [ [ , ] MAX_CPU_PERCENT = value ] 
    [ [ , ] CAP_CPU_PERCENT = value ] 
    [ [ , ] AFFINITY {SCHEDULER = AUTO | (Scheduler_range_spec) | NUMANODE = (NUMA_node_range_spec)} ] 
    [ [ , ] MIN_MEMORY_PERCENT = value ]
    [ [ , ] MAX_MEMORY_PERCENT = value ]
) 
][;]

CAP_CPU_PERCENT =value              

Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. Limits the maximum CPU bandwidth level to be the same as the specified value. value is an integer with a default setting of 100. The allowed range for value is from 1 through 100.

CAP_CPU_PERCENT differs from MAX_CPU_PERCENT in that workloads associated with the pool can use CPU capacity above the value of MAX_CPU_PERCENT if it is available, but not above the value of CAP_CPU_PERCENT.

CAP_CPU_PERCENT 是 Resource Pool 用于的CPU 资源的 hard upper limit，can not exceed this hard cap。

AFFINITY {SCHEDULER = AUTO | (Scheduler_range_spec) | NUMANODE = (<NUMA_node_range_spec>)}              

Attach the resource pool to specific schedulers. The default value is AUTO.

AFFINITY SCHEDULER = (Scheduler_range_spec) maps the resource pool to the SQL Server schedules identified by the given IDs. These IDs map to the values in the scheduler_id column in sys.dm_os_schedulers (Transact-SQL).

When you use AFFINITY NAMANODE = (NUMA_node_range_spec), the resource pool is affinitized to the SQL Server schedulers that map to the physical CPUs that correspond to the given NUMA node or range of nodes. You can use the following Transact-SQL query to discover the mapping between the physical NUMA configuration and the SQL Server scheduler IDs.

2，创建Workload Group

Creates a Resource Governor workload group and associates the workload group with a Resource Governor resource pool.

CREATE WORKLOAD GROUP wgLowResource_Percent_20
WITH
(
    IMPORTANCE = MEDIUM,
    REQUEST_MAX_MEMORY_GRANT_PERCENT=20,
    REQUEST_MAX_CPU_TIME_SEC=0,
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,
    MAX_DOP=0,
    GROUP_MAX_REQUESTS=0
)
using rpLowReource_Percent_20;
go

using 子句的作用是将指定Workload Group使用的Resource Pool，多个Workload Group可以使用同一个Resource Pool，但是一个Workload Group 只能使用一个Resource Pool中的Resource。

Syntax

CREATE WORKLOAD GROUP group_name
[ WITH
( 　　 [ IMPORTANCE = { LOW | MEDIUM | HIGH } ]
      [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ]
      [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ]
      [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ] 
      [ [ , ] MAX_DOP = value ]
      [ [ , ] GROUP_MAX_REQUESTS = value ] 
)
]
[ USING { pool_name | "default" } ]
[ ; ]

USING { pool_name | [default]}

Associates the workload group with the user-defined resource pool identified by pool_name. This in effect puts the workload group in the resource pool. If pool_name is not provided, or if the USING argument is not used, the workload group is put in the predefined Resource Governor default pool.

3，创建 Classifier Function

CREATE FUNCTION dbo.rgClassifierFunction_LowResource_Percent_20() 
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Workload_Group_Name AS sysname
      IF (SUSER_NAME() = ‘USER_READONLY‘)
          SET @workload_group_name = ‘wgLowResource_Percent_20‘
    RETURN @workload_group_name
END;
GO

Syntax 参考MSDN的示例

CREATE FUNCTION schema_name.function_name ()
RETURNS sysname
WITH  SCHEMABINDING
[ AS ]
BEGIN 
        function_body 
        RETURN scalar_expression
END [ ; ]

SCHEMABINDING                         

Specifies that the function is bound to the database objects that it references. When SCHEMABINDING is specified, the base objects cannot be modified in a way that would affect the function definition. The function definition itself must first be modified or dropped to remove dependencies on the object that is to be modified.

The binding of the function to the objects it references is removed only when one of the following actions occurs:  

• The function is dropped.

• The function is modified by using the ALTER statement with the SCHEMABINDING option not specified.

MSDN Example

CREATE FUNCTION dbo.rgclassifier_v1() 
RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
-- Declare the variable to hold the value returned in sysname.
    DECLARE @grp_name AS sysname
-- If the user login is ‘sa‘, map the connection to the groupAdmin  workload group. 
    IF (SUSER_NAME() = ‘sa‘)
        SET @grp_name = ‘groupAdmin‘
-- Use application information to map the connection to the groupAdhoc workload group.
    ELSE IF (APP_NAME() LIKE ‘%MANAGEMENT STUDIO%‘)
        OR (APP_NAME() LIKE ‘%QUERY ANALYZER%‘)
            SET @grp_name = ‘groupAdhoc‘
-- If the application is for reporting, map the connection to the groupReports workload group.
    ELSE IF (APP_NAME() LIKE ‘%REPORT SERVER%‘)
        SET @grp_name = ‘groupReports‘
-- If the connection does not map to any of the previous groups, put the connection into the default workload group.
    ELSE
        SET @grp_name = ‘default‘
    RETURN @grp_name
END;
GO

-- Register the classifier user-defined function and update the 
-- the in-memory configuration.
ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

4，ALTER RESOURCE GOVERNOR

This command is used to perform the following actions:

• Apply the configuration changes specified when the CREATE|ALTER|DROP WORKLOAD GROUP or CREATE|ALTER|DROP RESOURCE POOL statements are issued.

• Enable or disable Resource Governor.

• Configure classification for incoming requests.

• Reset workload group and resource pool statistics.

Syntax

ALTER RESOURCE GOVERNOR  
{ DISABLE | RECONFIGURE }
| WITH ( CLASSIFIER_FUNCTION = { schema_name.function_name | NULL } )
| RESET STATISTICS
[ ; ]

Term	Definition
DISABLE | RECONFIGURE	DISABLE disables Resource Governor. Disabling Resource Governor has the following results: The classifier function is not executed. All new connections are automatically classified into the default group. System-initiated requests are classified into the internal workload group. All existing workload group and resource pool settings are reset to their default values. In this case, no events are fired when limits are reached. Normal system monitoring is not affected. Configuration changes can be made, but the changes do not take effect until Resource Governor is enabled. Upon restarting SQL Server, the Resource Governor will not load its configuration, but instead will have only the default and internal groups and pools. When the Resource Governor is not enabled, RECONFIGURE enables the Resource Governor. Enabling Resource Governor has the following results: The classifier function is executed for new connections so that their workload can be assigned to workload groups. The resource limits that are specified in the Resource Governor configuration are honored and enforced. Requests that existed before enabling Resource Governor are affected by any configuration changes that were made when Resource Governor was disabled. When Resource Governor is running, RECONFIGURE applies any configuration changes requested when the CREATE|ALTER|DROP WORKLOAD GROUP or CREATE|ALTER|DROP RESOURCE POOL statements are executed.                              Important                           ALTER RESOURCE GOVERNOR RECONFIGURE must be issued in order for any configuration changes to take effect.
CLASSIFIER_FUNCTION = { schema_name.function_name | NULL }	Registers the classification function specified by schema_name.function_name. This function classifies every new session and assigns the session requests and queries to a workload group. When NULL is used, new sessions are automatically assigned to the default workload group.
RESET STATISTICS	Resets statistics on all workload groups and resource pools. For more information, see sys.dm_resource_governor_workload_groups (Transact-SQL) and sys.dm_resource_governor_resource_pools (Transact-SQL).

Example

A. Starting the Resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE;

ALTER RESOURCE GOVERNOR 
WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
Go

CREATE RESOURCE POOL rpLowReource_Percent_20
WITH 
(
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 20,
     CAP_CPU_PERCENT = 20,
     AFFINITY SCHEDULER = auto,
     MIN_MEMORY_PERCENT = 0,
     MAX_MEMORY_PERCENT = 20
);
go

CREATE WORKLOAD GROUP wgLowResource_Percent_20
WITH
(
    IMPORTANCE = MEDIUM,
    REQUEST_MAX_MEMORY_GRANT_PERCENT=20,
    REQUEST_MAX_CPU_TIME_SEC=0,
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC=0,
    MAX_DOP=0,
    GROUP_MAX_REQUESTS=0
)
using rpLowReource_Percent_20;
go


CREATE FUNCTION dbo.rgClassifierFunction_LowResource_Percent_20() 
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Workload_Group_Name AS sysname
      IF (SUSER_NAME() = ‘USER_READONLY‘)
          SET @workload_group_name = ‘wgLowResource_Percent_20‘
    RETURN @workload_group_name
END;
GO


-- Register the classifier user-defined function and update in-memory configuration.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.rgClassifierFunction_LowResource_Percent_20);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

参考文档：

http://www.cnblogs.com/nzperfect/archive/2009/07/26/1531512.html

https://msdn.microsoft.com/en-us/library/bb895329(v=sql.110).aspx

https://msdn.microsoft.com/en-us/library/bb934146(v=sql.110).aspx

https://msdn.microsoft.com/en-us/library/bb934013(v=sql.110).aspx

Resource Governor 控制 Memory 和 CPU 的使用

原文：http://www.cnblogs.com/ljhdo/p/4995633.html