Что нам стоит Resource Governor настроить
Наверное, каждый, кто работает с SQL Server оказывался, или ещё окажется, в такой ситуации: вечер пятницы, ты уже мысленно готов отдыхать и тут начинают прилетать уведомления о высокой нагрузке на CPU твоего SQL Server, телефон начинает звонить и в системе мониторинга ты видишь картинку как на КДПВ.
И, с одной стороны, в этом может не быть особой проблемы — да, серверу тяжело, да, это неприятно, но ведь, господи, ну сформируется ваш отчёт не за 15, а за 45 секунд — делов-то, вы же видите — вас много, а сервер один, нужно просто немножко подождать. Но что, если при всём при этом, есть какие-то бизнес-процессы, которые не могут ждать? Что если при такой нагрузке продажа товара становится настолько медленной, что покупатели отказываются от покупки?
Вот было бы здорово, если бы можно было разделить процессы/пользователей и сказать SQL Server — вот это вот очень важные ребята, их запросы должны выполняться в первую очередь. А вот эти — они, конечно, тоже важные, но они могут подождать подольше. А вот это вот — это вообще запросы от системы мониторинга, которые давно уже надо переписать, и на них вообще можно не обращать внимания, пока мы тут занимаемся важными вещами.
И иногда их и правда можно разделить — с помощью Resource Governor.
Сразу несколько примечаний:
Resource Governor доступен только в Enterprise Edition. Если у вас любая другая редакция (ну ещё Developer, но он же у вас не в проде, да?) — к сожалению, использовать его вы не сможете.
Тут мы не говорим о таких важных вещах, как оптимизация запросов, структур хранения данных, разнесении нагрузки на разные реплики, секционировании, шардировании.
Мы говорим только о том, что такое 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
| ( )
| NUMANODE = ( )
} ]
[ [ , ] MIN_MEMORY_PERCENT = value ]
[ [ , ] MAX_MEMORY_PERCENT = value ]
[ [ , ] MIN_IOPS_PER_VOLUME = value ]
[ [ , ] MAX_IOPS_PER_VOLUME = value ]
)
]
[;]
::=
{ SCHED_ID | SCHED_ID TO SCHED_ID }[,...n]
::=
{ 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
в 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 разными пулами (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.
Дополнительное чтиво:
MSDN про Resource Governor
Roy Ernest: Resoruce Governor
MSDN про функции классификации
