Diagnostiquer et résoudre la contention de verrouillage tournant dans SQL Server

Cet article fournit des informations détaillées sur la façon d’identifier et de résoudre les problèmes liés à la contention de verrouillage tournant (« spinlock ») dans les applications SQL Server sur des systèmes à haute concurrence.

Arrière-plan

Par le passé, les ordinateurs Windows Server de base n’utilisaient qu’une ou deux puces microprocesseurs, lesquelles étaient conçues avec un seul processeur ou « cœur ». Des augmentations de la capacité de traitement informatique ont été obtenues à l’aide de processeurs plus rapides, ce qui a permis d’améliorer en grande partie la densité du transistor. Selon la « loi de Moore », la densité des transistors, c’est-à-dire le nombre de transistors pouvant être placés sur un circuit intégré, double tous les deux ans, et ce depuis le développement du premier processeur monopuce universel en 1971. Ces dernières années, l’approche traditionnelle consistant à augmenter la capacité de traitement des ordinateurs avec des processeurs plus rapides a bénéficié de l’apport des ordinateurs multiprocesseurs. À ce stade, l’architecture du processeur Intel Nehalem prend en charge jusqu’à huit cœurs par processeur, qui, lorsqu’elle est utilisée dans un système de huit sockets, peut ensuite être doublée à 128 processeurs logiques à l’aide de la technologie de multithreading simultanée (SMT). Sur les processeurs Intel, SMT est appelé Hyper-Threading. Mais l’augmentation du nombre de processeurs logiques sur les ordinateurs compatibles x86 entraîne une hausse des problèmes de concurrence à mesure que les processeurs logiques se disputent les ressources. Ce guide décrit comment identifier et résoudre les problèmes de contention de ressources particulières observés lors de l’exécution d’applications SQL Server sur des systèmes à haute concurrence avec certaines charges de travail.

Dans cette section, nous analysons les leçons apprises par l’équipe SQLCAT à partir du diagnostic et de la résolution des problèmes de contention de blocage. La contention de verrouillage tournant est un type de problème de concurrence observé dans des charges de travail client réelles sur des systèmes à grande échelle.

Symptômes et causes de la contention de verrouillage tournant

Cette section décrit comment diagnostiquer les problèmes de contention de verrouillage tournant qui nuisent aux performances des applications OLTP sur SQL Server. Le diagnostic et la résolution de ces problèmes sont des sujets avancés qui nécessitent des connaissances sur les outils de débogage et les éléments internes de Windows.

Les verrouillages tournants sont des primitives de synchronisation légères qui permettent de protéger l’accès aux structures de données. Les verrous spinlocks ne sont pas uniques à SQL Server. Le système d’exploitation les utilise quand l’accès à une structure de données n’est nécessaire que pendant une brève période. Si un thread qui tente d’acquérir un verrouillage tournant ne parvient pas à obtenir l’accès, il s’exécute dans une boucle pour vérifier régulièrement si la ressource est disponible au lieu de céder immédiatement le contrôle (« yield »). Après un certain temps, un thread en attente sur un spinlock sera généré avant de pouvoir acquérir la ressource. La cession du contrôle permet à d’autres threads sur le même processeur de s’exécuter. Ce comportement est appelé backoff et est abordé plus en détail plus loin dans cet article.

SQL Server utilise des verrouillages tournants pour protéger l’accès à certaines de ses structures de données internes. De manière similaire aux verrous internes, les verrouillages tournants sont utilisés dans le moteur pour sérialiser l’accès à certaines structures de données. La principale différence entre un verrou et un verrou est le fait que les spinlocks (exécuter une boucle) pendant une période de temps case activée pour la disponibilité d’une structure de données pendant qu’un thread qui tente d’acquérir l’accès à une structure protégée par un verrou génère immédiatement si la ressource n’est pas disponible. La cession du contrôle nécessite le changement de contexte d’un thread du processeur pour qu’un autre thread puisse s’exécuter. Il s’agit d’une opération relativement coûteuse et pour les ressources qui sont conservées pendant une courte durée, il est plus efficace globalement de permettre à un thread d’exécuter dans une boucle régulièrement case activée ing pour la disponibilité de la ressource.

Les ajustements internes apportés au Moteur de base de données introduits dans SQL Server 2022 (16.x) rendent les verrous plus efficaces.

Symptômes

Sur n’importe quel système d’accès concurrentiel élevé occupé, il est normal de voir la contention active sur des structures fréquemment sollicitées protégées par des verrous de spinlocks. La situation ne devient problématique que si la contention introduit une surcharge processeur importante. Les statistiques spinlock sont exposées par la sys.dm_os_spinlock_stats vue de gestion dynamique (DMV) dans SQL Server. Par exemple, cette requête produit la sortie suivante :

SELECT * FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

Les statistiques exposées par cette requête sont décrites ci-dessous :

Dans le cadre de cette discussion, les statistiques particulièrement intéressantes sont le nombre de « collisions », de « spins » et de « backoffs » qui se produisent au cours d’une période spécifique quand le système est soumis à une charge importante. Quand un thread tente d’accéder à une ressource protégée par un verrouillage tournant, une collision se produit. Le nombre de collisions est alors incrémenté et le thread commence à tourner en boucle pour vérifier périodiquement si la ressource est disponible. Chaque fois que le thread tourne (effectue une boucle), le nombre de spins est incrémenté.

Les spins par collision sont une mesure de la quantité de spins qui se produisent pendant qu’un spinlock est maintenu par un thread et indique le nombre de spins qui se produisent pendant que les threads tiennent le verrou de spin. Par exemple, les petites rotations par collision et le nombre élevé de collisions signifient qu’il y a une petite quantité de spins qui se produisent sous le verrou de spinlock et qu’il y a de nombreux threads qui y sont confrontés. Un grand nombre de spins signifie que le temps passé à tourner en boucle dans le code du verrouillage tournant est relativement long (autrement dit, le code a affaire à un grand nombre d’entrées dans un compartiment de hachage). À mesure que la contention augmente (et, par voie de conséquence, le nombre de collisions), le nombre de spins augmente également.

Les backoffs sont étroitement liés aux spins. Par conception, pour éviter un gaspillage excessif du processeur, les verrous de rotation ne continuent pas indéfiniment à tourner jusqu’à ce qu’ils puissent accéder à une ressource détenue. Pour vous assurer qu’un spinlock n’utilise pas excessivement les ressources du processeur, les blocages de retour ou arrêtent l’épinglage et le « veille ». Un backoff se produit même si le verrouillage ne devient pas propriétaire de la ressource cible. D’autres threads peuvent ainsi être planifiés sur le processeur dans l’espoir d’autoriser l’exécution de tâches plus productives. Le comportement par défaut du moteur est de tourner pendant un intervalle de temps constant avant d’effectuer un backoff. La tentative d’obtention d’un verrouillage tournant nécessite le maintien d’un état de concurrence du cache, ce qui est une opération gourmande en ressources processeur par rapport à une exécution en boucle. Les tentatives d’obtention d’un verrouillage tournant sont donc effectuées avec modération et ne se produisent pas chaque fois qu’un thread s’exécute en boucle. Dans SQL Server, certains types de spinlocks (par exemple : LOCK_HASH) ont été améliorés en utilisant un intervalle d’augmentation exponentielle entre les tentatives d’acquisition du verrouillage (jusqu’à une certaine limite), ce qui réduit souvent l’effet sur les performances du processeur.

Le diagramme suivant fournit une vue conceptuelle de l’algorithme de verrouillage tournant :

Scénarios classiques

Une contention de verrouillage tournant peut se produire pour diverses raisons qui peuvent n’avoir aucun lien avec les décisions de conception de base de données. Étant donné que les verrous de spinlocks contrôlent l’accès aux structures de données internes, la contention de verrouillage de spinlock n’est pas manifeste de la même façon que la contention de latch de mémoire tampon, qui est directement affectée par les choix de conception de schéma et les modèles d’accès aux données.

Le principal symptôme associé à la contention de verrouillage tournant est une consommation élevée du processeur, celle-ci résultant de la quantité élevée de spins et des nombreux threads tentant d’acquérir le même verrouillage tournant. En général, ce phénomène est observé sur des systèmes avec >=24 cœurs (mais le plus souvent sur des systèmes avec >=32 cœurs). Comme indiqué précédemment, un certain niveau de contention sur les spinlocks est normal pour les systèmes OLTP à forte concurrence avec une charge importante et il y a souvent un grand nombre de spins (milliards/billions) signalés par la sys.dm_os_spinlock_stats DMV sur les systèmes qui ont été exécutés depuis longtemps. Là encore, l’observation d’un nombre élevé de spins pour un type de verrouillage donné n’est pas assez d’informations pour déterminer qu’il y a un impact négatif sur les performances de la charge de travail.

Une combinaison de plusieurs symptômes peut indiquer une contention de verrouillage tournant. Ces symptômes sont les suivants :

• Un nombre élevé de spins et de backoffs est observé pour un type de verrouillage tournant particulier.

• Le processeur du système fait l’objet d’une utilisation intensive ou de pics de consommation. Dans les scénarios de processeur lourd, vous voyez des attentes de signal élevée sur SOS_SCHEDULER_YIELD (signalées par la vue DMV sys.dm_os_wait_stats).

• Le système fait l’objet d’une haute concurrence.

• L’utilisation du processeur et le nombre de spins augmentent de manière disproportionnée par rapport au débit.

  Important

  Même si chacune des conditions précédentes est vraie, il est toujours possible que la cause racine de la consommation élevée du processeur se trouve ailleurs. En fait, dans la grande majorité des cas, l’augmentation de la consommation du processeur est due à des raisons autres que la contention de verrouillage tournant. Voici quelques-unes des causes les plus courantes de la hausse de la consommation du processeur :

• Requêtes devenant plus onéreuses au fil du temps en raison de la croissance des données sous-jacentes, d’où la nécessité d’effectuer des lectures logiques supplémentaires des données résidentes en mémoire.

• Changements dans les plans de requête aboutissant à une exécution sous-optimale.

Si toutes ces conditions sont vraies, effectuez un examen plus approfondi pour déterminer la présence éventuelle d’une contention de verrouillage tournant.

Un phénomène courant facilement diagnostiqué est une divergence significative entre le débit et l’utilisation du processeur. De nombreuses charges de travail OLTP se caractérisent par une relation entre, d’une part, le rapport débit/nombre d’utilisateurs sur le système et, d’autre part, la consommation du processeur. L’observation conjointe d’un nombre élevé de spins et d’une divergence significative entre la consommation du processeur et le débit peut indiquer que la surcharge du processeur est causée par une contention de verrouillage tournant. Une chose importante à noter ici est qu’il est également courant de voir ce type de divergence sur les systèmes lorsque certaines requêtes deviennent plus coûteuses au fil du temps. Par exemple, des requêtes émises sur des jeux de données qui effectuent un plus grand nombre de lectures logiques au fil du temps peuvent donner lieu à des symptômes similaires.

Il est essentiel d’exclure d’autres causes plus courantes du processeur élevé lors de la résolution de ces types de problèmes.

Exemples

Dans l’exemple suivant, il existe une relation presque linéaire entre la consommation du processeur et le débit, mesurée par les transactions par seconde. Il est normal de voir ici une certaine divergence, car la surcharge est générée au fur et à mesure que toute charge de travail augmente. Comme vous pouvez le voir ici, cette divergence devient importante. Il y a également une baisse rapide du débit une fois que la consommation du processeur atteint 100 %.

Si nous mesurons le nombre de spins à 3 minutes d’intervalle, nous pouvons voir une augmentation plus exponentielle que linéaire des spins, ce qui indique que la contention de verrouillage tournant peut être problématique.

Comme indiqué précédemment, les verrouillages tournants sont les plus fréquents sur des systèmes à haute concurrence soumis à une charge importante.

Voici quelques-uns des scénarios où ce problème est susceptible de se manifester :

• Problèmes de résolution de noms causés par l’utilisation de noms d’objets non complets. Pour plus d’informations, consultez Description du blocage SQL Server provoqué par des verrous de compilation. Ce problème spécifique est décrit plus en détail dans cet article.

• Contention sur des compartiments de hachage de verrou dans le gestionnaire de verrous pour les charges de travail qui accèdent fréquemment au même verrou (par exemple, un verrou partagé sur une ligne fréquemment lue). Ce type de contention est un verrouillage tournant de type LOCK_HASH. Dans un cas particulier, nous avons constaté que ce problème était dû à des modèles d’accès mal modélisés dans un environnement de test. Dans cet environnement, des threads en quantité supérieure à celle attendue accédaient en permanence à la même ligne en raison d’une mauvaise configuration des paramètres de test.

• Taux élevé de transactions DTC en cas de latence élevée entre les coordinateurs de transactions MSDTC. Ce problème spécifique est documenté en détail dans le billet de blog suivant de l’équipe SQLCAT : Resolving DTC Related Waits and Tuning Scalability of DTC.

Diagnostiquer la contention de verrouillage de spinlock

Cette section fournit des informations pour diagnostiquer la contention de verrouillage tournant dans SQL Server. Les principaux outils utilisés pour diagnostiquer la contention de verrouillage tournant sont les suivants :

Le processus technique généralement employé pour diagnostiquer une contention de verrouillage tournant dans SQL Server est le suivant :

1. Étape 1 : Déterminez qu’il y a des contentions qui peuvent être liées au verrouillage.

2. Étape 2 : Capturer des statistiques à partir de sys.dm_ os_spinlock_stats pour trouver le type de spinlock qui rencontre le plus de contention.

3. Étape 3 : Obtenir des symboles de débogage pour sqlservr.exe (sqlservr.pdb) et placer les symboles dans le même répertoire que le fichier .exe du service SQL Server (sqlservr.exe) pour l’instance de SQL Server.\ Pour voir les piles d’appels pour les événements d’annulation, vous devez avoir des symboles pour la version particulière de SQL Server que vous exécutez. Les symboles pour SQL Server sont disponibles sur le serveur de symboles Microsoft. Pour plus d’informations sur le téléchargement de symboles à partir du serveur de symboles Microsoft, consultez Débogage avec des symboles.

4. Étape 4 : Utilisez les événements étendus SQL Server pour tracer les événements de retour pour les types d’intérêt de spinlock.

Les événements étendus permettent d’effectuer le suivi de l’événement « backoff » et de capturer la pile des appels pour ces opérations les plus répandues en essayant d’obtenir le verrouillage. En analysant la pile des appels, il est possible de déterminer quel type d’opération contribue à la contention d’un verrouillage spécifique.

Procédure pas à pas de diagnostic

La procédure pas à pas suivante vous montre comment utiliser les outils et techniques présentés dans cet article pour diagnostiquer un problème de contention de verrouillage tournant dans un scénario réel. Cette procédure se base sur un engagement client exécutant un test de référence pour simuler environ 6 500 utilisateurs simultanés sur un serveur contenant 8 sockets, 64 cœurs physiques et 1 To de mémoire.

Symptômes

Des pics périodiques sont observés, provoquant une utilisation du processeur proche des 100 %. Une divergence entre le débit et la consommation du processeur est également constatée avant l’apparition du problème. Au moment du pic de consommation du processeur, un modèle composé d’un grand nombre de spins se produisant durant les périodes de forte utilisation du processeur à des intervalles particuliers est établi.

Dans ce cas extrême, la contention est telle qu’elle crée une condition de convoi de verrouillage tournant. Un convoi se produit lorsque les threads n’avancent plus dans la gestion de la charge de travail et qu’ils utilisent à la place toutes les ressources de traitement pour tenter d’accéder au verrou. Le journal de l’Analyseur de performances montre cette divergence entre le débit du journal des transactions et la consommation du processeur, et enfin le pic d’utilisation du processeur.

Après avoir interrogé sys.dm_os_spinlock_stats pour déterminer l’existence d’une contention importante sur SOS_CACHESTORE, un script d’événements étendus a été utilisé pour mesurer le nombre d’événements d’interruption pour les types d’intérêt spinlock.

La façon la plus simple de quantifier l’impact des spins consiste à examiner le nombre d’événements d’interruption exposés par sys.dm_os_spinlock_stats le même intervalle de 1 minute pour le ou les types de spinlocks avec le plus grand nombre de spins. Cette méthode est idéale pour détecter une contention importante, car elle indique quand les threads arrivent à la limite de spins dans l’attente d’acquérir le verrouillage tournant. Le script suivant illustre une technique avancée qui utilise des événements étendus pour mesurer les événements de backoff associés et identifier les chemins de code spécifiques où se situe la contention.

Pour plus d’informations sur les événements étendus dans SQL Server, consultez Présentation des événements étendus SQL Server.

Script

/*
This script is provided "AS IS" with no warranties, and confers no rights.

This script will monitor for backoff events over a given period of time and
capture the code paths (callstacks) for those.

--Find the spinlock types
select map_value, map_key, name from sys.dm_xe_map_values
where name = 'spinlock_types'
order by map_value asc

--Example: Get the type value for any given spinlock type
select map_value, map_key, name from sys.dm_xe_map_values
where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')

Examples:
61LOCK_HASH
144 SOS_CACHESTORE
08MUTEX

*/
--create the even session that will capture the callstacks to a bucketizer
--more information is available in this reference: http://msdn.microsoft.com/en-us/library/bb630354.aspx
CREATE EVENT SESSION spin_lock_backoff ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    OR TYPE = 144 --SOS_CACHESTORE
    OR TYPE = 8 --MUTEX
) ADD TARGET package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
);

--Ensure the session was created
SELECT * FROM sys.dm_xe_sessions
WHERE name = 'spin_lock_backoff';

--Run this section to measure the contention
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = START;

--wait to measure the number of backoffs over a 1 minute period
WAITFOR DELAY '00:01:00';

--To view the data
--1. Ensure the sqlservr.pdb is in the same directory as the sqlservr.exe
--2. Enable this trace flag to turn on symbol resolution
DBCC TRACEON (3656, -1);

--Get the callstacks from the bucketize target
SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spin_lock_backoff';

--clean up the session
ALTER EVENT SESSION spin_lock_backoff ON SERVER STATE = STOP;
DROP EVENT SESSION spin_lock_backoff ON SERVER;

En analysant la sortie, nous pouvons voir les piles d’appels pour les chemins de code les plus courants pour les spins SOS_CACHESTORE. Le script a été exécuté plusieurs fois pendant la période de forte utilisation du processeur pour vérifier la cohérence dans les piles d’appels retournées. Les piles d’appels avec le nombre de compartiments d’emplacements le plus élevé sont courantes entre les deux sorties (35 668 et 8 506). Ces piles d’appels ont un nombre d’emplacements (« slot count ») deux fois supérieur à l’entrée suivante. Cette condition indique un chemin de code digne d’intérêt.

Sortie 1

<BucketizerTarget truncated="0" buckets="256">
<Slot count="35668" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid
      CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey
      CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
  </value>
</Slot>
<Slot count="752" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
      SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey             CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
  </value>
  </Slot>

Sortie 2

<BucketizerTarget truncated="0" buckets="256">
<Slot count="8506" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep+c7 [ @ 0+0x0 SpinlockBase::Backoff Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      NTGroupInfo::`vector deleting destructor'
</value>
 </Slot>
<Slot count="190" trunc="0">
  <value>
      XeSosPkg::spinlock_backoff::Publish
      SpinlockBase::Sleep
       SpinlockBase::Backoff
      Spinlock<144,1,0>::SpinToAcquireOptimistic
      SOS_CacheStore::GetUserData
      OpenSystemTableRowset
      CMEDScanBase::Rowset
      CMEDScan::StartSearch
      CMEDCatalogOwner::GetOwnerAliasIdFromSid CMEDCatalogOwner::LookupPrimaryIdInCatalog CMEDCacheEntryFactory::GetProxiedCacheEntryByAltKey CMEDCatalogOwner::GetProxyOwnerBySID
      CMEDProxyDatabase::GetOwnerBySID
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
      ISECTmpEntryStore::Get
   </value>
 </Slot>

Dans l’exemple précédent, les piles les plus intéressantes ont le nombre d’emplacements le plus élevé (35 668 et 8 506), qui, en fait, ont un nombre d’emplacements >1 000.

À présent, vous vous demandez peut-être ce que vous devez faire de ces informations. En général, une connaissance approfondie du moteur SQL Server est nécessaire pour utiliser les informations de la pile d’appels. À ce stade, le processus de résolution des problèmes entre dans une zone grise. Dans ce cas particulier, en examinant les piles d’appels, nous pouvons voir que le chemin d’accès du code où le problème se produit est lié aux recherches de sécurité et de métadonnées (comme indiqué par les images CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID)de pile suivantes.

En isolation, il est difficile d’utiliser ces informations pour résoudre le problème, mais il nous donne quelques idées où concentrer davantage la résolution des problèmes afin d’isoler davantage le problème.

Étant donné que ce problème semble lié aux chemins de code qui effectuent des contrôles liés à la sécurité, nous décidons d’exécuter un test dans lequel l’utilisateur de l’application se connectant à la base de données se voit accorder des privilèges sysadmin. Bien que cette technique ne soit jamais recommandée dans un environnement de production, elle constitue une étape de dépannage utile dans notre environnement de test. Quand les sessions sont exécutées avec des privilèges élevés (sysadmin), les pics d’utilisation du processeur liés à la contention disparaissent.

Options et solutions de contournement

De toute évidence, le dépannage d’une contention de verrouillage tournant est loin d’être une tâche négligeable. Il n’y a pas de « meilleure approche commune ». La première étape en vue de résoudre des problèmes de performances est d’identifier la cause racine. Commencez par utiliser les techniques et les outils décrits dans cet article pour analyser et comprendre les points de contention liés au verrouillage tournant.

À mesure que de nouvelles versions de SQL Server sont développées, le moteur continue d’améliorer la scalabilité en implémentant du code qui est mieux optimisé pour les systèmes à haute concurrence. SQL Server a ainsi introduit de nombreuses optimisations pour les systèmes à haute concurrence, l’une d’entre elles étant le backoff exponentiel pour les points de contention les plus courants. Des améliorations spécifiques ont été apportées à partir de SQL Server 2012 pour renforcer spécifiquement cette zone particulière en tirant parti des algorithmes de backoff exponentiel pour tous les verrouillages tournants au sein du moteur.

Quand vous concevez des applications haut de gamme nécessitant des performances et une scalabilité extrêmes, pensez à réduire au maximum le chemin de code nécessaire dans SQL Server. Un chemin de code plus court permet de réduire la charge de travail du moteur de base de données et d’éviter naturellement les points de contention. De nombreuses bonnes pratiques ont pour effet secondaire de réduire la quantité de travail du moteur, d’où une optimisation des performances de la charge de travail.

Voici quelques exemples de bonnes pratiques précédemment traitées dans cet article :

• Noms complets : les noms qualifiés complets de tous les objets entraînent la suppression de la nécessité pour SQL Server d’exécuter des chemins de code requis pour résoudre les noms. Nous avons également observé des points de contention sur le type de verrouillage tournant SOS_CACHESTORE lors de l’utilisation de noms non complets dans les appels à des procédures stockées. Si vous n’utilisez pas de noms complets, SQL Server doit rechercher le schéma par défaut de l’utilisateur, ce qui se traduit par un chemin de code plus long requis pour exécuter le code SQL.

• Requêtes paramétrables : un autre exemple consiste à utiliser des requêtes paramétrables et des appels de procédure stockée pour réduire le travail nécessaire pour générer des plans d’exécution. Vous obtenez là encore un chemin de code plus court pour l’exécution.

• LOCK_HASH Contention : la contention sur certaines collisions de compartiments de verrouillage ou de hachage est inévitable dans certains cas. Bien que le moteur SQL Server partitionne la majorité des structures de verrouillage, il arrive encore que l’acquisition d’un verrou entraîne l’accès au même compartiment de hachage. Cela se produit par exemple quand une application accède simultanément à la même ligne (des données de référence) par le biais de plusieurs threads. Ces types de problèmes peuvent être abordés par des techniques qui effectuent un scale-out de ces données de référence dans le schéma de base de données ou utilisent des indicateurs NOLOCK si possible.

La première ligne de défense en matière de réglage des charges de travail SQL Server est de faire appel aux pratiques standard (indexation, optimisation des requêtes, optimisation des E/S, etc.). Toutefois, en plus du réglage standard, une approche importante consiste à suivre des pratiques qui réduisent la quantité de code nécessaire pour effectuer des opérations. Même lorsque les meilleures pratiques sont suivies, il est toujours possible que la contention de blocage se produise sur des systèmes d’accès concurrentiel élevé occupés. L’utilisation des outils et techniques de cet article peut aider à isoler ou à exclure ces types de problèmes et à déterminer quand il est nécessaire d’engager les ressources Microsoft appropriées pour vous aider.

Espérons que ces techniques vous fourniront à la fois une méthodologie utile pour ce type de dépannage et des insights sur certaines des techniques de profilage des performances plus avancées disponibles avec SQL Server.

Annexe : Automatiser la capture de vidage de mémoire

Le script d’événements étendus suivant est utile pour automatiser la collecte des images mémoire quand la contention de verrouillage tournant devient importante. Dans certains cas, des images mémoire sont nécessaires pour effectuer un diagnostic complet du problème ou sont demandées par les équipes du support technique de Microsoft pour procéder à une analyse approfondie. Dans SQL Server 2008, il existe une limite de 16 images dans les piles d’appels capturées par le bucketizer, ce qui peut ne pas être suffisamment profond pour déterminer exactement où dans le moteur à partir duquel la pile d’appels est entrée. Dans SQL Server 2012, le nombre de frames dans les piles d’appels capturées par le bucketizer passe à 32.

Vous pouvez utiliser le script SQL suivant pour automatiser le processus de capture d’images mémoire afin de faciliter l’analyse de la contention de verrouillage tournant :

/*
This script is provided "AS IS" with no warranties, and confers no rights.

Use:    This procedure will monitor for spinlocks with a high number of backoff events
        over a defined time period which would indicate that there is likely significant
        spin lock contention.

        Modify the variables noted below before running.


Requires:
        xp_cmdshell to be enabled
            sp_configure 'xp_cmd', 1
            go
            reconfigure
            go

*********************************************************************************************************/
USE tempdb;
GO

IF object_id('sp_xevent_dump_on_backoffs') IS NOT NULL
    DROP PROCEDURE sp_xevent_dump_on_backoffs
GO

CREATE PROCEDURE sp_xevent_dump_on_backoffs (
    @sqldumper_path NVARCHAR(max) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"',
    @dump_threshold INT = 500, --capture mini dump when the slot count for the top bucket exceeds this
    @total_delay_time_seconds INT = 60, --poll for 60 seconds
    @PID INT = 0,
    @output_path NVARCHAR(MAX) = 'c:\',
    @dump_captured_flag INT = 0 OUTPUT
)
AS
/*
    --Find the spinlock types
    select map_value, map_key, name from sys.dm_xe_map_values
    where name = 'spinlock_types'
    order by map_value asc

    --Example: Get the type value for any given spinlock type
    select map_value, map_key, name from sys.dm_xe_map_values
    where map_value IN ('SOS_CACHESTORE', 'LOCK_HASH', 'MUTEX')
*/
IF EXISTS (
        SELECT *
        FROM sys.dm_xe_session_targets xst
        INNER JOIN sys.dm_xe_sessions xs
            ON (xst.event_session_address = xs.address)
        WHERE xs.name = 'spinlock_backoff_with_dump'
        )
    DROP EVENT SESSION spinlock_backoff_with_dump
        ON SERVER

CREATE EVENT SESSION spinlock_backoff_with_dump ON SERVER
ADD EVENT sqlos.spinlock_backoff (
    ACTION(package0.callstack) WHERE type = 61 --LOCK_HASH
    --or type = 144           --SOS_CACHESTORE
    --or type = 8             --MUTEX
    --or type = 53            --LOGCACHE_ACCESS
    --or type = 41            --LOGFLUSHQ
    --or type = 25            --SQL_MGR
    --or type = 39            --XDESMGR
) ADD target package0.asynchronous_bucketizer (
    SET filtering_event_name = 'sqlos.spinlock_backoff',
    source_type = 1,
    source = 'package0.callstack'
)
WITH (
    MAX_MEMORY = 50 MB,
    MEMORY_PARTITION_MODE = PER_NODE
)

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = START;

DECLARE @instance_name NVARCHAR(MAX) = @@SERVICENAME;
DECLARE @loop_count INT = 1;
DECLARE @xml_result XML;
DECLARE @slot_count BIGINT;
DECLARE @xp_cmdshell NVARCHAR(MAX) = NULL;

--start polling for the backoffs
PRINT 'Polling for: ' + convert(VARCHAR(32), @total_delay_time_seconds) + ' seconds';

WHILE (@loop_count < CAST(@total_delay_time_seconds / 1 AS INT))
BEGIN
    WAITFOR DELAY '00:00:01'

    --get the xml from the bucketizer for the session
    SELECT @xml_result = CAST(target_data AS XML)
    FROM sys.dm_xe_session_targets xst
    INNER JOIN sys.dm_xe_sessions xs
        ON (xst.event_session_address = xs.address)
    WHERE xs.name = 'spinlock_backoff_with_dump';

    --get the highest slot count from the bucketizer
    SELECT @slot_count = @xml_result.value(N'(//Slot/@count)[1]', 'int');

    --if the slot count is higher than the threshold in the one minute period
    --dump the process and clean up session
    IF (@slot_count > @dump_threshold)
    BEGIN
        PRINT 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 c:\ '''

        SELECT @xp_cmdshell = 'exec xp_cmdshell ''' + @sqldumper_path + ' ' + convert(NVARCHAR(max), @PID) + ' 0 0x800 0 ' + @output_path + ' '''

        EXEC sp_executesql @xp_cmdshell

        PRINT 'loop count: ' + convert(VARCHAR(128), @loop_count)
        PRINT 'slot count: ' + convert(VARCHAR(128), @slot_count)

        SET @dump_captured_flag = 1

        BREAK
    END

    --otherwise loop
    SET @loop_count = @loop_count + 1
END;

--see what was collected then clean up
DBCC TRACEON (3656, -1);

SELECT event_session_address,
    target_name,
    execution_count,
    cast(target_data AS XML)
FROM sys.dm_xe_session_targets xst
INNER JOIN sys.dm_xe_sessions xs
    ON (xst.event_session_address = xs.address)
WHERE xs.name = 'spinlock_backoff_with_dump';

ALTER EVENT SESSION spinlock_backoff_with_dump ON SERVER STATE = STOP;
DROP EVENT SESSION spinlock_backoff_with_dump ON SERVER;
GO

/* CAPTURE THE DUMPS
******************************************************************/
--Example: This will run continuously until a dump is created.
DECLARE @sqldumper_path NVARCHAR(MAX) = '"c:\Program Files\Microsoft SQL Server\100\Shared\SqlDumper.exe"';
DECLARE @dump_threshold INT = 300; --capture mini dump when the slot count for the top bucket exceeds this
DECLARE @total_delay_time_seconds INT = 60; --poll for 60 seconds
DECLARE @PID INT = 0;
DECLARE @flag TINYINT = 0;
DECLARE @dump_count TINYINT = 0;
DECLARE @max_dumps TINYINT = 3; --stop after collecting this many dumps
DECLARE @output_path NVARCHAR(max) = 'c:\'; --no spaces in the path please :)
--Get the process id for sql server
DECLARE @error_log TABLE (
    LogDate DATETIME,
    ProcessInfo VARCHAR(255),
    TEXT VARCHAR(max)
);

INSERT INTO @error_log
EXEC ('xp_readerrorlog 0, 1, ''Server Process ID''');

SELECT @PID = convert(INT, (REPLACE(REPLACE(TEXT, 'Server Process ID is ', ''), '.', '')))
FROM @error_log
WHERE TEXT LIKE ('Server Process ID is%');

PRINT 'SQL Server PID: ' + convert(VARCHAR(6), @PID);

--Loop to monitor the spinlocks and capture dumps. while (@dump_count < @max_dumps)
BEGIN
    EXEC sp_xevent_dump_on_backoffs @sqldumper_path = @sqldumper_path,
        @dump_threshold = @dump_threshold,
        @total_delay_time_seconds = @total_delay_time_seconds,
        @PID = @PID,
        @output_path = @output_path,
        @dump_captured_flag = @flag OUTPUT

    IF (@flag > 0)
        SET @dump_count = @dump_count + 1

    PRINT 'Dump Count: ' + convert(VARCHAR(2), @dump_count)

    WAITFOR DELAY '00:00:02'
END;

Annexe : Capturer les statistiques de verrouillage au fil du temps

Vous pouvez utiliser le script suivant pour examiner les statistiques d’un verrouillage tournant sur une période de temps spécifique. Chaque fois que vous l’exécutez, il retourne la valeur delta entre les valeurs actuelles et les valeurs précédentes collectées.

/* Snapshot the current spinlock stats and store so that this can be compared over a time period
   Return the statistics between this point in time and the last collection point in time.

   **This data is maintained in tempdb so the connection must persist between each execution**
   **alternatively this could be modified to use a persisted table in tempdb. if that
   is changed code should be included to clean up the table at some point.**
*/
USE tempdb;
GO

DECLARE @current_snap_time DATETIME;
DECLARE @previous_snap_time DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (
    SELECT name
    FROM tempdb.sys.sysobjects
    WHERE name LIKE '#_spin_waits%'
)
CREATE TABLE #_spin_waits (
    lock_name VARCHAR(128),
    collisions BIGINT,
    spins BIGINT,
    sleep_time BIGINT,
    backoffs BIGINT,
    snap_time DATETIME
);

--capture the current stats
INSERT INTO #_spin_waits (
    lock_name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    snap_time
    )
SELECT name,
    collisions,
    spins,
    sleep_time,
    backoffs,
    @current_snap_time
FROM sys.dm_os_spinlock_stats;

SELECT TOP 1 @previous_snap_time = snap_time
FROM #_spin_waits
WHERE snap_time < (
    SELECT max(snap_time)
    FROM #_spin_waits
)
ORDER BY snap_time DESC;

--get delta in the spin locks stats
SELECT TOP 10 spins_current.lock_name,
    (spins_current.collisions - spins_previous.collisions) AS collisions,
    (spins_current.spins - spins_previous.spins) AS spins,
    (spins_current.sleep_time - spins_previous.sleep_time) AS sleep_time,
    (spins_current.backoffs - spins_previous.backoffs) AS backoffs,
    spins_previous.snap_time AS [start_time],
    spins_current.snap_time AS [end_time],
    DATEDIFF(ss, @previous_snap_time, @current_snap_time) AS [seconds_in_sample]
FROM #_spin_waits spins_current
INNER JOIN (
    SELECT *
    FROM #_spin_waits
    WHERE snap_time = @previous_snap_time
    ) spins_previous
    ON (spins_previous.lock_name = spins_current.lock_name)
WHERE spins_current.snap_time = @current_snap_time
    AND spins_previous.snap_time = @previous_snap_time
    AND spins_current.spins > 0
ORDER BY (spins_current.spins - spins_previous.spins) DESC;

--clean up table
DELETE
FROM #_spin_waits
WHERE snap_time = @previous_snap_time;

Étape suivante