👶 🈹 ⚛️ ¿Qué deberíamos configurar Resource Governor? 🛸 🕵🏽 👏🏻

Probablemente, todos los que trabajan con SQL Server se encuentran, o aún se encontrarán en tal situación: el viernes por la noche, ya está mentalmente listo para descansar y luego comienzan a llegar notificaciones de alta carga en la CPU de su SQL Server, el teléfono comienza a sonar y en el sistema de monitoreo que ve imagen como en KDPV.

Y, por un lado, esto puede no ser un problema en particular; sí, es difícil para el servidor, sí, es desagradable, pero después de todo, Señor, bueno, su informe no se formará en 15, sino en 45 segundos, negocios, ya ve. Hay muchos de ustedes, pero solo hay un servidor, solo necesitan esperar un poco. Pero, ¿y si, con todo esto, hay algunos procesos de negocio que no pueden esperar? ¿Qué pasa si, bajo tal carga, la venta de un producto se vuelve tan lenta que los compradores se niegan a comprar?

Sería genial si pudiera separar procesos / usuarios y decir SQL Server: estos son tipos muy importantes, sus consultas deben ejecutarse primero. Pero estos ... ellos, por supuesto, también son importantes, pero pueden esperar un poco más. Pero, en general, se trata de solicitudes del sistema de supervisión, que durante mucho tiempo ha sido necesario reescribir, y puede ignorarlas mientras hacemos cosas importantes aquí.

Y a veces realmente puede separarlos, con la ayuda de Resource Governor.

Varias notas a la vez:

Resource Governor solo está disponible en Enterprise Edition. Si tiene alguna otra edición (bueno, también Developer, pero no está en producción para usted, ¿verdad?), Desafortunadamente, no puede usarla.
, , , , , .
, 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

El gobernador de recursos dmv tiene mucha información interesante — 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) - , "" , .

arriba - uso de CPU por diferentes grupos; bottom: uso de CPU dentro del segundo grupo — - 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.

Dios sabe que estaba tratando de conseguir un horario uniforme en alguna parte — , -

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

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

En el caso de IO probablemente pueda ayudar, pero hay que calcular todo con mucho cuidado, ya que no operamos con porcentajes, sino directamente con el número de operaciones, e incluso sin dividir en lectura y escritura. Además, especificamos el mismo número de operaciones, que se aplica a todos los volúmenes a la vez, y si se conectan matrices / discos con diferente "ancho de banda", el uso de dicha limitación de E / S se reduce drásticamente.

Tenga cuidado de no olvidarse del DAC .

Lectura adicional:

¿Qué deberíamos configurar Resource Governor?

More articles: