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

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

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

• Каковы требования ЦП?
  Определите пропускную способность ЦП, умножив среднюю общую загрузку ЦП для запроса на среднее количество запросов в секунду.

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

• Каково число параллельных запросов?
  Чтобы определить коэффициент минимальной параллельности, используйте статистику по максимальным и средним количествам параллельных запросов.

• Какой требуется общий объем памяти?
  Чтобы определить требования к памяти, используйте значения максимального и среднего использования памяти.

• Какой объем памяти необходим для отдельного запроса?
  Чтобы определить данные требования к памяти, используйте статистику по максимальному и среднему использованию памяти на запрос.

• Обнаружены ли запросы, выполнение которых невозможно из-за недостатка памяти?
  Чтобы определить наличие запросов, блокированных вследствие недостаточных объемов доступной памяти, используйте статистику по среднему времени ожидания запросов в очереди памяти.

В: Установлена новая версия SQL Server, и хотелось бы использовать регулятор ресурсов. Каким образом можно им воспользоваться в этом окружении?

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

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

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

3. Зарегистрируйте эту классифицирующую функцию в регуляторе ресурсов.

4. Включите регулятор ресурсов.

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

Примеры

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

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

В: По результатам мониторинга результатов сценария 1 хотелось бы видеть событие каждый раз, когда на выполнение запроса в нерегламентированной группе (GroupAdhoc) требуется более 30 секунд.

О. Выполните приведенные ниже шаги для изменения текущей конфигурации регулятора ресурсов. Представлен пример конфигурации.

1. Установите для нерегламентированной группы ограничение по загрузке ЦП.

2. Просмотрите события SQL Trace (события класса управления в регуляторе ресурсов).

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

Примеры

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

В: Необходимо дополнительно ограничить нерегламентированную группу, чтобы при накоплении всех запросов загрузка ЦП не превышала 50 процентов.

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

1. Создайте новый пул ресурсов и настройте ограничения ЦП.

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

Примеры

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

В: Необходимо, чтобы у группы admin всегда были ресурсы для выполнения диагностических запросов, но при этом запросы не должны занимать более 10 процентов ресурсов памяти сервера.

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

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

2. Настройте для группы рабочей нагрузки admin использование нового пула ресурсов.

Примеры

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

В: Поскольку отчеты содержатся в данный момент в пуле ресурсов по умолчанию, но с использованием отдельной группы рабочей нагрузки (GroupReports), необходимо убедиться в том, что у всех остальных элементов в группе ресурсов по умолчанию более низкий приоритет по использованию ресурсов, чем у отчетов.

О. Чтобы добиться этого, в группе по умолчанию необходимо настроить параметр IMPORTANCE.

Примечание
Изменить можно параметры группы по умолчанию, но не пула по умолчанию. Если возникла необходимость изменения пула по умолчанию, это признак того, что требуется новый пул ресурсов.

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

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

Примеры

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

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

О. Чтобы настроить новое окружение регулятора ресурсов, выполните следующие шаги. Представлен пример конфигурации.

1. Создайте новый пул ресурсов с параметрами по умолчанию.

2. Создайте новую группу рабочей нагрузки, использующую существующий пул.

3. Создайте и зарегистрируйте новую функцию-классификатор для обработки запросов.

Примеры

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

В: Группа рабочей нагрузки (созданная в сценарии 6) больше не требуется, поэтому нужно удалить группу рабочей нагрузки и пул ресурсов, в котором она находится.

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

1. Создайте и зарегистрируйте новую функцию-классификатор, которая будет классифицировать запросы в оставшейся группе рабочей нагрузки.

2. Удалите группу рабочей нагрузки.

3. Удалите пул ресурсов.

4. Примените изменения конфигурации.

Примеры

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO