Shulou(Shulou.com)06/01 Report--

The emergence of Resource Governor solves the need to manage multi-user workload and resource isolation on a SQL Server instance. It allows administrators to limit the number of CPU and Memory resources consumed by the system when processing Requsts, and limits and isolates runaway queries to a certain extent. For SQL Server 2012, users can achieve complete isolation of CPU resources based on workload and set a hard upper limit (CAP Usage,Hard Limit) of CPU resource usage. On a multi-user, high concurrency SQL Server instance, the administrator uses Resource Governor to control the amount of memory and CPU resources used by different workloads, so that different applications are isolated from each other in the resources used, and the system performance is controlled and guaranteed predictably.

First, basic implementation (fundamental implementation)

The programmable part of Resource Governor consists of three parts: Resource Pool,Workload Group and Classifier Function, each of which implements different functions.

1, resource pool (Resource Pool)

In the SQL Server instance, the basic unit of resource isolation is the Resource Pool, which, as its name suggests, is the resource pool, and when the Resource Pool is created, specify the range of CPU and Memory resources that the Pool owns. In the SQL Server 2012 release, you can create up to 62 user-defined Resource Pool. SQL Server has two built-in Resource Pools:internal for system Task, which cannot be configured by users; default is the default Resource Pool for any Request that does not specify a Resource Pool

2, load grouping (Workload Group)

A Workload Group is a logical entity that represents one or more workloads. In fact, a workload is a query request (Request) received by an SQL Server instance that divides multiple Requests with common attributes into the same Workload Group through Classifier Function. Each Resource Pool serves one or more workload groups, that is, these workload groups can share resources owned by the same Resource Pool.

SQL Server has two built-in load packets: internal and default, which are associated with the corresponding internal and default resource pools. the internal load packet is used for the system Task,SQL Server to divide the Request which is not explicitly specified by the classification function into the default packet.

3, classification function (Classifier Function)

The classification function divides Request into different load groups according to Login, application name, database name and other attributes, and can specify user-defined load groups or default load groups.

4, processing flow

Various parts of Resource Governor cooperate with each other to control the use of memory and CPU resources: Classification classifies the Requests received by SQL Server instances and divides them into different load groups. The processing flow of CPU and memory resources contained in the Resource Pool associated with the load group is shown below:

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

Second, use examples

1, create Resource Pool

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

The CAP_CPU_PERCENT option sets the hard upper limit for the resource pool to have CPU resources, the number of CPU used by any Workload Group cannot exceed this limit, and the resource pool may use more CPU resources than the percentage specified by the MAX_CPU_PERCENT option.

2, create Workload Group

Create a workload grouping and associate the resource pools that can be used by the grouping through the using clause. A workload grouping can only be associated with one resource pool, and one resource pool serves one or more workload groups.

WORKLOAD

IMPORTANCE option: this option specifies the relative importance of the Workload Group in the Resource Pool. Because the same Resource Pool is associated with multiple Workload Group with high Workload Group,Importance values, it is easier to win when competing for resources in Resource Pool. This option has three optional values: Low,Medium and High, and the default value is Medium.

3, create Classifier Function

The classification function divides the Request into wg_20Percent load groups based on the user name, and by default, any Request that does not specify a Workload Group uses the Default resource pool. In a real production environment, you can also use APP_NAME () to get the application name and divide the load into groups based on the application.

()

4, start Resource Governor

First, configure the classification function used by Resource Governor; then, execute the reconfiguration command to start Resource Governor,SQL Server to divide the Requests received by the SQL Server instance into different load groups using the classification function.

(CLASSIFIER_FUNCTION RESOURCE GOVERNOR

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.