Использование регулятора ресурсов для ограничения загрузки ЦП при сжатии резервной копии (компонент Transact-SQL)Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server нетБаза данных SQL Azure нетAzure Synapse Analytics (хранилище данных SQL) нетParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

По умолчанию резервное копирование с использованием сжатия существенно увеличивает загрузку ЦП, а дополнительная загрузка ЦП процессом сжатия может неблагоприятно повлиять на параллельные операции.By default, backing up using compression significantly increases CPU usage, and the additional CPU consumed by the compression process can adversely impact concurrent operations. Поэтому может понадобиться создать низкоприоритетную сжатую резервную копию в сеансе, загрузка ЦП в котором ограничиваетсяResource Governor в случае конфликта ЦП.Therefore, you might want to create a low-priority compressed backup in a session whose CPU usage is limited byResource Governor when CPU contention occurs. В этом разделе представлен сценарий, классифицирующий сеансы отдельного пользователя SQL ServerSQL Server путем сопоставления их с той или иной группой рабочей нагрузки регулятора ресурсов, которая в таких случаях ограничивает загрузку ЦП.This topic presents a scenario that classifies the sessions of a particular SQL ServerSQL Server user by mapping them to a Resource Governor workload group that limits CPU usage in such cases.

Важно!

В данном сценарии регулятора ресурсов классификация сеансов может основываться на имени пользователя, названии приложения или каком-либо другом критерии различения соединения.In a given Resource Governor scenario, session classification might be based on a user name, an application name, or anything else that can differentiate a connection. Дополнительные сведения см. в разделах Resource Governor Classifier Function и Resource Governor Workload Group.For more information, see Resource Governor Classifier Function and Resource Governor Workload Group.

Этот раздел содержит следующий набор сценариев, представленных в следующей последовательности.This topic contains the following set of scenarios, which are presented in sequence:

  1. Создание учетной записи и пользователя для операций с низким приоритетомSetting Up a Login and User for Low-Priority Operations

  2. Настройка регулятора ресурсов для ограничения загрузки ЦПConfiguring Resource Governor to Limit CPU Usage

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

  4. Сжатие резервных копий в сеансе с ограничением доступа к ЦПCompressing Backups Using a Session with Limited CPU

Создание учетной записи и пользователя для операций с низким приоритетомSetting Up a Login and User for Low-Priority Operations

Для сценария в этом разделе требуется низкоприоритетное имя входа в систему SQL ServerSQL Server и пользователь.The scenario in this topic requires a low-priority SQL ServerSQL Server login and user. Имя пользователя будет использоваться для классификации сеансов, запущенных в процессе входа, и для направления их в группу рабочей нагрузки регулятора ресурсов, которая ограничивает загрузку ЦП.The user name will be used to classify sessions running in the login and route them to a Resource Governor workload group that limits CPU usage.

Ниже описываются этапы настройки имени входа и пользователя для этой цели, за которыми следует пример на языке Transact-SQLTransact-SQL: "Пример А. Настройка имени входа и пользователя (Transact-SQL)".The following procedure describes the steps for setting up a login and user for this purpose, followed by a Transact-SQLTransact-SQL example, "Example A: Setting Up a Login and User (Transact-SQL)."

Настройка имени входа и пользователя базы данных для классификации сеансовTo set up a login and database user for classifying sessions

  1. Создайте имя входа на SQL ServerSQL Server для создания низкоприоритетных сжатых резервных копий.Create a SQL ServerSQL Server login for creating low-priority compressed backups.

    Создание имени входаTo create a login

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

    Дополнительные сведения см. в разделе GRANT, предоставление разрешений на участника базы данных (Transact-SQL).For more information, see GRANT Database Principal Permissions (Transact-SQL).

  3. Создайте пользователя SQL ServerSQL Server для данного имени входа.Create a SQL ServerSQL Server user for this login.

    Создание пользователяTo create a user

  4. Чтобы разрешить для сеансов данного имени входа и пользователя резервное копирование заданной базы данных, добавьте пользователя к роли db_backupoperator для этой базы данных.To enable sessions of this login and user to back up a given database, add the user to the db_backupoperator database role of that database. Выполните это для каждой из баз данных, резервные копии которых должен создать данный пользователь.Do this for each database that this user will back up. При необходимости добавьте пользователя к другим предопределенным ролям баз данных.Optionally, add the user to other fixed database roles.

    Добавление пользователя к предопределенной роли базы данныхTo add a user to a fixed database role

    Дополнительные сведения см. в разделе GRANT, предоставление разрешений на участника базы данных (Transact-SQL).For more information, see GRANT Database Principal Permissions (Transact-SQL).

Пример А. Настройка имени входа и пользователя (Transact-SQL)Example A: Setting Up a Login and User (Transact-SQL)

Следующий пример касается только случая, когда выбрано создание нового имени входа и пользователя SQL ServerSQL Server для низкоприоритетных резервных копий.The following example is relevant only if you choose to create a new SQL ServerSQL Server login and user for low-priority backups. В качестве альтернативы можно использовать существующие имя входа и пользователя, если таковой существует.Alternatively, you can use an existing login and user, if an appropriate one exists.

Важно!

В приведенном ниже примере используется образец имени для входа и пользователя — имя_домена\MAX_CPU.The following example uses a sample login and user name, domain_name\MAX_CPU. Замените их именами входа и пользователя SQL ServerSQL Server , которые планируется использовать при создании низкоприоритетных сжатых резервных копий.Replace these with the names of the SQL ServerSQL Server login and user that you plan to use when creating your low-priority compressed backups.

В этом примере создается имя для входа для учетной записи Windows имя_домена\MAX_CPU , а затем этому имени для входа предоставляется разрешение VIEW SERVER STATE.This example creates a login for the domain_name\MAX_CPU Windows account and then grants VIEW SERVER STATE permission to the login. Это разрешение позволяет проверить классификацию сеансов имени входа в регуляторе ресурсов.This permission enables you to verify the Resource Governor classification of sessions of the login. Затем для имени имя_домена\MAX_CPU создается пользователь и добавляется к предопределенной роли db_backupoperator для образца базы данных AdventureWorks2012AdventureWorks2012 .The example then creates a user for domain_name\MAX_CPU and adds it to the db_backupoperator fixed database role for the AdventureWorks2012AdventureWorks2012 sample database. Это имя пользователя будет использоваться функцией-классификатором в регуляторе ресурсов.This user name will be used by the Resource Governor classifier function.

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

Настройка регулятора ресурсов для ограничения загрузки ЦПConfiguring Resource Governor to Limit CPU Usage

Примечание

Убедитесь, что регулятор ресурсов включен.Ensure that Resource Governor is enabled. Дополнительные сведения см. в разделе Активация регулятора ресурсов.For more information, see Enable Resource Governor.

В этом сценарии регулятора ресурсов процесс настройки состоит из следующих основных этапов.In this Resource Governor scenario, configuration comprises the following basic steps:

  1. Создайте и настройте для регулятора ресурсов пул ресурсов, который ограничивает максимальную среднюю пропускную способность ЦП, предоставляемую запросам из пула ресурсов в случае состязания из-за ЦП.Create and configure a Resource Governor resource pool that limits the maximum average CPU bandwidth that will be given to requests in the resource pool when CPU contention occurs.

  2. Создайте и настройте группу рабочей нагрузки регулятора ресурсов, которая будет использовать этот пул.Create and configure a Resource Governor workload group that uses this pool.

  3. Создайте функцию-классификатор— определяемую пользователем функцию, возвращаемые значения которой используются регулятором Resource Governor для классификации сеансов с целью направления в соответствующую группу рабочей нагрузки.Create a classifier function, which is a user-defined function (UDF) whose return values are used by Resource Governor for classifying sessions so that they are routed to the appropriate workload group.

  4. Зарегистрируйте эту функцию-классификатор в регуляторе ресурсов.Register the classifier function with Resource Governor.

  5. Примените изменения в хранящейся в памяти конфигурации регулятора ресурсов.Apply the changes to the Resource Governor in-memory configuration.

Примечание

Сведения о пулах ресурсов Resource Governor, группах рабочей нагрузки и классификации см. в разделе Resource Governor.For information about Resource Governor resource pools, workload groups, and classification, see Resource Governor.

Инструкции языка Transact-SQLTransact-SQL для указанных этапов описаны в процедуре «Настройка регулятора ресурсов для ограничения загрузки ЦП», за которой следует пример процедуры на языке Transact-SQLTransact-SQL .The Transact-SQLTransact-SQL statements for these steps are described in the procedure, "To configure Resource Governor for limiting CPU usage," which is followed by a Transact-SQLTransact-SQL example of the procedure.

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

Настройка регулятора ресурсов для ограничения загрузки ЦП (Transact-SQL)To configure Resource Governor for limiting CPU usage (Transact-SQL)

  1. Выполните инструкцию CREATE RESOURCE POOL , чтобы создать пул ресурсов.Issue a CREATE RESOURCE POOL statement to create a resource pool. В примере этой процедуры используется следующий синтаксис:The example for this procedure uses the following syntax:

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

    Value — целое число от 1 до 100, которое указывает максимальную среднюю пропускную способность ЦП в процентах.Value is an integer from 1 to 100 that indicates the percentage of maximum average CPU bandwidth. Рекомендуемое значение зависит от конкретной среды.The appropriate value depends on your environment. Для иллюстрации в примере из этого разделе используется значение 20% (MAX_CPU_PERCENT = 20).For the purpose of illustration, the example in this topic uses 20% percent (MAX_CPU_PERCENT = 20.)

  2. Выполните инструкцию CREATE WORKLOAD GROUP , чтобы создать группу рабочей нагрузки для низкоприоритетных операций, загрузку ЦП которыми следует регулировать.Issue a CREATE WORKLOAD GROUP statement to create a workload group for low-priority operations whose CPU usage you want to govern. В примере этой процедуры используется следующий синтаксис:The example for this procedure uses the following syntax:

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

  3. Выполните инструкцию CREATE FUNCTION , чтобы создать функцию-классификатор, сопоставляющую созданную на предыдущем этапе группу рабочей нагрузки с пользователем с низкоприоритетным именем входа.Issue a CREATE FUNCTION statement to create a classifier function that maps the workload group created in the preceding step to the user of the low-priority login. В примере этой процедуры используется следующий синтаксис:The example for this procedure uses the following syntax:

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

    WITH SCHEMABINDINGWITH SCHEMABINDING

    ASAS

    BEGINBEGIN

    DECLARE @workload_group_name AS sysnameDECLARE @workload_group_name AS sysname

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

    SET @workload_group_name = 'workload_group_name'SET @workload_group_name = 'workload_group_name'

    RETURN @workload_group_nameRETURN @workload_group_name

    ENDEND

    Дополнительные сведения о компонентах этой инструкции CREATE FUNCTION см. в разделах:For information about the components of this CREATE FUNCTION statement, see:

  4. Выполните инструкцию ALTER RESOURCE GOVERNOR , чтобы зарегистрировать функцию-классификатор в регуляторе ресурсов.Issue an ALTER RESOURCE GOVERNOR statement to register the classifier function with Resource Governor. В примере этой процедуры используется следующий синтаксис:The example for this procedure uses the following syntax:

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = имя_схемы.имя_функции);ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = schema_name.function_name);

  5. Выполните еще одну инструкцию ALTER RESOURCE GOVERNOR, чтобы применить изменения в хранящейся в памяти конфигурации регулятора ресурсов, как указано ниже:Issue a second ALTER RESOURCE GOVERNOR statement to apply the changes to the Resource Governor in-memory configuration, as follows:

    ALTER RESOURCE GOVERNOR RECONFIGURE;  
    

Пример Б. Настройка Resource Governor (Transact-SQL)Example B: Configuring Resource Governor (Transact-SQL)

В нижеприведенном примере в одной транзакции выполняются следующие шаги.The following example performs the following steps within a single transaction:

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

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

  3. Создание функции-классификатора rgclassifier_MAX_CPU() , в которой используется имя пользователя, созданное в предыдущем примере.Creates the rgclassifier_MAX_CPU() classifier function, which uses the user name created in the preceding example.

  4. Регистрация функции-классификатора в регуляторе ресурсов.Registers the classifier function with Resource Governor.

После того как транзакция зафиксирована, применяются изменения в конфигурации, запрошенные в инструкциях ALTER WORKLOAD GROUP или ALTER RESOURCE POOL.After committing the transaction, the example applies the configuration changes requested in the ALTER WORKLOAD GROUP or ALTER RESOURCE POOL statements.

Важно!

В следующем примере используется образец имени пользователя SQL ServerSQL Server, созданный в разделе "Пример А. Настройка имени входа и пользователя (Transact-SQL)", — domain_name\MAX_CPU.The following example uses the user name of the sample SQL ServerSQL Server user created in "Example A: Setting Up a Login and User (Transact-SQL)," domain_name\MAX_CPU. Замените его именем пользователя, соответствующего имени входа, которое планируется использовать для создания низкоприоритетных сжатых резервных копий.Replace this with the name of the user of the login that you plan to use for creating low-priority compressed backups.

-- 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)Verifying the Classification of the Current Session (Transact-SQL)

При необходимости войдите в систему как пользователь, указанный в функции-классификаторе, и проверьте классификацию сеанса, выполнив следующую инструкцию SELECT в обозревателе объектов:Optionally, log in as the user that you specified in your classifier function, and verify the session classification by issuing the following SELECT statement in Object Explorer:

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 должен содержать один или несколько сеансов для имени группы рабочей нагрузки, указанного в функции-классификаторе.In the results pane, the name column should list one or more sessions for the workload-group name that you specified in your classifier function.

Примечание

Дополнительные сведения о динамических административных представлениях, вызываемых этой инструкцией SELECT, см. в разделах sys.dm_exec_sessions (Transact-SQL) и sys.dm_resource_governor_workload_groups (Transact-SQL).For information about the dynamic management views called by this SELECT statement, see sys.dm_exec_sessions (Transact-SQL) and sys.dm_resource_governor_workload_groups (Transact-SQL).

[В начало][Top]

Сжатие резервных копий в сеансе с ограничением доступа к ЦПCompressing Backups Using a Session with Limited CPU

Чтобы создать сжатую резервную копию в сеансе с ограниченной максимальной загрузкой ЦП, войдите в систему как пользователь, указанный в функции-классификаторе.To create a compressed backup in a session with a limited maximum CPU, log in as the user specified in your classifier function. В команде резервного копирования укажите предложение WITH COMPRESSION (Transact-SQLTransact-SQL) или выберите вариант Сжимать резервные копии (SQL Server Management StudioSQL Server Management Studio).In your backup command, either specify WITH COMPRESSION ( Transact-SQLTransact-SQL) or select Compress backup ( SQL Server Management StudioSQL Server Management Studio). Сведения о создании сжатой резервной копии базы данных см. в разделе Создание полной резервной копии базы данных (SQL Server).To create a compressed database backup, see Create a Full Database Backup (SQL Server).

Пример В. Создание сжатой резервной копии (Transact-SQL)Example C: Creating a Compressed Backup (Transact-SQL)

В следующем примере BACKUP создается сжатая полная резервная копия базы данных AdventureWorks2012AdventureWorks2012 в чистом форматированном файле резервной копии Z:\SQLServerBackups\AdvWorksData.bak.The following BACKUP example creates a compressed full backup of the AdventureWorks2012AdventureWorks2012 database in a newly formatted backup file, 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]

См. также:See Also

Создание и проверка определяемой пользователем функции-классификатора Create and Test a Classifier User-Defined Function
Resource GovernorResource Governor