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

  • Article

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

Version du produit d’origine : SQL Server
Numéro de la 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 subit 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 des pages dans tempdb. Ces pages peuvent être au format 2 :1 :1, 2 :1 :3, et ainsi de suite (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 peuvent rencontrer des conflits lorsqu’il tente d’allouer des pages. En fonction du degré de contention, les requêtes et les demandes 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 au mappage d’allocation d’index (IAM) et la seconde à la première page de l’objet. SQL Server effectue le suivi des étendues mixtes à l’aide de la page Shared Global Allocation Map (SGAM). Chaque page SGAM suit environ 4 gigaoctets de données.

Pour allouer une page à partir de l’étendue mixte, SQL Server devez analyser la page PFS (Page Free Space) 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 effectue le suivi d’environ 8 000 pages. Une 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, l’analyse démarre toujours 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

  • SQL Server 2016 et versions ultérieures :

    Révision

    Pour plus d’informations sur ces recommandations et d’autres modifications introduites dans SQL 2016, consultez

  • SQL Server 2014 et versions antérieures :

    Pour améliorer la concurrence de tempdb, essayez les méthodes suivantes :

    • Augmentez le nombre de fichiers de données dans tempdb pour optimiser la bande passante du disque et réduire la contention dans les structures d’allocation. En règle générale, si le nombre de processeurs logiques est inférieur ou égal à huit (8), utilisez le même nombre de fichiers de données que les processeurs logiques. Si le nombre de processeurs logiques est supérieur à huit (8), utilisez huit fichiers de données. Si la contention continue, augmentez le nombre de fichiers de données par des multiples de quatre (4) jusqu’à atteindre le nombre de processeurs logiques jusqu’à ce que la contention soit réduite à des niveaux acceptables. Vous pouvez également apporter des modifications à la charge de travail ou au code.

    • Envisagez d’implémenter les recommandations de bonnes pratiques dans Utilisation de tempdb dans SQL Server 2005.

    • Si les étapes précédentes ne réduisent pas considérablement la contention d’allocation et que la contention se trouve sur les pages SGAM, implémentez l’indicateur de trace -T1118. Sous cet indicateur de trace, SQL Server alloue des étendues complètes à chaque objet de base de données, éliminant ainsi la contention sur les pages SGAM.

      Remarque

Augmenter le nombre de fichiers de données tempdb dont le dimensionnement est é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 un 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 offre 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. Comme point de départ, vous pouvez configurer tempdb pour qu’il soit au moins égal au nombre de processeurs logiques affectés à SQL Server. Pour les systèmes haut de terminaison, le nombre 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 un dimensionnement égal des fichiers de données. SQL Server 2000 Service Pack 4 (SP4) 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épart est différent pour chaque allocation de page mixte consécutive (s’il en existe plusieurs). Le nouvel algorithme d’allocation pour SGAM est un tourniquet (round robin) pur et ne respecte pas le remplissage proportionnel pour maintenir la vitesse. Nous vous recommandons de créer tous les fichiers de données tempdb avec 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 dont le dimensionnement est é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 qui ont les mêmes tailles pour tempdb crée effectivement 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 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 provient 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 d’une seule page 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 étendue) à un objet, par opposition à une seule page d’une étendue pour les huit (8) premières 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 -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 les problèmes d’espace, consultez la section suivante.

Inconvénients

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

  • Les 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, gaspillant ainsi 56 Ko de stockage. Toutefois, si le nouvel objet utilise plus de 64 Ko (huit pages) pendant 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 peut 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