Gouvernance des ressources d’espace Tempdb

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

Lorsque vous activez la tempdb gestion des ressources spatiales, vous améliorez la fiabilité et évitez les interruptions de service en empêchant les requêtes ou charges de travail d'engloutir une grande quantité d'espace dans tempdb.

À compter de SQL Server 2025 (17.x), vous pouvez utiliser le gouverneur de ressources pour appliquer une limite sur la quantité totale d’espace consommé par un groupe de charge de tempdb travail. Lorsqu’une demande (requête) tente de dépasser la limite, le gouverneur de ressources l’interrompt en renvoyant un message d’erreur spécifique indiquant que la limite du groupe de charge de travail est imposée.

En effet, vous pouvez partitionner l’espace partagé tempdb entre différentes charges de travail. Par exemple, vous pouvez définir une limite plus élevée pour un groupe de charges de travail utilisé par une application stratégique et définir une limite inférieure pour le default groupe de charge de travail utilisé par toutes les autres charges de travail.

Pour obtenir des exemples de configuration pas à pas, consultez Tutoriel : Exemples de configuration de la gouvernance des ressources spatiales tempdb.

Prise en main du régulateur de ressources

Resource Governor fournit une infrastructure flexible pour définir différentes tempdb limites d’espace pour différentes applications, utilisateurs, groupes d’utilisateurs, etc. Vous pouvez également définir des limites basées sur une logique personnalisée.

Si vous débutez avec Resource Governor dans SQL Server, consultez Resource Governor pour en savoir plus sur ses concepts et ses fonctionnalités.

Pour obtenir une procédure pas à pas et des bonnes pratiques de configuration resource governor, consultez Tutoriel : Exemples de configuration resource governor et bonnes pratiques.

Définir des limites sur la consommation d’espace tempdb

Vous pouvez limiter la tempdb consommation d’espace par un groupe de charge de travail de deux façons :

• Définissez une limite fixe à l’aide de l’argument GROUP_MAX_TEMPDB_DATA_MB .

  Utilisez la limite fixe lorsque vous connaissez les exigences d’utilisation de la charge de travail tempdb à l’avance ou lorsque la tempdb taille ne change pas.

• Définissez une limite de pourcentage à l’aide de l’argument GROUP_MAX_TEMPDB_DATA_PERCENT .

  Utilisez la limite de pourcentage lorsque vous pouvez modifier la taille maximale au fil du tempdb temps et que vous souhaitez que l’espace disponible pour chaque groupe de charge de travail change proportionnellement sans modifier la tempdb configuration du groupe de charge de travail. Par exemple, si vous effectuez un scale-up d’une machine virtuelle Azure exécutant SQL Server et augmentez la taille maximale tempdb , l’espace tempdb disponible pour chaque groupe de charge de travail avec une limite de pourcentage augmente également.

Pour plus d’informations sur les arguments GROUP_MAX_TEMPDB_DATA_MB et GROUP_MAX_TEMPDB_DATA_PERCENT, consultez CREATE WORKLOAD GROUP ou ALTER WORKLOAD GROUP.

Si vous spécifiez des limites fixes et de pourcentage pour le même groupe de charge de travail, la limite fixe est prioritaire sur la limite de pourcentage.

Sur une instance SQL Server donnée, vous pouvez avoir un mélange de groupes de charges de travail avec des limites fixes, des limites de pourcentage ou aucune limite de tempdb consommation d’espace. Pour afficher les limites effectives, consultez l’exemple d’affichage des limites d’espace tempdb effectives par groupe de charge de travail .

Configuration de limite de pourcentage

Lorsque vous exécutez l’instruction ALTER RESOURCE GOVERNOR RECONFIGURE , les limites de pourcentage prennent effet en fonction du tableau suivant :

Paramétrage	Descriptif	Taille maximale de Tempdb (100%)	Limite de pourcentage en vigueur
- GROUP_MAX_TEMPDB_DATA_MB n’est pas défini - Pour tous les fichiers de données, MAXSIZE n’est pas UNLIMITED - Pour tous les fichiers de données, FILEGROWTH n’est pas zéro	tempdb fichiers de données peuvent croître automatiquement jusqu'à leur taille maximale	Somme des valeurs pour tous les fichiers de données MAXSIZE	Oui
- GROUP_MAX_TEMPDB_DATA_MB n’est pas défini - Pour tous les fichiers de données, MAXSIZE est UNLIMITED - Pour tous les fichiers de données, FILEGROWTH est égal à zéro	tempdb les fichiers de données sont préalablement dimensionnés à la taille prévue et ne peuvent pas être étendus davantage	Somme des valeurs pour tous les fichiers de données SIZE	Oui
Toutes les autres configurations			Non

