Partilhar via

Use o Administrador de Recursos para limitar o uso da CPU por compactação de backup (Transact-SQL)

Aplica-se a:SQL Server

Por padrão, o 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 de baixa prioridade compactado em uma sessão cujo uso da CPU é limitado pelo Administrador 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 Administrador de Recursos que limita o uso da CPU nesses casos.

Importante

Em um determinado cenário do Administrador de Recursos, a classificação da 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 Classificação do Administrador de Recursos e o Grupo de Carga 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 login e usuário para Low-Priority operações

  2. Configurando o Administrador de Recursos para Limitar o Uso da CPU

  3. Verificação da Classificação da Sessão Corrente (Transact-SQL)

  4. Compressão de Backups Usando uma Sessão com CPU Limitada

Configurando um login e um usuário para operações 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 sessões em execução no login 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 login e um usuário para essa finalidade, seguido por um Transact-SQL exemplo, "Exemplo A: Configurando um login e um usuário (Transact-SQL)."

Para configurar um usuário de login 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 login

  2. Opcionalmente, conceda VIEW SERVER STATE a este login.

    Para mais informações, consulte as Permissões Principais da Base de Dados GRANT (Transact-SQL).

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

    Para criar um usuário

  4. Para habilitar sessões deste login e permitir que o usuário faça 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 do qual 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

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

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

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

Importante

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

Este exemplo cria um logon para a conta domain_name\MAX_CPU Windows e, em seguida, concede a permissão VIEW SERVER STATE para o logon. Essa permissão permite que você verifique a classificação do Administrador de Recursos das sessões do login. O exemplo cria um usuário para domain_name\MAX_CPU e o adiciona à função de banco de dados fixa db_backupoperator para o banco de dados de exemplo AdventureWorks2022. 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 AdventureWorks2022 for this login  
USE AdventureWorks2022;  
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 Ativar o Administrador de Recursos.

Neste cenário do Administrador de Recursos, a configuração compreende as seguintes etapas básicas:

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

  2. Crie e configure um grupo de carga de trabalho do Administrador de Recursos que use esse pool.

  3. Crie uma função de classificador de , que é uma função definida pelo usuário (UDF) cujos valores de retorno são usados pelo Administrador de Recursos para classificar sessões para que elas sejam roteadas 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, grupos de carga de trabalho e classificação do Administrador de Recursos, consulte Administrador de Recursos.

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 Administrador de Recursos (SQL Server Management Studio)

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 para este procedimento usa a seguinte sintaxe:

    CREATE RESOURCE POOL [<pool_name>] WITH ( MAX_CPU_PERCENT = /*replace 10 with the actual value*/10 );

    Value é um número inteiro de 1 a 100 que indica a porcentagem da 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 para este procedimento usa a seguinte sintaxe:

    CREATE WORKLOAD GROUP [<group_name>] USING [<pool_name>];

  3. Emita uma instrução CREATE FUNCTION para criar uma função de classificador que mapeie o grupo de carga de trabalho criado na etapa anterior para o utilizador do login de baixa prioridade. O exemplo para este procedimento usa a seguinte sintaxe:

    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;

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

  4. Emita uma instrução ALTER RESOURCE GOVERNOR para registar a função de classificador no Administrador de Recursos. O exemplo para este 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 pool de recursos pMAX_CPU_PERCENT_20.

  2. Cria o grupo de carga de trabalho gMAX_CPU_PERCENT_20.

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

  4. Registra a função classificadora com o Administrador de Recursos.

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 SQL Server de exemplo criado em "Exemplo A: Configurando um logon e usuário (Transact-SQL)," domain_name\MAX_CPU. Substitua isso pelo nome do usuário do login que você planeja usar para criar backups compactados de baixa prioridade.

-- Configure Resource Governor.  
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 login como o usuário especificado na 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 deve listar uma ou mais sessões para o nome do grupo de trabalho especificado na 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 numa sessão com uma CPU máxima limitada, faça login como o utilizador especificado na função de classificação. No seu comando de backup, especifique WITH COMPRESSION ( Transact-SQL) ou selecione Compactar backup ( SQL Server Management Studio). Para criar um backup de banco de dados compactado, consulte Criar um backup completo de banco de dados (SQL Server).

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

O exemplo de BACKUP de a seguir cria um backup completo compactado do banco de dados AdventureWorks2022 em um arquivo de backup recém-formatado, Z:\SQLServerBackups\AdvWorksData.bak.

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

[Topo]

Ver também

Criar e testar um classificador User-Defined função
Administrador de Recursos