Partager via

Recommandations pour réduire la contention d’allocation dans la base de données tempdb de SQL Server

  • Article

Cet article vous aide à résoudre le problème où vous remarquez un blocage grave lorsque le serveur rencontre une charge importante.

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 2154845

Symptômes

Sur un serveur exécutant Microsoft SQL Server, vous remarquez un blocage grave lorsque le serveur rencontre une charge importante. Les vues de gestion dynamique [sys.dm_exec_request ou sys.dm_os_waiting_tasks] indiquent que ces requêtes ou tâches attendent les ressources tempdb . En outre, le type d’attente est PAGELATCH_UP, et la ressource d’attente pointe vers des pages dans tempdb. Ces pages peuvent être au format 2:1:1, 2:1:3, etc. (PAGES PFS et SGAM dans tempdb).

Note

Si une page est uniformément divisible par 8088, il s’agit d’une page PFS. Par exemple, la page 2:3:905856 est un PFS dans file_id=3 dans tempdb.

Les opérations suivantes utilisent largement tempdb :

  • Opération de création et de suppression répétitive de tables temporaires (locale ou globale).
  • Variables de table qui utilisent tempdb pour le stockage.
  • Tables de travail associées à CURSORS.
  • Tables de travail associées à une clause ORDER BY.
  • Tables de travail associées à une clause GROUP BY.
  • Fichiers de travail associés à HASH PLANS.

Ces activités peuvent entraîner des problèmes de contention.

Cause

Lorsque la base de données tempdb est fortement utilisée, SQL Server peut rencontrer des conflits lorsqu’il tente d’allouer des pages. En fonction du degré de contention, cela peut entraîner des requêtes et des requêtes qui impliquent tempdb de manière brève sans réponse.

Lors de la création de l’objet, deux (2) pages doivent être allouées à partir d’une étendue mixte et affectées au nouvel objet. Une page concerne la carte d’allocation d’index (IAM), et la seconde concerne la première page de l’objet. SQL Server effectue le suivi des étendues mixtes à l’aide de la page SGAM (Shared Global Allocation Map). Chaque page SGAM suit environ 4 gigaoctets de données.

Pour allouer une page à partir de l’étendue mixte, SQL Server doit analyser la page Espace libre de la page (PFS) pour déterminer quelle page mixte est libre d’être allouée. La page PFS effectue le suivi de l’espace libre disponible sur chaque page, et chaque page PFS suit environ 8 000 pages. La synchronisation appropriée est conservée pour apporter des modifications aux pages PFS et SGAM ; et qui peuvent bloquer d’autres modificateurs pendant de courtes périodes.

Lorsque SQL Server recherche une page mixte à allouer, il démarre toujours l’analyse sur le même fichier et la même page SGAM. Cela provoque une contention intense sur la page SGAM lorsque plusieurs allocations de pages mixtes sont en cours. Cela peut entraîner les problèmes documentés dans la section Symptômes .

Note

Les activités de désaffecter doivent également modifier les pages. Cela peut contribuer à l’augmentation de la contention.

Pour en savoir plus sur les différents mécanismes d’allocation utilisés par SQL Server (SGAM, GAM, PFS, IAM), consultez la section Références .

Résolution

Augmenter le nombre de fichiers de données tempdb qui ont un dimensionnement égal

Par exemple, si la taille de fichier de données unique de tempdb est de 8 Go et que la taille du fichier journal est de 2 Go, il est recommandé d’augmenter le nombre de fichiers de données à huit (8) (chacun de 1 Go pour conserver le dimensionnement égal) et de laisser le fichier journal tel quel. Le fait d’avoir les différents fichiers de données sur des disques distincts offre des avantages supplémentaires en matière de performances. Cependant, cette condition n'est pas requise. Les fichiers peuvent coexister sur le même volume de disque.

Le nombre optimal de fichiers de données tempdb dépend du degré de contention observé dans tempdb. En guise de point de départ, vous pouvez configurer tempdb pour qu’il soit au moins égal au nombre de processeurs logiques affectés pour SQL Server. Pour les systèmes haut de terminaison, le numéro de départ peut être huit (8). Si la contention n’est pas réduite, vous devrez peut-être augmenter le nombre de fichiers de données.

