Diagnose und Lösung von Spinlock-Konflikten in SQL Server

Dieser Artikel enthält ausführliche Informationen zum Identifizieren und Beheben von Problemen im Zusammenhang mit spinlock contention in SQL Server-Anwendungen auf Systemen mit hoher Parallelität.

Hintergrund

In der Vergangenheit haben Rohstoff-Windows Server-Computer nur einen oder zwei Mikroprozessor-/CPU-Chips verwendet, und CPUs wurden nur mit einem einzigen Prozessor oder "Kern" entwickelt. Die Steigerung der Computerverarbeitungskapazität wurde durch den Einsatz schnellerer CPUs erreicht, was weitgehend durch Weiterentwicklungen in der Transistordichte ermöglicht wurde. Nach "Moores Gesetz" haben sich die Transistordichte oder die Anzahl der Transistoren, die auf einem integrierten Schaltkreis untergebracht werden können, seit der Entwicklung der ersten Einzelchip-CPU im Jahr 1971 konsequent alle zwei Jahre verdoppelt. In den letzten Jahren wurde der traditionelle Ansatz zur Erhöhung der Computerverarbeitungskapazität mit schnelleren CPUs durch die Erstellung von Computern mit mehreren CPUs erweitert. Zum Zeitpunkt des Schreibens unterstützt die Intel Nehalem CPU-Architektur bis zu acht Kerne pro CPU. Bei Verwendung in einem Acht-Socket-System kann sie durch die Simultaneous Multithreading (SMT) Technologie auf 128 logische Prozessoren verdoppelt werden. Auf Intel CPUs wird SMT als Hyperthreading bezeichnet. Da die Anzahl der logischen Prozessoren auf x86 kompatiblen Computern zunimmt, erhöhen sich parallele Probleme, da logische Prozessoren für Ressourcen konkurrieren. In diesem Handbuch wird beschrieben, wie bestimmte Probleme mit Ressourcenkonflikten identifiziert und behoben werden, die beim Ausführen von SQL Server-Anwendungen auf systemen mit hoher Parallelität mit einigen Workloads beobachtet werden.

In diesem Abschnitt analysieren wir die vom SQLCAT-Team gelernten Erkenntnisse aus der Diagnose und Behebung von Spinlock-Contention-Problemen. Spinlockkonflikte sind eine Art von Parallelitätsproblem, das bei realen Arbeitsauslastungen von Kunden in umfangreichen Systemen auftritt.

Symptome und Gründe für Spinlockkonflikte

In diesem Abschnitt wird beschrieben, wie Sie Problembereiche mit Spinlock-Kontention diagnostizieren, die die Leistung von OLTP-Anwendungen auf SQL Server negativ beeinflussen. Diagnose und Fehlerbehebung von Spinlocks sollten als fortgeschrittenes Thema angesehen werden, das Kenntnisse über Entwicklungstools zur Fehlerbehebung und Windows-Interna erfordert.

Spinlocks sind einfache Synchronisierungsgrundtypen, die zum Schutz des Zugriffs auf Datenstrukturen verwendet werden. Spinlocks sind nicht einzigartig für SQL Server. Das Betriebssystem verwendet sie, wenn der Zugriff auf eine bestimmte Datenstruktur nur für kurze Zeit erforderlich ist. Wenn ein Thread, der versucht, einen Spinlock zu erlangen, keinen Zugriff erhält, wird er nicht sofort angehalten, sondern in einer Schleife ausgeführt, wobei regelmäßig überprüft wird, ob die Ressource verfügbar ist. Nach einiger Zeit gibt ein Thread, der auf ein Spinlock wartet, nach, bevor er die Ressource erwerben kann. Durch Yielding können andere Threads auf derselben CPU ausgeführt werden. Dieses Verhalten wird als Zurückweichen bezeichnet und wird später in diesem Artikel ausführlicher erläutert.

SQL Server verwendet Spinlocks, um den Zugriff auf einige der internen Datenstrukturen zu schützen. Spinlocks werden in der Engine verwendet, um den Zugriff auf bestimmte Datenstrukturen ähnlich wie bei Latches zu serialisieren. Der Hauptunterschied zwischen einem Riegel und einem Spinlock besteht darin, dass Spinlocks für eine gewisse Zeit in einer Schleife laufen, um die Verfügbarkeit einer Datenstruktur zu prüfen, während ein Thread, der versucht, Zugriff auf eine durch einen Riegel geschützte Struktur zu erhalten, sofort zurücktritt, wenn die Ressource nicht verfügbar ist. Das Anhalten erfordert einen Kontextwechsel des Threads nach außerhalb der CPU, damit ein anderer Thread ausgeführt werden kann. Dies ist ein relativ teurer Vorgang, und für Ressourcen, die für eine kurze Dauer gehalten werden, ist es insgesamt effizienter, dass ein Thread in einer Schleife regelmäßig die Verfügbarkeit der Ressource prüfen kann.

Interne Anpassungen am Datenbankmodul, das in SQL Server 2022 (16.x) eingeführt wurde, machen Spinlocks effizienter.

Symptome

Bei einem stark ausgelasteten System mit hoher Parallelität sind aktive Konflikte bei durch Spinlocks geschützten Strukturen, auf die häufig zugegriffen wird, normal. Diese Nutzung wird nur dann als problematisch angesehen, wenn die Konkurrenz zu einem erheblichen CPU-Aufwand führt. Spinlockstatistiken werden von der sys.dm_os_spinlock_stats dynamischen Verwaltungsansicht (Dynamic Management View, DMV) in SQL Server verfügbar gemacht. Diese Abfrage liefert z. B. die folgende Ausgabe:

SELECT *
FROM sys.dm_os_spinlock_stats
ORDER BY spins DESC;

Die von dieser Abfrage verfügbar gemachten Statistiken werden wie folgt beschrieben:

Für die Erläuterungen in diesem Artikel ist die Anzahl von Kollisionen, Drehungen und Backoff-Ereignissen innerhalb eines bestimmten Zeitraums bei starker Auslastung des Systems besonders interessant. Wenn ein Thread versucht, auf eine Ressource zuzugreifen, die durch ein Spinlock geschützt ist, tritt eine Kollision auf. Wenn eine Kollision auftritt, wird die Kollisionsanzahl erhöht, und der Thread beginnt in einer Schleife zu drehen und regelmäßig zu überprüfen, ob die Ressource verfügbar ist. Bei jeder Drehung des Threads (Schleife) erhöht sich die Anzahl von Drehungen.

Drehungen pro Kollision sind ein Maß für die Menge der Drehungen, die auftreten, während ein Spinlock von einem Thread gehalten wird, und gibt an, wie viele Drehungen auftreten, während Threads das Spinlock halten. Eine geringe Anzahl von Drehungen pro Kollision und eine hohe Anzahl von Kollisionen bedeutet z. B., dass bei einem Spinlock eine geringe Anzahl von Drehungen ausgeführt wird und viele Threads um den Spinlock konkurrieren. Eine hohe Anzahl von Drehungen bedeutet, dass der Thread sich im Spinlockcode relativ lang dreht (d. h., im Code wird eine hohe Anzahl von Einträgen in einem Hashbucket durchlaufen). Wenn die Anzahl von Konflikten steigt (und somit die Anzahl von Kollisionen), steigt auch die Anzahl von Drehungen.

