Clever Geek Handbook

Apa yang harus kita siapkan Gubernur Sumber Daya

Mungkin, semua orang yang bekerja dengan SQL Server menemukan dirinya, atau masih akan menemukan dirinya dalam situasi seperti ini: Jumat malam, Anda sudah siap secara mental untuk beristirahat dan kemudian pemberitahuan beban tinggi pada CPU SQL Server Anda mulai berdatangan, telepon mulai berdering dan dalam sistem pemantauan yang Anda lihat gambar seperti pada KDPV.

Dan, di satu sisi, ini mungkin bukan masalah khusus - ya, ini sulit untuk server, ya, itu tidak menyenangkan, tetapi bagaimanapun, Tuhan, laporan Anda akan dibuat bukan dalam 15, tetapi dalam 45 detik - bisnis, Anda tahu - Ada banyak dari Anda, tetapi hanya ada satu server, Anda hanya perlu menunggu sebentar. Namun bagaimana jika dengan semua itu, ada beberapa proses bisnis yang tidak bisa menunggu? Bagaimana jika, di bawah beban seperti itu, penjualan suatu produk menjadi sangat lambat sehingga pembeli menolak untuk membeli?

Akan sangat bagus jika Anda dapat memisahkan proses / pengguna dan mengatakan SQL Server - ini adalah orang yang sangat penting, kueri mereka harus dieksekusi terlebih dahulu. Tapi ini - mereka, tentu saja, juga penting, tapi bisa menunggu lebih lama. Tetapi ini - secara umum, ini adalah permintaan dari sistem pemantauan, yang telah lama perlu ditulis ulang, dan Anda biasanya dapat mengabaikannya saat kami melakukan hal-hal penting di sini.

Dan terkadang Anda benar-benar dapat memisahkannya - dengan bantuan Resource Governor.

Beberapa catatan sekaligus:

  1. Resource Governor hanya tersedia di Edisi Perusahaan. Jika Anda memiliki edisi lain (yah, juga Pengembang, tetapi tidak diproduksi untuk Anda, bukan?) - sayangnya, Anda tidak dapat menggunakannya.

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

?

( ) paint draw.io.

Saya melakukan yang terbaik

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

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
Gubernur sumber daya dmv memiliki banyak informasi menarik
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) - , "" , .

atas - penggunaan CPU oleh pool yang berbeda; bawah - Penggunaan CPU di dalam pool kedua
- 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.

Tuhan tahu aku berusaha mendapatkan jadwal yang seimbang di suatu tempat
, -

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

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

Dalam kasus IO, ini mungkin dapat membantu, tetapi semuanya perlu dihitung dengan sangat hati-hati, karena kami beroperasi tidak dengan persentase, tetapi langsung dengan jumlah operasi, dan bahkan tanpa membaginya menjadi baca dan tulis. Selain itu, kami menetapkan jumlah operasi yang sama, yang diterapkan ke semua volume sekaligus, dan jika array / disk dengan "bandwidth" berbeda dihubungkan, penggunaan pembatasan IO seperti itu akan berkurang tajam.

Berhati-hatilah untuk tidak melupakan DAC .

Bacaan tambahan:

  1. MSDN tentang Resource Governor

  2. Roy Ernest: Gubernur Resoruce

  3. MSDN pada Fungsi Klasifikasi



More articles:


All Articles