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

Par défaut, la sauvegarde à l’aide de la compression augmente considérablement l’utilisation du processeur, et l’UC supplémentaire consommée par le processus de compression peut avoir un impact négatif sur les opérations simultanées. Par conséquent, vous pouvez créer une sauvegarde compressée de faible priorité dans une session dont l’utilisation du processeur est limitée parResource Governor lorsque la contention du processeur se produit. 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 Resource Governor qui limite l’utilisation du processeur dans ce cas.

Important

Dans un scénario Resource Governor donné, la classification de session peut être basée sur un nom d’utilisateur, un nom d’application ou tout autre élément pouvant différencier une connexion. Pour plus d’informations, consultez la fonction classifieur Resource Governor et le groupe de charge de travail Resource Governor.

Cette rubrique contient l’ensemble de scénarios suivant, présentés en séquence :

1. Configuration d’une connexion et d’un utilisateur pour les opérations de Low-Priority

2. Configuration de 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 un processeur limité

Configuration d’une connexion et d’un utilisateur pour les opérations de Low-Priority

Le scénario de cette rubrique nécessite une connexion et un utilisateur SQL Server de faible priorité. Le nom d’utilisateur sera utilisé pour classifier les sessions s’exécutant dans la connexion et les acheminer vers un groupe de charge de travail Resource Governor qui limite l’utilisation du processeur.

La procédure suivante décrit les étapes de configuration d’une connexion et d’un utilisateur à cet effet, suivie d’un exemple de Transact-SQL, « Exemple A : Configuration d’une connexion et d’un utilisateur (Transact-SQL). »

Pour configurer un utilisateur de connexion et de base de données pour la classification des sessions

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

   Pour créer une connexion

   • Créer un compte de connexion

   • CRÉER UNE CONNEXION (Transact-SQL)

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

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

   Pour plus d’informations, consultez GRANT Database Principal Permissions (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 de cet utilisateur de sauvegarder une base de données, ajoutez l’utilisateur au rôle db_backupoperator de cette base de données. Effectuez cette opération pour chaque base de données que cet utilisateur sauvegardera. Si vous le souhaitez, ajoutez l’utilisateur à d’autres rôles de base de données fixes.

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

   • sp_addrolemember (Transact-SQL)

   Pour plus d’informations, consultez GRANT Database Principal Permissions (Transact-SQL).

Exemple A : Configuration d’une connexion et d’un utilisateur (Transact-SQL)

L’exemple suivant est pertinent uniquement si vous choisissez de créer une connexion SQL Server et un utilisateur pour les sauvegardes de faible priorité. Vous pouvez également utiliser une connexion et un utilisateur existants, s’il en existe un.

Important

L’exemple suivant utilise un exemple de nom d’utilisateur et de connexion, domain_name\MAX_CPU. Remplacez-les par les noms de la connexion SQL Server et de l’utilisateur que vous envisagez d’utiliser lors de la création de vos sauvegardes compressées de faible priorité.

Cet exemple crée une connexion pour le compte Windows domain_name\MAX_CPU , puis accorde l’autorisation VIEW SERVER STATE à la connexion. Cette autorisation vous permet de vérifier la classification Resource Governor des 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 AdventureWorks2012. Ce nom d’utilisateur sera utilisé par la fonction classifieur 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  
  

[Top]

Configuration de Resource Governor pour limiter l’utilisation du processeur

Remarque

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

Dans ce scénario Resource Governor, la configuration comprend les étapes de base suivantes :

1. Créez et configurez un pool de ressources Resource Governor qui limite la bande passante CPU maximale moyenne qui sera donnée aux requêtes dans le pool de ressources en cas de contention CPU.

2. Créez et configurez un groupe de charge de travail Resource Governor qui utilise ce pool.

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

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

5. Appliquez les modifications à la configuration en mémoire de Resource Governor.

Remarque

Pour plus d’informations sur les pools de ressources Resource Governor, les groupes de charge de travail et la classification, consultez Resource Governor.

Les instructions Transact-SQL pour ces étapes sont décrites dans la procédure « Pour configurer Resource Governor pour limiter l’utilisation du processeur », 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 pour limiter l’utilisation du processeur (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 :

   CRÉER RESOURCE POOL pool_name WITH ( MAX_CPU_PERCENT = valeur ).

   La valeur est un entier compris entre 1 et 100 qui indique le pourcentage de bande passante moyenne maximale du processeur. La valeur appropriée dépend de votre environnement. Dans le cadre de l’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 faible priorité dont vous souhaitez régir l’utilisation du processeur. L’exemple de cette procédure utilise la syntaxe suivante :

   CRÉER GROUPE DE CHARGEMENT DE TRAVAIL group_name EN UTILISANT 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 faible priorité. L’exemple de cette procédure utilise la syntaxe suivante :

   CREATE FUNCTION [schema_name.]function_name() RETURNS sysname

   AVEC SCHEMABINDING

   COMME

   COMMENCER

   DECLARE @workload_group_name AS sysname

   IF (SUSER_NAME() = 'user_of_low_priority_login')

   SET @workload_group_name = 'workload_group_name'

   RENDRE @workload_group_name

   FIN

   Pour plus d’informations sur les composants de cette instruction CREATE FUNCTION, consultez :

   • DECLARE @local_variable (Transact-SQL)

   • SUSER_SNAME (Transact-SQL)

     Important

     SUSER_NAME n’est qu’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 de classifieur User-Defined.

   • 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 suivant effectue les étapes suivantes dans une seule transaction :

1. Crée le pool de ressources pMAX_CPU_PERCENT_20.

2. Crée le gMAX_CPU_PERCENT_20 groupe de charge de travail.

3. Crée la rgclassifier_MAX_CPU() fonction classifieur, qui utilise le nom d’utilisateur créé dans l’exemple précédent.

4. Enregistre la fonction de classifier auprès de Resource Governor.

Après avoir commité 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), » domain_name\MAX_CPU. Remplacez-le par le nom de l’utilisateur de la connexion que vous envisagez d’utiliser pour créer des sauvegardes compressées de faible priorité.

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

[Top]

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

Si vous le souhaitez, connectez-vous en tant qu’utilisateur que vous avez spécifié dans votre fonction classifieur et vérifiez la classification de session en émettant 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 de nom doit répertorier une ou plusieurs sessions pour le nom du groupe de charge de travail que vous avez spécifié dans votre fonction classifieur.

Remarque

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

[Top]

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

Pour créer une sauvegarde compressée dans une session avec un processeur maximal limité, connectez-vous en tant qu’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 nouvellement mis en forme. 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  

[Top]

Voir aussi

Créer et tester une fonction de classifieur User-Defined
gouverneur de ressources