Partager via


Scénarios de gestion de la charge de travail du gouverneur de ressources

Vous pouvez utiliser le gouverneur de ressources de plusieurs manières pour surveiller et gérer les charges de travail sur votre système SQL Server. Cette rubrique fournit une vue d'ensemble sur la manière de configurer et d'utiliser le gouverneur de ressources. Les scénarios fournis incluent des exemples de code Transact-SQL permettant de créer et de modifier des groupes de charges de travail et des pools de ressources.

Configuration du gouverneur de ressources

Après avoir installé SQL Server 2008, le gouverneur de ressources est disponible mais n'est pas activé. Les groupes de charges de travail interne et par défaut et leurs pools de ressources correspondants existent.

Pour créer et utiliser vos propres pools de ressources et groupes de charges de travail, vous devez compléter les étapes suivantes :

  • Créer un pool de ressources dont vous spécifiez les limites.

  • Créer un groupe de charges de travail dont vous spécifiez les limites et les stratégies et identifier le pool de ressources auquel appartient le groupe de charges de travail.

  • Définir et activer une fonction classifieur que vous souhaitez utiliser pour les requêtes entrantes.

Lorsque les étapes précédentes sont effectuées, vous pouvez consulter la configuration du gouverneur de ressources active et l'état de toutes les demandes actives classées.

Définition des spécifications de pool de ressources et de groupe de charges de travail

Votre défi consiste à déterminer le groupe de charges de travail et la configuration du pool de ressources que vous souhaitez utiliser pour votre environnement SQL Server. Pour cela, vous devez savoir comment les ressources SQL Server sont consommées réellement par une application.

La solution consiste à créer un groupe de charges de travail qui appartient au pool de ressources par défaut. Exécutez l'application pendant plusieurs jours et utilisez les informations que vous recueillez pour répondre aux questions suivantes.

  • Quelle est la configuration de l'UC ?
    Déterminez une évaluation de la bande passante de l'UC en multipliant l'utilisation totale de l'UC en moyenne par demande par le nombre moyen de demandes par seconde.

    L'écart type maximal, moyen et standard de l'utilisation de l'UC peut être utilisé pour déterminer si une limite maximale de l'UC est nécessaire.

  • Quel est le nombre de demandes simultanées ?
    Utilisez le nombre maximal et moyen des statistiques de demandes simultanées pour déterminer le facteur de concurrence minimal.

  • Quelle est la mémoire totale nécessaire ?
    Utilisez la mémoire totale maximale et moyenne utilisée pour déterminer les besoins en mémoire.

  • Quelle est la quantité de mémoire nécessaire pour une requête unique ?
    Utilisez la mémoire maximale et moyenne par statistique de requête pour déterminer la quantité de mémoire nécessaire.

  • Est-ce qu'une requête ne s'exécute pas en raison d'un manque de mémoire ?
    Utilisez la durée moyenne d'attente des statistiques de la file d'attente de la mémoire pour déterminer si le blocage de certaines requêtes est lié à la disponibilité de la mémoire.

Scénarios

Voici sept scénarios en exemples. Les scénarios 1 à 5 sont cumulatifs ; chacun est élaboré à partir du scénario précédent. Le scénario 6 n'est pas lié aux scénarios précédents, et le scénario 7 est élaboré à partir du scénario 6.

Scénario 1

Q : je viens d'installer une nouvelle version de SQL Server et souhaiterais utiliser le gouverneur de ressources. Comment puis-je l'utiliser dans mon environnement ?

R : utilisez le gouverneur de ressources pour surveiller la consommation des ressources par les charges de travail. Suivez les étapes ci-dessous pour configurer un environnement du gouverneur de ressources. Un exemple de configuration est fourni à votre attention.

  1. Créez des groupes de charges de travail pour vos charges de travail.

  2. Créez une fonction de classification.

  3. Enregistrez la fonction de classification avec le gouverneur de ressources.

  4. Activez le gouverneur de ressources.

  5. Surveillez les compteurs de performance du gouverneur de ressources et interrogez le DMV qui retournera des informations sur l'utilisation des ressources pour un groupe de charges de travail.

