Как использовать регулятор ресурсов для ограничения загрузки ЦП при сжатии резервной копии (компонент Transact-SQL)

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

Важно!
В данном сценарии регулятора ресурсов классификация сеансов может основываться на имени пользователя, названии приложения или каком-либо другом критерии различения соединения. Дополнительные сведения см. в разделах Моменты, которые следует учитывать при создании функции-классификатора и Сценарии управления рабочей нагрузкой регулятора ресурсов.

Этот раздел содержит следующий набор сценариев, представленных в следующей последовательности.

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

2. Настройка регулятора ресурсов для ограничения загрузки ЦП

3. Проверка классификации текущего сеанса (Transact-SQL)

4. Сжатие резервных копий в сеансе с ограничением доступа к ЦП

Создание учетной записи и пользователя для операций с низким приоритетом

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

Следующая процедура описывает этапы настройки имени входа и пользователя для этой цели, за которыми следует пример на языке Transact-SQL — «Пример А. Настройка имени входа и пользователя (Transact-SQL)».

Настройка имени входа и пользователя базы данных для классификации сеансов

1. Создайте имя входа на SQL Server для создания низкоприоритетных сжатых резервных копий. 

   Создание имени входа

   • Как создать имя входа SQL Server

   • CREATE LOGIN (Transact-SQL)

2. При необходимости можно также предоставить этому имени входа разрешение VIEW SERVER STATE.

   • GRANT, предоставление разрешения на системный объект (Transact-SQL)

   Дополнительные сведения см. в разделе Разрешения предопределенных ролей сервера (компонент Database Engine).

3. Создайте пользователя SQL Server для данного имени входа.

   Создание пользователя

   • Как создать пользователя базы данных

   • CREATE USER (Transact-SQL)

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

   Добавление пользователя к предопределенной роли базы данных

   • Хранимая процедура sp_addrolemember (Transact-SQL)

   Дополнительные сведения см. в разделе Разрешения предопределенных ролей базы данных (компонент Database Engine).

Пример А. Настройка имени входа и пользователя (Transact-SQL)

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

Важно!
В следующем примере используется образец имени входа и пользователя — domain_name\MAX_CPU. Замените их именами входа и пользователя SQL Server, которые планируется использовать при создании низкоприоритетных сжатых резервных копий.

В этом примере создается имя входа для учетной записи Windows domain_name\MAX_CPU, а затем этому имени предоставляется разрешение VIEW SERVER STATE. Это разрешение позволяет проверить классификацию сеансов имени входа в регуляторе ресурсов. Затем в примере создается пользователь для имя_домена\MAX_CPU, который добавляется в предопределенную роль базы данных db_backupoperator в образце базы данных AdventureWorks. Это имя пользователя будет использоваться функцией-классификатором в регуляторе ресурсов.

-- Create a SQL Server login for low-priority operations
USE master;
CREATE LOGIN [domain_name\MAX_CPU] FROM WINDOWS;
GRANT VIEW SERVER STATE TO [domain_name\MAX_CPU];
GO
-- Create a SQL Server user in AdventureWorks for this login
USE AdventureWorks;
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';
GO

[В начало]

Настройка регулятора ресурсов для ограничения загрузки ЦП

Примечание
Убедитесь, что регулятор ресурсов включен. Дополнительные сведения см. в разделе Как включить или отключить регулятор ресурсов.

В этом сценарии регулятора ресурсов процесс настройки состоит из следующих основных этапов.

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

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

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

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

5. Примените изменения в хранящейся в памяти конфигурации регулятора ресурсов.

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

Инструкции языка Transact-SQL для указанных этапов описаны в процедуре «Настройка регулятора ресурсов для ограничения загрузки ЦП», за которой следует пример процедуры на языке Transact-SQL.

Настройка регулятора ресурсов (среда SQL Server Management Studio)

• Как настроить регулятор ресурсов с использованием шаблона

• Как создать пул ресурсов

• Как создать группу рабочей нагрузки

Настройка регулятора ресурсов для ограничения загрузки ЦП (Transact-SQL)

1. Выполните инструкцию CREATE RESOURCE POOL, чтобы создать пул ресурсов. В примере этой процедуры используется следующий синтаксис:

   CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value );

   Value — целое число от 1 до 100, которое указывает максимальную среднюю пропускную способность ЦП в процентах. Рекомендуемое значение зависит от конкретной среды. Для иллюстрации в примере из этого разделе используется значение 20% (MAX_CPU_PERCENT = 20).

2. Выполните инструкцию CREATE WORKLOAD GROUP, чтобы создать группу рабочей нагрузки для низкоприоритетных операций, загрузку ЦП которыми следует регулировать. В примере этой процедуры используется следующий синтаксис:

   CREATE WORKLOAD GROUP group_name USING pool_name;

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

   CREATE FUNCTION [schema_name.]function_name() RETURNS sysname

   WITH SCHEMABINDING

   AS.

   BEGIN

   DECLARE @workload\_group\_name AS sysname

   IF (SUSER_NAME() = 'user_of_low_priority_login')

   SET @workload\_group\_name = 'workload_group_name'

   RETURN @workload\_group\_name

   END

   Дополнительные сведения о компонентах этой инструкции CREATE FUNCTION см. в разделах:

   • DECLARE @local\_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Важно!
     SUSER_NAME — лишь одна из нескольких системных функций, которые можно использовать в функции-классификаторе. Дополнительные сведения см. в разделе Моменты, которые следует учитывать при создании функции-классификатора.

   • SET @local\_variable (Transact-SQL).

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

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

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

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

Пример Б. Настройка регулятора ресурсов (Transact-SQL)

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

1. Создание пула ресурсов pMAX_CPU_PERCENT_20.

2. Создание группы рабочей нагрузки gMAX_CPU_PERCENT_20.

3. Создание функции-классификатора rgclassifier_MAX_CPU(), в которой используется имя пользователя, созданное в предыдущем примере.

4. Регистрация функции-классификатора в регуляторе ресурсов.

После того как транзакция зафиксирована, применяются изменения в конфигурации, запрошенные в инструкциях ALTER WORKLOAD GROUP или ALTER RESOURCE POOL.

Важно!
В следующем примере используется образец имени пользователя SQL Server, созданный в «Примере А. Настройка имени входа и пользователя (Transact-SQL)», — domain_name\MAX_CPU. Замените его именем пользователя, соответствующего имени входа, которое планируется использовать для создания низкоприоритетных сжатых резервных копий.

-- Configure Resource Governor.
BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%. 
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20
   WITH
      (MAX_CPU_PERCENT = 20);
GO
-- Create a workload group to use this pool. 
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20
USING pMAX_CPU_PERCENT_20;
GO
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'Default' group.
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workload_group_name AS sysname
      IF (SUSER_NAME() = 'domain_name\MAX_CPU')
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'
    RETURN @workload_group_name
END;
GO

-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);
COMMIT TRAN;
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

[В начало]

Проверка классификации текущего сеанса (Transact-SQL)

При необходимости войдите в систему как пользователь, указанный в функции-классификаторе, и проверьте классификацию сеанса, выполнив следующую инструкцию SELECT в обозревателе объектов:

USE master;
SELECT sess.session_id, sess.login_name, sess.group_id, grps.name 
FROM sys.dm_exec_sessions AS sess 
JOIN sys.dm_resource_governor_workload_groups AS grps 
    ON sess.group_id = grps.group_id
WHERE session_id > 50;
GO

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

Примечание
Дополнительные сведения о динамических административных представлениях, вызываемых этой инструкцией SELECT, см. в разделах sys.dm_exec_sessions (Transact-SQL) и sys.dm_resource_governor_workload_groups (Transact-SQL).

[В начало]

Сжатие резервных копий в сеансе с ограничением доступа к ЦП

Чтобы создать сжатую резервную копию в сеансе с ограниченной максимальной загрузкой ЦП, войдите в систему как пользователь, указанный в функции-классификаторе. В команде резервного копирования укажите предложение WITH COMPRESSION (Transact-SQL) или выберите вариант Сжимать резервные копии (SQL Server Management Studio).

Создание сжатой резервной копии базы данных

• Как создать полную резервную копию базы данных (Transact-SQL)

• Как создать резервную копию базы данных (среда SQL Server Management Studio)

Пример В. Создание сжатой резервной копии (Transact-SQL)

В следующем примере BACKUP создается сжатая полная резервная копия базы данных AdventureWorks в чистом форматированном файле резервной копии Z:\SQLServerBackups\AdvWorksData.bak.

--Run backup statement in the gBackup session.
BACKUP DATABASE AdventureWorks TO DISK='Z:\SQLServerBackups\AdvWorksData.bak' 
WITH 
   FORMAT, 
   MEDIADESCRIPTION='AdventureWorks Compressed Data Backups'
   DESCRIPTION='First database backup on AdventureWorks Compressed Data Backups media set'
   COMPRESSION;
GO

[В начало]

См. также

Задания

Как создать и проверить определяемую пользователем функцию-классификатор (Transact-SQL)

Основные понятия

Знакомство с регулятором ресурсов

Основные понятия регулятора ресурсов

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

Диагностика проблем регулятора ресурсов

Другие ресурсы

Управление рабочей нагрузкой и ресурсами SQL Server

Инструкции по работе с регулятором ресурсов