Pour afficher votre tempdb configuration, consultez l’exemple de configuration de fichier de données Tempdb View .

Lorsque vous utilisez des limites de pourcentage, tenez compte des éléments suivants :

• Si vous définissez GROUP_MAX_TEMPDB_DATA_PERCENT et exécutez l’instruction ALTER RESOURCE GOVERNOR RECONFIGURE , mais que la configuration du fichier de données ne répond pas aux exigences, l’instruction se termine correctement et les limites de pourcentage sont stockées, mais elles ne sont pas appliquées. Dans ce cas, vous recevez le message d’avertissement 10989, gravité 10, qui est également journalisée dans le journal des erreurs :

  GROUP_MAX_TEMPDB_DATA_PERCENT is not in effect because tempdb
  configuration requirements aren't met.
  

• Pour rendre les limites de pourcentage effectives, reconfigurez tempdb les fichiers de données pour répondre aux exigences et réexécuter ALTER RESOURCE GOVERNOR RECONFIGURE . Pour plus d’informations sur la configuration de SIZE, FILEGROWTH et MAXSIZE, voir ALTER DATABASEOptions de fichier et de groupe de fichiers.

• Si une limite de pourcentage est en vigueur et que vous ajoutez, supprimez ou redimensionnez tempdb des fichiers de données, vous devez exécuter ALTER RESOURCE GOVERNOR RECONFIGURE pour mettre à jour resource governor avec la nouvelle taille maximale de tempdb (100%).

Remarque

Pour une nouvelle instance de SQL Server, le fichier de données MAXSIZE est UNLIMITED et FILEGROWTH est supérieur à zéro, ce qui signifie que les limites en pourcentage ne s’appliquent pas. Pour utiliser des limites de pourcentage, vous devez :

• Prédimensionnez tempdb les fichiers de données à leur taille prévue et définissez FILEGROWTH sur zéro.
• Définissez MAXSIZE de chaque fichier de données sur une valeur limitée.
• Pour chaque tempdb volume de fichiers de données, assurez-vous que la somme des valeurs des MAXSIZE fichiers sur le volume est inférieure ou égale à l’espace disque disponible sur le volume. Par exemple, si un volume a 100 Go d’espace libre et a deux tempdb fichiers de données, faites de MAXSIZE chaque fichier 50 Go ou moins.

Fonctionnement

Cette section décrit tempdb en détail la gouvernance des ressources spatiales.

• À mesure que les pages de données dans tempdb sont allouées et libérées, le régulateur de ressources conserve la comptabilité de l'espace tempdb consommé par chaque groupe de charge de travail.

  Si resource governor est activé et qu’une tempdb limite de consommation d’espace est définie pour un groupe de charge de travail et qu’une requête (requête) exécutée dans le groupe de charge de travail tente d’amener la consommation totale tempdb d’espace par le groupe au-dessus de la limite, la requête est abandonnée avec l’erreur 1138, gravité 17 :

  Could not allocate a new page for database 'tempdb' because that
  would exceed the limit set for workload group 'workload-group-name'".
  

  Lorsqu’une demande est abandonnée avec l’erreur 1138, la valeur dans la total_tempdb_data_limit_violation_count colonne de la vue de gestion dynamique (DMV) sys.dm_resource_governor_workload_groups est incrémentée d’une, et l’événement tempdb_data_workload_group_limit_reached étendu se déclenche.

• Le régulateur de ressources effectue le suivi de toutes les tempdb utilisations qui peuvent être attribuées à un groupe de charges de travail, notamment les tables temporaires, les variables (y compris les variables de table), les paramètres de valeur de table, les tables nontemporaires, les curseurs, ainsi que tempdb l’utilisation pendant le traitement des requêtes, telles que les bobines, les déversements, les tables de travail et les fichiers de travail.

  La consommation d’espace pour les tables temporaires globales et les tables nontemporaires est tempdb prise en compte sous le groupe de charge de travail qui insère la première ligne dans la table, même si les sessions d’autres groupes de charges de travail ajoutent, modifient ou suppriment des lignes dans la même table.

• Les limites de consommation configurées tempdb pour chaque groupe de charge de travail sont exposées dans la vue de catalogue sys.resource_governor_workload_groups, dans les colonnes group_max_tempdb_data_mb et group_max_tempdb_data_percent.

  La consommation actuelle et la consommation maximale de l’espace tempdb par un groupe de charges de travail sont indiquées dans le DMV sys.dm_resource_governor_workload_groups, respectivement dans les colonnes XXX tempdb_data_space_kbetpeak_tempdb_data_space_kb XXX.

  Conseil / Astuce

  tempdb_data_space_kb et peak_tempdb_data_space_kb les colonnes de sys.dm_resource_governor_workload_groups sont conservées même si aucune limite tempdb de consommation d’espace n’est définie.

  Vous pouvez créer la fonction classifieur et les groupes de charge de travail sans définir de limites initialement. Surveillez l’utilisation par chaque groupe au fil du temps pour établir des modèles d’utilisation représentatifs, puis définissez tempdb des limites selon les besoins.

• tempdb L’utilisation par les magasins de versions, y compris le magasin de versions persistantes (PVS), n’est pas restreinte lorsque l’accélération de la récupération de base de données (ADR) est activée dans tempdb, car les versions de lignes peuvent être utilisées par des requêtes dans plusieurs groupes de charges de travail.

• La consommation d’espace dans tempdb est comptée comme le nombre de pages de données de 8 Ko utilisées. Même si une page n’est pas remplie de données entièrement, elle ajoute 8 Ko à la tempdb consommation par un groupe de charge de travail.

• tempdb la comptabilité de l’espace est conservée pendant toute la durée de vie d’un groupe de charge de travail. Si un groupe de charge de travail est supprimé alors que des tables temporaires globales ou des tables non temporaires dont les données sont attribuées à ce groupe de charge de travail demeurent dans tempdb, l’espace utilisé par ces tables n’est pas pris en compte dans un autre groupe de charge de travail.

• tempdb la gouvernance des ressources spatiales contrôle l’espace dans tempdb les fichiers de données, mais pas l’espace disque sur les volumes sous-jacents. Sauf si vous préallouez aux fichiers de données tempdb leur taille prévue, l’espace sur les volumes sur lesquels se trouve tempdb risque d’être occupé par d’autres fichiers. S'il n'y a plus d'espace disponible pour que les fichiers de données de tempdb se développent, alors tempdb risque de manquer d'espace avant que toute limite de la consommation d'espace de tempdb par un groupe de charge de travail ne soit atteinte.

• La gouvernance des ressources spatiales tempdb s’applique aux fichiers de données, mais pas au fichier journal des transactions. Pour vous assurer que le journal des transactions dans tempdb ne consomme pas beaucoup d’espace, activez ADR dans tempdb.

Différences par rapport au suivi de l’espace au niveau de la session

L'attribut sys.dm_db_session_space_usage DMV fournit des tempdb statistiques d’allocation d’espace et de désallocation pour chaque session. Même s’il n’existe qu’une seule session dans un groupe de charge de travail, les statistiques d’utilisation de l’espace de cette vue DMV peuvent ne pas correspondre exactement aux statistiques de l’affichage sys.dm_resource_governor_workload_groups , pour les raisons suivantes :

• Contrairement à sys.dm_resource_governor_workload_groups, sys.dm_db_session_space_usage:
  • Ne reflète tempdb pas l’utilisation de l’espace par les tâches en cours d’exécution. Les statistiques de sys.dm_db_session_space_usage sont mises à jour lorsqu'une tâche complète. Les statistiques internes à sys.dm_resource_governor_workload_groups sont mises à jour en continu.
  • Ne suit pas les pages du plan de répartition des index (IAM). Pour plus d’informations, consultez le guide d’architecture des pages et des étendues.
• Une fois les lignes supprimées ou lorsqu’une table, un index ou une partition est supprimé ou tronqué, le moteur de base de données libère les pages de données. La désallocation peut être synchrone ou effectuée par un processus en arrière-plan asynchrone. sys.dm_resource_governor_workload_groups reflète ces désallocations de pages au fur et à mesure qu’elles se produisent, même si la session qui a provoqué ces désallocations a été fermée et n’est plus présente dans sys.dm_db_session_space_usage.

Meilleures pratiques pour la gouvernance des ressources d’espace tempdb

Avant de configurer la gouvernance des tempdb ressources spatiales, tenez compte des meilleures pratiques suivantes :

• Passez en revue les meilleures pratiques générales pour le gouverneur de ressources.

• Pour la plupart des scénarios, évitez de définir la tempdb limite de consommation d’espace sur une petite valeur ou zéro, en particulier pour le default groupe de charge de travail. Si vous définissez cette limite sur une petite valeur ou zéro, de nombreuses tâches courantes peuvent commencer à échouer s’ils doivent allouer de l’espace dans tempdb. Par exemple, si vous définissez la limite fixe ou de pourcentage sur 0 pour le default groupe de charge de travail, vous ne pourrez peut-être pas ouvrir l’Explorateur d’objets dans SQL Server Management Studio (SSMS).

• Sauf si vous créez des groupes de charges de travail personnalisés et une fonction classifieur qui place les charges de travail dans leurs groupes dédiés, évitez de limiter tempdb l’utilisation du default groupe de charge de travail. Si vous limitez tempdb la consommation d’espace par le default groupe de charge de travail, les requêtes peuvent renvoyer l’erreur 1138. Cette erreur se produit lorsque tempdb dispose encore d’un espace inutilisé qui ne peut être utilisé par aucune charge de travail utilisateur.

• La somme des valeurs GROUP_MAX_TEMPDB_DATA_MB pour tous les groupes de charge de travail peut dépasser la taille maximale tempdb. Par exemple, si la taille maximale tempdb est de 100 Go, les limites pour le groupe de charge de travail GROUP_MAX_TEMPDB_DATA_MB et le groupe de charge de travail B peuvent être de 80 Go chacun.

  Cette approche empêche toujours chaque groupe de charge de travail de consommer tout l’espace en tempdb laissant 20 Go pour d’autres groupes de charge de travail. En même temps, vous évitez les abandons de requête inutiles lorsque l’espace libre tempdb est toujours disponible, car les groupes de charge de travail A et B ne sont pas susceptibles de consommer une grande quantité d’espace tempdb en même temps.

  De même, la somme des valeurs de GROUP_MAX_TEMPDB_DATA_PERCENT pour tous les groupes de charge de travail peut dépasser 100 %. Vous pouvez allouer davantage tempdb d’espace à chaque groupe si vous savez que plusieurs groupes sont peu susceptibles d’entraîner une utilisation élevée tempdb en même temps.

Examples

Afficher la configuration du fichier de données tempdb

La requête suivante montre la configuration actuelle tempdb du fichier de données :

SELECT file_id,
       name,
       size * 8. / 1024 AS size_mb,
       IIF (max_size = -1, NULL, max_size * 8. / 1024) AS maxsize_mb,
       IIF (is_percent_growth = 0, growth * 8. / 1024, NULL) AS filegrowth_mb,
       IIF (is_percent_growth = 1, growth, NULL) AS filegrowth_percent
FROM sys.master_files
WHERE database_id = 2
      AND type_desc = 'ROWS';

Pour un fichier donné dans le jeu de résultats :

• Si la maxsize_mb colonne est NULL, alors MAXSIZE est UNLIMITED.
• Lorsque filegrowth_mb ou filegrowth_percent est égal à zéro, alors FILEGROWTH est égal à zéro.

Afficher les limites d’espace tempdb effectives par groupe de charge de travail

La requête suivante montre la limite effective tempdb de consommation d’espace pour chaque groupe de charge de travail. La limite est retournée en mégaoctets pour la configuration de limite fixe ou de pourcentage .

Si la group_effective_limit_mb colonne est NULL, cela signifie l’une des opérations suivantes :

• Aucune limite fixe ni de pourcentage n’est configurée.
• Les exigences relatives à l’utilisation de la configuration de limite de pourcentage ne sont pas remplies.

SELECT wg.group_id,
       wg.name,
       tf.tempdb_max_size_mb,
       CASE
           WHEN wg.group_max_tempdb_data_mb IS NOT NULL
               THEN wg.group_max_tempdb_data_mb
           WHEN wg.group_max_tempdb_data_percent IS NOT NULL AND tf.tempdb_max_size_mb IS NOT NULL
               THEN 0.01 * wg.group_max_tempdb_data_percent * tf.tempdb_max_size_mb
       ELSE NULL END AS group_effective_limit_mb
FROM sys.resource_governor_workload_groups AS wg
CROSS APPLY (
    SELECT IIF (SUM(IIF (max_size <> -1
        AND growth > 0, 1, 0)) = COUNT(1) /* autogrow up to the maxsize */
        OR SUM(IIF (max_size = -1
        AND growth = 0, 1, 0)) = COUNT(1), /* pregrown and fixed */
        SUM(IIF (growth = 0, size, max_size)) * 8 / 1024., NULL) AS tempdb_max_size_mb
    FROM sys.master_files
    WHERE database_id = 2
        AND type_desc = 'ROWS'
) AS tf;

Étape suivante

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

Contenu connexe

• Gouverneur de ressources
• didacticiel : Exemples de configuration Resource Governor et meilleures pratiques
• ALTER RESOURCE GOVERNOR (Transact-SQL)
• CREATE WORKLOAD GROUP (Transact-SQL)
• ALTER WORKLOAD GROUP (Transact-SQL)
• DROP WORKLOAD GROUP (Transact-SQL)
• sys.resource_governor_workload_groups (Transact-SQL)
• sys.dm_resource_governor_workload_groups (Transact-SQL)