SQL Server Resource Controller

A friend asked me a long time ago if I could allocate SQLServer resources based on the business priority level so that different applications can receive different responses, before SQLServer2008, there seems to be no solution to this requirement. However, since SQLServer2008, this requirement has become very simple, and SQLServer directly provides us with user-required allocation.

A friend asked me a long time ago if I could allocate SQLServer resources based on the business priority level so that different applications can receive different responses, before SQLServer2008, there seems to be no solution to this requirement. However, since SQLServer2008, this requirement has become very simple, and SQLServer directly provides us with user-required allocation.

A friend asked me a long time ago if I could allocate SQLServer resources based on the business priority level so that different applications could receive different responses. SQLServer2008 had no such requirement.

Solution, but from SQLServer2008, this requirement becomes very simple. SQLServer directly provides us with the ability to allocate resources as required by users. Next we will introduce this function.

The SQL Server Resource Controller is divided into three parts: resource pool, load group, and classifier function. The resource pool allows us to divide resources (such as CPU and Memory) into different carriers, the load group carries the load and

These loads are mapped to the resource pool, and the classifier function maps different sessions to different load groups.

Resource Pool:

08 provides two predefined resource pools

Internal pool: The internal pool is only used for the SQLServer database engine. The system administrator cannot change or set it;

Default pool: The default pool is used for various loads that are not allocated to a resource pool. Therefore, if you do not specify a resource governor, the default pool is used for all loads. The default Pool cannot be changed or deleted, but its resources can be modified.

Lower limit.

Upper and lower limits of resource pools:

The sum of the lower limit of each resource pool cannot exceed 100%Because SQLServer will try its best to satisfy each lower limit;

The upper limit can be set to any value between the lower limit and 100%.

The basic operations on the resource pool are as follows:

-- Create a resource pool
Create Resource Pool UserQueries with (max_cpu_percent = 100) -- delete a Resource Pool
Drop Resource Pool UserQueries

Load group:

The server Load balancer allows administrators to easily monitor resource usage and move loads between different resource pools.

A load group is mapped to a resource pool. A resource pool can have zero or more load groups. A load Group provides a bucket for a group of user sessions.

-- Create a load Group Create WorkLoad Group DailyExecReports USING UserQueries; -- delete a load Group drop WorkLoad Group DailyExecReports

Classifier functions:

Classifier classifies connected sessions and assigns a load group to Session requests and queries. You can assign groups based on any attribute (IP Address/Application name, user name, etc.) in the connection string.

Assign groups as follows:

• When a user accesses and uses the SAP_Login login name, the user is assigned a SAPUsers load group;
• A user access program named SSMS makes it a member of The AdhocAdmin load group;
• If a user is a member of the ReportUsers group, make it a member of The DaliyExecReports load group;
• A user connects with the shared memory and is allocated to the NightlyMaintanceTask group in the user group of the users.

-- Create Resource Pool AdminQueries with (max_cpu_percent = 100) Create Resource Pool UserQueries with (max_cpu_percent = 100) -- Create a load Group Create WorkLoad Group NightlyMaintenanceTasks USING AdminQueries; create WorkLoad Group AdhocAdmin USING AdminQueries; Create WorkLoad Group SAPUsers USING UserQueries; Create WorkLoad Group DailyExecReports USING UserQueries; -- create classifier function use master GO Create FUNCTION class_func_1 () returns sysname with schemabinding begin Declare @ val sysname -- Handle workload groups defined by login names IF SUSER_SNAME () = 'sap _ login' begin SET @ val = 'sapusers'; Return @ val; end IF APP_NAME () like 'Microsoft SQL Server Management Studio % 'begin Set @ val = 'adhocadmin'; Return @ val; end IF IS_MEMBER ('reportusers ') = 1 begin Set @ val = 'dailyexecreport'; Return @ val; end if connectionproperty ('net _ transport ') = 'shared memory' and IS_MEMBER ('nightlyadmin ') = 1 begin Set @ val = 'maid; Return @ val; end

Bind classifier function:

-- Bind the Classifier_Function to the Resource Governor Alter Resource Governor With (Classifier_Function = dbo. class_func_1 );

Enable and disable classifier functions:

-- Enable ALter Resource Governor Reconfigure; -- disable alter resource governor disable;

Test:

Now we use the SAP_Login and sysadmin users to call this script respectively.

-- Test script (called by SAP_Login and sysadmin respectively) set nocount on Declare @ I int = 100000000; Declare @ s varchar (100), @ count int; while @ I> 0 begin Select @ s = @ VERSION; select @ count = COUNT (0) from sys. sysobjects set @ I = @ I-1; end

View resource allocation through performance counters:

We can choose Resource statistics for performance counters: SQL Server: Resource Pools Stats;

First, we will allocate the resource pool in a one-to-one ratio:

Create Resource Pool AdminQueries with(max_cpu_percent=100)Create Resource Pool UserQueries with(max_cpu_percent=100)

Run the test script. The CPU usage diagram is as follows:

Adjust the resource allocation as follows:

Create Resource Pool AdminQueries with(max_cpu_percent=10)Create Resource Pool UserQueries with(max_cpu_percent=90)

Run the test script again. The CPU usage diagram is as follows:

We can see that when we adjust resources, the two sessions run the same script, and the resources they use vary greatly, in this way, different resources are allocated based on different applications.

DMV view resource pool:

-- View the select s resource pool of the Session. session_id, s. login_name, s. program_name, s. group_id, g. name from sys. dm_exec_sessions s join sys. dm_resource_governor_workload_groups g on s. group_id = g. group_id where session_id> 50

-- View the resource pool status select * from sys. dm_resource_governor_resource_pools

We can see that the two resource pools we created (two are system resource pools and default resource pools), and different sessions correspond to different resource pools.