Como usar o Administrador de Recursos para limitar o uso de CPU por meio de compactação de backup (Transact-SQL)

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

   Para criar um logon

   • Como criar um logon do SQL Server

   • 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 Permissões de funções de servidor fixas (Mecanismo de Banco de Dados).

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

   Para criar um usuário

   • Como criar um usuário de banco de dados

   • CREATE USER (Transact-SQL)

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

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

   • sp_addrolemember (Transact-SQL)

   Para obter mais informações, consulte Permissões de funções de banco de dados fixas (Mecanismo de Banco de Dados).

O exemplo a seguir é relevante apenas se você optar por criar um novo logon e usuário do SQL Server para backups de baixa prioridade. Alternativamente, você pode usar um logon e um usuário existentes, se forem apropriados.

Importante
O exemplo a seguir usa um logon e um nome de usuário de exemplo, domain_name\MAX_CPU. Substitua-os pelos nomes de logon e de usuário do SQL Server que serão usados ao criar backups compactados de baixa prioridade.

Esse exemplo cria um logon para a conta domain_name\MAX_CPU do Windows e, em seguida, concede a permissão VIEW SERVER STATE ao logon. Essa permissão permite verificar a classificação do Administrador de Recursos de sessões do logon. 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 do banco de dados de exemplo do AdventureWorks2008R2. Esse nome de usuário será usado pela função de classificação 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 AdventureWorks2008R2 for this login
USE AdventureWorks2008R2;
CREATE USER [domain_name\MAX_CPU] FOR LOGIN [domain_name\MAX_CPU];
EXEC sp_addrolemember 'db_backupoperator', 'domain_name\MAX_CPU';
GO

[Início]

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

   CREATE RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = value);

   Value é um inteiro de 1 a 100 que indica a porcentagem da largura de banda média máxima da CPU. O valor adequado depende do ambiente. Para fins de ilustração, o exemplo deste 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 de CPU deve ser administrado. O exemplo deste 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 classificação que mapeie 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

   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 dessa 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 classificação. Para obter mais informações, consulte Considerações sobre como escrever uma função de classificação.

   • SET @local_variable (Transact-SQL).

4. Emita uma instrução ALTER RESOURCE GOVERNOR para registrar a função de classificação com o Administrador de Recursos. 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 GOVERNADOR para aplicar as alterações à configuração na memória do Administrador de Recursos, da seguinte maneira:

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

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 classificação rgclassifier_MAX_CPU() que usa o nome de usuário criado no exemplo anterior.

4. Registra a função de classificação no Administrador de Recursos.

Após confirmar a transação, o exemplo aplica as alterações da configuração nas instruções ALTER WORKLOAD GROUP ou ATER RESOURCE POOL.

Importante
O exemplo a seguir usa o nome de usuário de exemplo do SQL Server criado no "Exemplo A: Configurando um logon e um usuário (Transact-SQL)", domain_name\MAX_CPU. Substitua-os pelo nome do usuário do logon que será usado 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

[Início]

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

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

[Início]