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

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.

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

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

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

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

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

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.

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

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

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