• Article

Procédure : utiliser le gouverneur de ressources pour limiter l'utilisation de l'UC par compression de sauvegarde (Transact-SQL)

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, il peut être préférable, dans une session où l'utilisation de l'UC est limitée, de créer une sauvegarde compressée de priorité basse à l'aide du gouverneur de ressources 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 du gouverneur de ressources qui limite l'utilisation de l'UC dans de tels cas.

Important

Dans un scénario donné du gouverneur de ressources, 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 Considérations sur l'écriture d'une fonction classifieur et Scénarios de gestion de la charge de travail du gouverneur de ressources.

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 du gouverneur de ressources 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 du gouverneur de ressources 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 Autorisations des rôles serveur fixes (moteur de base de données).

  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 Autorisations des rôles de base de données fixes (moteur de base de données).

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, domain_name\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 domain_name\MAX_CPU, puis accorde l'autorisation VIEW SERVER STATE à la connexion. Cette autorisation vous permet de vérifier la classification du gouverneur de ressources 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 AdventureWorks2008R2. Ce nom d'utilisateur sera utilisé par la fonction classifieur du gouverneur de ressources.

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

[Haut de la page]

Configuration du gouverneur de ressources pour limiter l'utilisation de l'UC

Notes

Vérifiez que le gouverneur de ressources est activé. Pour plus d'informations, consultez Procédure : activer ou désactiver le gouverneur de ressources (SQL Server Management Studio).

Dans ce scénario du gouverneur de ressources, la configuration comprend les étapes de base indiquées ci-dessous.

  1. Création et configuration d'un pool de ressources du gouverneur de ressources 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 du gouverneur de ressources 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 le gouverneur de ressources pour classifier des sessions de sorte qu'elles soient routées vers le groupe de charge de travail approprié.

  4. Inscription de la fonction classifieur auprès du gouverneur de ressources.

  5. Application des modifications à la configuration en mémoire du gouverneur de ressources.

Notes

Pour plus d'informations sur les pools de ressources, les groupes de charge de travail et la classification du gouverneur de ressources, consultez Concepts du gouverneur de ressources.

Les instructions Transact-SQL pour ces étapes sont décrites dans la procédure « Pour configurer le gouverneur de ressources 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)

Pour configurer le gouverneur de ressources 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 = value );

    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() sysname RETURNS

    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 plus d'informations sur les composants de cette instruction CREATE FUNCTION, consultez :

  4. Émettez une instruction ALTER RESOURCE GOVERNOR pour inscrire la fonction classifieur auprès du gouverneur de ressources. 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 du gouverneur de ressources, comme suit :

    ALTER RESOURCE GOVERNOR RECONFIGURE;

Exemple B : configuration du gouverneur de ressources (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 du gouverneur de ressources.

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) », domain_name\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.
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

[Haut de la page]

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 de la page]

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

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

[Haut de la page]