🌒 🕵🏼 🏴󠁧󠁢󠁳󠁣󠁴󠁿 リソースガバナーを何に設定すればよいですか 🦌 ⛳️ 🍓

おそらく、SQL Serverを使用するすべての人が、そのような状況に自分自身を見つけたか、それでもそうなるでしょう。金曜日の夜、あなたはすでに精神的に休む準備ができており、SQL ServerのCPUに高い負荷がかかっているという通知が届き始め、電話が鳴り始め、監視システムに表示されます。 KDPVのような写真。

そして、一方で、これは特に問題ではないかもしれません-はい、それはサーバーにとって難しいです、はい、それは不快です、しかし結局のところ、主よ、あなたのレポートは15秒ではなく45秒で形成されます-ビジネス、あなたが見る-多くの方がいらっしゃいますが、サーバーは1つなので、少し待つ必要があります。しかし、これだけで、待つことができないビジネスプロセスがあるとしたらどうでしょうか。このような負荷の下で、製品の販売が非常に遅くなり、購入者が購入を拒否した場合はどうなりますか？

プロセス/ユーザーを分離してSQLServerに伝えることができれば素晴らしいと思います-これらは非常に重要な人物であり、クエリを最初に実行する必要があります。しかし、これらはもちろん重要ですが、もう少し待つことができます。しかし、これは一般的に、書き直しに長い間必要とされてきた監視システムからの要求であり、ここで重要なことをしている間は、それらをまったく無視することができます。

また、リソースガバナーの助けを借りて、実際にそれらを分離できる場合もあります。

一度にいくつかのメモ：

Resource Governorは、EnterpriseEditionでのみ使用できます。他のエディションがある場合（まあ、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

dmvリソースガバナーには興味深い情報がたくさんあります — 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) - , "" , .

上-異なるプールによるCPU使用率。下-2番目のプール内の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: