Поделиться через

Учебное пособие: Примеры конфигурации регулятора ресурсов и передовые методы

  • Статья

применимо к:SQL ServerУправляемому экземпляру SQL Azure

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

В статье также приведены примеры запросов мониторинга регулятора ресурсов и список лучших практикрегулятора ресурсов .

Все примеры предполагают, что изначально регулятор ресурсов отключен и использует параметры по умолчанию, и что пользовательские пулы ресурсов, группы рабочей нагрузки и функции классификатора не существуют.

Заметка

Для управляемого экземпляра SQL Azure вы должны находиться в контексте базы данных master, чтобы изменить конфигурацию регулятора ресурсов.

Изменение группы по умолчанию

В этом примере используется регулятор ресурсов, чтобы ограничить максимальный размер предоставления памяти для всех запросов пользователей. Это достигается путем уменьшения настройки REQUEST_MAX_MEMORY_GRANT_PERCENT для группы рабочих нагрузок default с 25% по умолчанию до 10%. В этом примере не используется функция классификатора. Это означает, что обработка входа не затрагивается, и все сеансы пользователей продолжают классифицироваться в группе рабочих нагрузок default.

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

  1. Измените группу рабочей нагрузки по умолчанию.

    ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 10);

  2. Включите регулятор ресурсов, чтобы сделать конфигурацию эффективной.

    ALTER RESOURCE GOVERNOR RECONFIGURE;

  3. Проверьте новый параметр, включая новый максимальный размер предоставления памяти.

    SELECT group_id,
       wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       wg.request_max_memory_grant_percent_numeric AS request_max_memory_grant_percent,
       rp.max_memory_kb * wg.request_max_memory_grant_percent_numeric AS request_max_memory_grant_size_kb
FROM sys.resource_governor_workload_groups AS wg
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id;

  4. Чтобы вернуться к начальной конфигурации, выполните следующий сценарий:

    ALTER RESOURCE GOVERNOR DISABLE;
ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25);

Использование определяемой пользователем группы рабочей нагрузки

В этом примере используется регулятор ресурсов для обеспечения того, чтобы все запросы на сеансах с определенным именем приложения не выполнялись с степенью параллелизма (DOP) выше четырех. Это делается путем классификации сеансов в группу рабочей нагрузки с установленным значением MAX_DOP, равным 4.

Дополнительные сведения о настройке максимальной степени параллелизма см. в разделе Server configuration: max degree of parallelism.

  1. Создайте группу рабочей нагрузки, которая ограничивает DOP. Группа использует пул ресурсов default, так как мы хотим ограничить doP для определенного приложения, но не зарезервировать или ограничить ресурсы ЦП, памяти или ввода-вывода.

    CREATE WORKLOAD GROUP limit_dop
WITH (
     MAX_DOP = 4
     )
USING [default];

  2. Создайте функцию классификатора. Функция использует встроенную функцию APP_NAME() для определения имени приложения, указанного в строке подключения клиента. Если для имени приложения задано значение limited_dop_application, функция возвращает имя группы рабочей нагрузки, ограничивающей DOP. В противном случае функция возвращает default в качестве имени группы рабочей нагрузки.

    USE master;
GO

CREATE FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

IF APP_NAME() = N'limited_dop_application'
    SELECT @WorkloadGroupName = N'limit_dop';

RETURN @WorkloadGroupName;

END;
GO

  3. Измените конфигурацию регулятора ресурсов, чтобы сделать конфигурацию эффективной и включить регулятор ресурсов.

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

  4. Запрос к sys.resource_governor_configuration , чтобы проверить, включен ли диспетчер ресурсов и использует ли он созданную нами функцию классификации.

    SELECT OBJECT_SCHEMA_NAME(classifier_function_id) AS classifier_schema_name,
       OBJECT_NAME(classifier_function_id) AS classifier_object_name,
       is_enabled
FROM sys.resource_governor_configuration;

    classifier_schema_name      classifier_object_name      is_enabled
----------------------      ----------------------      ----------
dbo                         rg_classifier               1

  5. Убедитесь, что сеансы с определенным именем приложения классифицируются в группу рабочей нагрузки limit_dop, а другие сеансы продолжают классифицироваться в группе рабочей нагрузки default. Мы будем использовать запрос, использующий sys.dm_exec_sessions и sys.resource_governor_workload_groups системные представления для возврата имени приложения и имени группы рабочей нагрузки для текущего сеанса.

    1. В SQL Server Management Studio (SSMS) выберите Файл в главном меню, Создать, Запрос Движка Базы Данных.

    2. В диалоговом окне Connect to Database Engine укажите тот же экземпляр ядра СУБД, где вы создали группу рабочей нагрузки и функцию классификатора. Перейдите на вкладку Дополнительные параметры подключения и введите App=limited_dop_application. Это заставляет SSMS использовать limited_dop_application в качестве имени приложения при подключении к экземпляру.

    3. Выберите , затем Подключитесь с, чтобы открыть новое подключение.

    4. В том же окне запроса выполните следующий запрос:

      SELECT s.program_name AS application_name,
       wg.name AS workload_group_name,
       wg.max_dop
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id = @@SPID;

      Вы увидите следующие выходные данные, показывающие, что сеанс был классифицирован в группу рабочих нагрузок limit_dop с максимальным значением DOP, равным четырем:

      application_name            workload_group_name     max_dop
----------------            -------------------     -------
limited_dop_application     limit_dop               4

    5. Повторите описанные выше шаги, но не введите ничего в поле на вкладке Дополнительные параметры подключения. Выходные изменения, показывающие имя приложения SSMS по умолчанию и группу рабочих нагрузок default с значением по умолчанию 0 для максимального значения DOP.

      application_name                                    workload_group_name     max_dop
----------------                                    -------------------     -------
Microsoft SQL Server Management Studio - Query      default                 0

  6. Чтобы вернуться к начальной конфигурации этого примера, выполните следующий скрипт T-SQL:

    ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP FUNCTION IF EXISTS dbo.rg_classifier;
DROP WORKLOAD GROUP limit_dop;

Использование нескольких пулов ресурсов и групп рабочей нагрузки

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

  1. Создайте два пула ресурсов для пикового времени и внерабочего времени обработки.

    • Пул peak_hours_pool гарантирует зарезервированный минимум в 20% средней полосы пропускания процессора через MIN_CPU_PERCENT, и не ограничивает пропускную способность ЦП, задав MAX_CPU_PERCENT значение 100.
    • Пул off_hours_pool не резервирует пропускную способность ЦП, устанавливая MIN_CPU_PERCENT на 0, но ограничивает пропускную способность ЦП до 50% при возникновении конфликтов ЦП, устанавливая MAX_CPU_PERCENT на 50.
    CREATE RESOURCE POOL peak_hours_pool
WITH (
     MIN_CPU_PERCENT = 20,
     MAX_CPU_PERCENT = 100
     );

CREATE RESOURCE POOL off_hours_pool
WITH (
     MIN_CPU_PERCENT = 0,
     MAX_CPU_PERCENT = 50
     );

    Пулы ресурсов могут резервировать и ограничивать системные ресурсы, такие как ЦП, память и ввод-вывод. Дополнительные сведения см. в разделе CREATE RESOURCE POOL.

  2. Создайте две группы рабочей нагрузки, по одному для каждого пула ресурсов соответственно.

    • peak_hours_group не ограничивает количество одновременных запросов, установив с помощью GROUP_MAX_REQUESTS значение по умолчанию 0.
    • off_hours_group ограничивает количество одновременных запросов во всех сеансах, классифицируемых в этой группе, установив для GROUP_MAX_REQUESTS значение 200.
    CREATE WORKLOAD GROUP peak_hours_group
WITH (
     GROUP_MAX_REQUESTS = 0
     )
USING peak_hours_pool;

CREATE WORKLOAD GROUP off_hours_group
WITH (
     GROUP_MAX_REQUESTS = 200
     )
USING off_hours_pool;

    Группы рабочих нагрузок определяют такие политики, как максимальное количество запросов, максимальный уровень параллелизма и максимальный размер предоставления памяти. Дополнительные сведения см. в разделе CREATE WORKLOAD GROUP.

  3. Создайте и заполните таблицу, которая определяет пиковые и нерабочие интервалы времени.

    • Каждая строка в таблице определяет время начала и окончания интервала, а также имя группы рабочей нагрузки, используемой в течение интервала.
    • Время начала и окончания каждого интервала учитывается полностью.
    • Таблица создается в базе данных master, чтобы ее можно было использовать в функции классификатора с привязкой к схеме.
    USE master;
GO

CREATE TABLE dbo.workload_interval
(
workload_group_name sysname NOT NULL,
start_time time(7) NOT NULL,
end_time time(7) NOT NULL,
CONSTRAINT pk_workload_interval PRIMARY KEY (start_time, workload_group_name),
CONSTRAINT ak_workload_interval_1 UNIQUE (end_time, workload_group_name),
CONSTRAINT ck_workload_interval_1 CHECK (start_time < end_time)
);
GO

INSERT INTO dbo.workload_interval
VALUES (N'off_hours_group', '00:00', '06:29:59.9999999'),
       (N'peak_hours_group', '06:30', '18:29:59.9999999'),
       (N'off_hours_group', '18:30', '23:59:59.9999999');

  4. Создайте функцию классификатора.

    • Ожидается, что данные в таблице имеют одну соответствующую строку для любого заданного времени дня. Если данные нарушают это правило, функция возвращает default в качестве имени группы рабочей нагрузки.
    • В следующем примере функция также возвращает default, если имя приложения, возвращаемое встроенной функцией APP_NAME(), является что-либо, отличное от order_processing.
    USE master;
GO

CREATE OR ALTER FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

SELECT @WorkloadGroupName = workload_group_name
FROM dbo.workload_interval
WHERE APP_NAME() = N'order_processing'
      AND
      CAST(GETDATE() AS time(7)) BETWEEN start_time AND end_time;

IF @@ROWCOUNT > 1
    SELECT @WorkloadGroupName = N'default';

RETURN @WorkloadGroupName;

END;
GO

  5. Это необязательный шаг. Вместо создания таблицы в базе данных master можно использовать табличное значение конструктора для определения интервалов времени непосредственно в функции классификатора. Это рекомендуемый подход, если размер данных мал, и критерии функции классификатора часто не изменяются. Ниже приведен пример того же классификатора, который использует конструктор с табличным значением вместо таблицы в master.

    USE master;
GO

CREATE OR ALTER FUNCTION dbo.rg_classifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroupName sysname = N'default';

SELECT @WorkloadGroupName = workload_group_name
FROM (
     VALUES (CAST(N'off_hours_group' AS sysname),  CAST('00:00' AS time(7)), CAST('06:29:59.9999999' AS time(7))),
            (CAST(N'peak_hours_group' AS sysname), CAST('06:30' AS time(7)), CAST('18:29:59.9999999' AS time(7))),
            (CAST(N'off_hours_group' AS sysname),  CAST('18:30' AS time(7)), CAST('23:59:59.9999999'AS time(7)))
     ) AS wg (workload_group_name, start_time, end_time)
WHERE APP_NAME() = N'order_processing'
      AND
      CAST(GETDATE() AS time(7)) BETWEEN start_time AND end_time;

IF @@ROWCOUNT > 1
    SELECT @WorkloadGroupName = N'default';

RETURN @WorkloadGroupName;

END;
GO

  6. Измените конфигурацию регулятора ресурсов, чтобы сделать конфигурацию эффективной и включить регулятор ресурсов.

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

  7. Убедитесь, что регулятор ресурсов включен, использует указанную функцию классификатора, и что функция классификатора работает должным образом, используя аналогичные шаги, как в предыдущем примере. На этот раз мы введем App=order_processing на вкладке Дополнительные параметры подключения в диалоговом окне подключения SSMS, чтобы соответствовать имени приложения в функции классификатора. Выполните следующий запрос, чтобы определить имя приложения, группу рабочей нагрузки, пул ресурсов и резервирование ЦП и ограничение текущего сеанса:

    SELECT s.program_name AS application_name,
       wg.name AS workload_group_name,
       wg.group_max_requests,
       rp.name AS resource_pool_name,
       rp.min_cpu_percent,
       rp.max_cpu_percent
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
INNER JOIN sys.resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
WHERE s.session_id = @@SPID;

    Результаты зависят от времени дня. Например, если текущее время равно 14:30, результат показывает, что используются peak_hours_group и peak_hours_pool:

    application_name    workload_group_name     group_max_requests      resource_pool_name      min_cpu_percent     max_cpu_percent
-----------------   --------------------    ------------------      -------------------     ---------------     ---------------
order_processing    peak_hours_group        0                       peak_hours_pool         20                  100

  8. Чтобы вернуться к начальной конфигурации, выполните следующий скрипт T-SQL:

    ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP FUNCTION IF EXISTS dbo.rg_classifier;
DROP TABLE IF EXISTS dbo.workload_interval;
DROP WORKLOAD GROUP peak_hours_group;
DROP WORKLOAD GROUP off_hours_group;
DROP RESOURCE POOL peak_hours_pool;
DROP RESOURCE POOL off_hours_pool;

Мониторинг регулятора ресурсов с помощью системных представлений

Примеры запросов в этом разделе показывают, как отслеживать статистику и поведение диспетчера ресурсов.

Статистика регулятора ресурсов является накопительной с момента последнего перезапуска сервера. Если вам нужно собрать статистику, начиная с определенного времени, можно сбросить статистику с помощью инструкции ALTER RESOURCE GOVERNOR RESET STATISTICS.

Статистика времени выполнения пула ресурсов

Для каждого пула ресурсов регулятор ресурсов отслеживает использование ЦП и памяти, события вне памяти, предоставление памяти, ввод-вывод и другую статистику. Дополнительную информацию см. в разделе sys.dm_resource_governor_resource_pools.

Следующий запрос возвращает подмножество доступной статистики для всех пулов ресурсов:

SELECT rp.pool_id,
       rp.name AS resource_pool_name,
       wg.workload_group_count,
       rp.statistics_start_time,
       rp.total_cpu_usage_ms,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count,
       rp.active_memgrant_count,
       rp.total_memgrant_count,
       rp.total_memgrant_timeout_count,
       rp.read_io_completed_total,
       rp.write_io_completed_total,
       rp.read_bytes_total,
       rp.write_bytes_total,
       rp.read_io_stall_total_ms,
       rp.write_io_stall_total_ms
FROM sys.dm_resource_governor_resource_pools AS rp
OUTER APPLY (
            SELECT COUNT(1) AS workload_group_count
            FROM sys.dm_resource_governor_workload_groups AS wg
            WHERE wg.pool_id = rp.pool_id
            ) AS wg;

Статистика времени выполнения группы рабочей нагрузки

Для каждой группы рабочей нагрузки регулятор ресурсов отслеживает время ЦП, количество запросов, заблокированные задачи, время ожидания блокировки, оптимизацию запросов и другую статистику. Дополнительные сведения см. в sys.resource_governor_workload_groups.

Следующий запрос возвращает подмножество доступной статистики для всех групп рабочих нагрузок:

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       wg.statistics_start_time,
       wg.total_request_count,
       wg.total_cpu_usage_ms,
       wg.blocked_task_count,
       wg.total_lock_wait_time_ms,
       wg.total_query_optimization_count,
       wg.max_request_grant_memory_kb,
       wg.active_parallel_thread_count,
       wg.effective_max_dop,
       wg.request_max_memory_grant_percent_numeric
FROM sys.dm_resource_governor_workload_groups AS wg
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id

Агрегирование сеансов по группам рабочей нагрузки и атрибутам сеанса

Следующий запрос возвращает распределение сеансов между группами рабочей нагрузки и статистической статистикой сеансов для каждой группы рабочей нагрузки.

Большое количество сеансов с состоянием preconnect может указывать на замедление выполнения классификатора.

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       s.program_name AS application_name,
       s.login_name,
       s.host_name,
       s.status,
       d.name AS database_name,
       MIN(s.login_time) AS first_login_time,
       MAX(s.login_time) AS last_login_time,
       MAX(s.last_request_start_time) AS last_request_start_time,
       COUNT(1) AS session_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
INNER JOIN sys.databases AS d
ON s.database_id = d.database_id
GROUP BY wg.name,
         rp.name,
         s.program_name,
         s.login_name,
         s.host_name,
         s.status,
         d.name;

Агрегирование запросов по группам рабочей нагрузки и атрибутам запроса

Следующий запрос возвращает распределение запросов между группами рабочей нагрузки и статистические статистические статистические данные запросов для каждой группы рабочей нагрузки:

SELECT wg.name AS workload_group_name,
       rp.name AS resource_pool_name,
       r.command,
       r.status,
       d.name AS database_name,
       COUNT(1) AS request_count,
       MIN(r.start_time) AS first_request_start_time,
       MAX(r.start_time) AS last_request_start_time,
       SUM(CAST(r.total_elapsed_time AS bigint)) AS total_elapsed_time_ms
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON r.group_id = wg.group_id
INNER JOIN sys.dm_resource_governor_resource_pools AS rp
ON wg.pool_id = rp.pool_id
INNER JOIN sys.databases AS d
ON r.database_id = d.database_id
GROUP BY wg.name,
         rp.name,
         r.command,
         r.status,
         d.name;

Рекомендации по управлению ресурсами

  • Настройте выделенное подключение администратора (DAC) и узнайте, как его использовать. Дополнительные сведения см. в разделе Диагностическое подключение для администраторов баз данных. Если конфигурация регулятора ресурсов не работает, можно использовать DAC для устранения неполадок или отключитьрегулятора ресурсов.
  • При настройке пулов ресурсов следует тщательно указывать большие значения для MIN_CPU_PERCENT, MIN_MEMORY_PERCENTи MIN_IOPS_PER_VOLUME. Параметр конфигурации MIN резервирует ресурсы для пула ресурсов и делает их недоступными для других пулов ресурсов, включая пул default. Дополнительные сведения см. в статье Создание пула ресурсов.
  • Функция классификатора расширяет время обработки входа. Избегайте сложных логики и длительных или ресурсоемких запросов в классификаторе, особенно если запросы используют большие таблицы. Слишком сложная функция может привести к задержкам входа или истечению времени ожидания подключения.
  • Если необходимо использовать таблицу в классификаторе, а таблица является небольшой и в основном статической, рассмотрите возможность использования табличного конструктора, как показано в примере выше в этой статье.
  • Избегайте использования часто измененной таблицы в классификаторе. Это повышает риск блокировки, которая может отложить входы и вызвать время ожидания подключения. Следующие обходные пути могут снизить риск, однако они имеют недостатки, включая риск неправильной классификации:
    • Рекомендуется использовать подсказку таблицы NOLOCK или эквивалентную READUNCOMMITTED. Дополнительные сведения см. в READUNCOMMITTED.
    • Рекомендуется использовать параметр LOCK_TIMEOUT в начале функции классификатора, установив его на низкое значение, например 1000 миллисекундах. Дополнительные сведения см. в разделе SET LOCK_TIMEOUT.
  • Вы не можете изменить функцию классификатора, пока она используется в конфигурации регулятора ресурсов. Однако можно изменить конфигурацию, чтобы использовать другую функцию классификатора. Если вы хотите внести изменения в классификатор, попробуйте создать пару функций классификатора. Например, можно создать dbo.rg_classifier_A() и dbo.rg_classifier_B(). Если требуется изменить логику классификатора, выполните следующие действия.
    1. Используйте инструкцию ALTER FUNCTION ALTER FUNCTION, чтобы внести изменения в функции, не в настоящее время, используемой в конфигурации регулятора ресурсов.
    2. Используйте инструкцию ALTER RESOURCE GOVERNOR, чтобы активировать изменённый классификатор, а затем перенастройте регулятор ресурсов. Например: 
      ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier_B);
ALTER RESOURCE GOVERNOR RECONFIGURE;
    3. Если изменение необходимо еще раз, выполните те же действия, что и другая функция (dbo.rg_classifier_A()).
  • Конфигурация регулятора ресурсов хранится в базе данных master. Обязательно периодически создайте резервную копию masterи узнайте, как восстановить ее. Дополнительные сведения см. в разделе Резервное копирование и восстановление: системные базы данных. Так как существуют ограничения для восстановления master, рекомендуется также сохранить копию скриптов конфигурации регулятора ресурсов отдельно. Вы можете повторно создать конфигурацию регулятора ресурсов из скриптов, если необходимо перестроить базу данных master.

Связанное содержимое