![](https://habrastorage.org/getpro/habr/upload_files/171/9fd/b20/1719fdb204776a20d13673f17c600faa.png)
Наверное, каждый, кто работает с 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.
![я старался я старался](https://habrastorage.org/getpro/habr/upload_files/d28/2b6/dd3/d282b6dd35a6a05edfcfde1aa85acad7.png)
Если вы посмотрите в 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
![в dmv resource governor есть ещё много интересной информации в dmv resource governor есть ещё много интересной информации](https://habrastorage.org/getpro/habr/upload_files/6e8/5a6/1a8/6e85a61a80150b6a4f904860da84e33a.png)
Мы видим, что все новые пользователи благополучно "расползлись" по назначенным группам. 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:
![](https://habrastorage.org/getpro/habr/upload_files/c14/539/ed9/c14539ed92fe2524357727ce8021bcc2.png)
CPU Usage% упёрся в CAP_CPU_PERCENT = 20, Resource Governor не даёт использовать больше CPU моему запросу, а сам запрос может использовать только 2 ядра, вместо положенных 8, из-за того, что при создании группы нагрузки, я ограничил запросы из группы именно этим значением. CPU Delayed %, в свою очередь показывает, что пул хотел бы получить в своё распоряжение ещё около 5% процессорного времени, но Resource Governor строго его ограничил.
Посмотрим тот же запрос и те же счётчики, но выполненный от имени p2g3, пользователя, который входит в группу с наименьшим приоритетом (IMPORTANCE = LOW) во втором пуле.
![](https://habrastorage.org/getpro/habr/upload_files/359/3de/b7f/3593deb7fda1172ae767665bc4038852.png)
Поскольку это был единственный запрос, который выполнялся в этот момент, запросу ничего не мешало использовать все ядра и все 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
![](https://habrastorage.org/getpro/habr/upload_files/768/576/0c6/7685760c6043a7aeb7911bfe61730a85.png)
Тут картинка становится интереснее. Пока запрос был один, запрос из группы с высоким приоритетом забирал весь доступный CPU, но как только появился запрос из группы с низким приоритетом - пришлось делиться. От щедрот, как видно на графике, было выдано 10% CPU, соответственно, пока запросы выполнялись параллельно, запрос из группы с низким приоритетом мог использовать только 10% CPU, и только когда первый запрос завершился, он смог использовать все 100%.
Теперь я параллельно запущу тот же самый запрос из всех трёх групп, привязанных к пулу 2, чтобы посмотреть как будет разруливаться такая ситуация с приоритетами. На графике оставлю только CPU Usage разных групп.
Сначала запрос был запущен от имени пользователя с низким приоритетом, потом со средним и в конце - с высоким:
![](https://habrastorage.org/getpro/habr/upload_files/d97/3a7/b8e/d973a7b8ebf30f9af4c7e48b7034f1a1.png)
Видно, что сначала группе с низким приоритетом было доступно 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 внутри второго пула](https://habrastorage.org/getpro/habr/upload_files/f13/0a0/33c/f130a033c834222c412a8c7a45c1807c.png)
Верхний график - это использование 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';
![](https://habrastorage.org/getpro/habr/upload_files/aed/5dd/832/aed5dd83207af208603cab449a56c326.png)
Обе сессии в состоянии 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" и результат может быть несколько некорректным. На графике ниже можно оценить с какой любовью и заботой подбирал жёсткий диск производитель моего ноута.
![](https://habrastorage.org/getpro/habr/upload_files/58b/f8d/6c0/58bf8d6c0219b5df3fae941fe8741e72.png)
Итак, в среднем 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.
![видит Бог, я пытался получить ровный график хоть где-то видит Бог, я пытался получить ровный график хоть где-то](https://habrastorage.org/getpro/habr/upload_files/091/ad8/5fd/091ad85fd129ab3262bdf1100c9b1a1b.png)
К сожалению, с моим 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.
Дополнительное чтиво: