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

S’applique à : SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express

Symptômes


Sur un serveur exécutant Microsoft SQL Server, vous remarquez que le blocage est important lorsque le serveur rencontre une charge importante. Affichages de gestion dynamique [sys. dm_exec_request ou sys. dm_os_waiting_tasks] indique que ces requêtes ou tâches attendent des ressources tempdb . Par ailleurs, le type d’attente est PAGELATCH_UP et la ressource d’attente pointe sur les pages de la page de tempdb. Il peut s’agir du 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 une PFS dans file_id = 3 dans tempdb.
Les opérations suivantes utilisent de manière intensive tempdb :
  • Repetative de création-suppression de tables temporaires (locales ou globales).
  • Variables de table utilisant tempdb pour le stockage.
  • Tables de tâches associées aux CURSEURs.
  • Tables de tâches associées à une clause ORDER BY.
  • Tables de tâches associées à une clause GROUP BY.
  • Fichiers professionnels associés aux PLANS de hachage.
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 faire l’d’une contention lors de la tentative d’allocation de pages. En fonction du degré de contention, il est possible que des requêtes et des requêtes impliquant tempdb n’aient pas de réponse brièvement.
Lors de la création d’objets, deux (2) pages doivent être allouées à partir d’une étendue mixte et affectées au nouvel objet. Une page correspond au plan d’allocation d’index (IAM) et le second à la première page de l’objet. SQL Server effectue le suivi d’extensions mélangées à l’aide de la page SGAM (Global Allocation Map) partagée. Chaque page SGAM effectue le suivi de 4 gigaoctets de données.Pour allouer une page à partir de l’étendue mixte, SQL Server doit analyser la page de l’espace disponible (PFS) de la page pour déterminer quelle page mixte est libre d’être allouée. La page PFS assure le suivi de l’espace libre disponible sur chaque page et chaque page PFS suit les pages 8000. La synchronisation appropriée est assurée pour apporter des modifications aux pages PFS et SGAM ; et cela peut bloquer d’autres modificateurs pour des périodes courtes.Lorsque SQL Server effectue une recherche sur une page mixte à allouer, il démarre toujours l’analyse sur le même fichier et la page SGAM. Cela entraîne une contention intense sur la page SGAM lorsque plusieurs allocations de pages mixtes sont en cours d’opération. Cela peut entraîner les problèmes décrits dans la section « symptômes ».Remarques Les activités de dédistribution doivent également modifier les pages. Cela peut contribuer à la contention croissante.Pour en savoir plus sur les différents mécanismes d’allocation utilisés par SQL Server (SGAM, GAM, PFS, IAM), voir la section « Références ».

Résolution


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 et réduire les contenus 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 le contenu est toujours en conflit, augmentez le nombre de fichiers de données à l’aide des multiples de quatre (4) jusqu’au nombre de processeurs logiques jusqu’à ce que le contenu soit ramené à des niveaux acceptables. Vous pouvez également modifier la charge de travail ou le code.
  • Envisagez d’implémenter les recommandations recommandées dans le sujet TechNet suivant :  
  • Si les étapes ci-dessus ne réduisent pas de façon significative la contention d’allocation et que le contenu se trouve sur les pages SGAM, implémentez l’indicateur de suivi-T1118. Sous cet indicateur de suivi, SQL Server alloue toutes les extensions à chaque objet de base de données, ce qui supprime la contention sur les pages SGAM. Remarque  
  • Appliquez le CU approprié pour SQL Server 2016 et 2017 pour tirer parti de la mise à jour récente. Une amélioration a été apportée afin de réduire davantage la contention dans SQL Server 2016 et SQL Server 2017. En plus de l’attribution de la répétition circulaire sur tous les fichiers de données tempdb , le correctif améliore l’allocation de la page PFS en effectuant des attributions de tourniquet sur plusieurs pages PFS dans le même fichier de données. Pour plus d’informations, consultez le contenu suivant :  

Informations supplémentaires


Augmenter le nombre de fichiers de données tempdb ayant une taille égale

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 conseillé 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 de disposer des différents fichiers de données sur des disques séparés peut offrir des avantages supplémentaires en matière de performances. Toutefois, cela n’est pas obligatoire. Les fichiers peuvent cohabiter sur le même volume de disque. Le nombre optimal de fichiers de données tempdb dépend du degré de contention affiché dans tempdb. Comme point de départ, vous pouvez configurer tempdbêtre au moins égal au nombre de processeurs logiques attribués pour SQL Server. Pour les systèmes de haut-parleurs, le numéro de départ peut être de huit (8). Si le contenu n’est pas réduit, il est possible que vous deviez 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 des allocations de pages mélangées. En raison de cette amélioration, le fichier de départ est différent pour chaque allocation de page mixte consécutif (s’il existe plus d’un fichier). Le nouvel algorithme d’allocation pour la fonction SGAM est la répétition circulaire pure et ne respecte pas le remplissage proportionnel pour la vitesse de conservation. Nous vous recommandons de créer tous les tempdbfichiers de données tempdb de la même taille.  

Comment augmenter le nombre de fichiers de données tempdb réduit le contenu

La liste suivante décrit l’augmentation du nombre de fichiers de tempdb données tempdb dont le dimensionnement est égal réduit :
  • Si vous avez un fichier de données pour tempdb, vous n’avez qu’une seule page GAM et une page SGAM pour chaque 4 Go d’espace.
  • Augmenter le 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 la fonction GAM alloue une extension à la fois (huit pages contiguës) à partir du nombre de fichiers de manière alternée en respectant le remplissage proportionnel. Par conséquent, si vous avez 10 fichiers de taille égale, le premier taux d’utilisation est de fichier1, le second du fichier2, le troisième de file3, etc.
  • Le contenu des ressources de la page PFS est réduit, car huit pages à la fois sont marquées comme complètes, car la GAM alloue les pages.
 

Mise en œuvre de l’indicateur de suivi-T1118 réduction de la contention

La liste suivante décrit l’utilisation de l’indicateur de trace-T1118 réduit le contenu :
  • -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 de façon à ce 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 uniques, vous réduisez le contenu de la page SGAM.
  • Si -T1118 est activé, la plupart des nouvelles attributions sont effectuées à partir d’une page GAM (par exemple, 2:1:2), qui alloue huit (8) pages (1 extension) à un objet plutôt qu’une seule page à partir d’une extension pour les huit premières pages d’un objet, sans l’indicateur de suivi.
  • Les pages IAM utilisent toujours les allocations de pages uniques à partir de la page SGAM, même si -T1118 est activé. En revanche, lorsqu’il est combiné avec le correctif 8.00.0702 et les fichiers de données tempdb plus importants, l’effet net est une réduction du contenu de la page SGAM. Pour plus d’espace, voir la section suivante.
Liés

Le désavantage de l’utilisation de -T1118 est que vous risquez de voir augmenter la taille de la base de données si les conditions suivantes sont remplies :

  • Les nouveaux objets sont créés dans une base de données utilisateur.
  • Chacun des nouveaux objets est occupé à être inférieur à 64 Ko d’espace de stockage.

Si ces conditions sont vraies, vous pouvez allouer 64 Ko (8 pages * 8 Ko = 64 Ko) pour un objet qui nécessite uniquement 8 Ko d’espace, ce qui a pour effet de gaspiller 56 Ko d’espace de stockage. Toutefois, si le nouvel objet utilise plus de 64 Ko (8 pages) pendant sa durée de vie, il n’y a aucun inconvénient pour l’indicateur de suivi. Par conséquent, dans le pire des cas, SQL Server peut allouer sept (7) pages supplémentaires lors de la première attribution uniquement pour les nouveaux objets qui ne se développent jamais au-delà d’une (1) page.

Références


Pour plus d’informations sur les produits ou outils qui vérifient automatiquement cette condition sur votre instance de SQL Server et sur les versions du produit SQL Server, consultez le tableau suivant.

Logiciel de règle Titre de la règle Description de la règle Versions de produit auxquelles la règle est évaluée
System Center Advisor Configuration de la base de données SQL Server : le nombre de fichiers de données tempdb peut entraîner le blocage System Center Advisor vérifie le nombre de fichiers de données configurés pour la base de données tempdb. S’il existe un seul processeur et que le serveur exécutant SQL Server utilise plusieurs processeurs, cette alerte en cas de génération. Passez en revue les informations contenues dans cet article, puis ajoutez des fichiers de données à la base de données tempdb. SQL Server 2008 SQL Server 2008 R2 SQL Server 2012