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

S’applique à : SQL Server

Par défaut, sauvegarder en utilisant la compression augmente considérablement l'utilisation de l'UC et l'UC supplémentaire consommée par le processus de compression peut nuire aux 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. Configuration de Resource Governor pour limiter l'utilisation de l'UC

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

4. Compression de sauvegardes dans une session à utilisation maximale de l'UC limitée

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 classifier des sessions exécutées dans la connexion et pour les router vers un groupe de charge de travail de Resource Governor qui limite l'utilisation de l'UC.

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. Accordez éventuellement à cette connexion l'autorisation VIEW SERVER STATE.

   • GRANT – octroi d'autorisations d'objet système (Transact-SQL)

   Pour plus d’informations, consultez GRANT – octroi d’autorisations de 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

   • CRÉER UN UTILISATEUR (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 – octroi d’autorisations de 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 Windows domaine_nom\MAX_CPU , puis accorde l’autorisation VIEW 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 domaine_nom\MAX_CPU et l’ajoute au rôle de base de données fixe db_backupoperator pour l’exemple de base de données AdventureWorks2022. 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  

[Haut]

Configuration de Resource Governor pour limiter l'utilisation de l'UC

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. Inscription de 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 Resource Governor (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 de l'UC (Transact-SQL)

1. Émettez une instruction CREATE RESOURCE POOL 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 CREATE WORKLOAD GROUP pour créer un groupe de charge de travail pour les opérations de priorité basse dont vous souhaitez régir l’utilisation de l’UC. 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 classifieur qui mappe le groupe de charge de travail créé à l’étape précédente à l’utilisateur de la connexion de priorité basse. 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 instruction ALTER RESOURCE GOVERNOR pour inscrire la fonction classifieur auprès de 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 instruction ALTER RESOURCE GOVERNOR pour appliquer les modifications à la configuration en mémoire de 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. Il inscrit la fonction classifieur auprès de Resource Governor.

Après avoir validé la transaction, l'exemple applique les modifications de configuration demandées dans les instructions ALTER WORKLOAD GROUP 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    

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

[Haut]

Compression de sauvegardes dans une session à utilisation maximale de l'UC limitée

Pour créer une sauvegarde compressée dans une session à utilisation maximale de l'UC limitée, connectez-vous en tant que l'utilisateur spécifié dans votre fonction classifieur. 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 BACKUP suivant crée une sauvegarde complète compressée de la base de données AdventureWorks2022 dans un fichier de sauvegarde récemment formaté, 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  

[Haut]

Voir aussi

Créer et tester une fonction classifieur définie par l’utilisateur
gouverneur de ressources