[SQL Server] TempDB: identification de problèmes de contention sur les structures d’allocation

Ce billet offre quelques astuces pour détecter des éléments de contention sur des structures d’allocation de tempDB. Nous aborderons également le cas des page latches.

Pour connaître l’utilité de tempDB et les éléments d’optimisation usuels, allez ici.

Qu’est-ce qu’un problème de contention ?

Quand tempDB est surutilisée, SQL Server peut rencontrer des contentions quand il est amené à procéder à l’allocation de pages de données. Ce phénomène intervient quand plusieurs sessions d’une même base de données (ici, tempDB) se retrouvent en conflit lors de l’accès aux mêmes métadonnées stockées dans des pages d’allocation système (PFS, GAM, SGAM). Pour comprendre la signification et le fonctionnement des différentes pages d’allocation, vous pouvez jeter un coup d’œil ici. Grosso-modo :

  • GAM (Global Allocation Map) : capture les informations relatives aux extents alloués. Chaque espace GAM couvre 64 000 extents, soit près de 4 Go de données.
  • SGAM (Shared Global Allocation Map): capture les informations relatives aux extents mixtes alloués, et avec au moins une page inutilisée. Chaque espace GAM couvre 64 000 extents, soit près de 4 Go de données.
  • PFS (Page Free Space) : capture les informations d’allocation de chaque page, à savoir : quelles pages ont été allouées et quelle est la quantité d’espace libre sur chacune d’elle ? Chaque page PFS récupère 64 Mo de données.

Identification des contentions avec…

… le Perfmon

Le Perfmon (Performance Monitor) offre des compteurs utiles permettant d’auditer les contentions potentielles relatives aux allocation/désallocation au sein de SQL Server :

Compteur Description
SQL Server:Access Methods:Worktables Created/sec Indique le nombre de tables de travail créées par seconde. La valeur doit être généralement inférieure à 200 (suivant les performances matérielles).
On rappelle que les tables de travail sont des objets temporaires utilisés par SQL Server pour y stocker des curseurs, des résultats intermédiaires de requêtes,…
SQL Server:Access Methods:Workfiles Created/sec Indique le nombre de tables de travail créées par seconde. La valeur ne doit pas être fréquemment élevée.
On rappelle que les fichiers de travail sont des objets temporaires créés par SQL Server durant des opérations de hachage, et sont utilisés pour y stocker des résultats temporaires pour le hachage.
SQL Server:General Statistics:Temp Tables Creation Rate Indique le nombre de tables temporaires (ou variables) créées par seconde. Une valeur fréquemment trop élevée peut souligner une présence potentielle de contentions.
SQL Server:General Statistics:Temp Tables For Destruction Indique le nombre de tables temporaires (ou variables) en attente de suppression. La valeur doit être régulièrement proche de zéro.

… les DMV pour l’analyse des latches

Les latches (ou loquets, ou verrous internes « légers ») sont des objets de synchronisation permettant l’accès en mémoire à n’importe quelle partie d’un fichier de données ou de logs. Il existe principalement 5 types de page latches (à ne pas confondre avec les page IO latches basés sur les accès I/O):

  • PAGELATCH_UP : type d’attente relatif au verrouillage de page de mémoire tampon (buffer) pour mise-à-jour (UPdate mode).
  • PAGELATCH_SH : type d’attente relatif au verrouillage de page de mémoire tampon pour accès partagé (SHare mode).
  • PAGELATCH_KP : type d’attente relatif au conservation du latch de la page (KeeP mode).
  • PAGELATCH_EX : type d’attente relatif au verrouillage exclusif de page de mémoire tampon (EXclusive mode).
  • PAGELATCH_DT : type d’attente relatif au déverrouillage de page de mémoire tampon et destruction du latch associé (DeleTe mode).

Avec les DMV (Dynamic Management Views), il est possible de procéder à une identification de contentions potentielles grâce à l’analyse des tâches d’attente via sys.dm.os_waiting_tasks en ne prenant en compte que les attentes de type PAGELATCH :

SELECT session_id,
   wait_type,
   wait_duration_ms,
   blocking_session_id,
   resource_description,
   CASE
     WHEN CAST(RIGHT(resource_description, LEN(resource_description) - CHARINDEX(':', resource_description, 3)) AS Int) - 1 % 8088 = 0 THEN 'Page PFS'
     WHEN CAST (RIGHT(resource_description, LEN(resource_description) - CHARINDEX(':', resource_description, 3)) AS Int) - 2 % 511232 = 0 THEN 'Page GAM'
     WHEN CAST (RIGHT(resource_description, LEN(resource_description) - CHARINDEX(':', resource_description, 3)) AS Int) - 3 % 511232 = 0 THEN 'Page SGAM'
     ELSE 'Pas une page PFS, GAM ou SGAM'
   END resource_type
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH_%' AND resource_description LIKE '2:%'
GO

Cette requête est à exécuter de façon régulière (toutes les 5 ou 10 minutes, par exemple). En effet, s’il y a au moins une ligne de résultat qui s’affiche fréquemment sur une période de plusieurs minutes, c’est qu’il y a une présence potentielle d’un problème de contention (et principalement s’il s’agit de PAGELATCH_UP). A noter que si une ligne de la colonne resource_description (« 2:x:y », où « « 2 » est le numéro de la base de données tempDB,
« x » est le numéro du fichier de données et « y » celui de la page cible)
:

  • Est égale à « 2:x:1 », il y a contention sur l’espace PFS.
  • Est égale à « 2:x:2 », il y a contention sur l’espace GAM.
  • Est égale à « 2:x:3 », il y a contention sur l’espace SGAM.

Si vous souhaitez creuser un peu plus, vous pouvez lancer la requête suivante afin d’avoir une idée des objets concernés :

SELECT p.object_id,
   object_name(p.object_id) AS object_name,
   p.index_id,
   obd.page_type
FROM sys.dm_os_buffer_descriptors obd, sys.allocation_units au, sys.partitions p
WHERE obd.allocation_unit_id = A.allocation_unit_id AND au.container_id =p.partition_id
GO

Résolution de problèmes de contention

En règle générale, pour résoudre les problèmes de contention de tempDB, les bonnes pratiques suivantes sont à honorer (voir également ici):

  • Placer tempDB sur un LUN dédié favorisant les accès en écriture (RAID1 ou RAID10).
  • Créer des fichiers de données multiples pour tempDB (et de même taille). En l’occurrence, un nombre de fichiers égal à de ¼ à ½ du nombre de CPUs.
  • La règle très connue du « 1 fichier de données par CPU » n’est plus d’actualité à partir de SQL Server 2005.
  • Concernant l’activation du traceflag –T1118, cela n’est plus une nécessité absolue à partir de SQL Server 2005, mais peut toujours être utile si les contentions persistent malgré l’application des recommandations précédentes. Plus de détails ici : http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/.
Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s