🐏 👨🏽‍🌾 ⭕️ 我们应该怎样设置资源调控器 👩🏾‍🎨 🧗🏿 🚼

可能每个使用SQL Server的人都会发现自己，或者仍然会遇到这种情况：星期五晚上，您已经精神上可以休息了，然后SQL Server CPU上的高负载通知开始到来，电话开始响起，并且您看到的监视系统中图片如KDPV上所示。

而且，一方面，这可能不是一个特殊的问题-是的，这对服务器来说很困难，是的，这令人不愉快，但毕竟，勋爵，好吧，您的报告将不在15秒内形成，而是在45秒内形成-业务，您会看到-你们很多，但只有一台服务器，您只需要稍等一下。但是，如果有所有这些，有些业务流程迫不及待了怎么办？如果在这样的负担下，产品的销售变得如此缓慢而买家拒绝购买，该怎么办？

如果您可以将进程/用户分开并说SQL Server，那就太好了-这些都是非常重要的人，应该首先执行他们的查询。但是这些-它们当然也很重要，但是它们可以等待更长的时间。但这通常是监视系统发出的请求，这些请求很久以来一直是重写的，在我们在这里做重要的事情时，您可以完全忽略它们。

有时，在资源管理器的帮助下，它们确实可以分开。

同时注意以下几点：

资源调控器仅在企业版中可用。如果您有其他任何版本（当然，还有Developer，但是它还没有在生产中，对吗？）-不幸的是，您不能使用它。
, , , , , .
, Resource Governor, , , , , ( ).
- , - , .
, , .

Resource Governor, . CPU - SQL Server buffer pool ( Resource Governor buffer pool), "" CPU. , , buffer pool, , ( , varchar(max)).

, Resource Governor :

(CPU, RAM, IOPS) - ( ), .
(workload group), , .
(, , ) .

( ) paint draw.io.

dmv sys.dm_exec_sessions, group_id - resource governor, (, ) (, , ) .

-, - internal default - internal default ( Internal group). , , - internal - , SQL Server - , - . default - , , -, .

, . SQL Server Dedicated Admin Connection (DAC) - , , , DAC, internal pool. - , DAC - . DAC, - Resource Governor, , , , , . , - SSMS.

, , Resource Governor.

CREATE RESOURCE POOL (MSDN):

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 ]  
        [ [ , ] MIN_IOPS_PER_VOLUME = value ]  
        [ [ , ] MAX_IOPS_PER_VOLUME = value ]  
    )   
]  
[;]  
  
<scheduler_range_spec> ::=  
{ SCHED_ID | SCHED_ID TO SCHED_ID }[,...n]  
  
<NUMA_node_range_spec> ::=  
{ NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID }[,...n]

MIN_CPU_PERCENT - 100%, , . : , "" CPU, , .
MAX_CPU_PERCENT - 100%, , , . : , , , .
CAP_CPU_PERCENT - . , , .
AFFINITY - (-) (, ), (-) NUMA-
MIN/MAX_MEMORY_PERCENT - ( , ) . - buffer pool, . memory grants.
MIN/MAX_IOPS_PER_VOLUME - IO ( , ), .

MIN_CPU_PERCENT - MIN_CPU_PERCENT 100%. , 100% , - internal default .

, CREATE WORKLOAD GROUP (MSDN):

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" ]
    [ [ , ] EXTERNAL external_pool_name | "default" ] ]
    } ]
[ ; ]

IMPORTANCE - "" . , , , " ", . , " " , " " - " " .
REQUEST_MAX_MEMORY_GRANT_PERCENT - 25%, . , - .
REQUEST_MAX_CPU_TIME_SEC - , , , cpu_threshold_exceeded, Extended Events. CU SQL Server 2016/2017, trace-, , , CPU time .
REQUEST_MEMORY_GRANT_TIMEOUT_SEC - , , - .
MAX_DOP - . "", , , . 1, 4 - MAX DOP = 4.
GROUP_MAX_REQUESTS - . , , . .

, , - . , SQL Server ?

, , . , , , ( 1). - scalar UDF, master. - SCHEMABINDING SYSNAME. , , , -- , DAC, .

MSDN lookup- , NOLOCK SNAPSHOT IL, , , lookup- - best practice .

, (ALTER) , , , . ! : , .

Resource Governor :

ALTER RESOURCE GOVERNOR DISABLE;

// / .

CPU :

CREATE RESOURCE POOL [pool1]
WITH (
    MIN_CPU_PERCENT = 15,
    MAX_CPU_PERCENT = 15,
    CAP_CPU_PERCENT = 20
);

CREATE RESOURCE POOL [pool2]
WITH (
    MIN_CPU_PERCENT = 50,
    MAX_CPU_PERCENT = 90
);

, "" 15 , - CAP_CPU_PERCENT 20 CPU, "" . , "" 50 90 , , - 100.

CREATE WORKLOAD GROUP [pool1_group1]
WITH (
    IMPORTANCE = HIGH,
    REQUEST_MAX_CPU_TIME_SEC = 5,
    MAX_DOP = 2
)
USING [pool1];

CREATE WORKLOAD GROUP [pool2_group1]
WITH (
    IMPORTANCE = HIGH
)
USING [pool2];

CREATE WORKLOAD GROUP [pool2_group2]
WITH (
    IMPORTANCE = MEDIUM
)
USING [pool2];

CREATE WORKLOAD GROUP [pool2_group3]
WITH (
    IMPORTANCE = LOW,
    GROUP_MAX_REQUESTS = 1 
)
USING [pool2];

, , 2 ( MAXDOP = 4), 5 . , , , .

, .

, . , .

USE [StackOverflow2013]
GO

CREATE LOGIN p1g1 WITH PASSWORD = N'1', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
CREATE LOGIN p2g1 WITH PASSWORD = N'1', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
CREATE LOGIN p2g2 WITH PASSWORD = N'1', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
CREATE LOGIN p2g3 WITH PASSWORD = N'1', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;

CREATE USER p1g1 FOR LOGIN p1g1;
CREATE USER p2g1 FOR LOGIN p2g1;
CREATE USER p2g2 FOR LOGIN p2g2;
CREATE USER p2g3 FOR LOGIN p2g3; 

EXEC sp_addrolemember N'db_owner', N'p1g1';
EXEC sp_addrolemember N'db_owner', N'p2g1';
EXEC sp_addrolemember N'db_owner', N'p2g2';
EXEC sp_addrolemember N'db_owner', N'p2g3';

USE [master]
GO

CREATE FUNCTION fnClassify()
RETURNS SYSNAME 
WITH SCHEMABINDING
AS
BEGIN
    RETURN 
        CASE ORIGINAL_LOGIN() 
            WHEN 'p1g1' THEN 'pool1_group1'
            WHEN 'p2g1' THEN 'pool2_group1'
            WHEN 'p2g2' THEN 'pool2_group2'
            WHEN 'p2g3' THEN 'pool2_group3'
        ELSE 'default' END;
END;

, :

SELECT master.dbo.fnClassify();

- default,

NULL - - Resource Governor , default.

, - Resource Governor :

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnClassify);  
ALTER RESOURCE GOVERNOR RECONFIGURE;

, .

SELECT 
    s.session_id, 
    s.login_name, 
    wg.group_id,
    wg.name AS workload_group_name,
    wg.pool_id,
    rp.name AS pool_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups wg ON s.group_id = wg.group_id
JOIN sys.dm_resource_governor_resource_pools rp ON wg.pool_id = rp.pool_id
WHERE s.session_id >= 50

dvv资源调控器有很多有趣的信息 — dmv resource governor

, "" . Object Explorer default.

- . perfmon : SQLServer: Workload Group Stats SQL Server: Resource Pool Stats. , SQL Server.

p1g1 , , , , 8 i5-8250u,

USE StackOverflow2013;
GO

SELECT SUM (CAST (VoteTypeId AS decimal(12,2))) / AVG (CAST (VoteTypeId AS decimal(14,4)))
FROM dbo.Votes
OPTION (MAXDOP 8);
GO

, perfmon SQLServer: Workload Group Stats: CPU Usage% CPU Delayed % pool1_group1:

CPU Usage% CAP_CPU_PERCENT = 20, Resource Governor CPU , 2 , 8, - , , . CPU Delayed %, , 5% , Resource Governor .

, p2g3, , (IMPORTANCE = LOW) .

, , 100% CPU. CPU Delayed - 0,483%, - Internal pool, CPU. , , - (pool2), , ?

p2g1 (IMPORTANCE = HIGH) p2g3 (IMPORTANCE = LOW):

USE StackOverflow2013;
GO

SELECT SUM (CAST (VoteTypeId AS decimal(12,2))) / AVG (CAST (VoteTypeId AS decimal(14,4))) * AVG (CAST (PostId AS decimal(19,4)))
FROM dbo.Votes
OPTION (MAXDOP 8);

GO

. , CPU, - . , , 10% CPU, , , 10% CPU, , 100%.

, 2, . CPU Usage .

, - :

, 100%. - 70-75%, 25-30%. , 65-70% , 22,5-25%, 7.5-10% .

, , - . , !

, , . : p1g1 1 p2g1 p2g3 2, . -, CPU , - CPU , , , CPU . , "" (IMPORTANCE) - , "" , .

top-不同池的CPU使用率； bottom-第二个池中的CPU使用率 — - CPU ; - CPU

- CPU (SQL Server: Resource Pool Stats: CPU Usage %). p1g1, CPU. - 20%, , .

, MAX_CPU_PERCENT = 15, MAX_CPU_PERCENT = 90. , , 100%, , Resource Governor , . = 15%, - 50%. 15%, 85%.

- . 85%, , , 75% . , CPU , 5% .

, - , , , , . p2g3 :

SELECT 
    s.session_id,
    s.status,
    r.task_address,
    r.scheduler_id
FROM sys.dm_exec_sessions s 
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.login_name = N'p2g3';

running, (request) (). , , ().

IO. Resource Governor, :

USE [master];
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);  
ALTER RESOURCE GOVERNOR DISABLE; 

DROP WORKLOAD GROUP [pool1_group1];
DROP WORKLOAD GROUP [pool2_group1];
DROP WORKLOAD GROUP [pool2_group2];
DROP WORKLOAD GROUP [pool2_group3];

DROP RESOURCE POOL [pool1];
DROP RESOURCE POOL [pool2];

IO . , - , . IOPS , , Resource Governor .

- : /, -, , ( ):

USE [StackOverflow2013]
GO

DBCC DROPCLEANBUFFERS;
GO

SELECT *
FROM dbo.Posts;    -- 1
SELECT *
FROM dbo.Comments; -- 2
SELECT *
FROM dbo.Votes;    -- 3

, Enterprise (, , Developer) Edition "shared scan" . .

, SQL Server 75 IOPS ( , , ). , , , Resource Governor.

USE [master];
GO

CREATE RESOURCE POOL [pool1]
WITH (
    MIN_IOPS_PER_VOLUME = 50
);

CREATE RESOURCE POOL [pool2]
WITH (
    MIN_IOPS_PER_VOLUME = 30,
    MAX_IOPS_PER_VOLUME = 50
);
GO

CREATE WORKLOAD GROUP [pool1_group1]
USING [pool1];

CREATE WORKLOAD GROUP [pool2_group1]
USING [pool2];

ALTER FUNCTION fnClassify()
RETURNS SYSNAME 
WITH SCHEMABINDING
AS
BEGIN
    RETURN 
        CASE ORIGINAL_LOGIN() 
            WHEN 'p1g1' THEN 'pool1_group1'
            WHEN 'p2g1' THEN 'pool2_group1'
        ELSE 'default' END;
END;
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnClassify);  
ALTER RESOURCE GOVERNOR RECONFIGURE;

, , IO, , , - Disk Read IO/sec Disk Read IO Throttled/sec SQL Server: Resource Pool Stats. , SQL Server: Workload Group Stats , IO, , , "" IO.

, HDD, ( Latency), , .

, Read IOPS, , 2 Resource Governor "" 50, IOPS. , , , , - 50 30 . , , - , IO "", Resource Governor , "" IO , .

, , Resource Governor SQL Server 2008 . , , , SQL Server 2014, - .

, - Resource Governor . , , , .

CPU, Resource Governor , CPU - . 100% - , Governor' . , CAP_PERCENT, , , , .

, , , , , . , . , , , , - , , , .

在IO的情况下，它可能会有所帮助，但是所有事情都需要非常仔细地计算，因为我们不是按百分比进行运算，而是直接根据运算数量进行运算，甚至不划分为读写。此外，我们指定了相同数量的操作，该操作一次应用于所有卷，并且如果连接了具有不同“带宽”的阵列/磁盘，则可以大大减少此类IO限制的使用。

注意不要忘记DAC。

补充阅读：

我们应该怎样设置资源调控器

More articles: