On on-Demand method of database resource use

The role of the SQL Server resource adjuster

If there are 5 of busy databases that need to be deployed on a single server, in order to avoid mutual resource preemption, we typically install 5 SQL Server instances on the server to host the 5 databases individually, and then set the maximum and minimum memory, CPU mask, and so on for each instance. Resource allocation for S control of these 5 business databases.

The author of the company once there is such a case. The disadvantage of this approach is obvious: first, authorization, in the above example, the cost of 5 SQL Server instances should be higher than one, followed by an increase in management costs, the DBA had to install and maintain 5 instances. So, is there a better way?

In fact, from SQL Server2008 (Enterprise Edition), Microsoft has already considered this situation, and proposed the solution-"SQL Server resource Adjuster". SQL Server's resource adjuster introduces a multi-tenancy concept that "rents" the resources of one instance of SQL Server to different client loads, each of which is isolated from each other and not affected by each client load.

Let's look at the principle of SQL Server resource adjuster and an example to understand the resource adjuster under SQL Server:

The principle of SQL Server resource adjuster

There are three important concepts in the SQL Server resource adjuster:

1. Resource Pools

In the multi-tenancy concept above, different client-side workloads are able to allocate resources that are isolated from each other because they have their own separate resource pools.

Within a resource pool, the DBA can set the maximum, minimum, and CPU resources, that is, the resource pool is actually a collection of memory and CPU resources, and the DBA can set up a large pool of resources for important business, based on the needs of the business, while the lower SLA business sets a small resource pool.

2. Workload of the Working Group

A workgroup payload is a collection of client requests that have common characteristics. SQL Server forms multiple workgroup loads after the client requests are categorized by a certain number of rules.

The workgroup payload is the principal used by the resource pool. Different resource pools are bound to different workgroup workloads, which enables resource isolation for each workgroup load.

3. Classifier

The classifier provides a set of rules that divide the workload of the workgroup, based on which SQL Server classifies the client's requests into multiple workgroups.

For example, with the user name of SQL Server as a classifier, all requests from a user are divided into workgroup load 1, and all requests from B users are divided into workgroup load 2. then workgroup load 1 and 2 correspond to different resource pools, respectively.

Schematic diagram is as follows:

Allocating CPU resources to two databases with a resource adjuster

Suppose you have a market and sell two departments, each with a different database, but two databases on the same SQL Server instance, to ensure that two departments of the database are not affected by each other's resource usage, we use the SQL Server Resource governor to control the resource usage of two databases.

1. Create a test database

Create DATABASE Sales

Create DATABASE Marketing

2. Configuring the CPU Environment

To better see the contention for CPU resources, we first set up the SQL Server CPU affinity mask so that SQL Server can only use one logical core, and all the workgroup workloads share the same CPU core resources.

sp_configure ' show advanced ', 1

GO

RECONFIGURE

GO

sp_configure ' affinity mask ', 1

GO

RECONFIGURE

GO

3. Configure Resource Governor

1) Create a resource pool

CREATE RESOURCE POOL Salespool

CREATE RESOURCE POOL Marketingpool

2) Create a workgroup load

--Create a workgroup of Salesgroup and bind to Salespool

CREATE WORKLOAD GROUP Salesgroup

USING Salespool

--Create a workgroup of Marketinggroup and bind to Marketingpool

CREATE WORKLOAD GROUP Marketinggroup

USING Marketingpool

GO

3) Create classifier (classification function)

--Create a custom two classification function that classifies and returns the name of the database according to the database in the client connection string

CREATE FUNCTION Classifier_dbname ()

RETURNS SYSNAME with SCHEMABINDING

BEGIN

DECLARE @val varchar (32)

SET @val = ' default ';

IF ' Sales ' = Original_db_name ()

SET @val = ' salesgroup ';

ELSE IF ' Marketing ' = Original_db_name ()

SET @val = ' marketinggroup ';

RETURN @val;

END

GO

--Bind the classification function to the resource adjuster

ALTER RESOURCE GOVERNOR

With (classifier_function = dbo. Classifier_dbname)

GO

4) Enable resource Governor

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

4. Test the load of the sales database

Ø Create the following test SQL statement and save it to the Workload.sql file

SET NOCOUNT on

DECLARE @i INT

DECLARE @s VARCHAR (100)

SET @i = 100000000

While @i > 0

BEGIN

SELECT @s = @ @version;

SET @i = @i-1;

END

Ø run cmd as Administrator

Ø then load the Workload.sql statement in SQLCMD mode

Sqlcmd-s localhost\sql2014-u sa-p 95938-d sales–i "D:\workload.sql"

This statement means that the workload.sql is executed in the sales database,

Ø Turn on performance counters

Sqlserver:resource Pool STATS->CPU Usage

You can see the CPU resource usage of Salespool as follows:

, the Salespool CPU Resource usage is 25%, (equivalent to the use of the author computer 4 core CPU 1 Kernel resources (the previous set of SQL Server affinity mask is 1)). Because we bind the sales database to the resource pool Salespool in the previous classifier, the CPU resource consumption of the sales database is 25%.

5. Re-test the resource contention for the sales library and the marketing library

Ø Open a new CMD window, execute the following statement:

Sqlcmd-s localhost\sql2014-u sa-p 95938-d marketing-i "D:\workload.sql"

This statement means that the workload.sql is executed in the marketing database,

The following results appear:

Salespool and Marketingpool share 25% of the CPU resources (and the same amount of resources they consume (near), because they are all using the default resource allocation weights). Here Marketingpool the same as the Salespool described in this article, it is also bound to a database, the difference is that the marketing database is bound here.

Therefore, the CPU resource contention for these two resource pools reflects resource contention for the sales database and the marketing database.

Note: The red curve represents the sales pool

The green curve represents Marketingpool

Ø Now, we change the weight of the CPU resources for the resource pool of the sales system to 70

ALTER RESOURCE POOL Salespool

With (min_cpu_percent = 70)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

The results are as follows:

At this point, the resources occupied by Salespool are increased to 70%, while the Marketingpool CPU resources are reduced to 30%, and both are allocated according to 7:3.

This is equivalent to allocating more CPU resources to the sales database and cutting down the marketing CPU resources.

6. Finally, let's look at an example that sets the maximum CPU resource weight

--Set maximum CPU usage for Marketingpool

ALTER RESOURCE POOL Marketingpool

With (max_cpu_percent = 5)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

At this point, the resources occupied by marketingpool such as:

If the workload.sql executed at this time in the sales database has been executed, then the marketing database will break through the Max_cpu_percent = 5 limit previously set and use all CPU resources, the advantage is that the resources can be fully utilized, Avoid wasting resources. such as the curve in the blue box.

Of course, if you have to limit the marketing database CPU resources must be within 5%, can be implemented by the following statement:

ALTER RESOURCE POOL Marketingpool

With (cap_cpu_percent=5)

GO

ALTER RESOURCE GOVERNOR RECONFIGURE

GO

Summarize

SQL Server resource adjuster can be used to allocate resources such as CPU, memory, IO and so on for different databases, different requests and different users through the mechanism of resource pool, workgroup load, classifier and so on. With this technology, we can combine multiple databases to the same instance, and can also pre-target server resources for some SQL code and avoid excessive resources for some SQL statements, and can allocate resources according to the priority of the business, while ensuring that each database is allocated to reasonable resources. In summary, using SQL Server resource conditioners to allocate server resources more delicately is one of the must-kill techniques for DBAs.

On on-Demand method of database resource use