Поделиться через


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

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

Важное примечаниеВажно!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Дополнительные сведения см. в разделе Разрешения предопределенных ролей базы данных (компонент 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 см. в разделах:

  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)

В следующем примере 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

[В начало]