...

понедельник, 5 октября 2020 г.

Что нам стоит Resource Governor настроить

Наверное, каждый, кто работает с SQL Server оказывался, или ещё окажется, в такой ситуации: вечер пятницы, ты уже мысленно готов отдыхать и тут начинают прилетать уведомления о высокой нагрузке на CPU твоего SQL Server, телефон начинает звонить и в системе мониторинга ты видишь картинку как на КДПВ.

И, с одной стороны, в этом может не быть особой проблемы - да, серверу тяжело, да, это неприятно, но ведь, господи, ну сформируется ваш отчёт не за 15, а за 45 секунд - делов-то, вы же видите - вас много, а сервер один, нужно просто немножко подождать. Но что, если при всём при этом, есть какие-то бизнес-процессы, которые не могут ждать? Что если при такой нагрузке продажа товара становится настолько медленной, что покупатели отказываются от покупки?

Вот было бы здорово, если бы можно было разделить процессы/пользователей и сказать SQL Server - вот это вот очень важные ребята, их запросы должны выполняться в первую очередь. А вот эти - они, конечно, тоже важные, но они могут подождать подольше. А вот это вот - это вообще запросы от системы мониторинга, которые давно уже надо переписать, и на них вообще можно не обращать внимания, пока мы тут занимаемся важными вещами.

И иногда их и правда можно разделить - с помощью Resource Governor.

Сразу несколько примечаний:

  1. Resource Governor доступен только в Enterprise Edition. Если у вас любая другая редакция (ну ещё Developer, но он же у вас не в проде, да?) - к сожалению, использовать его вы не сможете.

  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.

я старался
я старался

Если вы посмотрите в 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
в dmv resource governor есть ещё много интересной информации
в 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 разными пулами; снизу - использование CPU внутри второго пула
сверху - использование CPU разными пулами; снизу - использование CPU внутри второго пула

Верхний график - это использование CPU разными пулами (SQL Server: Resource Pool Stats: CPU Usage %). Сначала я запустил запрос от имени пользователя p1g1, привязанного к пулу с жёстко ограниченным CPU. Он сразу забрал свои максимально-максимально допустимые 20%, но как только подоспели запросы из второго пула, пришлось часть ресурсов отдать.

Напоминаю, что в первом пуле у нас стоит MAX_CPU_PERCENT = 15, а во втором пуле MAX_CPU_PERCENT = 90. Суммарно, получается, больше 100%, соответственно в дело вступают минимальные проценты. У первого пула минимум = 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.

Дополнительное чтиво:

  1. MSDN про Resource Governor

  2. Roy Ernest: Resoruce Governor

  3. MSDN про функции классификации

Let's block ads! (Why?)

на
Ярлыки: ,

Комментариев нет:

Отправить комментарий

Подписаться на: Комментарии к сообщению (Atom)