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

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 qui exécute 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 des ressources tempdb . En outre, le type d’attente est PAGELATCH_UP, et la ressource d’attente pointe vers les pages dans tempdb. Ces pages peuvent être au format 2:1:1, 2:1:3, etc. (pages PFS et SGAM dans tempdb).

Remarque

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 (locales ou globales).
  • 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 pouvez rencontrer une contention lorsqu’elle tente d’allouer des pages. Selon le degré de contention, les requêtes et requêtes qui impliquent tempdb ne répondent pas brièvement.

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 est destinée à la carte d’allocation d’index (IAM) et la seconde à la première page de l’objet. SQL Server effectue le suivi des extensions mixtes à l’aide de la page SGAM (Shared Global Allocation Map). Chaque page SGAM effectue le suivi d’environ 4 gigaoctets de données.

Pour allouer une page à partir de l’étendue mixte, SQL Server devez 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. Une synchronisation appropriée est maintenue pour apporter des modifications aux pages PFS et SGAM ; et qui peut bloquer d’autres modificateurs pendant de courtes périodes.

Lorsque SQL Server recherche une page mixte à allouer, elle 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 .

Remarque

Les activités de désallocation 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 ayant un dimensionnement égal

Par exemple, si la taille du 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 maintenir le dimensionnement égal) et de laisser le fichier journal tel qu’il est. Le fait d’avoir les différents fichiers de données sur des disques distincts serait un avantage supplémentaire en matière de performances. Toutefois, cela n’est pas obligatoire. 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. À titre 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 gamme, le nombre de départ peut être de 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 un dimensionnement égal des fichiers de données. SQL Server Service Pack 4 (SP4) 2000 a introduit un correctif qui utilise un algorithme de tourniquet (round robin) 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 (s’il existe plusieurs fichiers). Le nouvel algorithme d’allocation pour SGAM est un tourniquet (round robin) 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 de la même taille.

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

La liste suivante explique comment l’augmentation du nombre de fichiers de données tempdb ayant 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 extension à la fois (huit pages contiguës) à partir du nombre de fichiers en tourniquet (round robin) tout en respectant le remplissage proportionnel. Par conséquent, si vous avez 10 fichiers de taille égale, la première allocation est à partir de File1, la seconde de File2, la troisième de File3, et ainsi de suite.

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

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

Remarque

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

La liste suivante explique comment l’utilisation de 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 pour 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 page unique sur le serveur.
  • En désactivant la plupart des allocations de page unique, 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 seule page à 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 page unique de la page SGAM, même si -T1118est activé. Toutefois, lorsqu’il est combiné avec le correctif logiciel 8.00.0702 et l’augmentation des 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 constater une augmentation de 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 ne nécessite que 8 Ko d’espace, ce qui gaspille 56 Ko de stockage. Toutefois, si le nouvel objet utilise plus de 64 Ko (huit pages) dans sa durée de vie, il n’y a aucun inconvénient à l’indicateur de trace. Par conséquent, dans le pire des cas, SQL Server pouvez allouer sept (7) pages supplémentaires lors de la première allocation uniquement pour les nouveaux objets qui ne dépassent jamais une (1) page.

References