Exemple

[!REMARQUE]

La configuration suivante ne spécifie pas un pool de ressources que le groupe de charge de travail doit utiliser. Par défaut, les groupes de charge de travail utilisent le pool par défaut.

BEGIN TRAN;
-- Create 3 workload groups based on the nature of their workload.
-- One handles ad hoc requests, the second handles reports, and the
-- third handles admin requests. These groups all use the default 
-- settings for workload groups.
-- These workloads are divided into groups that cover ad hoc queries,
-- reports, and administration jobs. 
CREATE WORKLOAD GROUP GroupAdhoc;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO
COMMIT TRAN;
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'default' group.
CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (SUSER_NAME() = 'sa')
          SET @grp_name = 'GroupAdmin'
      IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupAdhoc'
      IF (APP_NAME() LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Scénario 2

Q : en fonction des résultats d'analyse du scénario 1, j'aimerais consulter un événement à chaque fois qu'une requête dans le groupe ad hoc (GroupAdhoc) s'exécute pendant plus de 30 secondes.

R : suivez les étapes ci-dessous pour modifier la configuration actuelle du gouverneur de ressources. Un exemple de configuration est fourni à votre attention.

  1. Définissez une limite sur l'utilisation de l'UC pour le groupe ad hoc.

  2. Surveillez les événements SQL Trace (événement de classe de gestion du gouverneur de ressources).

  3. Effectuez une action sur l'événement. Par exemple, ignorez l'événement, envoyez un message électronique, envoyez une page ou exécutez la commande KILL sur la demande.

Exemple

-- Specify a limit on CPU usage for the ad hoc workload group.
-- An event is automatically generated when the limit is reached.
ALTER WORKLOAD GROUP GroupAdhoc
WITH (REQUEST_MAX_CPU_TIME_SEC = 30);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Scénario 3

Q : je souhaite restreindre davantage le groupe ad hoc afin qu'il ne dépasse pas 50 pour cent de l'utilisation de l'UC lorsque toutes les demandes sont cumulées.

R : étant donné que les scénarios précédents ont utilisé le pool par défaut, vous devez créer un nouveau pool de ressources. Suivez les étapes ci-dessous pour modifier la configuration actuelle du gouverneur de ressources. Un exemple de configuration est fourni à votre attention.

  1. Créez un nouveau pool de ressources et configurez des limites pour l'UC.

  2. Configurez le groupe de charges de travail ad hoc pour utiliser le nouveau pool de ressources.

Exemple

BEGIN TRAN;
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdhoc
WITH (MAX_CPU_PERCENT = 50);
-- Configure the workload group so it uses the new resource pool. 
-- The following statement moves 'GroupAdhoc' from the 'default' pool --- to 'PoolAdhoc'
ALTER WORKLOAD GROUP GroupAdhoc
USING PoolAdhoc;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Scénario 4

Q : je souhaite maintenant vérifier que le groupe d'administration a toujours des ressources pour exécuter des requêtes de diagnostic, mais ces requêtes ne doivent pas utiliser plus de 10 pour cent des ressources de la mémoire du serveur.

R : pour cela, vous devez créer un pool de ressources. Suivez les étapes ci-dessous pour modifier la configuration actuelle du gouverneur de ressources. Un exemple de configuration est fourni à votre attention.

  1. Créez un nouveau pool de ressources et définissez les limites des ressources.

  2. Configurez le groupe de charges de travail d'administrateur pour utiliser le nouveau pool.

Exemple

BEGIN TRAN;
-- Create a new resource pool and set resource limits.
CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 10,
     MIN_MEMORY_PERCENT = 10,
     MAX_MEMORY_PERCENT = 10);
-- Note that no limit is specified for MAX CPU on this pool.
-- Configure the admin group to use the new pool.
-- The following statement moves 'GroupAdmin' from the 'default' pool 
-- to 'PoolAdmin'.
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;
COMMIT TRAN;
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Scénario 5

