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

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

Важно!

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

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

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

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

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

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

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

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

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

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

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

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

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

   • CREATE LOGIN (Transact-SQL)

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

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

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

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

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

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

   • CREATE USER (Transact-SQL)

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

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

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

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

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

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

Важно!

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

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

-- 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 AdventureWorks2012 for this login  
USE AdventureWorks2012;  
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. Создайте функцию-классификатор— определяемую пользователем функцию, возвращаемые значения которой используются регулятором Resource Governor для классификации сеансов с целью направления в соответствующую группу рабочей нагрузки.

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

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

Примечание

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

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

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

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

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

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

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

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

   CREATE RESOURCE POOL имя_пула WITH ( MAX_CPU_PERCENT = значение );

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

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

   CREATE WORKLOAD GROUP имя_группы USING имя_пула;

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

   CREATE FUNCTION [имя_схемы.]имя_функции() RETURNS sysname

   WITH SCHEMABINDING

   AS

   BEGIN

   DECLARE @workload_group_name AS sysname

   IF (SUSER_NAME() = 'пользователь_с_низкоприоритетным_именем_входа')

   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 = имя_схемы.имя_функции);

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

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Пример Б. Настройка Resource Governor (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). Сведения о создании сжатой резервной копии базы данных см. в статье Создание полной резервной копии базы данных (SQL Server).

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

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

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

[В начало]

См. также:

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