Use o Resource Governor para limitar o uso da CPU pela compressão de backup (Transact-SQL)

Por padrão, fazer backup usando compactação aumenta significativamente o uso da CPU e a CPU adicional consumida pelo processo de compactação pode afetar negativamente as operações simultâneas. Portanto, convém criar um backup compactado de baixa prioridade em uma sessão cujo uso da CPU é limitado peloAdministrador de Recursos quando ocorre a contenção da CPU. Este tópico apresenta um cenário que classifica as sessões de um usuário específico do SQL Server mapeando-as para um grupo de carga de trabalho do Resource Governor que limita o uso da CPU nesses casos.

Importante

Em um determinado cenário do Resource Governor, a classificação de sessão pode ser baseada em um nome de usuário, um nome de aplicativo ou qualquer outra coisa que possa diferenciar uma conexão. Para obter mais informações, consulte a Função de Classificador do Administrador de Recursos e o Grupo de Cargas de Trabalho do Administrador de Recursos.

Este tópico contém o seguinte conjunto de cenários, que são apresentados em sequência:

1. Configurando um logon e um usuário para operações de Low-Priority

2. Configurando o Administrador de Recursos para limitar o uso da CPU

3. Verificando a classificação da sessão atual (Transact-SQL)

4. Compactando backups usando uma sessão com CPU limitada

Configurando um login e um usuário para operações de Low-Priority

O cenário neste tópico requer um logon e um usuário do SQL Server de baixa prioridade. O nome de usuário será usado para classificar as sessões em execução no logon e roteá-las para um grupo de carga de trabalho do Administrador de Recursos que limita o uso da CPU.

O procedimento a seguir descreve as etapas para configurar um logon e um usuário para essa finalidade, seguido por um Transact-SQL exemplo, "Exemplo A: Configurando um logon e um usuário (Transact-SQL)."

Para configurar um usuário de logon e banco de dados para classificar sessões

1. Crie um logon do SQL Server para criar backups compactados de baixa prioridade.

   Para criar um logon

   • Criar um Login

   • CREATE LOGIN (Transact-SQL)

2. Opcionalmente, conceda VIEW SERVER STATE a esse logon.

   • Permissões de objeto do sistema GRANT (Transact-SQL)

   Para obter mais informações, consulte GRANT Database Principal Permissions (Transact-SQL).

3. Crie um usuário do SQL Server para esse logon.

   Para criar um usuário

   • Criar um usuário de banco de dados

   • CRIAR USUÁRIO (Transact-SQL)

4. Para habilitar as sessões das credenciais de login e do usuário para fazer backup de um determinado banco de dados, adicione o usuário à função db_backupoperator desse banco de dados. Faça isso para cada banco de dados que esse usuário fará backup. Opcionalmente, adicione o usuário a outras funções de banco de dados fixas.

   Para adicionar um usuário a uma função de banco de dados fixa

   • sp_addrolemember (Transact-SQL)

   Para obter mais informações, consulte GRANT Database Principal Permissions (Transact-SQL).

Exemplo A: Configurando um logon e um usuário (Transact-SQL)

O exemplo a seguir é relevante somente se você optar por criar um novo logon do SQL Server e um usuário para backups de baixa prioridade. Como alternativa, você pode usar um logon e um usuário existentes, se houver um apropriado.

Importante

O exemplo a seguir usa um nome de usuário e logon de exemplo, domain_name\MAX_CPU. Substitua-os pelos nomes do logon do SQL Server e do usuário que você planeja usar ao criar seus backups compactados de baixa prioridade.

Este exemplo cria um logon na conta do Windows do domínio domain_name\MAX_CPU e concede permissão VIEW SERVER STATE ao logon. Esta permissão permite verificar a classificação do Resource Governor das sessões do login. Em seguida, o exemplo cria um usuário para domain_name\MAX_CPU e adiciona-o à função de banco de dados fixa db_backupoperator para o banco de dados de exemplo AdventureWorks2012. Esse nome de usuário será usado pela função de classificador do Administrador de Recursos.

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

[Topo]

Configurando o Administrador de Recursos para limitar o uso da CPU

Observação

Verifique se o Administrador de Recursos está habilitado. Para obter mais informações, consulte Habilitar o Administrador de Recursos.

Neste cenário do Resource Governor, a configuração compreende as seguintes etapas básicas:

1. Crie e configure um pool de recursos do Resource Governor que limite a largura de banda média máxima da CPU que será fornecida às solicitações no pool de recursos quando ocorrer a contenção da CPU.

2. Crie e configure um grupo de cargas de trabalho do Resource Governor que usa esse pool.

3. Crie uma função de classificador, que é uma UDF (função definida pelo usuário) cujos valores retornados são usados pelo Administrador de Recursos para classificar sessões para que sejam roteada para o grupo de carga de trabalho apropriado.

4. Registre a função de classificador com o Administrador de Recursos.

5. Aplique as alterações à configuração na memória do Administrador de Recursos.

Observação

Para obter informações sobre pools de recursos do Resource Governor, grupos de carga de trabalho e classificação, consulte Resource Governor.

