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.

À partir de SQL Server 2025 (17.x), vous pouvez utiliser le gouverneur de ressources pour appliquer une limite à la quantité totale d’espace consommé par un groupe de charge de tempdb travail. Un groupe de charge de travail peut être associé à une application, un utilisateur, un groupe d’utilisateurs, etc. Lorsqu’une requête (requête) tente de dépasser la limite, resource governor l’abandonne avec une erreur distincte indiquant que la limite du groupe de charge de travail a été appliqué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 de Resource Governor

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 .

  La limite fixe est utile lorsque les exigences d’utilisation de la charge de travail tempdb sont connues à l’avance ou lorsque tempdb la taille ne change pas.

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

  La limite de pourcentage est utile lorsque vous pourriez modifier la taille maximale de tempdb avec le temps et que vous souhaitez que l’espace tempdb disponible pour chaque groupe de charge de travail change proportionnellement sans reconfigurer le Resource Governor. 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 les limites fixes et de pourcentage sont spécifiées 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.

Configuration de limite de pourcentage

Les limites de pourcentage sont en vigueur uniquement lorsque la configuration du tempdb fichier de données répond aux exigences résumées dans le tableau suivant :

La requête suivante vous permet de voir 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.

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 un message d’avertissement 10989, gravité 10, GROUP_MAX_TEMPDB_DATA_PERCENT n’est pas en vigueur, car les exigences de configuration tempdb ne sont pas remplies. Le message est également consigné dans le journal des erreurs.

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 SIZE, FILEGROWTHet , consultez MAXSIZEles options de fichier et de groupe de fichiers ALTER DATABASE.

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%).

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 gouverneur 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, Impossible d’allouer une nouvelle page pour la base de données « tempdb », car cela dépasserait la limite définie pour le groupe de charge de travail « 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.

• Resource Governor 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. À moins que vous n’augmentiez au préalable la taille des fichiers de données tempdb à la taille souhaitée, l’espace sur le ou les volumes où tempdb est situé peut être consommé 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

La 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 fournies par cette vue DMV peuvent ne pas correspondre exactement aux statistiques fournies dans la vue 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 sont sys.dm_resource_governor_workload_groups mises à jour en continu.
  • Ne suit pas les pages du plan de répartition des index (IAM). Pour plus d’informations, consultez le guide de l'architecture des pages et des étendues.
• Une fois les lignes supprimées ou lorsqu’une table, un index ou une partition sont supprimés ou tronqués, les pages de données peuvent être libérées par un processus d’arrière-plan asynchrone. Cette désallocation de page peut se produire avec un délai. 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 le faites, 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).

• À moins que vous n’ayez créé des groupes de charges de travail personnalisés et une fonction de classification qui place les charges de travail dans leurs groupes dédiés, évitez de limiter l’utilisation de tempdb par le groupe de charges de travail default. Cela peut abandonner les requêtes avec l'erreur 1138 lorsque tempdb possède encore de l'espace inutilisé qui ne peut être utilisé par aucune charge de travail utilisateur.

• La somme des valeurs pour tous les groupes de travail GROUP_MAX_TEMPDB_DATA_MB 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.

Contenu connexe

• Tutoriel : Exemples de configuration de la gouvernance des ressources d’espace tempdb
• Gouverneur de ressources
• didacticiel : Exemples de configuration Resource Governor et meilleures pratiques
• MODIFIER LE GOUVERNEUR DE RESSOURCES
• CRÉER UN GROUPE DE CHARGES DE TRAVAIL
• MODIFIER LE GROUPE DE CHARGE DE TRAVAIL
• ANNULER LE GROUPE DE CHARGE DE TRAVAIL
• sys.resource_governor_workload_groups
• sys.dm_resource_governor_workload_groups