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

Область применения: SQL Server

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

Внимание

В данном сценарии регулятора ресурсов классификация сеансов может основываться на имени пользователя, названии приложения или каком-либо другом критерии различения соединения. Дополнительные сведения см. в разделах Resource Governor Classifier Function и Resource Governor Workload Group.

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

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

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

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

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

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

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

В следующей процедуре описаны шаги по настройке имени входа и пользователя для этой цели, а затем пример Transact-SQL : "Пример А. Настройка имени входа и пользователя (Transact-SQL).

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

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

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

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

   • CREATE LOGIN (Transact-SQL)

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

   • Grant System Object Permissions (Transact-SQL)

   Дополнительные сведения см. в разделе GRANT Database Principal Permissions (Transact-SQL).

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

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

   • Создание пользователя базы данных

   • СОЗДАНИЕ ПОЛЬЗОВАТЕЛЯ (Transact-SQL)

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

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

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

   Дополнительные сведения см. в разделе GRANT Database Principal Permissions (Transact-SQL).

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

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

Внимание

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

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

-- 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 AdventureWorks2022 for this login  
USE AdventureWorks2022;  
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];  
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';  
GO  

[Top]

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

Примечание.

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

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

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

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

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

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

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

Примечание.

Сведения о пулах ресурсов Resource Governor, группах рабочей нагрузки и классификации см. в разделе Resource Governor.

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

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

• Настройка регулятора ресурсов с помощью шаблона

• Создание пула ресурсов

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

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

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

   CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );
   

   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, созданного в разделе "Пример A: настройка имени входа и пользователя (Transact-SQL)," domain_name\MAX_CPU. Замените его именем пользователя, соответствующего имени входа, которое планируется использовать для создания низкоприоритетных сжатых резервных копий.

-- Configure Resource Governor.  
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    

[Top]

Проверка классификации текущего сеанса (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).

[Top]

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

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

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

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

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

[Top]

См. также

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