Utiliser Resource Governor pour limiter l’utilisation du CPU pour la compression de sauvegarde (Transact-SQL)

S’applique à :SQL Server

Par défaut, la sauvegarde par compression augmente considérablement l’utilisation du processeur, et le CPU supplémentaire consommé par le processus de compression peut affecter négativement les opérations simultanées. Par conséquent, en cas de contention du processeur, il peut être préférable de créer une sauvegarde compressée de priorité basse à l’aide de Resource Governor dans les sessions où l’utilisation du processeur est limitée. Cette rubrique présente un scénario qui classifie les sessions d’un utilisateur SQL Server particulier en les mappant à un groupe de charge de travail de Resource Governor qui limite l’utilisation de l’UC dans de tels cas.

Cette rubrique propose les scénarios suivants, présentés dans l'ordre :

1. Configuration d'une connexion et d'un utilisateur pour les opérations de priorité basse

2. Configurer Resource Governor pour limiter l'utilisation du processeur

3. Vérification de la classification de la session active (Transact-SQL)

4. Compression des sauvegardes à l’aide d’une session avec processeur limité

Configuration d'une connexion et d'un utilisateur pour les opérations de priorité basse

Le scénario de cette rubrique requiert une connexion SQL Server de priorité basse et un utilisateur. Le nom d’utilisateur sera utilisé pour classer les sessions associées à cette connexion et les acheminer vers un groupe de charges de travail du Resource Governor qui limite l’utilisation du processeur.

La procédure ci-dessous décrit les étapes nécessaires à la configuration d’une connexion et d’un utilisateur à cette fin. Elle est suivie d’un exemple Transact-SQL, « Exemple A : configuration d’une connexion et d’un utilisateur (Transact-SQL) ».

Pour configurer une connexion et un utilisateur de base de données afin de classifier des sessions

1. Créez une connexion SQL Server pour créer des sauvegardes compressées de priorité basse.

   Pour créer une connexion

   • Créer un compte de connexion

   • CREATE LOGIN (Transact-SQL)

2. Si vous le souhaitez, accordez VIEW l’ÉTAT DU SERVEUR à cette connexion.

   • GRANT Autorisations d’objet système (Transact-SQL)

   Pour plus d’informations, consultez GRANT Autorisations du principal de base de données (Transact-SQL).

3. Créez un utilisateur SQL Server pour cette connexion.

   Pour créer un utilisateur

   • Créer un utilisateur de base de données

   • CREATE USER (Transact-SQL)

4. Pour permettre aux sessions de cette connexion et cet utilisateur de sauvegarder une base de données spécifique, ajoutez l'utilisateur au rôle de base de données db_backupoperator de cette base de données. Répétez l'opération pour chaque base de données que cet utilisateur doit sauvegarder. Ajoutez éventuellement l'utilisateur à d'autres rôles de base de données fixes.

   Pour ajouter un utilisateur de base de données à un rôle de base de données fixe

   • sp_addrolemember (Transact-SQL)

   Pour plus d’informations, consultez GRANT Autorisations du principal de base de données (Transact-SQL).

Exemple A : configuration d'une connexion et d'un utilisateur (Transact-SQL)

L’exemple ci-dessous est pertinent uniquement si vous choisissez de créer une connexion et un utilisateur SQL Server pour des sauvegardes de priorité basse. Vous avez également la possibilité d'utiliser une connexion et un utilisateur existants, le cas échéant.

Cet exemple crée une connexion pour le compte domain_name Windows\MAX_CPU, puis accorde VIEW l’autorisation SERVER STATE à la connexion. Cette autorisation vous permet de vérifier la classification de Resource Governor pour les sessions de la connexion. L’exemple crée ensuite un utilisateur pour domain_name\MAX_CPU et l’ajoute au rôle de base de données fixe db_backupoperator pour l’exemple de base de données AdventureWorks2025. Ce nom d'utilisateur sera utilisé par la fonction classifieur de Resource Governor.

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

[Retour en haut]

Configuration du Resource Governor pour limiter l’utilisation du processeur

Dans ce scénario de Resource Governor, la configuration comprend les étapes de base indiquées ci-dessous.

1. Création et configuration d'un pool de ressources de Resource Governor limitant la bande passante processeur moyenne maximale qui sera allouée aux demandes dans le pool de ressources en cas de contention du processeur.

2. Création et configuration d'un groupe de charge de travail de Resource Governor qui utilise ce pool.

3. Création d’une fonction classifieur, qui est une fonction définie par l’utilisateur et dont les valeurs de retour sont utilisées par Resource Governor pour classifier des sessions pour qu’elles soient routées vers le groupe de charge de travail approprié.

4. Inscrivez la fonction classifieur auprès de Resource Governor.

5. Application des modifications à la configuration en mémoire de Resource Governor.

Les instructions Transact-SQL pour ces étapes sont décrites dans la procédure « Pour configurer Resource Governor afin de limiter l’utilisation de l’UC » qui est suivie d’un exemple Transact-SQL de la procédure.

Pour configurer le Gouverneur de ressources (SQL Server Management Studio)

• Configurer Resource Governor à l’aide d’un modèle

• Créer un pool de ressources

• Créer un groupe de charge de travail

Pour configurer Resource Governor afin de limiter l'utilisation du processeur (Transact-SQL)

1. Émettez une CREATE RESOURCE POOL instruction pour créer un pool de ressources. L'exemple de cette procédure utilise la syntaxe suivante :

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

   Value est un entier de 1 à 100 qui indique le pourcentage de bande passante processeur moyenne maximale. La valeur appropriée dépend de votre environnement. À des fins d'illustration, l'exemple de cette rubrique utilise 20 % (MAX_CPU_PERCENT = 20.)

2. Émettez une instruction pour créer un CREATE WORKLOAD GROUP groupe de charge de travail pour les opérations de faible priorité dont vous souhaitez régir l’utilisation du processeur. L'exemple de cette procédure utilise la syntaxe suivante :

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

3. Émettez une instruction CREATE FUNCTION pour créer une fonction de classification qui associe le groupe de charge de travail créé à l’étape précédente à l’utilisateur de la connexion de faible priorité. L'exemple de cette procédure utilise la syntaxe suivante :

   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;
   

   Pour obtenir des informations sur les composants de cette instruction CREATE FUNCTION, consultez :

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Important

     SUSER_NAME est simplement l'une des fonctions système qui peuvent être utilisées dans une fonction classifieur. Pour plus d’informations, consultez Créer et tester une fonction classifieur définie par l’utilisateur.

   • SET @local_variable (Transact-SQL).

4. Émettez une ALTER RESOURCE GOVERNOR instruction pour inscrire la fonction classifieur avec Resource Governor. L'exemple de cette procédure utilise la syntaxe suivante :

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [<schema_name>].[<function_name>]);
   

5. Émettez une deuxième ALTER RESOURCE GOVERNOR instruction pour appliquer les modifications apportées à la configuration en mémoire Resource Governor, comme suit :

   ALTER RESOURCE GOVERNOR RECONFIGURE;  
   

Exemple B : configuration de Resource Governor (Transact-SQL)

L'exemple ci-dessous effectue les étapes qui suivent dans une transaction unique.

1. Il crée le pool de ressources pMAX_CPU_PERCENT_20 .

2. Il crée le groupe de charge de travail gMAX_CPU_PERCENT_20 .

3. Il crée la fonction classifieur rgclassifier_MAX_CPU() , qui utilise le nom d'utilisateur créé à l'exemple précédent.

4. Enregistre la fonction de classification avec Resource Governor.

Après avoir commité la transaction, l’exemple applique les modifications de configuration demandées dans les ALTER WORKLOAD GROUP instructions ou ALTER RESOURCE POOL .

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

[Retour en haut]

Vérification de la classification de la session active (Transact-SQL)

Éventuellement, connectez-vous en tant que l’utilisateur spécifié dans votre fonction classifieur et vérifiez la classification des sessions au moyen de l’instruction SELECT suivante dans l’Explorateur d’objets :

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  

Dans le volet de résultats, la colonne nom doit répertorier une ou plusieurs sessions pour le nom de groupe de charge de travail que vous avez spécifié dans votre fonction classifieur.

[Retour en haut]

Compression des sauvegardes à l’aide d’une session avec utilisation limitée du processeur

Pour créer une sauvegarde compressée dans une session avec une limite maximale d’utilisation du processeur, connectez-vous avec le compte de l’utilisateur spécifié dans votre fonction de classification. Dans votre commande de sauvegarde, spécifiez WITH COMPRESSION (Transact-SQL) ou sélectionnez Compresser la sauvegarde (SQL Server Management Studio). Pour créer une sauvegarde de base de données compressée, consultez Créer une sauvegarde complète de base de données (SQL Server).

Exemple C : création d'une sauvegarde compressée (Transact-SQL)

L’exemple suivant BACKUP crée une sauvegarde complète compressée de la base de données AdventureWorks2025 dans un fichier de sauvegarde nouvellement mis en forme. 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  

[Retour en haut]

Contenu connexe

• Tutoriel : Exemples de configuration resource governor et bonnes pratiques
• Gouverneur de ressources