Backoffs können in ähnlicher Weise wie Spins betrachtet werden. Um übermäßige CPU-Verschwendung zu vermeiden, drehen Spinlocks sich nicht endlos weiter, bis sie auf eine gehaltene Ressource zugreifen können. Threads führen ein Backoff durch bzw. hören auf, sich zu drehen und „ruhen“, um sicherzustellen, dass der Spinlock die CPU-Ressourcen nicht übermäßig nutzt. Spinlocks ziehen sich unabhängig davon zurück, ob sie jemals den Besitz der Zielressource erlangen. Dies geschieht, damit andere Threads auf der CPU geplant werden können, in der Hoffnung, dass dies eine produktivere Arbeit ermöglicht. Das Standardverhalten für den Motor besteht darin, zuerst für eine konstante Zeitspanne laufen zu lassen, bevor ein Zurückfahren erfolgt. Der Versuch, einen Spinlock zu erlangen, erfordert, dass der Zustand der Cacheparallelität beibehalten wird. Im Vergleich zum Drehen ist dies ein relativ CPU-intensiver Vorgang. Daher werden Versuche, einen Spinlock zu erlangen, möglichst selten ausgeführt und nicht jedes Mal, wenn ein Thread sich dreht. In SQL Server wurden bestimmte Spinlocktypen (z. B. LOCK_HASH) verbessert, indem ein exponentiell zunehmendes Intervall zwischen den Versuchen genutzt wurde, das Spinlock zu erwerben (bis zu einem bestimmten Grenzwert), was häufig die Auswirkungen auf die CPU-Leistung verringert.

Das folgende Diagramm bietet eine konzeptionelle Ansicht des Spinlockalgorithmus:

Typische Szenarien

Spinlock-Kontention kann aus einer Vielzahl von Gründen auftreten, die möglicherweise nicht mit Entscheidungen des Datenbankentwurfs zusammenhängen. Da Spinlocks den Zugriff auf interne Datenstrukturen kontrollieren, äußern sich Spinlockkonflikte nicht auf dieselbe Weise wie Pufferlatchkonflikte, die direkt von Schemaentwurfsentscheidungen und Datenzugriffsmustern betroffen sind.

Das Symptom, das hauptsächlich mit Spinlockkonflikten assoziiert wird, ist eine hohe CPU-Auslastung aufgrund der hohen Anzahl von Drehungen und der vielen Threads, die versuchen, denselben Spinlock zu erlangen. Im Allgemeinen wurde dies auf Systemen mit 24 und mehr CPU-Kernen und am häufigsten auf Systemen mit mehr als 32 CPU-Kernen beobachtet. Wie bereits erwähnt, ist eine gewisse Anzahl von Konflikten für Spinlocks bei OLTP-Systemen mit hoher Parallelität und erheblicher Auslastung normal, und die sys.dm_os_spinlock_stats-DMV meldet auf Systemen, die bereits über einen längeren Zeitraum ausgeführt werden, oft eine hohe Anzahl von Drehungen (Milliarden/Billionen). Die Beobachtung einer hohen Anzahl von Drehungen für einen bestimmten Spinlocktyp reicht nicht aus, um festzustellen, dass negative Auswirkungen auf die Arbeitsauslastungsleistung auftreten.

Eine Kombination aus mehreren der folgenden Symptome kann auf Spinlock-Contention hinweisen. Wenn alle diese Bedingungen zutreffen, führen Sie weitere Untersuchungen zu möglichen Problemen im Zusammenhang mit Spinlockkonflikten durch.

• Für einen bestimmten Spinlocktyp ist eine hohe Anzahl von Drehungen und Backoffs zu beobachten.

• Das System hat eine hohe CPU-Auslastung oder Spitzen im CPU-Verbrauch. In schweren CPU-Szenarien werden hohe Signalwaitezeiten auf SOS_SCHEDULER_YIELD angezeigt (vom DMV sys.dm_os_wait_stats gemeldet).

• Das System verfügt über eine hohe Parallelität.

• Die CPU-Auslastung und -Drehungen werden im Verhältnis zum Durchsatz erhöht.

Ein häufiges Phänomen, das leicht diagnostiziert wird, ist eine signifikante Divergenz bei der Durchsatz- und CPU-Auslastung. Viele OLTP-Workloads haben eine Beziehung zwischen (Durchsatz / Anzahl der Benutzer auf dem System) und cpu-Auslastung. Eine zu beobachtende hohe Anzahl von Drehungen in Verbindung mit einer erheblichen Abweichung zwischen CPU-Auslastung und Durchsatz kann ein Hinweis auf Spinlockkonflikte sein, die zu CPU-Mehraufwand führen. Ein wichtiger Punkt hier ist, dass es auch üblich ist, diese Art von Divergenz auf Systemen zu sehen, wenn bestimmte Abfragen im Laufe der Zeit teurer werden. Beispielsweise können Abfragen, die für Datasets ausgegeben werden, die im Laufe der Zeit logischere Lesevorgänge ausführen, zu ähnlichen Symptomen führen.

