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 la ko d’origine :   2154845

Symptômes

Sur un serveur en cours d’Microsoft SQL Server, vous remarquez un blocage grave lorsque le serveur rencontre une charge importante. Dynamic Management Views [ ou ] indique que ces demandes ou tâches sont en attente sys.dm_exec_request sys.dm_os_waiting_tasks de ressources tempdb. En outre, le type d’attente PAGELATCH_UP est , 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).

Notes

Si une page est 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 tempdb de manière intensive :

  • Opération répétitive de création et de dépose de tables temporaires (locales ou globales).
  • Tableau des variables qui utilisent tempdb pour le stockage.
  • Tables de travail associées aux CURSEurs.
  • Tables de travail associées à une clause ORDER BY.
  • Tables de travail associées à une clause GROUP BY.
  • Fichiers de travail associés à DES PLANS DE HACHAGE.

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

Cause

Lorsque la base de données tempdb est très utilisée, SQL Server peut être en conflit lorsqu’elle tente d’allouer des pages. Selon le degré de contention, les requêtes et les requêtes qui impliquent tempdb risquent de ne pas répondre 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 pour la carte d’allocation d’index (IAM) et la seconde pour la première page pour l’objet. SQL Server suit les é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 d’espace libre de page (PFS) pour déterminer quelle page mixte est libre à allouer. La page PFS assure le suivi de l’espace 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 peut bloquée 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 un conflit 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.

Notes

Les activités de dé allocation doivent également modifier les pages. Cela peut contribuer à augmenter 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

    • Optimisation des performances de base de données tempdb dans SQL Server.

    • TEMPDB : fichiers et indicateurs de suivi et mises à jour, Oh My!

    • Appliquez la mise à jour SQL Server 2016 et 2017 pour tirer parti de la mise à jour suivante. Une amélioration a été apportée afin de réduire davantage la contention dans SQL Server 2016 et SQL Server 2017. Outre l’allocation round robin sur tous les fichiers de données tempdb, le correctif améliore l’allocation de page PFS en faisant des allocations round robin sur plusieurs pages PFS dans le même fichier de données. Pour plus d’informations, voir KB4099472 - Amélioration de l’algorithme de tour robin de page PFS dans SQL Server 2014, 2016 et 2017.

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

  • 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 persiste, augmentez le nombre de fichiers de données par multiples de quatre (4) jusqu’au 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 des meilleures pratiques dans Working with 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 suivi -T1118. Sous cet indicateur de suivi, SQL Server alloue des étendues complètes à chaque objet de base de données, éliminant ainsi la contention sur les pages SGAM.

      Notes

      • Cet indicateur de suivi affecte chaque base de données sur l’instance de SQL Server. Pour plus d’informations sur la façon de déterminer si la contention d’allocation se trouve sur les pages SGAM, voir la contention de surveillance causée par les opérations DML.

      • Pour SQL Server environnements 2014, veillez à appliquer le Service Pack 3 pour tirer parti du correctif documenté dans l’article de la Ko suivant. Cette amélioration réduit davantage la contention dans SQL Server environnements 2014. Outre l’allocation round robin sur tous les fichiers de données tempdb, le correctif améliore l’allocation de page PFS en faisant des allocations round robin sur plusieurs pages PFS dans le même fichier de données.

        KB4099472 - Amélioration de l’algorithme de tour robin de page PFS SQL Server 2014, 2016 et 2017

      • Blog de l’équipe MSSQLQl : Fichiers et indicateurs de suivi et mises à jour dans SQL Server tempdb

Augmenter le nombre de fichiers de données tempdb dont le taille est égal

Par exemple, si la taille du fichier de données unique 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 d’entre eux de 1 Go pour maintenir un dimensionnement égal) et de laisser le fichier journal tel quelle. Le fait d’avoir les différents fichiers de données sur des disques distincts serait plus avantageux en terme 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 de niveau supérieur, le nombre de départ peut être huit (8). Si la contention n’est pas réduite, vous de devez peut-être augmenter le nombre de fichiers de données.

Nous vous recommandons d’utiliser le même resserrage des fichiers de données. SQL Server 2000 Service Pack 4 (SP4) a introduit un correctif qui utilise un algorithme 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 existe plusieurs fichiers). Le nouvel algorithme d’allocation pour SGAM est un tourn 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 dont le taille 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 mode round robin tout en respectant le remplissage proportionnel. Par conséquent, si vous avez 10 fichiers de taille égale, la première allocation est de File1, la seconde de File2, la troisième de File3, etc.

  • 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 suivi -T1118 réduit la contention

Notes

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

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

  • -T1118 est un paramètre à l’échelle du serveur.
  • Incluez l’indicateur de suivi -T1118 dans les paramètres de démarrage de SQL Server afin que l’indicateur de suivi reste en vigueur même après SQL Server recyclage.
  • -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 allumé, presque toutes les nouvelles allocations sont réalisées à partir d’une page GAM (par exemple, 2:1:2) qui alloue huit (8) pages (une étendue) à la fois à un objet, par opposition à une seule page à partir d’une étendue pour les huit (8) premières pages d’un objet, sans l’indicateur de suivi.
  • Les pages IAM utilisent toujours les allocations de page unique de la page SGAM, même si -T1118 est allumé. Toutefois, lorsqu’elle est combinée avec le correctif 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 vous pouvez constater une augmentation de la taille de la base de données si les conditions suivantes sont vraies :

  • 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 vraies, vous pouvez allouer 64 Ko (huit pages * 8 Ko = 64 Ko) pour un objet qui ne requiert que 8 Ko d’espace, ce qui vous permet de perdre 56 Ko de stockage. Toutefois, si le nouvel objet utilise plus de 64 Ko (huit pages) au cours de sa durée de vie, l’indicateur de suivi n’a aucun inconvénient. 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.

Références