Nous vous recommandons d’utiliser le dimensionnement égal des fichiers de données. SQL Server 2000 Service Pack 4 (SP4) a introduit un correctif qui utilise un algorithme de tourniquet pour les allocations de pages mixtes. En raison de cette amélioration, le fichier de démarrage est différent pour chaque allocation de page mixte consécutive (si plusieurs fichiers existent). Le nouvel algorithme d’allocation pour SGAM est un tourniquet pur et n’honore pas le remplissage proportionnel pour maintenir la vitesse. Nous vous recommandons de créer tous les fichiers de données tempdb à la même taille.

L’augmentation du nombre de fichiers de données tempdb réduit la contention

La liste suivante explique comment augmenter le nombre de fichiers de données tempdb qui ont un dimensionnement égal réduit la contention :

  • Si vous avez un fichier de données pour tempdb, vous n’avez qu’une seule page GAM et une page SGAM pour chaque espace de 4 Go.

  • L’augmentation du nombre de fichiers de données ayant les mêmes tailles pour tempdb crée efficacement une ou plusieurs pages GAM et SGAM pour chaque fichier de données.

  • L’algorithme d’allocation pour GAM alloue une étendue à la fois (huit pages contiguës) à partir du nombre de fichiers d’une mode tourniquet tout en respectant le remplissage proportionnel. Par conséquent, si vous avez 10 fichiers de taille égale, la première allocation provient de File1, la deuxième de File2, la troisième de File3, et ainsi de suite.

  • La contention des ressources de la page PFS est réduite, car huit pages à la fois sont marquées comme FULL, car GAM alloue les pages.

Comment l’implémentation de l’indicateur de trace -T1118 réduit la contention

Note

Cette section s’applique uniquement à SQL Server 2014 et aux versions antérieures.

La liste suivante explique comment utiliser l’indicateur de trace -T1118 réduit la contention :

  • -T1118 est un paramètre à l’échelle du serveur.
  • Incluez l’indicateur de trace -T1118 dans les paramètres de démarrage de SQL Server afin que l’indicateur de trace reste en vigueur même après le recyclage de SQL Server.
  • -T1118 supprime presque toutes les allocations de pages uniques sur le serveur.
  • En désactivant la plupart des allocations de pages uniques, vous réduisez la contention sur la page SGAM.
  • Si -T1118 est activé, presque toutes les nouvelles allocations sont effectuées à partir d’une page GAM (par exemple, 2:1:2) qui alloue huit (8) pages (une extension) à la fois à un objet par opposition à une page unique à partir d’une étendue pour les huit premières (8) pages d’un objet, sans l’indicateur de trace.
  • Les pages IAM utilisent toujours les allocations de pages uniques de la page SGAM, même si -T1118 est activé. Toutefois, lorsqu’il est combiné avec le correctif logiciel 8.00.0702 et augmente les fichiers de données tempdb , l’effet net est une réduction de la contention sur la page SGAM. Pour connaître les problèmes d’espace, consultez la section suivante.

Inconvénients

L’inconvénient de l’utilisation de -T1118 est que vous pouvez voir augmenter la taille de la base de données si les conditions suivantes sont remplies :

  • De nouveaux objets sont créés dans une base de données utilisateur.
  • Chacun des nouveaux objets occupe moins de 64 Ko de stockage.

Si ces conditions sont remplies, vous pouvez allouer 64 Ko (huit pages * 8 Ko = 64 Ko) pour un objet qui nécessite seulement 8 Ko d’espace, ce qui a pour conséquence de perdre 56 Ko de stockage. Toutefois, si le nouvel objet utilise plus de 64 Ko (huit pages) dans sa durée de vie, il n’existe aucun inconvénient à l’indicateur de trace. Par conséquent, dans le pire des cas, SQL Server peut allouer sept (7) pages supplémentaires pendant la première allocation uniquement pour les nouveaux objets qui ne dépassent jamais une page (1).

References