Probably, everyone who works with SQL Server found himself, or will still find himself in such a situation: Friday evening, you are already mentally ready to rest and then notifications of high load on the CPU of your SQL Server begin to arrive, the phone starts ringing and in the monitoring system you see picture as on KDPV.
And, on the one hand, this may not be a particular problem - yes, it's hard for the server, yes, it's unpleasant, but after all, Lord, well, your report will be formed not in 15, but in 45 seconds - business, you see - There are many of you, but there is only one server, you just need to wait a little. But what if, with all this, there are some business processes that cannot wait? What if, under such a load, the sale of a product becomes so slow that buyers refuse to buy?
It would be great if you could separate processes / users and say SQL Server - these are very important guys, their queries should be executed first. But these - they, of course, are also important, but they can wait a little longer. But this one is, in general, requests from the monitoring system, which have long been necessary to rewrite, and you can ignore them at all while we are doing important things here.
And sometimes they can really be separated - with the help of Resource Governor.
Several notes at once:
Resource Governor is only available in Enterprise Edition. If you have any other edition (well, also Developer, but it's not in production for you, right?) - unfortunately, you cannot use it.
, , , , , .
, 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
, "" . 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, , , , .
, , , , , . , . , , , , - , , , .
In the case of IO, it probably can help, but everything needs to be calculated very carefully, since we operate not with percentages, but directly with the number of operations, and even without dividing into read and write. In addition, we specify the same number of operations, which is applied to all volumes at once, and if arrays / disks with different "bandwidth" are connected, the use of such an IO limitation is sharply reduced.
Be careful not to forget about the DAC .
Additional reading: