O que devemos configurar o Administrador de Recursos

Provavelmente, todo mundo que trabalha com SQL Server se encontrou, ou ainda vai, em tal situação: sexta-feira à noite, você já está mentalmente pronto para descansar e então começam a chegar notificações de alta carga na CPU do seu SQL Server, o telefone começa a tocar e você vê no sistema de monitoramento imagem como no KDPV.

E, por um lado, isso pode não ser um problema particular - sim, é difícil para o servidor, sim, é desagradável, mas afinal, Senhor, bem, seu relatório será formado não em 15, mas em 45 segundos - negócios, entende - São muitos, mas só existe um servidor, é só esperar um pouco. Mas e se, com tudo isso, houver alguns processos de negócios que não podem esperar? E se, sob tal carga, a venda de um produto se tornar tão lenta que os compradores se recusem a comprar?

Seria ótimo se você pudesse separar processos / usuários e dizer ao SQL Server - esses são caras muito importantes, suas consultas devem ser executadas primeiro. Mas estes - eles, é claro, também são importantes, mas podem esperar um pouco mais. Mas este é, em geral, solicitações do sistema de monitoramento, que há muito tempo precisam ser reescritas, e você pode ignorá-las enquanto fazemos coisas importantes aqui.

E às vezes eles podem realmente ser separados - com a ajuda do Administrador de Recursos.

Várias notas de uma vez:

1. O Resource Governor está disponível apenas na Enterprise Edition. Se você tiver qualquer outra edição (bem, também Developer, mas não está em produção para você, certo?) - infelizmente, você não pode usá-la.

2. , , , , , .

3. , Resource Governor, , , , , ( ).

4. - , - , .

5. , , .

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

, Resource Governor :

1. (CPU, RAM, IOPS) - ( ), .

2. (workload group), , .

3. (, , ) .

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]  

:

1. MIN_CPU_PERCENT - 100%, , . : , "" CPU, , .

2. MAX_CPU_PERCENT - 100%, , , . : , , , .

3. CAP_CPU_PERCENT - . , , .

4. AFFINITY - (-) (, ), (-) NUMA-

5. MIN/MAX_MEMORY_PERCENT - ( , ) . - buffer pool, . memory grants.

6. 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" ] ]
    } ]
[ ; ]

:

1. IMPORTANCE - "" . , , , " ", . , " " , " " - " " .

2. REQUEST_MAX_MEMORY_GRANT_PERCENT - 25%, . , - .

3. REQUEST_MAX_CPU_TIME_SEC - , , , cpu_threshold_exceeded, Extended Events. CU SQL Server 2016/2017, trace-, , , CPU time .

4. REQUEST_MEMORY_GRANT_TIMEOUT_SEC - , , - .

5. MAX_DOP - . "", , , . 1, 4 - MAX DOP = 4.

6. GROUP_MAX_REQUESTS - . , , . .

, , - . , SQL Server ?

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

, (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();

, - 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

, "" . 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 (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, , , , .

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

No caso do IO, provavelmente pode ajudar, mas tudo precisa ser calculado com muito cuidado, já que operamos não com porcentagens, mas diretamente com o número de operações, e mesmo sem dividir em leitura e escrita. Além disso, especificamos o mesmo número de operações, que é aplicado a todos os volumes de uma vez, e se matrizes / discos com "largura de banda" diferente forem conectados, o uso de tal limitação de IO é drasticamente reduzido.

Tenha cuidado para não se esquecer do DAC .

Leitura adicional:

1. MSDN no Resource Governor

2. Roy Ernest: Governador do Resoruce

3. MSDN sobre funções de classificação