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

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

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

1. Настройка имени входа и пользователя для операций Low-Priority

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

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

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

Настройка имени входа и пользователя для операций Low-Priority

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

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

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

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

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

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

   • CREATE LOGIN (Transact-SQL)

2. При желании предоставьте этому имени входа доступ к просмотру состояния сервера.

   • 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).

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

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

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

-- 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  
  

[Top]

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

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

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

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

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

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

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

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

Настроить регулятор ресурсов (SQL Server Management Studio)

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

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

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

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

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

   СОЗДАНИЕ ПУЛА РЕСУРСОВ pool_name WITH (MAX_CPU_PERCENT = значение );

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

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

   СОЗДАТЬ ГРУППУ НАГРУЗКИ group_name ИСПОЛЬЗУЯ pool_name;

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

   CREATE FUNCTION [schema_name.]function_name() ВОЗВРАЩАЕТ имя sysname

   ИСПОЛЬЗОВАНИЕ SCHEMABINDING

   КАК

   НАЧИНАТЬ

   DECLARE @workload_group_name AS sysname

   IF (SUSER_NAME() = "user_of_low_priority_login")

   SET @workload_group_name = "workload_group_name"

   ВОЗВРАТ @workload_group_name

   КОНЕЦ

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

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Это важно

     SUSER_NAME — это только одна из нескольких системных функций, которые можно использовать в функции классификатора. Дополнительные сведения см. в разделе "Создание и проверка функции классификатора User-Defined".

   • SET @local_variable (Transact-SQL).

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

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

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

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

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

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

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

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

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

4. Регистрирует функцию классификатора в Resource Governor.

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

-- 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  
  

[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  

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

[Top]

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

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

Пример C. Создание сжатой резервной копии (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  

[Top]

См. также

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