Diagnostiquer et résoudre la contention de verrou tournant sur SQL Server

Cet article fournit des informations approfondies sur comment identifier et résoudre les problèmes liés à la contention de spinlock dans les applications SQL Server sur des systèmes à forte concurrence.

Remarque

Les recommandations et les meilleures pratiques documentées ici sont basées sur une expérience réelle au cours du développement et du déploiement de systèmes OLTP réels. Elle a été publiée à l’origine par l’équipe microsoft SQL Server Customer Advisory Team (SQLCAT).

Contexte

Par le passé, les ordinateurs Windows Server de base n’ont utilisé qu’un ou deux microprocesseurs/processeurs, et les processeurs ont été conçus 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. Suite à la loi de Moore, la densité du transistor ou le nombre de transistors qui peuvent être placés sur un circuit intégré ont constamment doublé tous les deux ans depuis le développement du premier processeur à puce unique à usage général en 1971. Ces dernières années, l’approche traditionnelle de l’augmentation de la capacité de traitement des ordinateurs avec des PROCESSEURs plus rapides a été augmentée en créant des ordinateurs avec plusieurs PROCESSEURs. À 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. À mesure que le nombre de processeurs logiques sur les ordinateurs compatibles x86 augmente, les problèmes liés à la concurrence augmentent, car les processeurs logiques rivalisent pour les ressources. Ce guide explique comment identifier et résoudre des problèmes de contention de ressources particuliers 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 spinlock. La contention spinlock est un type de problème d’accès concurrentiel observé dans les charges de travail client réelles sur des systèmes à grande échelle.

Symptômes et causes de contention de verrou tournant

Cette section explique comment diagnostiquer les problèmes liés à la contention de verrou de spinlock, ce qui est préjudiciable aux performances des applications OLTP sur SQL Server. Le diagnostic et la résolution des problèmes de spinlock doivent être considérés comme un sujet avancé, qui nécessite une connaissance des outils de débogage et des internes de Windows.

Les spinlocks sont des primitives de synchronisation légères utilisées pour protéger l’accès aux structures de données. Les spinlocks ne sont pas uniques à SQL Server. Le système d’exploitation les utilise lorsque l’accès à une structure de données donnée n’est nécessaire qu’à court terme. Lorsqu’un thread qui tente d’acquérir un spinlock ne parvient pas à obtenir l’accès, il s’exécute dans une boucle, vérifiant périodiquement si la ressource est disponible, plutôt que de céder immédiatement. Après un certain temps, un thread en attente sur un spinlock va céder la place avant de pouvoir acquérir la ressource. Le rendement permet à d’autres threads s’exécutant sur le même processeur de s’exécuter. Ce comportement est appelé "recul" (backoff) et est abordé plus en détail plus loin dans cet article.

SQL Server utilise des verrous de rotation pour protéger l’accès à certaines de ses structures de données internes. Les spinlocks sont utilisés dans le moteur pour sérialiser l’accès à certaines structures de données de manière similaire aux verrous. La principale différence entre un loquet et un verrou est que les spinlocks tournent (exécutent une boucle) pendant un certain temps pour vérifier la disponibilité d'une structure de données, tandis qu'un thread tentant d'accéder à une structure protégée par un loquet se rend immédiatement si la ressource n'est pas disponible. Céder le contrôle nécessite un changement de contexte du thread pour le retirer du processeur afin 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 de vérifier périodiquement 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 spinlocks plus efficaces.

Symptômes

Sur n'importe quel système à haute concurrence actif, il est normal de voir une concurrence active sur des structures fréquemment sollicitées protégées par des spinlocks. Cette utilisation est considérée comme problématique uniquement lorsque la contention introduit une surcharge importante du processeur. 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 génère la sortie suivante :

Remarque

Pour plus d’informations sur l’interprétation des informations retournées par cette vue DMV, consultez plus loin dans cet article.

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

Les statistiques exposées par cette requête sont décrites comme suit :

Colonne	Descriptif
Collisions	Cette valeur est incrémentée chaque fois qu’un thread est bloqué pour accéder à une ressource protégée par un verrou.
Tours	Cette valeur est incrémentée pour chaque fois qu’un thread exécute une boucle en attendant qu’un spinlock devienne disponible. Il s’agit d’une mesure de la quantité de travail qu’un thread effectue pendant qu’il tente d’acquérir une ressource.
Spins_per_collision	Rapport de spins par collision.
Temps de veille	Liés aux événements de repli ; ne sont pas pertinents pour les techniques décrites dans cet article.
Retraits	Se produit lorsqu'un thread "spinning" qui tente d'accéder à une ressource verrouillée a déterminé qu'il doit autoriser d'autres threads sur le même processeur à s'exécuter.

À des fins de cette discussion, les statistiques d’intérêt particulier sont le nombre de collisions, de spins et d’événements d’interruption qui se produisent dans une période spécifique lorsque le système est soumis à une charge importante. Lorsqu’un thread tente d’accéder à une ressource protégée par un blocage, une collision se produit. Lorsqu’une collision se produit, le nombre de collisions est incrémenté et le thread commence à tourner dans une boucle et vérifie régulièrement si la ressource est disponible. Chaque fois que le thread tourne en 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 vous indiquent combien de spins se produisent pendant que les threads maintiennent le spinlock. Par exemple, de petits spins par collision et un nombre élevé de collisions signifient qu’il y a une petite quantité de spins qui se produisent autour du spinlock et que de nombreux threads sont en concurrence pour celui-ci. Une grande quantité de spins signifie que le temps passé à tourner dans le code de spinlock est considérablement long (c'est-à-dire, le code passe sur un grand nombre d’entrées dans un seau de hachage). À mesure que la contention augmente (donc l’augmentation du nombre de collisions), le nombre de spins augmente également.

Les backoffs peuvent être pensés d’une manière similaire 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 garantir qu'un spinlock n'utilise pas excessivement les ressources CPU, il réduit son activité ou se met en veille. Les spinlocks prennent du recul indépendamment de s'ils obtiennent un jour l'accès à la ressource cible. Cela est fait pour permettre aux autres threads d’être planifiés sur l’unité centrale, en espérant ainsi favoriser un travail plus productif. Le comportement par défaut du moteur consiste à effectuer une rotation pendant un intervalle de temps constant avant de procéder à un retrait progressif. La tentative d'obtention d'un spinlock nécessite de maintenir un état de cohérence du cache, ce qui est une opération intensive en termes de processeur par rapport au coût du temps passé en attente active. Par conséquent, les tentatives d’obtention d’un spinlock sont effectuées avec parcimonie et ne sont pas effectuées chaque fois qu’un thread tourne. Dans SQL Server, certains types de spinlocks (par exemple, LOCK_HASH) ont été améliorés en utilisant un intervalle croissant de façon exponentielle entre les tentatives d’acquisition du spinlock (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 de spinlock :

Scénarios classiques

La contention spinlock peut se produire pour diverses raisons qui peuvent ne pas être liées à la conception de base de données. Étant donné que les spinlocks contrôlent l’accès aux structures de données internes, la contention de spinlock n’est pas manifeste de la même façon que la contention de latch de tampon, qui est directement influencée par les choix de conception de schéma et les modèles d’accès aux données.

Le symptôme principalement associé à la contention de spinlock est une consommation élevée du processeur en conséquence du grand nombre de spins et de nombreux threads qui tentent d’acquérir le même spinlock. En général, cela a été observé sur les systèmes avec 24 cœurs de processeur et plus, et le plus souvent sur les systèmes avec plus de 32 cœurs de processeur. 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é ne constitue pas assez d'informations pour déterminer qu’il y a un impact négatif sur les performances du flux de travail.

Une combinaison de plusieurs des symptômes suivants peut indiquer une contention sur un spinlock. Si toutes ces conditions sont vraies, effectuez un examen approfondi des éventuels problèmes de contention de spinlock.

• Un nombre élevé de spins et de reculs est observé pour un type particulier de verrouillage par spin.

• Le système connaît une utilisation intensive du processeur ou des pics de consommation du processeur. Dans les scénarios de processeur lourd, vous voyez des attentes de signal élevées sur SOS_SCHEDULER_YIELD (signalées par la DMV sys.dm_os_wait_stats).

• Le système rencontre une concurrence élevée.

• L’utilisation et les spins du processeur sont augmentés de manière disproportionnée au débit.

Un phénomène courant facilement diagnostiqué est une divergence significative du débit et de l’utilisation du processeur. De nombreuses charges de travail OLTP ont une relation entre le débit et le nombre d’utilisateurs sur le système et la consommation du processeur. Les spins élevés observés conjointement avec une divergence significative entre la consommation de CPU et le débit peuvent indiquer une contention de verrous à spin qui introduit une surcharge du processeur. 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, les requêtes émises sur des jeux de données qui effectuent des lectures plus logiques au fil du temps peuvent entraîner des symptômes similaires.

Important

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

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 l'utilisation du processeur est due à des raisons autres que la contention de verrou.

Voici quelques-unes des causes les plus courantes de l’augmentation de la consommation du processeur :

• Les requêtes qui deviennent plus coûteuses au fil du temps en raison de la croissance des données sous-jacentes, ce qui entraîne la nécessité d’effectuer des lectures logiques supplémentaires de données résidentes en mémoire.
• Modifications apportées aux plans de requête, ce qui entraîne une exécution non optimale.

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 illustré ici, cette divergence devient significative. Il existe également une baisse de débit immédiate une fois que la consommation du processeur atteint 100%.

Lors de la mesure du nombre de spins à des intervalles de 3 minutes, nous pouvons voir une augmentation plus exponentielle que linéaire des spins, ce qui indique que la contention de spinlock peut être problématique.

Comme indiqué précédemment, les spinlocks sont les plus couramment utilisés sur des systèmes à forte concurrence qui sont sous une charge importante.

Voici quelques-uns des scénarios susceptibles d’être exposés à ce problème :

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

• Compétition des compartiments de hachage de verrouillage dans le gestionnaire de verrouillage lorsque les charges de travail accèdent fréquemment au même verrou (par exemple, un verrou partagé sur une ligne fréquemment lue). Ce type de contention se manifeste sous la forme d’un verrouillage de type LOCK_HASH. Dans un cas particulier, nous avons constaté que ce problème s’est produit suite à des modèles d’accès mal modélisés dans un environnement de test. Dans cet environnement, plus que le nombre attendu de threads accédait constamment à la même ligne en raison de paramètres de test mal configurés.

• 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 l’entrée de blog SQLCAT Résolution des délais d'attente liés au DTC et de la scalabilité du paramétrage de DTC.

Diagnostiquer la contention du spinlock

Cette section fournit des informations pour diagnostiquer la contention de spinlock SQL Server. Les principaux outils utilisés pour diagnostiquer la contention de verrou de spin sont les suivants :

Outil	Utiliser
Analyseur de performances	Recherchez des conditions de processeur élevées ou une divergence entre le débit et la consommation du processeur.
Statistiques de spinlock	Interrogez le sys.dm_os_spinlock_stats DMV pour rechercher un nombre élevé d’événements de rotation et d’interruption sur des périodes de temps.
Statistiques d’attente	À compter de SQL Server 2025 (17.x) Preview, interrogez les sys.dm_os_wait_stats et sys.dm_exec_session_wait_stats DMV à l’aide du SPINLOCK_EXT type d’attente. Nécessite l’indicateur de trace 8134. Pour plus d’informations, consultez SPINLOCK_EXT.
Événements étendus SQL Server	Permet de suivre les piles d’appels pour les verrous de spinlocks qui rencontrent un nombre élevé de spins.
Vidages de mémoire	Dans certains cas, les vidages de mémoire du processus SQL Server et les outils de débogage Windows. En général, ce niveau d’analyse est effectué lorsque les équipes de support Microsoft sont engagées.

Le processus technique général pour diagnostiquer la contention des verrous tournants dans SQL Server est le suivant :

1. Étape 1 : Vérifiez s'il y a une contention susceptible d'être liée à un spinlock.

2. Étape 2 : Capturez les 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’interruption, 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 suivre les événements de réduction pour les types de spinlocks qui vous intéressent. Les événements à capturer sont spinlock_backoff et spinlock_backoff_warning.

Les événements étendus permettent de suivre les événements de recul et de capturer la pile des appels pour les opérations qui essaient le plus souvent d'obtenir le verrou de spin. En analysant la pile des appels, il est possible de déterminer quel type d’opération contribue à la compétition pour un verrou tournant particulier.

Procédure pas à pas de diagnostic

La procédure pas à pas suivante montre comment utiliser les outils et les techniques pour diagnostiquer un problème de contention de verrouillage dans un scénario réel. Cette procédure pas à pas est basée sur un engagement client exécutant un test d’évaluation pour simuler environ 6 500 utilisateurs simultanés sur un serveur de 8 sockets, 64 cœurs physiques avec 1 To de mémoire.

Symptômes

Des pics périodiques du processeur ont été observés, ce qui a poussé l’utilisation du processeur à près de 100%. Une divergence entre le débit et la consommation du processeur a été observée, ce qui a entraîné le problème. Au moment où le pic de processeur important s’est produit, un modèle d’un grand nombre de spins se produisant pendant des périodes d’utilisation intensive du processeur à des intervalles particuliers a été établi.

C’était un cas extrême dans lequel la contention était si forte qu’elle a entraîné une condition de convoi de spinlock. Une congestion de threads se produit lorsque les threads ne peuvent plus progresser dans le traitement de la charge de travail, mais dépensent plutôt toutes les ressources de traitement pour essayer d’obtenir l'accès au verrou. Le journal du moniteur de performances illustre cette divergence entre le débit du journal des transactions et la consommation du processeur et, en fin de compte, le pic important d’utilisation du processeur.

Après avoir interrogé sys.dm_os_spinlock_stats pour déterminer l'existence d'une contention significative sur SOS_CACHESTORE, un script d'événements étendus a été utilisé pour mesurer le nombre d'événements de repli pour les types de spinlock concernés.

Nom	Collisions	Tourne	Tours par collision	Retraits
SOS_CACHESTORE	14,752,117	942,869,471,526	63,914	67,900,620
SOS_SUSPEND_QUEUE	69,267,367	473,760,338,765	6 840	2,167,281
LOCK_HASH	5,765,761	260,885,816,584	45,247	3,739,208
MUTEX	2,802,773	9,767,503,682	3,485	350,997
SOS_SCHEDULER	1,207,007	3,692,845,572	3,060	109,746

La façon la plus simple de quantifier l’impact des spins est d'examiner le nombre d’événements d’interruption révélés par sys.dm_os_spinlock_stats pendant le même intervalle de 1 minute, pour le type ou les types de spinlock ayant le plus grand nombre de spins. Cette méthode permet de détecter une contention significative, car elle indique quand les threads épuisent la limite de rotation en attendant d’acquérir le verrou. Le script suivant illustre une technique avancée qui utilise des événements étendus pour mesurer les événements d’interruption connexes et identifier les chemins de code spécifiques où se trouve la contention.

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

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 bucketizer 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 SOS_CACHESTORE spins. Le script a été exécuté plusieurs fois lorsque l'utilisation du processeur était élevée afin de vérifier la cohérence des piles d'appels retournées. Les piles d’appels avec le plus grand nombre de compartiments de slots sont communes aux deux sorties (35 668 et 8 506). Ces piles d’appels ont un nombre d’emplacements qui est de deux ordres de grandeur supérieurs à l’entrée la plus élevée suivante. Cette condition indique un chemin de code pertinent.

Remarque

Il n’est pas rare de voir les piles d’appels fournies par le script précédent. Lorsque le script s’est exécuté pendant 1 minute, nous avons observé que les piles d’appels avec un nombre d’emplacements de > 1 000 étaient problématiques, mais le nombre d’emplacements de > 10 000 était plus susceptible d’être problématique, car il s’agit d’un nombre d’emplacements plus élevé.

Remarque

La mise en forme de la sortie suivante a été nettoyée à des fins de lisibilité.

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 supérieur à 1 000.

Maintenant, la question pourrait être : « Que dois-je faire avec ces informations » ? En général, une connaissance approfondie du moteur SQL Server est nécessaire pour utiliser les informations de pile d’appels, et ainsi, à ce stade, le processus de résolution des problèmes se déplace dans une zone grise. Dans ce cas particulier, en examinant les piles d'appels, nous pouvons voir que la trajectoire du code où le problème se produit est liée aux recherches de sécurité et de métadonnées (comme le montrent clairement les images de pile suivantes CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID).

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 semblait lié aux chemins de code qui effectuent des vérifications liées à la sécurité, nous avons décidé d’exécuter un test dans lequel l’utilisateur de l’application se connectant à la base de données a reçu sysadmin des privilèges. Bien que cette technique ne soit jamais recommandée dans un environnement de production, dans notre environnement de test, il s’est avéré être une étape de résolution des problèmes utile. Lorsque les sessions ont été exécutées à l’aide de privilèges élevés (sysadmin), les pics d’UC liés à la contention ont disparu.

Options et solutions de contournement

Évidemment, la résolution des problèmes de contention de verrou peut être une tâche complexe. Il n’y a pas de « meilleure approche commune ». La première étape de la résolution et de la résolution de tout problème de performances consiste à identifier la cause racine. L’utilisation des techniques et des outils décrits dans cet article est la première étape pour effectuer l’analyse nécessaire à la compréhension des points de contention liés aux spinlocks.

À mesure que de nouvelles versions de SQL Server sont développées, le moteur continue d’améliorer l’extensibilité en implémentant du code qui est mieux optimisé pour les systèmes à accès concurrentiel élevé. SQL Server a introduit de nombreuses optimisations pour les systèmes à haute concurrence, dont l'une est le backoff exponentiel pour les points de contention les plus courants. Il existe des améliorations introduites avec SQL Server 2012 qui ont spécifiquement amélioré cet aspect particulier en tirant parti des algorithmes d’attente exponentielle pour tous les spinlocks dans le moteur.

Lors de la conception d’applications haut de gamme nécessitant des performances et une mise à l’échelle extrêmes, envisagez de conserver le chemin du code nécessaire dans SQL Server aussi court que possible. Un chemin de code plus court signifie que moins de travail est effectué par le moteur de base de données et évitera naturellement les points de contention. De nombreuses bonnes pratiques ont un effet secondaire sur la réduction de la quantité de travail requise du moteur, et par conséquent, l’optimisation des performances de la charge de travail.

Prenons quelques bonnes pratiques plus haut dans cet article comme exemples :

• Noms complets : Les noms 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 observé des points de contention également sur le SOS_CACHESTORE type de verrouillage spinlock rencontré lorsqu’il n’utilise pas de noms qualifiés complets dans les appels aux procédures stockées. L’échec de la qualification complète de ces noms entraîne la nécessité pour SQL Server de rechercher le schéma par défaut de l’utilisateur, ce qui entraîne un chemin de code plus long nécessaire 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. Cela entraîne à nouveau un chemin de code plus court pour l’exécution.

• LOCK_HASH Contention : La contention sur certaines structures de verrouillage ou sur des seaux de hachage en collision est inévitable dans certains cas. Même si le moteur SQL Server partitionne la majorité des structures de verrou, il existe encore des moments où l’acquisition d’un verrou entraîne l’accès au même compartiment de hachage. Par exemple, une application accède à la même ligne par de nombreux threads simultanément (c’est-à-dire des données de référence). 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 au sein du schéma de base de données ou utilisent le contrôle d’accès concurrentiel optimiste et le verrouillage optimisé si possible.

La première ligne de défense dans le réglage des charges de travail SQL Server est toujours les pratiques de réglage standard (par exemple, indexation, optimisation des requêtes, optimisation des E/S, etc.). Toutefois, en plus du réglage standard, une approche importante consiste à suivre les 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 spinlock se produise sur des systèmes très concurrents et 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.

Annexe : Automatiser la capture de vidage de mémoire

Le script d’événements étendus suivant s'est révélé être un outil efficace pour automatiser la collecte de vidages de mémoire lorsque la contention de spinlock devient significative. Dans certains cas, les vidages de mémoire sont nécessaires pour effectuer un diagnostic complet du problème ou sont demandés par les équipes Microsoft pour effectuer une analyse approfondie.

Le script SQL suivant peut être utilisé pour automatiser le processus de capture des vidages de mémoire afin d'aider à analyser la contention des verrous tournants :

/*
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 spinlock au fil du temps

Le script suivant peut être utilisé pour examiner les statistiques de spinlock sur une période spécifique. Chaque fois qu’il s’exécute, il retourne le 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;

Contenu connexe

• Outils de surveillance et de réglage des performances