Zu den häufigeren Ursachen für einen erhöhten CPU-Verbrauch gehören:

• Abfragen, die im Laufe der Zeit aufgrund des Wachstums der zugrunde liegenden Daten teurer werden, was dazu führt, dass zusätzliche logische Lesevorgänge von speicherresidenten Daten ausgeführt werden müssen.
• Änderungen an Abfrageplänen, die zu einer suboptimalen Ausführung führen.

Beispiele

Im folgenden Beispiel gibt es eine nahezu lineare Beziehung zwischen CPU-Verbrauch und Durchsatz, wie durch Transaktionen pro Sekunde gemessen. Es ist normal, hier einige Abweichungen zu sehen, da der Aufwand mit zunehmendem Arbeitsaufwand steigt. Wie hier dargestellt, wird diese Divergenz erheblich. Es gibt auch einen starken Rückgang des Durchsatzes, wenn die CPU-Auslastung 100%erreicht.

Bei der Messung der Anzahl der Umdrehungen in 3-Minuten-Intervallen sehen wir eine exponentiellere als lineare Zunahme der Umdrehungen, was darauf hindeutet, dass Spinlock-Contention problematisch sein könnte.

Wie bereits erwähnt, werden Spinlocks meist auf stark ausgelasteten Systemen mit hoher Parallelität verwendet.

Einige der Szenarien, die anfällig für dieses Problem sind, sind:

• Probleme bei der Namensauflösung, die durch einen Fehler beim vollständigen Qualifizieren von Namen von Objekten verursacht werden. Weitere Informationen finden Sie unter Beschreibung der SQL Server-Blockierung durch Kompilierungssperren. Dieses spezifische Problem wird in diesem Artikel ausführlicher beschrieben.

• Im Sperren-Manager treten Konflikte für Sperrhashbuckets für Arbeitsauslastungen auf, die häufig auf dieselbe Sperre zugreifen (z. B. eine gemeinsame Sperre für eine häufig gelesene Zeile). Diese Art von Konflikt äußert sich in einem LOCK_HASH-Spinlock. In einem bestimmten Fall haben wir festgestellt, dass dieses Problem aufgrund falsch modellierter Zugriffsmuster in einer Testumgebung aufgetreten ist. In dieser Umgebung haben mehr als die erwartete Anzahl von Threads aufgrund falsch konfigurierter Testparameter ständig auf die gleiche Zeile zugegriffen.

• Hohe Rate von DTC-Transaktionen, wenn zwischen den MSDTC-Transaktionskoordinatoren eine hohe Latenz besteht. Dieses spezifische Problem wird ausführlich im SQLCAT-Blogeintrag "Die Lösung von DTC-bezogenen Wartezeiten und die Abstimmung der Skalierbarkeit von DTC" dokumentiert.

Diagnose von Spinlockkonflikten

Dieser Abschnitt enthält Informationen zur Diagnose von SQL-Server-Spinlock-Konflikten. Die primären Tools für die Diagnose von Spinlockkonflikten sind:

Der allgemeine technische Ansatz zur Diagnose von SQL Server Spinlock-Konflikten ist:

1. Schritt 1: Ermitteln Sie, ob es einen Streit gibt, der sich möglicherweise auf Spinlock bezieht.

2. Schritt 2: Erfassen Sie Statistiken von sys.dm_os_spinlock_stats, um den Spinlocktyp mit der größten Konkurrenz zu ermitteln.

3. Schritt 3: Abrufen von Debugsymbolen für sqlservr.exe (sqlservr.pdb) und Platzieren Sie die Symbole im selben Verzeichnis wie die SQL Server-Dienstdatei ( .exesqlservr.exe) für die Instanz von SQL Server.\ Um die Aufrufstapel für die Backoff-Ereignisse anzuzeigen, müssen Sie Über Symbole für die bestimmte Version von SQL Server verfügen, die Sie ausführen. Symbole für SQL Server sind auf dem Microsoft Symbol Server verfügbar. Weitere Informationen zum Herunterladen von Symbolen vom Microsoft Symbol Server finden Sie unter Debuggen mit Symbolen.

4. Schritt 4: Verwenden Sie erweiterte SQL Server-Ereignisse, um die Backoff-Ereignisse für die interessanten Spinlocktypen zu verfolgen. Die zu erfassenden Ereignisse sind spinlock_backoff und spinlock_backoff_warning.

Extended Events bieten die Möglichkeit, Backoff-Ereignisse zu verfolgen und den Aufrufstapel (Call Stack) für diejenigen Vorgänge zu erfassen, die am häufigsten versuchen, den Spinlock zu erlangen. Durch die Analyse des Aufrufstapels ist es möglich, zu bestimmen, welche Art von Vorgang zu einem Konflikt bei einem bestimmten Spinlock beiträgt.

Exemplarische Vorgehensweise für die Diagnose

Im Rahmen der folgenden exemplarischen Vorgehensweise wird gezeigt, wie Sie die Tools und Methoden verwenden, um ein Problem mit Spinlockkonflikten in einem realen Szenario zu diagnostizieren. Diese Schritt-für-Schritt-Anleitung basiert auf einem Kundenprojekt, das einen Benchmark-Test ausführt, um ungefähr 6.500 gleichzeitige Benutzer auf einem 8-Socket-Server mit 64 physikalischen Kernen und 1 TB Arbeitsspeicher zu simulieren.

Symptome

Periodische Spitzen in der CPU wurden beobachtet, die die CPU-Auslastung auf fast 100%trieben. Es wurde eine Divergenz zwischen Durchsatz und CPU-Auslastung beobachtet, was zu dem Problem führte. Zu dem Zeitpunkt, als die große CPU-Spitze auftrat, wurde ein Muster einer großen Anzahl von Schleifen festgelegt, die während hoher CPU-Auslastung zu bestimmten Intervallen auftreten.

Dies war ein Extremfall, bei dem durch den Konflikt ein Spinlockkonvoi entstand. Ein Konvoi tritt auf, wenn Threads die Arbeitsauslastung nicht mehr weiter ausführen können, sondern stattdessen alle Verarbeitungsressourcen dafür verwenden, zu versuchen, auf die Sperre zuzugreifen. Das Leistungsüberwachungsprotokoll veranschaulicht diese Divergenz zwischen Transaktionsprotokolldurchsatz und CPU-Auslastung und letztendlich der große Anstieg der CPU-Auslastung.

Nachdem durch eine Abfrage von sys.dm_os_spinlock_stats festgestellt worden war, ob auf SOS_CACHESTORE eine signifikante Konkurrenzsituation vorlag, wurde ein Extended-Events-Skript verwendet, um die Anzahl der Backoff-Ereignisse für die betreffenden Spinlock-Typen zu messen.

Die einfachste Methode zur Quantifizierung der Auswirkungen der Drehungen besteht darin, die Anzahl der Backoffereignisse zu betrachten, die über sys.dm_os_spinlock_stats denselben 1-Minuten-Intervall für die Spinlock-Typen mit der höchsten Anzahl von Drehungen aufgetreten sind. Diese Methode ist am besten geeignet, um signifikante Konflikte zu erkennen, da sie angibt, wann Threads das Spin-Limit ausschöpfen, während sie darauf warten, das Spinlock zu erhalten. Das folgende Skript veranschaulicht eine komplexe Methode, bei der erweiterte Ereignisse verwendet werden, um verwandte Backoff-Ereignisse zu messen und die spezifischen Codepfade zu identifizieren, bei denen die Konflikte auftreten.

Weitere Informationen zu erweiterten Ereignissen in SQL Server finden Sie in der Übersicht über erweiterte Ereignisse.

Skript

/*
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;

Wenn Sie die Ausgabe analysieren, sehen Sie die Aufruflisten für die häufigsten Codepfade für die SOS_CACHESTORE-Drehungen. Das Skript wurde mehrfach während der Zeit ausgeführt, in der die CPU-Auslastung hoch war, um die Konsistenz in den zurückgegebenen Aufrufstapeln zu überprüfen. Die Aufruflisten mit der höchsten Slotbucketanzahl in den beiden Ausgaben gleich sind (35.668 und 8.506). Diese Aufruflisten verfügen über eine Slotanzahl, die zwei Größenordnungen größer als der Eintrag mit der nächstniedrigeren Anzahl ist. Diese Bedingung gibt einen Codepfad an, der von Interesse ist.

Ausgabe 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>

Ausgabe 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>

Im vorherigen Beispiel weisen die interessantesten Stapel die höchste Slotanzahl auf (35.668 und 8.506), die tatsächlich eine Slotanzahl größer als 1.000 haben.

Jetzt könnte die Frage sein: "Was tue ich mit diesen Informationen"? Im Allgemeinen ist ein umfassendes Wissen über das SQL Server-Modul erforderlich, um die Callstack-Informationen zu nutzen, und an diesem Punkt wechselt der Problembehandlungsprozess in einen grauen Bereich. In diesem speziellen Fall können wir anhand der Aufrufstapel sehen, dass der Codepfad, in dem das Problem auftritt, mit Sicherheits- und Metadaten-Nachschlagevorgängen verknüpft ist (Wie durch die folgenden Stapelframes CMEDCatalogOwner::GetProxyOwnerBySID & CMEDProxyDatabase::GetOwnerBySID)ersichtlich.

Isoliert ist es schwierig, diese Informationen zu verwenden, um das Problem zu beheben, aber es gibt uns einige Ideen, wo wir zusätzliche Problembehandlung konzentrieren können, um das Problem weiter zu isolieren.

Da dieses Problem mit Codepfaden in Zusammenhang steht, die sicherheitsbezogene Prüfungen durchführen, haben wir beschlossen, einen Test auszuführen, bei dem dem Anwendungsbenutzer, der eine Verbindung mit der Datenbank herstellt, Berechtigungen gewährt sysadmin wurde. Diese Technik wird zwar in einer Produktionsumgebung niemals empfohlen, aber in unserer Testumgebung hat sich dies als nützlicher Problembehandlungsschritt erwiesen. Wenn die Sitzungen mit erhöhten Rechten (sysadmin) ausgeführt wurden, verschwanden die CPU-Spitzen im Zusammenhang mit Konflikten.

Optionen und Problemumgehungen

Die Problembehandlung bei Spinlockkonflikten kann auf jeden Fall eine nicht triviale Aufgabe sein. Es gibt keinen "gemeinsamen besten Ansatz". Der erste Schritt bei der Problembehandlung und Behebung eines Leistungsproblems besteht darin, die Ursache zu identifizieren. Die Verwendung der in diesem Artikel beschriebenen Techniken und Tools ist der erste Schritt bei der Durchführung der Analyse, die erforderlich ist, um die Spinlock-bezogenen Konfliktpunkte zu verstehen.

Da neue Versionen von SQL Server entwickelt werden, verbessert das Modul weiterhin die Skalierbarkeit durch die Implementierung von Code, der besser für hohe Parallelitätssysteme optimiert ist. SQL Server hat viele Optimierungen für Systeme mit hoher Parallelität eingeführt. Eine davon ist das exponentielle Backoff für die häufigsten Konfliktpunkte. Ab SQL Server 2012 wurden spezifische Verbesserungen eingeführt, die diesen Bereich durch die Nutzung exponentieller Backoff-Algorithmen für alle Spinlocks innerhalb der Engine verbessert haben.

Berücksichtigen Sie beim Entwerfen von High-End-Anwendungen, die extreme Leistung und Skalierung erfordern, wie Sie den in SQL Server benötigten Codepfad so kurz wie möglich halten. Ein kürzerer Codepfad bedeutet, dass die Datenbank-Engine weniger Arbeit ausführen muss und dadurch Konfliktpunkte vermieden werden. Eine Nebenwirkung bei vielen bewährten Methoden ist, dass die erforderliche Arbeit der Engine reduziert und somit die Arbeitsauslastungsleistung optimiert wird.

Einige der in diesem Artikel bereits erwähnten bewährten Methoden sind Beispiele hierfür:

• Vollqualifizierte Namen: Das vollständige Qualifizieren der Namen aller Objekte führt dazu, dass SQL Server keine Codepfade mehr ausführen muss, die zur Auflösung von Namen erforderlich sind. Beim Spinlocktyp SOS_CACHESTORE waren Konfliktpunkte auch zu beobachten, wenn beim Aufrufen gespeicherter Prozeduren keine vollqualifizierten Namen verwendet wurden. Wenn diese Namen nicht vollständig qualifiziert werden, müssen SQL Server das Standardschema für den Benutzer nachschlagen, was zu einem längeren Codepfad führt, der zum Ausführen der SQL erforderlich ist.

• Parametrisierte Abfragen: Ein weiteres Beispiel ist die Verwendung parametrisierter Abfragen und Gespeicherter Prozeduraufrufe, um die arbeit zu reduzieren, die zum Generieren von Ausführungsplänen erforderlich ist. Dies führt erneut zu einem kürzeren Codepfad für die Ausführung.

• LOCK_HASH Konflikt: In manchen Fällen ist der Konflikt bei bestimmten Sperrstrukturen oder Hash-Bucket-Kollisionen unvermeidbar. Obwohl das SQL Server-Modul die meisten Sperrstrukturen partitioniert, gibt es immer noch Situationen, in denen das Abrufen einer Sperre zu einem Zugriff auf denselben Hash-Bucket führt. Beispielsweise greift eine Anwendung auf dieselbe Zeile durch viele Threads gleichzeitig zu (d. a. Referenzdaten). Diese Arten von Problemen können durch Techniken näher behandelt werden, die diese Referenzdaten entweder innerhalb des Datenbankschemas skalieren oder optimistische Parallelitätssteuerung und optimierte Sperrung verwenden, wenn möglich.

Die erste Verteidigungslinie bei der Optimierung von SQL Server-Workloads ist immer die Standardoptimierungspraktiken (z. B. Indizierung, Abfrageoptimierung, E/A-Optimierung usw.). Neben den standardmäßig durchgeführten Optimierungsmaßnahmen stellt jedoch auch die Verwendung von Methoden zur Reduzierung der für das Ausführen von Vorgängen erforderlichen Codemenge einen wichtigen Ansatz dar. Selbst wenn bewährte Methoden befolgt werden, besteht immer noch die Möglichkeit, dass Spinlock-Contention bei stark ausgelasteten Systemen mit hoher Parallelität auftreten kann. Die Verwendung der Tools und Techniken in diesem Artikel kann dazu beitragen, diese Arten von Problemen zu isolieren oder auszuschließen und zu bestimmen, wann es erforderlich ist, die richtigen Microsoft-Ressourcen zu unterstützen.

Anhang: Automatisieren der Speicherabbilderfassung

Das folgende Skript für erweiterte Ereignisse hat sich für die Automatisierung der Erfassung von Arbeitsspeicherabbildern, wenn Spinlockkonflikte erheblich werden, als nützlich erwiesen. In einigen Fällen müssen Speicherabbilder eine vollständige Diagnose des Problems durchführen oder von den Microsoft Teams aufgefordert werden, eine eingehende Analyse durchzuführen.

Das folgende SQL-Skript kann für die Automatisierung des Prozesses der Erfassung von Speicherabbildern verwendet werden, um Spinlock-Konflikte zu analysieren.

/*
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;

Anhang: Erfassen von Spinlockstatistiken im Laufe der Zeit

Das folgende Skript kann verwendet werden, um Spinlockstatistiken über einen bestimmten Zeitraum zu betrachten. Jedes Mal, wenn es ausgeführt wird, gibt es das Delta zwischen den aktuellen Werten und vorherigen gesammelten Werten zurück.

/* 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;

Verwandte Inhalte

• Tools für die Leistungsüberwachung und -optimierung