Tutoriel : Exemples de configuration de la gouvernance des ressources d’espace tempdb

S’applique à : SQL Server 2025 (17.x) et versions ultérieures

Les exemples de cet article vous montrent comment définir des limites sur tempdb la consommation d’espace et afficher la consommation d’espace par chaque groupe de charge de tempdb travail.

Pour une présentation de la tempdb gouvernance des ressources spatiales, consultez La gouvernance des ressources spatiales Tempdb.

Ces exemples sont destinés à vous aider à vous familiariser avec tempdb la gouvernance des ressources spatiales dans un environnement de test et de non-production.

Par exemple, supposons que resource governor n’est pas activé initialement et que sa configuration n’est pas modifiée par défaut. Ils supposent également que toutes les autres charges de travail sur votre instance SQL Server ne contribuent pas matériellement à tempdb la consommation d’espace pendant que vous exécutez les scripts.

Définir une limite fixe pour le default groupe de charge de travail

Cet exemple limite la consommation totale tempdb d’espace par les requêtes dans le groupe de charge de travail default à une limite fixe.

1. Modifiez le default groupe de charge de travail pour configurer une limite fixe de 20 Go sur tempdb la consommation d’espace.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
   

2. Activez Resource Governor pour rendre la configuration actuelle efficace.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

3. Affichez les limites relatives tempdb à la consommation d’espace.

   SELECT group_id,
          name,
          group_max_tempdb_data_mb,
          group_max_tempdb_data_percent
   FROM sys.resource_governor_workload_groups
   WHERE name = 'default';
   

4. Vérifiez la consommation actuelle tempdb d’espace par le default groupe de charge de travail, ajoutez des données en tempdb créant une table temporaire et en insérant une ligne, puis vérifiez à nouveau la consommation d’espace pour voir l’augmentation.

   SELECT group_id,
          name,
          tempdb_data_space_kb
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'default';
   
   SELECT REPLICATE('A', 1000) AS c
   INTO #t;
   
   SELECT group_id,
          name,
          tempdb_data_space_kb
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'default';
   

5. Si vous le souhaitez, supprimez les limites du groupe default et désactivez le gouverneur de ressources pour revenir à une consommation d'espace non régulé dans tempdb:

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);
   
   ALTER RESOURCE GOVERNOR DISABLE;
   

Définir une limite de pourcentage pour le default groupe de charge de travail

Cet exemple configure les tempdb fichiers de données afin que la limite de pourcentage puisse être utilisée, puis limite la consommation totale tempdb d’espace par les requêtes (requêtes) du default groupe de charge de travail à une limite de pourcentage.

1. Définissez FILEGROWTH et MAXSIZE pour tous les tempdb fichiers de données pour répondre aux exigences, limitant la taille maximale de tempdb 1 Go.

   Cet exemple part du principe que tempdb quatre fichiers de données sont présents. Vous devrez peut-être ajuster le script si votre tempdb configuration utilise un nombre différent de fichiers, ou si les noms logiques de fichier sont différents. Vous devrez peut-être également redémarrer votre instance SQL Server ou réduire tempdb l’utilisation si vous obtenez l’erreur 5040, MODIFY FILE a échoué pour la base de données « tempdb ». Taille du fichier ... est supérieur à MAXSIZE ... lors de l’exécution de ce script.

   ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp2', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp3', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = N'temp4', FILEGROWTH = 64 MB, MAXSIZE = 256 MB);
   

2. Modifiez le default groupe de charge de travail pour configurer une limite de cinq pour cent sur la consommation d’espace tempdb . Avec la taille maximale tempdb de 1 Go, cela limite le default groupe à environ 51 Mo d’espace tempdb .

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_PERCENT = 5);
   

3. Si une limite fixe est définie, supprimez-la afin qu’elle ne remplace pas la limite de pourcentage.

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL);
   

4. Permettre à Resource Governor de rendre la configuration efficace.

   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

5. Affichez les limites relatives tempdb à la consommation d’espace.

   SELECT group_id,
          name,
          group_max_tempdb_data_mb,
          group_max_tempdb_data_percent
   FROM sys.resource_governor_workload_groups
   WHERE name = 'default';
   

6. Ajoutez des données dans tempdb pour atteindre la limite.

   SELECT *
   INTO #m
   FROM sys.messages;
   

   L’instruction est interrompue avec l’erreur 1138.

7. Veuillez vérifier les statistiques du groupe de charge de travail pour tempdb.

   SELECT group_id,
          name,
          tempdb_data_space_kb,
          peak_tempdb_data_space_kb,
          total_tempdb_data_limit_violation_count
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'default';
   

   La valeur de la total_tempdb_data_limit_violation_count colonne est incrémentée par 1, indiquant qu’une requête dans le default groupe de charge de travail a été abandonnée, car sa tempdb consommation d’espace était limitée par le gouverneur de ressources.

8. Si vous le souhaitez, supprimez les limites du groupe default et désactivez le gouverneur de ressources pour revenir à une consommation d'espace non régulé dans tempdb:

   ALTER WORKLOAD GROUP [default]
   WITH (GROUP_MAX_TEMPDB_DATA_MB = NULL, GROUP_MAX_TEMPDB_DATA_PERCENT = NULL);
   
   ALTER RESOURCE GOVERNOR DISABLE;
   

9. Si vous le souhaitez, rétablissez les modifications apportées à la configuration du tempdb fichier de données précédemment dans cet exemple.

Définir une limite fixe pour un groupe de charges de travail défini par l’utilisateur

Cet exemple crée un groupe de charge de travail, puis crée une fonction classifieur pour affecter des sessions avec un nom d’application spécifique à ce groupe de charge de travail.

Dans le cadre de cet exemple, la limite fixe de la tempdb consommation d’espace pour le groupe de charge de travail est définie sur une petite valeur de 1 Mo. L’exemple montre ensuite qu’une tentative d’allocation d’espace dans tempdb qui dépasse la limite est abandonnée.

1. Créez un groupe de charge de travail et limitez sa tempdb consommation d’espace à 1 Mo.

   CREATE WORKLOAD GROUP limited_tempdb_space_group
   WITH (GROUP_MAX_TEMPDB_DATA_MB = 1);
   

2. Créez la fonction classifieur dans la master base de données. Le classifieur utilise la fonction APP_NAME intégrée pour déterminer le nom de l’application spécifié dans la chaîne de connexion cliente. Si le nom de l'application est défini sur limited_tempdb_application, la fonction retourne limited_tempdb_space_group comme le nom du groupe de charge de travail à utiliser. Sinon, la fonction retourne default comme nom de groupe de charge de travail.

   USE master;
   GO
   
   CREATE FUNCTION dbo.rg_classifier()
   RETURNS sysname
   WITH SCHEMABINDING
   AS
   BEGIN
   
   DECLARE @WorkloadGroupName sysname = N'default';
   
   IF APP_NAME() = N'limited_tempdb_application'
       SELECT @WorkloadGroupName = N'limited_tempdb_space_group';
   
   RETURN @WorkloadGroupName;
   
   END;
   GO
   

3. Modifiez resource Governor pour utiliser la fonction classifieur et reconfigurez resource governor pour utiliser la nouvelle configuration.

   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   

4. Ouvrez une nouvelle session classifiée dans le limited_tempdb_space_group groupe de charge de travail.

   1. Dans SQL Server Management Studio (SSMS), sélectionnez Fichier dans le menu principal, Nouveau, Requête du Moteur de Base de Données.

   2. Dans la boîte de dialogue Se connecter au moteur de base de données , spécifiez la même instance du moteur de base de données où vous avez créé le groupe de charge de travail et la fonction classifieur dans les étapes précédentes.

      Sélectionnez l’onglet Paramètres de connexion supplémentaires, puis entrez App=limited_tempdb_application. Cela permet à SSMS d’utiliser limited_tempdb_application comme nom d’application lors de la connexion à l’instance. La APP_NAME() fonction dans le classifieur retourne également cette valeur.

   3. Sélectionnez Se connecter pour ouvrir une nouvelle session.

5. Exécutez l’instruction suivante dans la fenêtre de requête ouverte à l’étape précédente. La sortie doit indiquer que votre session est classifiée dans le limited_tempdb_space_group groupe de charge de travail.

   SELECT wg.name AS workload_group_name
   FROM sys.dm_exec_sessions AS s
   INNER JOIN sys.dm_resource_governor_workload_groups AS wg
   ON s.group_id = wg.group_id
   WHERE s.session_id = @@SPID;
   

6. Exécutez l’instruction suivante dans la même fenêtre de requête.

   SELECT REPLICATE('S', 100) AS c
   INTO #t1;
   

   L’instruction se termine correctement. Exécutez l’instruction suivante dans la même fenêtre de requête :

   SELECT REPLICATE(CAST ('F' AS NVARCHAR (MAX)), 1000000) AS c
   INTO #t2;
   

   L’instruction est abandonnée avec l’erreur 1138, car elle tente de dépasser la limite de consommation d’espace de 1 Mo tempdb pour le groupe de charge de travail.

7. Consultez la consommation actuelle et maximale tempdb d’espace par le limited_tempdb_space_group groupe de charge de travail.

   SELECT group_id,
          name,
          tempdb_data_space_kb,
          peak_tempdb_data_space_kb,
          total_tempdb_data_limit_violation_count
   FROM sys.dm_resource_governor_workload_groups
   WHERE name = 'limited_tempdb_space_group';
   

   La valeur dans la total_tempdb_data_limit_violation_count colonne est 1, indiquant qu'une requête dans ce groupe de charge de travail a été annulée parce que sa tempdb consommation d'espace était restreinte par le gouverneur de ressources.

8. Si vous le souhaitez, pour revenir à la configuration initiale de cet exemple, déconnectez toutes les sessions à l’aide du limited_tempdb_space_group groupe de charge de travail et exécutez le script T-SQL suivant :

   /* Disable resource governor so that the classifier function can be dropped. */
   ALTER RESOURCE GOVERNOR DISABLE;
   ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
   DROP FUNCTION IF EXISTS dbo.rg_classifier;
   
   /* Drop the workload group. This requires that no sessions are using this workload group. */
   DROP WORKLOAD GROUP limited_tempdb_space_group;
   
   /* Reconfigure resource governor to reload the effective configuration without the classifier function and the workload group. This enables resource governor. */
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   
   /* Disable resource governor to revert to the initial configuration. */
   ALTER RESOURCE GOVERNOR DISABLE;
   

   Étant donné que SSMS conserve les paramètres de connexion dans l’onglet Paramètres de connexion supplémentaires , veillez à supprimer le App paramètre la prochaine fois que vous vous connectez à la même instance du moteur de base de données. Cela évite que vos connexions se classifient dans le limited_tempdb_space_group groupe de charge de travail s’il existe.

Contenu connexe

• Gouvernance des ressources d’espace Tempdb
• Gouverneur de ressources
• didacticiel : Exemples de configuration Resource Governor et meilleures pratiques
• MODIFIER LE GOUVERNEUR DE RESSOURCE (Transact-SQL)
• CRÉER GROUPE DE TRAVAIL (Transact-SQL)
• MODIFIER LE GROUPE DE CHARGE (Transact-SQL)
• SUPPRIMER GROUPE DE CHARGE DE TRAVAIL (Transact-SQL)
• sys.resource_governor_workload_groups
• sys.dm_resource_governor_workload_groups