Control Memory and CPU Resource usage

The advent of the Resource governor addresses the need to manage multi-user workloads and resource isolation on a single SQL Server instance, allowing administrators to limit the amount of CPU and memory resources that the system consumes when processing requsts, to some extent Runaway queries are restricted and quarantined. For SQL Server 2012, users are able to fully isolate CPU resources based on workloads, and can set a hard upper limit for CPU Resource usage (CAP usage,hard limit). On a multi-user, high-concurrency, SQL Server instance, administrators use resource Governor to control the amount of memory and CPU resources used by different workloads, isolating the resources used by different applications, and enabling predictable control and assurance of system performance.

First, the basic realization (fundamental implementation)

The programmable part of the Resource governor consists of three parts: Resource pool,workload Group and Classifier function, each of which implements different functions.

1, resource pools (Resource pool)

In an instance of SQL Server, the basic unit of resource isolation is the resource pool, which, as its name, is a resource pool, specifying the number of CPUs and memory resources that the pool has when it creates the resource pool. In the version of SQL Server 2012, you can create up to 62 user-defined resource Pool. SQL Server has two built-in resource pools:internal for system tasks that the user cannot configure; default is resource Pool for any unspecified resource request of the pool;
2, Load grouping (Workload group)

The Workload group is a logical entity that represents one or more workloads. In fact, a workload is a query request received by an instance of SQL Server that divides multiple requests with common attributes into the same workload group through the classifier function. Each resource pool serves one or more workload groupings, which means that these workload groupings can share resources that are owned by the same resource pool.

SQL Server built-in two load groups: internal and default, associated to the appropriate internal and default resource pools, internal load groupings for system Task,sql Server will divide the request that is not explicitly specified by the classification function into a load group into the default group.

3, classification functions (Classifier function)

The classification function divides the request into different load groupings based on login, application name, database name and other attributes, and can specify user-defined load groupings or default load groupings.

4, processing process

Resource governor each part mates to control the use of memory and CPU resources: Classification classifies the requests received by the SQL Server instance into different load groups, and the load group uses the corresponding Resource Pool has CPU and memory resources to handle Request,resource governor processing processes such as:

Resource Pool is a subset of physical resources in an instance of SQL Server, and because all the databases on the same instance share all the resources of that instance, it is a good idea to create the three constituent objects of the Resource pool in the master database.

Second, use the example

1, create resource Pool

CREATERESOURCE POOL rp_20percent with(Min_cpu_percent= 0, Max_cpu_percent=  -, Cap_cpu_percent=  -, AFFINITY SCHEDULER=Auto, Min_memory_percent= 0, Max_memory_percent=  -);

The cap_cpu_percent option sets the resource pool to have a hard upper limit on CPU resources, and no workload group can use more CPUs than that , and the resource pool may use more CPU resources than The max_cpu_percent option specifies the scale.

2, create workload Group

Create a workload group that associates a resource pool with a using clause that can be used by the grouping, a workload group can only associate one resource pool, and one resource pool service one or more workload groupings.

CREATEWORKLOADGROUPwg_20percent with(Importance=MEDIUM, Request_max_memory_grant_percent= -, Request_max_cpu_time_sec=0, Request_memory_grant_timeout_sec=0, Max_dop=0, Group_max_requests=0) USING rp_20percent;

importance option : This option specifies the importance of the workload group in the resource pool, because the same resource pool is associated with multiple workload group, Importance high-value workload Group is more likely to win when competing for resources in the resource pool.

3, create Classifier Function

The classification function divides the request into a wg_20percent load group based on the user name, and by default any request that does not specify the workload group uses the default resource pool. In a real-world product environment, you can also use App_name () to get the application name and group the load according to the application.

CREATE FUNCTIONdbo.rgclassifierfunction_20percent ()RETURNSsysname withSCHEMABINDING asBEGIN    DECLARE @Workload_Group_Name  assysnameIF(Suser_name()= 'user_readonly')          SET @workload_group_name = 'wg_20percent'    RETURN @workload_group_nameEND;

4, start resource Governor

First, configure the classification functions used by the resource Governor, and then execute the RECONFIGURE command, which starts resource Governor,sql server uses the classification function to divide the requests received by the SQL Server instance into different load groupings.

ALTER with (classifier_function=dbo.rgclassifierfunction_20percent); GO ALTER RESOURCE GOVERNOR
RECONFIGURE ; GO

MSDN Sample script:

CREATE FUNCTIONDBO.RGCLASSIFIER_V1 ()RETURNSsysname withSCHEMABINDING asBEGIN--Declare the variable to hold the value of returned in sysname.    DECLARE @grp_name  assysname--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 maps to any of the previous groups, put the connection into the default workload GROUP.    ELSE        SET @grp_name = 'default'    RETURN @grp_nameEND;GO--Register the classifier user-defined function and update the--The in-memory configuration.ALTERRESOURCE GOVERNOR with(classifier_function=dbo.rgclassifier_v1);GOALTERRESOURCE GOVERNORRECONFIGURE;GO

View Code

Reference Documentation:

Resource Governor

Resource Governor in SQL Server 2012

Use SQL Server 2008 resource governor to limit the CPU resources used to specify user queries

CREATE RESOURCE POOL (Transact-SQL)

CREATE WORKLOAD GROUP (Transact-SQL)

ALTER RESOURCE GOVERNOR (Transact-SQL)

Controlling the use of Memory and CPU resources