As instruções Transact-SQL para essas etapas são descritas no procedimento"Para configurar o Administrador de Recursos para limitar o uso da CPU", que é seguido por um Transact-SQL exemplo do procedimento.

Para configurar o Resource Governor (SQL Server Management Studio)

• Configurar o Resource Governor usando um modelo

• Criar um pool de recursos

• Criar um grupo de carga de trabalho

Para configurar o Administrador de Recursos para limitar o uso da CPU (Transact-SQL)

1. Emita uma instrução CREATE RESOURCE POOL para criar um pool de recursos. O exemplo deste procedimento usa a seguinte sintaxe:

   CRIAR POOL DE RECURSOS pool_name COM ( MAX_CPU_PERCENT = valor );

   O valor é um inteiro de 1 a 100 que indica o percentual de largura de banda média máxima da CPU. O valor apropriado depende do seu ambiente. Para fins de ilustração, o exemplo neste tópico usa 20% por cento (MAX_CPU_PERCENT = 20.)

2. Emita uma instrução CREATE WORKLOAD GROUP para criar um grupo de carga de trabalho para operações de baixa prioridade cujo uso da CPU você deseja controlar. O exemplo deste procedimento usa a seguinte sintaxe:

   CRIAR GRUPO DE CARGA DE TRABALHO GROUP_NAME USANDO POOL_NAME;

3. Emita uma instrução CREATE FUNCTION para criar uma função de classificador que mapeia o grupo de carga de trabalho criado na etapa anterior para o usuário do logon de baixa prioridade. O exemplo deste procedimento usa a seguinte sintaxe:

   CREATE FUNCTION [schema_name.]function_name() RETURNS sysname

   COM SCHEMABINDING

   COMO

   INÍCIO

   DECLARE @workload_group_name COMO sysname

   IF (SUSER_NAME() = 'usuario_de_login_de_baixa_prioridade')

   SET @workload_group_name = 'workload_group_name'

   RETORNE @workload_group_name

   FIM

   Para obter informações sobre os componentes da instrução CREATE FUNCTION, consulte:

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Importante

     SUSER_NAME é apenas uma das várias funções do sistema que podem ser usadas em uma função de classificador. Para obter mais informações, consulte Criar e testar uma função de classificação User-Defined.

   • SET @local_variable (Transact-SQL).

4. Emita uma instrução ALTER RESOURCE GOVERNOR para registrar a função de classificador com o Resource Governor. O exemplo deste procedimento usa a seguinte sintaxe:

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

5. Emita uma segunda instrução ALTER RESOURCE GOVERNOR para aplicar as alterações à configuração na memória do Administrador de Recursos, da seguinte maneira:

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Exemplo B: Configurando o Administrador de Recursos (Transact-SQL)

O exemplo a seguir executa as seguintes etapas em uma única transação:

1. Cria o pMAX_CPU_PERCENT_20 grupo de recursos.

2. Cria o grupo de carga de trabalho gMAX_CPU_PERCENT_20.

3. Cria a rgclassifier_MAX_CPU() função de classificador, que usa o nome de usuário criado no exemplo anterior.

4. Registra a função de classificador com o Resource Governor.

Depois de confirmar a transação, o exemplo aplica as alterações de configuração solicitadas nas instruções ALTER WORKLOAD GROUP ou ALTER RESOURCE POOL.

Importante

O exemplo a seguir usa o nome de usuário do exemplo de usuário do SQL Server criado em "Exemplo A: Configurando um logon e um usuário (Transact-SQL)" domain_name\MAX_CPU. Substitua isso pelo nome do usuário do logon que você planeja usar para criar backups compactados de baixa prioridade.

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

[Topo]

Verificando a classificação da sessão atual (Transact-SQL)

Opcionalmente, faça logon como o usuário especificado em sua função de classificador e verifique a classificação da sessão emitindo a seguinte instrução SELECT no Pesquisador de Objetos:

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  

No painel de resultados, a coluna nome deve listar uma ou mais sessões para o nome do grupo de carga de trabalho especificado na sua função de classificador.

Observação

Para obter informações sobre as exibições de gerenciamento dinâmico chamadas por esta instrução SELECT, consulte sys.dm_exec_sessions (Transact-SQL) e sys.dm_resource_governor_workload_groups (Transact-SQL).

[Topo]

Compactando backups usando uma sessão com CPU limitada

Para criar um backup compactado em uma sessão com uma CPU máxima limitada, faça login como o usuário especificado em sua função de classificação. No comando de backup, especifique WITH COMPRESSION (Transact-SQL) ou selecione Compress backup (SQL Server Management Studio). Para criar um backup de banco de dados compactado, consulte Criar um Backup de Banco de Dados Completo (SQL Server).

Exemplo C: Criando um backup compactado (Transact-SQL)

O exemplo de BACKUP a seguir cria um backup completo compactado do banco de dados AdventureWorks2012 em um arquivo de backup recém-formatado. 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  

[Topo]

Consulte Também

Criar e testar uma função de classificação User-Defined
Administrador de Recursos