Q : comme mes rapports sont actuellement dans le pool de ressources par défaut, mais qu'ils utilisent un groupe de charges de travail séparé (GroupReports), j'aimerais vérifier que les éléments du groupe de ressources par défaut ont une priorité d'utilisation des ressources inférieure à celle des rapports.

R : pour ce faire, vous devez configurer le paramètre IMPORTANCE dans le groupe par défaut.

[!REMARQUE]

Vous pouvez modifier les paramètres pour le groupe par défaut, mais pas le pool par défaut. Si vous pensez qu'une modification est nécessaire pour le pool par défaut, cela indique que vous devez créer un nouveau pool de ressources.

Suivez l'étape ci-dessous pour modifier la configuration actuelle du gouverneur de ressources. Un exemple de configuration est fourni à votre attention.

  1. Modifiez le paramètre pour le groupe par défaut.

Exemple

-- Configure the IMPORTANCE setting.
ALTER WORKLOAD GROUP [default] 
WITH (IMPORTANCE = LOW);
GO
-- Apply the changes to the Resource Governor in-memory configuration.
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Scénario 6

Q : je souhaite créer un nouveau groupe de charges de travail qui utilise un pool de ressources existant. Je souhaite ensuite modifier la fonction classifieur pour déplacer le groupe vers un nouveau pool de ressources.

R : utilisez les étapes suivantes pour configurer ce nouvel environnement de gouverneur de ressources. Un exemple de configuration est fourni à votre attention.

  1. Créez un nouveau pool de ressources avec les paramètres par défaut.

  2. Créez un nouveau groupe de charges de travail présent dans un pool existant.

  3. Créez et enregistrez une nouvelle fonction classifieur pour gérer des demandes.

Exemple

BEGIN TRAN;
-- Create a new resource pool with the default pool settings.
CREATE RESOURCE POOL MyNewPool;
-- Create a new workload group that is in an existing 
-- resource pool named 'MyPool'.
CREATE WORKLOAD GROUP MyNewGroup USING MyPool;
GO
COMMIT TRAN;
GO
-- Create a classifier function that is based on a user login.
CREATE FUNCTION dbo.rgclassifier_v2 () 
RETURNS sysname
WITH SCHEMABINDING 
AS
BEGIN
    DECLARE @grp_name sysname
    IF SUSER_SNAME() = 'DOMAIN\username'
        SET @grp_name = 'MyNewGroup'
    ELSE
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
-- Register the function with Resource Governor and 
-- then start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v2);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Scénario 7

Q : je décide que je n'ai plus besoin d'un groupe de charges de travail (créé dans le scénario 6), aussi je souhaite supprimer le groupe de charges de travail et le pool de ressources qui le contiennent.

R : procédez comme suit pour modifier l'environnement de gouverneur de ressources. Un exemple de configuration est fourni à votre attention.

  1. Créez et enregistrez une nouvelle fonction classifieur chargée de classer des demandes dans le groupe de charges de travail restant.

  2. Supprimez le groupe de charges de travail.

  3. Supprimez le pool de ressources.

  4. Appliquez les modifications de la configuration.

Exemple

BEGIN TRAN;
GO
-- Create a new classifier function.
CREATE FUNCTION dbo.rgclassifier_v3 () 
RETURNS sysnameE 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
    IF suser_sname() = 'DOMAIN\username'
        SET @grp_name = 'MyGroup'
    RETURN @grp_name
END;
GO
COMMIT TRAN;
GO
-- Register the new function and start Resource Governor.
ALTER RESOURCE GOVERNOR
    WITH (CLASSIFIER_FUNCTION = dbo.rgclassifier_v3);
GO
-- Wait for all the current sessions that use 'MyNewGroup' to drain,
-- or KILL the sessions.
BEGIN TRAN;
GO
-- You have to drop the workload group before you can drop the
-- resource pool it is in.
DROP WORKLOAD GROUP MyNewGroup;
GO
DROP RESOURCE POOL MyNewPool;
GO
COMMIT TRAN;
-- Update the Resource Governor in-memory configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO