Utiliser le gouverneur de ressources pour limiter l'utilisation de l'UC par compression de sauvegarde (Transact-SQL)

S’applique à : ouiSQL Server (toutes les versions prises en charge)

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. Ainsi, il peut être préférable, dans une session où l’utilisation du processeur est limitée, de créer une sauvegarde compressée de priorité basse à l’aide deResource Governor en cas de contention du processeur. 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.

Important

Dans un scénario donné de Resource Governor, la classification des sessions peut être basée sur un nom d'utilisateur, un nom d'application ou tout autre élément permettant d'identifier une connexion. Pour plus d'informations, consultez Fonction classifieur de Resource Governor et Groupe de charge de travail de Resource Governor.

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

  2. Accordez éventuellement à cette connexion l'autorisation VIEW SERVER STATE.

    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

  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

    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.

Important

L’exemple ci-dessous utilise un exemple de connexion et de nom d’utilisateur, domaine_nom\MAX_CPU. Remplacez-le par les noms de la connexion et de l'utilisateur SQL Server que vous projetez d'utiliser lors de la création de vos sauvegardes compressées de priorité basse.

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

[Haut]

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

Notes

Vérifiez que Resource Governor est activé. Pour plus d’informations, consultez Activer Resource Governor.

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.

Notes

Pour plus d’informations sur les pools de ressources, les groupes de charge de travail et la classification de Resource Governor, consultez 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)

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 :

  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.

Important

L’exemple suivant utilise le nom d’utilisateur de l’exemple d’utilisateur SQL Server créé dans « Exemple A : configuration d’une connexion et d’un utilisateur (Transact-SQL) », nom_domaine\MAX_CPU. Remplacez-le par le nom de l'utilisateur de la connexion que vous projetez d'utiliser pour créer les sauvegardes compressées de priorité basse.

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

Notes

Pour plus d’informations sur les vues de gestion dynamique appelées par cette instruction SELECT, consultez sys.dm_exec_sessions (Transact-SQL) et sys.dm_resource_governor_workload_groups (Transact-SQL).

[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 AdventureWorks2012 dans un fichier de sauvegarde récemment formaté, 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  

[Haut]

Voir aussi

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