Diagnostizieren und Lösen von Latchkonflikten in SQL Server

In diesem Leitfaden wird beschrieben, wie Sie Latchkonflikte identifizieren und lösen, die auftreten, wenn SQL Server-Anwendungen mit bestimmten Arbeitsauslastungen auf Systemen mit hoher Parallelität ausgeführt werden.

Während die Anzahl der CPU-Kerne in Servern weiterhin steigt, kann der entsprechende Anstieg der Parallelität zu Konflikten in Datenstrukturen führen, auf die auf serielle Weise in der Datenbank-Engine zugegriffen werden muss. Dies gilt insbesondere für OLTP-Arbeitsauslastungen (Arbeitsauslastungen mit hohem Durchsatz/hoher Parallelität für die Transaktionsverarbeitung). Es gibt mehrere Tools, Techniken und Möglichkeiten, diese Herausforderungen zu bewältigen, sowie Methoden, die beim Entwerfen von Anwendungen befolgt werden können, die dazu beitragen können, sie vollständig zu vermeiden. Dieser Artikel befasst sich mit einer besonderen Art von Konflikten bei Datenstrukturen, die Spinlocks zur Serialisierung des Zugriffs auf diese Datenstrukturen verwenden.

Anmerkung

Dieser Inhalt wurde vom SQLCAT von Microsoft auf Grundlage seines Verfahrens zum Identifizieren und Lösen von Problemen im Zusammenhang mit Seitenlatchkonflikten in SQL Server-Anwendungen in Systemen mit hoher Parallelität verfasst. Die hier dokumentierten Empfehlungen und bewährten Methoden basieren auf praktischen Erfahrungen bei der Entwicklung und Bereitstellung von realen OLTP-Systemen.

Was sind SQL Server-Latchkonflikte?

Latches sind einfache Synchronisierungsprimitiven, die von der SQL Server-Engine verwendet werden, um die Konsistenz von In-Memory-Strukturen zu gewährleisten. Dazu gehören beispielsweise Indizes, Datenseiten und interne Strukturen wie Seiten, die sich nicht am Ende der Struktur befinden, in einer B-Struktur. SQL Server verwendet Pufferlatches, um Seiten im Pufferpool zu schützen, und E/A-Latches, um Seiten zu schützen, die noch nicht in den Pufferpool geladen wurden. Wenn Daten in eine Seite geschrieben oder aus dieser gelesen werden, die sich im SQL Server-Pufferpool befindet, muss ein Workerthread zunächst ein Pufferlatch für die Seite abrufen. Für den Zugriff auf Seiten im Pufferpool stehen verschiedene Pufferriegeltypen zur Verfügung, einschließlich exklusiver Riegel (PAGELATCH_EX) und freigegebener Riegel (PAGELATCH_SH). Wenn SQL Server versucht, auf eine Seite zuzugreifen, die noch nicht im Pufferpool vorhanden ist, wird eine asynchrone E/A bereitgestellt, um die Seite in den Pufferpool zu laden. Wenn SQL Server warten muss, bis das E/A-Subsystem reagiert, wartet es je nach Anforderungstyp auf eine exklusive (PAGEIOLATCH_EX) oder freigegebene (PAGEIOLATCH_SH) E/A-Klammer. Dadurch wird verhindert, dass ein anderer Workerthread dieselbe Seite in den Pufferpool mit einem inkompatiblen Latch lädt. Latches werden auch zum Schützen des Zugriff auf interne Arbeitsspeicherstrukturen verwendet, bei denen es sich nicht um Pufferpoolseiten handelt. Diese werden als Nicht-Pufferlatches bezeichnet.

Das häufigste Szenario auf Multi-CPU-Systemen sind Konflikte auf Seiten-Latches, weshalb sich der Großteil dieses Artikels auf diese konzentriert.

Latchkonflikte treten auf, wenn mehrere Threads gleichzeitig versuchen, inkompatible Latches für dieselbe In-Memory-Struktur abzurufen. Da Latches ein interner Steuermechanismus sind, bestimmt die SQL-Engine automatisch, wann sie verwendet werden sollen. Aufgrund des deterministischen Verhaltens von Latches können sich Anwendungsentscheidungen, z. B. der Schemaentwurf, auf dieses Verhalten auswirken. Dieser Artikel bietet die folgenden Informationen:

• Hintergrundinformationen zur Verwendung von Latches durch SQL Server
• Tools zum Untersuchen von Latchkonflikten
• Wie wird ermittelt, ob die vorhandene Konfliktmenge problematisch ist?

Wir besprechen einige gängige Szenarien und wie sie am besten bewältigt werden können, um die Konkurrenz zu verringern.

Wie verwendet SQL Server Latches?

Eine Seite in SQL Server ist 8 KB groß und kann mehrere Zeilen enthalten. Im Gegensatz zu Sperren, die für die Dauer der logischen Transaktion beibehalten werden, werden Pufferlatches zum Erhöhen der Parallelität und Leistung nur für die Dauer des physischen Vorgangs auf der Seite beibehalten.

Latches sind intern in der SQL-Engine und werden dazu verwendet, Arbeitsspeicherkonsistenz bereitzustellen, während Sperren von SQL Server verwendet werden, um Konsistenz für logische Transaktionen bereitzustellen. In der folgenden Tabelle werden Latches mit Sperren verglichen:

Struktur	Zweck	Gesteuert von	Leistungskosten	Zur Verfügung gestellt durch
Verriegelung	Gewährleisten der Konsistenz von In-Memory-Strukturen	Nur SQL Server-Engine	Die Leistungskosten sind niedrig. Im Gegensatz zu Sperren, die für die Dauer der logischen Transaktion beibehalten werden, werden Latches für die bestmögliche Parallelität und Leistung nur für die Dauer des physischen Vorgangs für die In-Memory-Struktur beibehalten.	sys.dm_os_wait_stats – Stellt Informationen zu PAGELATCH, PAGEIOLATCH und LATCH Wartetypen bereit (LATCH_EX wird verwendet, LATCH_SH um alle Nicht-Puffer-Latch-Wartezeiten zu gruppieren). sys.dm_os_latch_stats – Enthält detaillierte Informationen zu Nicht-Puffer-Latch-Wartezeiten. sys.dm_db_index_operational_stats - Dieser DMV liefert aggregierte Wartezeiten für jeden Index, was beim Behandeln von Problemen im Zusammenhang mit der Verriegelung-Leistung hilfreich ist.
Sperren Sie	Gewährleisten der Konsistenz von Transaktionen	Kann vom Benutzer gesteuert werden	Die Leistungskosten sind im Vergleich zu Latches hoch, da Sperren für die Dauer der Transaktion beibehalten werden müssen.	sys.dm_tran_locks. sys.dm_exec_sessions.

SQL Server-Latchmodi und -kompatibilität

Einige Latchkonflikte sind als normaler Teil des Vorgangs der SQL Server-Engine zu erwarten. Es ist unvermeidlich, dass mehrere gleichzeitige Schließanforderungen unterschiedlicher Kompatibilität auf einem hohen Parallelitätssystem auftreten. SQL Server erzwingt die Latchkompatibilität, indem erzwungen wird, dass inkompatible Latchanforderungen in einer Warteschlange warten müssen, bis ausstehende Latchanforderungen abgeschlossen sind.

Latches werden in einem von fünf Modi abgerufen, die sich auf das Ausmaß des Zugriffs beziehen. SQL Server-Latchmodi können folgendermaßen zusammengefasst werden:

• KP: Riegel behalten. Stellt sicher, dass die referenzierte Struktur nicht zerstört werden kann. Er wird verwendet, wenn ein Thread eine Pufferstruktur überprüfen möchte. Da der KP-Riegel mit allen Riegeln kompatibel ist, mit Ausnahme des Vernichtungsriegels (DT), wird die KP-Riegelung als leicht angesehen, was bedeutet, dass der Effekt auf die Leistung bei der Verwendung minimal ist. Da die KP-Riegelung nicht mit dem DT-Riegel kompatibel ist, wird verhindert, dass ein anderer Thread die referenzierte Struktur zerstört. So verhindert beispielsweise ein KP-Verriegelung, dass die Struktur, auf die er verweist, durch den Lazy-Writer-Prozess zerstört wird. Weitere Informationen dazu, wie der Lazy Writer-Prozess mit der SQL Server-Pufferseitenverwaltung verwendet wird, finden Sie unter Write pages in the Database Engine.

• SH: Gemeinsame Sperre. Erforderlich, um die referenzierte Struktur zu lesen (z. B. eine Datenseite lesen). Mehrere Threads können gleichzeitig auf eine Ressource zum Lesen unter einem freigegebenen Latch zugreifen.

• UP: Verriegelung aktualisieren. Kompatibel mit SH (Shared einrasten) und KP zu verbringen, aber keine anderen und erlaubt daher keine EX Verriegelung, um in die referenzierte Struktur zu schreiben.

• EX: Exklusive Sperre. Blockiert andere Threads beim Schreiben oder Lesen der referenzierten Struktur. Ein Beispiel hierfür ist die Verwendung zum Ändern der Inhalte einer Seite für den Schutz vor zerrissenen Seiten.

• DT: Riegel zerstören. Muss erworben werden, bevor der Inhalt der referenzierten Struktur zerstört wird. Beispielsweise muss ein DT-Verriegelung durch den Lazy-Writer-Prozess erworben werden, um eine saubere Seite freizugeben, bevor sie der Liste der freien Puffer hinzugefügt wird, die für die Verwendung durch andere Threads verfügbar sind.

Die Verriegelungsmodi haben unterschiedliche Kompatibilitätsebenen, zum Beispiel eine gemeinsame Verriegelung (SH) ist kompatibel mit einer aktualisierenden (UP) oder beibehaltenden (KP) Verriegelung, aber nicht kompatibel mit einer zerstörenden Verriegelung (DT). Mehrere Latches können gleichzeitig für dieselbe Struktur abgerufen werden, solange die Latches kompatibel sind. Wenn ein Thread versucht, ein Verriegelung in einem nicht kompatiblen Modus zu erfassen, wird er in eine Warteschlange gestellt, um auf ein Signal zu warten, das anzeigt, dass die Ressource verfügbar ist. Ein Spinlock vom Typ SOS_Task wird zum Schützen der Warteschlange verwendet, indem der serialisierte Zugriff auf die Warteschlange erzwungen wird. Dieser Spinlock muss zum Hinzufügen von Elementen zur Warteschlange abgerufen werden. Der SOS_Task-Spinlock informiert Threads in der Warteschlange auch, wenn inkompatible Latches freigegeben werden, sodass die wartenden Threads einen kompatiblen Latch abrufen und weiter arbeiten können. Die Warteschlange wird nach der FIFO-Methode (first in, first out) verarbeitet, wenn Latchanforderungen freigegeben werden. Latches unterliegen diesem FIFO-System, um Fairness sicherzustellen und Threadmangel zu vermeiden.

Die Kompatibilität des Latch-Modus wird in der folgenden Tabelle aufgeführt (Ja gibt Kompatibilität an, und "Nein " gibt die Inkompatibilität an):

Latchmodus	KP	SH	UP	EX	DT
KP	Ja	Ja	Ja	Ja	Nein
SH	Ja	Ja	Ja	Nein	Nein
UP	Ja	Ja	Nein	Nein	Nein
EX	Ja	Nein	Nein	Nein	Nein
DT	Nein	Nein	Nein	Nein	Nein

SQL-Server Superlatches und Sublatches

Mit der zunehmenden Präsenz von NUMA-basierten Multi-Core-Systemen hat SQL Server 2005 Superlatches eingeführt, die auch als Sublatches bezeichnet werden, die nur auf Systemen mit 32 oder mehr logischen Prozessoren wirksam sind. Superlatches verbessern die Effizienz der SQL-Engine bei bestimmten Nutzungsmustern in hochgradig konkurrierenden OLTP-Workloads, z. B. wenn bestimmte Seiten ein Muster von starkem Lesen Sie-Nur-Sharing aufweisen (SH) zugreifen, werden aber nur selten beschrieben. Ein Beispiel für eine Seite mit einem solchen Zugriffsmuster ist eine B-Baum- (d. h. Index-) Stammseite; die SQL-Engine erfordert, dass ein gemeinsamer Verriegelung auf der Stammseite gehalten wird, wenn eine Seitenteilung auf einer beliebigen Ebene im B-Baum erfolgt. In einer OLTP-Arbeitslast mit hoher Insert-Last und hoher Gleichzeitigkeit nimmt die Anzahl der Seitenaufteilungen im Allgemeinen entsprechend dem Durchsatz zu, was die Leistung beeinträchtigen kann. Superlatches können eine höhere Leistung beim Zugriff auf gemeinsam genutzte Seiten ermöglichen, wenn mehrere gleichzeitig ausgeführte Worker-Threads SH Verriegelungen. Um dies zu erreichen, erhebt die SQL-Server-Engine eine Verriegelung auf einer solchen Seite dynamisch zu einer Superverriegelung. Ein Superlatch partitioniert einen einzelnen Verriegelung in ein Array von Sublatch-Strukturen, einen Sublatch pro Partition pro CPU-Kern, wobei der Hauptlatch zu einem Proxy-Redirector wird und eine globale Zustandssynchronisierung für schreibgeschützte Latches nicht erforderlich ist. Dabei muss der Worker, der immer einer bestimmten CPU zugewiesen ist, nur die freigegebenen (SH) Unterverriegelung, die dem lokalen Planer zugewiesen ist.

Anmerkung

Die Dokumentation verwendet den Begriff B-Baum im Allgemeinen in Bezug auf Indizes. In Zeilenstoreindizes implementiert die Datenbank-Engine eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder Indizes auf speicheroptimierten Tabellen. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

Das Abrufen kompatibler Latches, z. B. gemeinsamer SuperLatches, nutzt weniger Ressourcen und kann besser als ein nicht partitionierter, gemeinsamer Latch für aktive Seiten skaliert werden, da das Entfernen der Synchronisierungsanforderung des globalen Zustands die Leistung erheblich verbessert wird, indem lediglich auf lokalen NUMA-Arbeitsspeicher zugegriffen wird. Umgekehrt ist der Erwerb eines exklusiven (EX) Superlatch teurer als der Erwerb eines EX regulären Latch, da SQL Signale über alle Unterlatches senden muss. Wenn ein Superlatch beobachtet wird, der ein Muster von schweren EX Wenn der Zugriff auf die Seite nicht möglich ist, kann die SQL-Engine sie zurückstufen, nachdem die Seite aus dem Pufferpool verworfen wurde. Das folgende Diagramm zeigt einen normalen Riegel und einen partitionierten Superlatch:

Verwenden Sie das SQL Server:Latches-Objekt und die zugehörigen Leistungsindikatoren im Leistungsmonitor, um Informationen zu Superlatches zu sammeln, einschließlich der Anzahl der Superlatches, der Superlatch-Beförderungen pro Sekunde und der Superlatch-Herabstufungen pro Sekunde. Weitere Informationen zum SQL Server:Latches-Objekt und den zugehörigen Leistungsindikatoren finden Sie unter SQL Server, Latches-Objekt.

Latchwartetypen

Die kumulativen Wartedaten werden von SQL-Server nachverfolgt und können über die dynamische Verwaltungsansicht (DMW) abgerufen werden. sys.dm_os_wait_stats. SQL Server nutzt drei Latchwartetypen, die vom entsprechenden Wert wait_type in der DMV sys.dm_os_wait_stats definiert werden:

• Puffer (BUF) verriegeln: wird verwendet, um die Konsistenz von Index- und Datenseiten für Benutzerobjekte zu gewährleisten. Sie werden auch verwendet, um den Zugriff auf Datenseiten zu schützen, die SQL Server für Systemobjekte verwendet. Beispielsweise werden Seiten, die Zuteilungen verwalten, durch Pufferlatches geschützt. Dazu gehören die Seiten Page Free Space (PFS), Global Allocation Map (GAM), Shared Global Allocation Map (SGAM) und Index Allocation Map (IAM). Pufferspeicher werden gemeldet in sys.dm_os_wait_stats mit einer wait_type of PAGELATCH_*.

• Nicht-Puffer (Nicht-BUF) Verriegelung: wird verwendet, um die Konsistenz aller In-Memory-Strukturen mit Ausnahme der Pufferpoolseiten zu gewährleisten. Alle Wartezeiten für Nicht-Puffer-Latches werden als Fehler gemeldet. wait_type of LATCH_*.

• IO-Verriegelung: eine Teilmenge von Pufferlatches, die die Konsistenz derselben Strukturen garantieren, die durch Pufferlatches geschützt sind, wenn diese Strukturen mit einer E/A-Operation in den Pufferpool geladen werden müssen. E/A-Latches hindern andere Threads am Laden derselben Seite in den Pufferpool mit einem inkompatiblen Latch. Assoziiert mit einer wait_type of PAGEIOLATCH_*.

  Anmerkung

  Wenn erhebliche PAGEIOLATCH Wartezeiten angezeigt werden, bedeutet dies, dass SQL Server auf das E/A-Subsystem wartet. Während eine bestimmte Anzahl von PAGEIOLATCH Wartezeiten erwartet wird und normales Verhalten erfolgt, sollten Sie untersuchen, warum das E/A-Subsystem unter Druck steht, wenn die durchschnittliche PAGEIOLATCH Wartezeit konsistent über 10 Millisekunden (ms) liegt.

Wenn Sie beim Untersuchen der DMV sys.dm_os_wait_stats Nicht-Puffer-Latches finden, müssen Sie sys.dm_os_latch_stats untersuchen, um eine detaillierte Aufschlüsselung der kumulativen Warteinformationen für Nicht-Puffer-Latches zu erhalten. Alle Puffer-Verriegelung-Wartezeiten werden unter der BUFFER Die verbleibenden werden zur Klassifizierung von Nicht-Puffer-Latches verwendet.

Symptome und Ursachen von SQL Server-Latchkonflikten

Bei einem ausgelasteten System mit hoher Parallelität ist es normal, aktuelle Konkurrenz auf Strukturen zu beobachten, auf die häufig zugegriffen wird und die durch Verriegelungen und andere Kontrollmechanismen in SQL Server geschützt werden. Es wird als problematisch angesehen, wenn die Konkurrenz und die Wartezeit, die mit dem Erwerb des Verriegelung für eine Seite verbunden sind, ausreichen, um die Auslastung der Ressourcen (CPU) zu reduzieren, was den Durchsatz behindert.

Beispiel für einen Latchkonflikt

Im folgenden Diagramm stellt die blaue Linie den Durchsatz in SQL Server in Form von Transaktionen pro Sekunde dar. Die schwarze Linie stellt die durchschnittliche Dauer von Latchwartevorgängen für Seiten dar. In diesem Fall führt jede Transaktion eine INSERT in einen geclusterten Index mit einem sequentiell ansteigenden führenden Wert, wie z. B. beim Auffüllen eines IDENTITY Spalte vom Datentyp bigint. Da die Anzahl der CPUs auf 32 ansteigt, ist offensichtlich, dass der Gesamtdurchsatz zurückgegangen ist und die Seitenriegelwartezeit auf etwa 48 Millisekunden gestiegen ist, wie durch die schwarze Linie belegt. Durch diese umgekehrte Beziehung zwischen dem Durchsatz und der Wartezeit für Seitenlatches ist ein gängiges Szenario, das mühelos diagnostiziert werden kann.

Leistung nach Auflösung des Latchkonflikts

Wie im folgenden Diagramm gezeigt, wurde der Engpass der Wartevorgänge für Seitenlatches in SQL Server gelöst und der Durchsatz in Transaktionen pro Sekunde wurde um 300 % gesteigert. Dies wurde mit dem Verfahren Verwenden der Hashpartitionierung mit einer berechneten Spalte erzielt, das später in diesem Artikel erläutert wird. Diese Leistungsverbesserung gilt für Systeme mit einer hohen Anzahl an Kernen und einem hohen Grad an Parallelität.

Aspekte von Latchkonflikten

Latchkonflikte, die die Leistung in OLTP-Umgebungen behindern, werden üblicherweise durch hohe Parallelität im Zusammenhang mit einem oder mehreren der folgenden Faktoren verursacht:

Faktor	Details
Hohe Anzahl von logischen CPUs, die von SQL-Server verwendet werden	Ein Latchkonflikt kann auf jedem System mit mehreren Kernen auftreten. In SQLCAT wurde übermäßige Latch-Konkurrenz, die sich auf die Anwendungsleistung über akzeptable Grenzen hinaus auswirkt, am häufigsten auf Systemen mit 16+ CPU-Kernen beobachtet und kann zunehmen, wenn mehr Kerne verfügbar werden.
Schemadesign und Zugriffsmuster	Der Umfang der B-Struktur, der Entwurf von gruppierten und nicht gruppierten Indizes, die Größe und Dichte der Zeilen pro Seite und die Zugriffsmuster (Lese-, Schreib- und Löschaktivitäten) sind Faktoren, die zu übermäßigen Seitenlatchkonflikten beitragen können.
Hoher Grad an Gleichzeitigkeit auf der Anwendungsebene	Übermäßige Seitenlatchkonflikte treten in der Regel im Zusammenhang mit einer großen Menge gleichzeitiger Anforderungen der Logikschicht auf. Es gibt gewisse Programmierverfahren, die ebenfalls zu einer hohen Anzahl von Anforderungen für eine spezifische Seite führen können.
Layout der von SQL-Server-Datenbanken verwendeten logischen Dateien	Das logische Dateilayout kann das Ausmaß der durch Zuweisungsstrukturen wie Page Free Space (PFS), Global Allocation Map (GAM), Shared Global Allocation Map (SGAM) und Index Allocation Map (IAM) verursachten Seitenlatch-Konkurrenz beeinflussen. Für weitere Informationen siehe TempDB-Überwachung und Behandeln von Problemen: Engpass bei der Zuweisung.
Leistung des I/O-Subsystems	Erhebliche PAGEIOLATCH Wartezeiten deuten darauf hin, dass SQL Server auf das E/A-Subsystem wartet.

Diagnose von SQL Server-Latch-Konflikten

In diesem Abschnitt finden Sie Informationen zur Diagnose von SQL-Server Verriegelung Contention, um festzustellen, ob dies für Ihre Umgebung problematisch ist.

Tools und Methoden für die Diagnose von Latchkonflikten

Die primären Tools für die Diagnose von Latchkonflikten sind:

• Leistungsmonitor zum Überwachen der CPU-Auslastung und Wartezeiten innerhalb von SQL Server und festlegen, ob eine Beziehung zwischen CPU-Auslastung und Wartezeiten besteht.

• Die SQL Server-DMVs, die zum Ermitteln der betroffenen Ressource und des Latchtyps verwendet werden können, der das Problem verursacht.

• In einigen Fällen müssen Arbeitsspeicherabbilder des SQL Server-Prozesses mit Windows-Debugtools abgerufen und analysiert werden.

Anmerkung

Diese erweiterte Problembehandlung ist in der Regel nur für die Problembehandlung von Konflikten bei Nicht-Pufferlatches erforderlich. Möglicherweise möchten Sie microsoft Product Support Services für diese Art von erweiterter Problembehandlung einbeziehen.

Der technische Prozess für die Diagnose von Latchkonflikten kann in den folgenden Schritten zusammengefasst werden:

1. Bestimmen Sie, dass es zu Konflikten kommt, die möglicherweise mit dem Verriegelung zusammenhängen.

2. Verwenden Sie die in Anhang: SQL Server Latch Contention Scripts bereitgestellten DMV-Ansichten, um den Typ des Verschlusses und der betroffenen Ressourcen zu bestimmen.

3. Lösen Sie mit einem der unter Behandeln von Latchkonflikten für verschiedene Tabellenmuster beschriebenen Verfahren.

Anzeichen für Latchkonflikte

Wie bereits erwähnt sind Latchkonflikte nur problematisch, wenn der Konflikt und die Wartezeiten zum Abrufen von Seitenlatches den Durchsatz daran hindert, zu steigen, wenn CPU-Ressourcen verfügbar sind. Zum Ermitteln der akzeptablen Menge von Konflikten ist ein ganzheitlicher Ansatz erforderlich, der Leistungs- und Durchsatzanforderungen zusammen mit den verfügbaren E/A- und CPU-Ressourcen berücksichtigt. Dieser Abschnitt führt Sie durch die Bestimmung der Auswirkungen von Verriegelung Contention auf den Workload wie folgt:

1. Messen Sie die Gesamtwartezeiten während eines repräsentativen Tests.
2. Sortieren Sie sie der Reihe nach.
3. Bestimmen Sie den Anteil der Wartezeiten, die mit Verriegelungen zusammenhängen.

Kumulative Wartezeit-Informationen sind verfügbar im sys.dm_os_wait_stats DMV. Die häufigste Art der Verriegelung-Konkurrenz ist die Puffer-Verriegelung-Konkurrenz, die sich in einer Zunahme der Wartezeiten für Latches mit einem wait_type of PAGELATCH_*. Nicht-Puffer-Latches werden unter dem Begriff "Verriegelung" gruppiert. LATCH* Typ warten. Wie im folgenden Diagramm veranschaulicht wird, sollten Sie zunächst die Systemwartezeiten kumulativ mithilfe der DMV sys.dm_os_wait_stats betrachten, um den Prozentsatz der Gesamtwartezeit zu bestimmen, die von Pufferlatches oder Nicht-Puffer-Latches verursacht wird. Wenn Sie auf nicht gepufferte Latches stoßen, wird die sys.dm_os_latch_stats DMV muss ebenfalls überprüft werden.

Das folgende Diagramm beschreibt die Beziehung zwischen den Informationen, die vom sys.dm_os_wait_stats und sys.dm_os_latch_stats DMVs.

Weitere Informationen zum DMV finden Sie in der sys.dm_os_wait_stats SQL Server-Hilfe sys.dm_os_wait_stats .

Weitere Informationen zum DMV finden Sie in der sys.dm_os_latch_stats SQL Server-Hilfe sys.dm_os_latch_stats .

Die folgenden Measures der Latchwartezeit sind Anzeichen dafür, dass übermäßige Latchkonflikte sich auf die Anwendungsleistung auswirken:

• Die durchschnittliche Wartezeit für Seiten-Latchs steigt mit dem Durchsatz: Wenn die durchschnittlichen Page-Verriegelung-Wartezeiten konstant mit dem Durchsatz ansteigen und wenn die durchschnittlichen Puffer-Verriegelung-Wartezeiten ebenfalls über den erwarteten Antwortzeiten der Datenträger liegen, sollten Sie die aktuell wartenden Tasks mit dem sys.dm_os_waiting_tasks DMV. Mittelwerte können irreführend sein, wenn sie isoliert analysiert werden, damit es wichtig ist, das System live zu betrachten, wenn möglich, um die Arbeitsauslastungsmerkmale zu verstehen. Überprüfen Sie insbesondere, ob es hohe Wartezeiten auf PAGELATCH_EX und/oder PAGELATCH_SH Anforderungen auf beliebigen Seiten. Führen Sie die folgenden Schritte aus, um steigende durchschnittliche Wartezeiten für Seitenlatches bei zunehmenden Durchsatz zu diagnostizieren:

  • Verwenden Sie die Beispielskripts Abfragen von sys.dm_os_waiting_tasks nach Sitzungs-ID und Berechnen der Wartezeiten in einem Zeitraum, um die aktuellen wartenden Tasks zu untersuchen und die durchschnittliche Latchwartezeit zu messen.

  • Verwenden Sie das Beispielskript Abfragen von Pufferdeskriptoren zum Ermitteln von Objekten als Ursache für Latchkonflikte, um den Index und die zugrunde liegende Tabelle zu ermitteln, in denen der Konflikt aufgetreten ist.

  • Messen Sie die durchschnittliche Wartezeit für die Seitenverriegelung mit dem Leistung Monitor Zähler MSSQL%InstanceName%\Wartestatistiken\Durchschnittliche Wartezeit der Verriegelung-Wartezeiten\ oder durch Ausführung des sys.dm_os_wait_stats DMV.

  Anmerkung

  Um die durchschnittliche Wartezeit für einen bestimmten Wartetyp zu berechnen (zurückgegeben durch sys.dm_os_wait_statswt_:type), dividieren Sie die Gesamtwartezeit (zurückgegeben als wait_time_ms) durch die Anzahl der Wartevorgänge (zurückgegeben als waiting_tasks_count).

• Prozentualer Anteil der Gesamtwartezeit, der während der Spitzenlast auf Verriegelung-Wartetypen entfällt: Wenn die durchschnittliche Verriegelung-Wartezeit als prozentualer Anteil an der Gesamtwartezeit mit der Anwendungslast ansteigt, beeinträchtigt die Verriegelung-Konkurrenz möglicherweise die Leistung und sollte untersucht werden.

  Messen Sie Page-Verriegelung-Wartezeiten und Non-Page-Verriegelung-Wartezeiten mit dem SQL-Server, Objekt Wait Statistik Leistungszähler. Vergleichen Sie dann die Werte für diese Leistungsindikatoren mit Leistungsindikatoren für die CPU, E/A-Vorgänge, den Arbeitsspeicher und den Netzwerkdurchsatz. Beispielsweise eignen sich die Measures „Transaktionen/Sekunde“ und „Batchanforderungen/Sekunde“ für die Ressourcennutzung.

  Anmerkung

  Die relative Wartezeit für jeden Wartetyp ist nicht in der sys.dm_os_wait_stats DMV enthalten, da diese DMV Wartezeiten seit dem letzten Start der SQL Server-Instanz misst oder die kumulierten Wartezeitstatistiken mithilfe von DBCC SQLPERF zurückgesetzt wurden. Um die relative Wartezeit für jede Warteart zu berechnen, machen Sie einen Schnappschuss von sys.dm_os_wait_stats vor der Spitzenlast, nach der Spitzenlast, und berechnen Sie dann die Differenz. Hierzu können Sie das Beispielskript Berechnen der Wartevorgänge in einem Zeitraum verwenden.

  Für eine Nicht-Produktionsumgebung nur, löschen Sie die sys.dm_os_wait_stats DMV mit dem folgenden Befehl:

  DBCC SQLPERF ('sys.dm_os_wait_stats', 'CLEAR');
  

  Ein ähnlicher Befehl kann ausgeführt werden, um die sys.dm_os_latch_stats DMV:

  DBCC SQLPERF ('sys.dm_os_latch_stats', 'CLEAR');
  

• Der Durchsatz steigt nicht und kann in einigen Fällen sinken, wenn die Auslastung der Anwendung und die Anzahl der für SQL Server verfügbaren CPUs steigen: Dies wurde im Beispiel für Latch Contention veranschaulicht.

• Die CPU-Auslastung erhöht sich nicht, wenn die Anwendungs-Workload zunimmt: Wenn die CPU-Auslastung auf dem System nicht ansteigt, während die Parallelität durch den Anwendungsdurchsatz zunimmt, ist dies ein Indikator dafür, dass SQL Server auf etwas wartet und symptomatisch für eine Latch-Contention ist.

Analysieren Sie die Grundursache. Auch wenn jede der vorstehenden Bedingungen erfüllt ist, ist es dennoch möglich, dass die Ursache für die Leistungsprobleme an anderer Stelle liegt. In den meisten Fällen werden suboptimale CPU-Auslastungen durch andere Arten von Wartezeiten verursacht, z. B. Blockieren von Sperren, E/A-bezogene Wartezeiten oder netzwerkbezogene Probleme. Als Faustregel empfiehlt es sich immer, die Ressourcenwartezeit aufzulösen, die den größten Anteil der Gesamtwartezeit darstellt, bevor Sie mit einer eingehenderen Analyse fortfahren.

Analysieren Sie die aktuellen Wartepufferverriegelungen

Puffer-Verriegelung-Konkurrenz manifestiert sich als eine Erhöhung der Wartezeiten für Latches mit einem wait_type von entweder PAGELATCH_* oder PAGEIOLATCH_* wie im Fenster sys.dm_os_wait_stats DMV. Um das System in Echtzeit zu betrachten, führen Sie die folgende Abfrage auf einem System aus, das in die sys.dm_os_wait_stats, sys.dm_exec_sessions und sys.dm_exec_requests DMVs. Anhand der Ergebnisse können Sie den aktuellen Wartetyp für Sitzungen ermitteln, die auf dem Server ausgeführt werden.

SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Die von dieser Abfrage zur Verfügung gestellten Statistiken werden wie folgt beschrieben:

Statistik	Beschreibung
session_id	ID der Sitzung, die dem Task zugeordnet ist.
wait_type	Dies ist der Typ des Wartevorgangs, den SQL Server in der Engine aufgezeichnet hat und der die Ausführung einer aktuellen Anforderung verhindert.
last_wait_type	Wenn diese Anforderung zuvor bereits blockiert war, gibt diese Spalte den Typ des letzten Wartevorgangs zurück. Lässt keine NULL-Werte zu.
wait_duration_ms	Die Gesamtwartezeit in Millisekunden, die für diesen Wartetyp aufgebracht wurde, seitdem die SQL Server-Instanz gestartet oder die kumulativen Wartestatistik zuletzt zurückgesetzt wurde.
blocking_session_id	ID der Sitzung, die die Anforderung blockiert.
blocking_exec_context_id	ID des Ausführungskontexts, der dem Task zugeordnet ist.
resource_description	In der Spalte resource_description ist die exakte Seite im Format <database_id>:<file_id>:<page_id> aufgeführt, auf die gewartet wird.

Die folgende Abfrage gibt Informationen für alle Nicht-Puffer-Latches zurück.

SELECT * FROM sys.dm_os_latch_stats
WHERE latch_class <> 'BUFFER'
ORDER BY wait_time_ms DESC;

Die von dieser Abfrage zur Verfügung gestellten Statistiken werden wie folgt beschrieben:

Statistik	Beschreibung
latch_class	Dies ist der Typ des Latch, den SQL Server in der Engine aufgezeichnet hat und der die Ausführung einer aktuellen Anforderung verhindert.
waiting_requests_count	Hierbei handelt es sich um die Anzahl der Wartevorgänge für Latches in dieser Klasse seit dem Neustart von SQL Server. Dieser Leistungsindikator wird beim Starten eines Latchwartevorgangs erhöht.
wait_time_ms	Hierbei handelt es sich um die Gesamtwartezeit in Millisekunden für diesen Latchtyp.
max_wait_time_ms	Hierbei handelt es sich um die maximale Zeit in Millisekunden, die alle Anforderungen auf diesen Latchtyp gewartet haben.

Die von dieser DMV zurückgegebenen Werte werden kumulativ seit dem letzten Neustart der Datenbank-Engine oder der letzten Zurücksetzung der DMV gezählt. Verwenden Sie die sqlserver_start_time-Spalte in sys.dm_os_sys_info, um die aktuellste Startzeit der Datenbank-Engine zu suchen. Bei einem System, das schon lange in Ausführung ist, bedeutet dies, dass einige Statistiken wie max_wait_time_ms selten nützlich sind. Der folgende Befehl kann dazu verwendet werden, die Wartestatistiken für diese DMV zurückzusetzen:

DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

Szenarios für SQL Server-Latchkonflikte

Die folgenden Szenarios sind dafür bekannt, übermäßige Latchkonflikte zu verursachen.

Konflikt beim Einfügen der letzten/nachstehenden Seite

Ein gängiges OLTP-Verfahren besteht darin, einen gruppierten Index für eine Identitäts- oder Datumsspalte zu erstellen. Dies trägt zu einer guten physischen Sortierung des Index bei, was sich positiv auf die Leistung von Lese- und Schreibvorgängen im Index auswirken kann. Dieser Schemaentwurf kann jedoch auch zu versehentlichen Latchkonflikten führen. Dieses Problem tritt häufig bei großen Tabellen mit kleinen Zeilen und Einfügevorgängen in einen Index, der eine sequenziell steigende führende Schlüsselspalte enthält, z. B. ein steigender Integer oder Datetime-Schlüssel. In diesem Szenario führt die Anwendung, wenn überhaupt, nur selten Update- oder Löschvorgänge durch, es sei denn, Vorgänge werden archiviert.

Im folgenden Beispiel möchten Thread 1 und Thread 2 beide einen Datensatz einfügen, der auf Seite 299 gespeichert wird. Aus Sicht der logischen Sperren gibt es kein Problem, da Sperren auf Zeilenebene verwendet werden und exklusive Sperren für beide Datensätze auf derselben Seite gleichzeitig gehalten werden können. Zum Sicherstellen der Integrität des physischen Arbeitsspeichers kann immer nur ein Thread einen exklusiven Latch abrufen, damit der Zugriff auf die Seite serialisiert wird, um verlorene Updates im Arbeitsspeicher zu vermeiden. In diesem Fall erhält Thread 1 die exklusive Sperre: Thread 2 wartet, wodurch in den Wartestatistiken eine PAGELATCH_EX Wartezeit für diese Ressource registriert wird. Dies wird durch die Option wait_type Wert im Menü sys.dm_os_waiting_tasks DMV.

Dieser Konflikt wird häufig als „Last Page Insert“ (Einfügen der letzten Seite) bezeichnet, da er wie im folgenden Diagramm gezeigt am Edge am rechten Rand der B-Struktur auftritt:

Diese Art von Latchkonflikt kann folgendermaßen erklärt werden: Wenn eine neue Zeile in einen Index eingefügt wird, verwendet SQL Server den folgenden Algorithmus, um die Änderung auszuführen:

1. Durchlaufen Sie die B-Struktur, um die richtige Seite zu finden, die den neuen Datensatz enthalten soll.

2. Verriegeln Sie die Seite mit PAGELATCH_EXund verhindern, dass andere sie ändern, und erwerben gemeinsame Latches (PAGELATCH_SH) auf allen Nicht-Blatt-Seiten.

   Anmerkung

   In einigen Fällen benötigt die SQL Engine EX Latches, die auch auf Nicht-Blatt-B-Baum-Seiten erfasst werden. Wenn zum Beispiel ein Seitensplit auftritt, müssen alle Seiten, die direkt betroffen sind, exklusiv gelatched werden (PAGELATCH_EX).

3. Zeichnen Sie einen Protokolleintrag auf, der angibt, dass die Zeile geändert wurde.

4. Fügen Sie die Zeile zur Seite hinzu, und markieren Sie die Seite als geändert.

5. Heben Sie die Latches für alle Seiten auf.

Wenn der Tabellenindex auf einem sequenziell steigenden Schlüssel basiert, wechselt jedes neue Einfügen an die gleiche Seite am Ende der B-Struktur, bis diese Seite voll ist. Bei Szenarien mit hoher Gleichzeitigkeit kann dies zu Kontentionen am rechten Rand des B-Baums führen und in gruppierten und nicht gruppierten Indizes auftreten. Tabellen, die von dieser Art von Konflikten betroffen sind, akzeptieren in erster Linie INSERT Abfragen und Seiten für die problematischen Indizes sind normalerweise relativ dicht (z. B. entspricht eine Zeilengröße von ~165 Byte (einschließlich Zeilen-Overhead) ~49 Zeilen pro Seite). In diesem Insert-lastigen Beispiel erwarten wir PAGELATCH_EX/PAGELATCH_SH Dies ist die typische Beobachtung, wenn es zu Wartezeiten kommt. Um Page Verriegelung-Wartezeiten im Vergleich zu Tree Page Verriegelung-Wartezeiten zu untersuchen, verwenden Sie den sys.dm_db_index_operational_stats DMV.

In der folgenden Tabelle werden die wichtigsten Faktoren zusammengefasst, die bei dieser Art von Latchkonflikten festgestellt werden:

Faktor	Typische Beobachtungen
Logische CPUs, die von SQL-Server verwendet werden	Diese Art von Latchkonflikten tritt hauptsächlich in Systemen mit mehr als 16 CPU-Kernen und am häufigsten auf Systemen mit mehr als 32 CPU-Kernen auf.
Schemadesign und Zugriffsmuster	Hierbei wird ein sequenziell steigender Identitätswert als führende Spalte in einem Index für eine Tabelle für Transaktionsdaten verwendet. Der Index weist einen steigenden Primärschlüssel mit einer hohen Rate von Einfügevorgängen auf. Der Index weist mindestens einen sequenziell steigenden Spaltenwert auf. In der Regel liegt eine kleine Zeilengröße mit vielen Zeilen pro Seite vor.
Wartentyp beobachtet	Viele Threads konkurrieren um dieselbe Ressource mit exklusiver (EX) oder freigegeben (SH) Verriegelung-Wartezeiten, die mit der gleichen resource_description in der sys.dm_os_waiting_tasks DMV wie zurückgegeben von der Abfrage von sys.dm_os_waiting_tasks, sortiert nach Wartezeit.
Zu berücksichtigende Designfaktoren	Erwägen Sie, die Reihenfolge der Indexspalten zu ändern, wie in der Strategie zur Minderung nicht sequentieller Indizes beschrieben, wenn Sie garantieren können, dass Einfügungen gleichmäßig über den B-Baum verteilt werden. Wenn die Hash-Partitionierungs-Minderungsstrategie verwendet wird, entfällt die Möglichkeit, die Partitionierung für andere Zwecke wie die Gleitfensterarchivierung zu nutzen. Die Verwendung der Hashpartitionsminderungsstrategie kann zu Partitionslöschproblemen bei SELECT Abfragen führen, die von der Anwendung verwendet werden.

Latchkonflikte für kleine Tabellen mit einem nicht gruppierten Index und zufälligen Einfügungen (Warteschlangentabelle)

Dieses Szenario tritt typischerweise auf, wenn eine SQL-Tabelle als temporäre Warteschlange verwendet wird (z. B. in einem asynchronen Messaging-System).

In diesem Szenario wird ausschließlich (EX) und gemeinsam (SH) Verriegelung Contention kann unter den folgenden Bedingungen auftreten:

• Einfüge-, Auswahl-, Aktualisierungs- oder Löschvorgänge treten unter hoher Parallelität auf.
• Die Zeilengröße ist relativ klein (was zu dichten Seiten führt).
• Die Anzahl der Zeilen in der Tabelle ist relativ klein, was zu einer flachen B-Struktur führt, die durch eine Indextiefe von zwei oder drei Ebenen definiert wird.

Anmerkung

Selbst bei tieferen B-Strukturen als diesen können Konflikte mit dieser Art von Zugriffsmuster auftreten, wenn die Häufigkeit der Datenbearbeitungssprache (DML) und Parallelität des Systems ausreichend hoch sind. Der Grad der Verriegelung-Konkurrenz kann mit zunehmender Gleichzeitigkeit ausgeprägt werden, wenn dem System 16 oder mehr CPU-Kerne zur Verfügung stehen.

Latchkonflikte können sogar auftreten, wenn der Zugriff in der gesamten B-Struktur zufällig erfolgt, z. B. wenn eine nicht sequenzielle Spalte der führende Schlüssel in einem gruppierten Index ist. Im folgenden Screenshot sehen Sie ein System mit dieser Art von Latchkonflikt. In diesem Beispiel ist die Dichte der Seiten, die von der kleinen Zeilengröße und der relativ flachen B-Struktur verursacht wird, die Ursache des Konflikts. Bei zunehmender Parallelität treten Latchkonflikte bei Seiten auf, obwohl die Einfügevorgänge zufällig auf die B-Struktur verteilt werden, da die führende Spalte im Index eine GUID ist.

Im folgenden Screenshot treten die Wartezeiten auf Pufferdatenseiten und PFS-Seiten auf. Selbst wenn die Anzahl der Datendateien erhöht wurde, blieb der Latchkonflikt auf den Pufferdatenseiten bestehen.

In der folgenden Tabelle werden die wichtigsten Faktoren zusammengefasst, die bei dieser Art von Latchkonflikten festgestellt werden:

Faktor	Typische Beobachtungen
Logische CPUs, die von SQL-Server verwendet werden	Der Latchkonflikt tritt hauptsächlich auf Computern mit mehr als 16 CPU-Kernen auf.
Schemadesign und Zugriffsmuster	Es liegt eine hohe Rate INSERT-, SELECT-, UPDATE- und DELETE-Zugriffsmustern für kleine Tabellen vor. Es liegt eine flache B-Struktur vor (Indextiefe von zwei oder drei Ebenen). Es liegt eine kleine Zeilengröße vor (viele Datensätze pro Seite).
Grad der Gleichzeitigkeit	Verriegelung Contention tritt nur bei einer hohen Anzahl gleichzeitiger Anforderungen von der Anwendungsebene auf.
Wartentyp beobachtet	Wartezeiten auf Puffer beobachten (PAGELATCH_EX und PAGELATCH_SH) und Nicht-Puffer-Sperre ACCESS_METHODS_HOBT_VIRTUAL_ROOT aufgrund von Root-Splits. Auch PAGELATCH_UP wartet auf PFS-Seiten. Weitere Informationen zu Wartezeiten bei Nicht-Puffer-Latches finden Sie in der SQL Server-Hilfe unter sys.dm_os_latch_stats.

Die Kombination einer flachen B-Struktur und der zufälligen Einfügungen im Index neigt dazu, Seitenteilungen in der B-Struktur zu verursachen. Um eine Seitenteilung durchzuführen, muss SQL Server freigegebene (SH) Verriegelungen für alle Ebenen abrufen und dann exklusive (EX) Verriegelungen auf den Seiten im B-Baum erwerben, die an den Seitenteilungen beteiligt sind. Auch bei hoher Parallelität und wenn Daten kontinuierlich eingefügt und gelöscht werden, können B-Baum-Wurzelteilungen auftreten. In diesem Fall müssen andere Einfügungen möglicherweise auf alle Nicht-Puffer-Latches warten, die im B-Baum erfasst werden. Dies äußert sich in einer großen Anzahl von Warteschleifen auf dem ACCESS_METHODS_HOBT_VIRTUAL_ROOT Riegeltyp im Feld sys.dm_os_latch_stats DMV.

Das folgende Skript kann geändert werden, um die Tiefe der B-Struktur für die Indizes in der betroffenen Tabelle zu ermitteln.

SELECT
    o.name AS [table],
    i.name AS [index],
    indexProperty(object_id(o.name), i.name, 'indexDepth') + indexProperty(object_id(o.name), i.name, 'isClustered') AS depth, --clustered index depth reported doesn't count leaf level
    i.[rows] AS [rows],
    i.origFillFactor AS [fillFactor],
    CASE (indexProperty(object_id(o.name), i.name, 'isClustered'))
        WHEN 1 THEN 'clustered'
        WHEN 0 THEN 'nonclustered'
        ELSE 'statistic'
    END AS type
FROM sysIndexes AS i
     INNER JOIN sysObjects AS o
         ON o.id = i.id
WHERE o.type = 'u'
      AND indexProperty(object_id(o.name), i.name, 'isHypothetical') = 0 --filter out hypothetical indexes
      AND indexProperty(object_id(o.name), i.name, 'isStatistics') = 0 --filter out statistics
ORDER BY o.name;

Latchkonflikte auf PFS-Seiten

PFS steht für Seitenfreier Speicherplatz, SQL Server weist jeder 8088 Seiten (beginnend mit PageID = 1) in jeder Datenbankdatei eine PFS-Seite zu. Jedes Byte auf der PFS-Seite zeichnet Informationen auf, darunter wie viel freier Speicherplatz auf der Seite vorhanden ist, ob er zugewiesen ist oder nicht und ob die Seite Geisterdatensätze speichert. Die PFS-Seite enthält Informationen über die Seiten, die für die Zuteilung verfügbar sind, wenn eine neue Seite für einen INSERT- oder UPDATE-Vorgang benötigt wird. Die PFS-Seite muss in mehreren Szenarien aktualisiert werden, unter anderem wenn Zuweisungen oder Freigaben erfolgen. Da die Verwendung eines UP-Latches zum Schutz der PFS-Seite erforderlich ist, können Latchkonflikte auf PFS-Seiten auftreten, wenn Sie über relativ wenige Datendateien in einer Dateigruppe und eine große Anzahl von CPU-Kernen verfügen. Eine einfache Lösung hierfür besteht darin, die Anzahl der Dateien pro Dateigruppe zu erhöhen.

Warnung

Das Erhöhen der Anzahl von Dateien pro Dateigruppe kann sich negativ auf die Leistung bestimmter Lasten auswirken, z. B. das Laden mit vielen großen Sortiervorgängen, die Arbeitsspeicher auf den Datenträger überlaufen.

Wenn viele PAGELATCH_UP Wartezeiten werden für PFS- oder SGAM-Seiten in tempdbFühren Sie die folgenden Schritte aus, um diesen Engpass zu beseitigen:

1. Fügen Sie Datendateien hinzu tempdb , damit die Anzahl der tempdb-Datendateien der Anzahl der Prozessorkerne auf Ihrem Server entspricht.

2. Aktivieren Sie den SQL Server-Trace-Flag 1118.

Weitere Informationen zu Zuordnungsengpässen, die durch Konflikte auf Systemseiten verursacht werden, finden Sie im Blogbeitrag Was ist Zuordnungsengpässe?

Tabellenwertfunktionen und Latchkonflikte in tempdb

Neben Zuweisungskonflikten gibt es weitere Faktoren, die zu Verriegelung-Konflikten führen können tempdbwie z. B. die intensive Nutzung von TVF in Abfragen.

Behandlung von Verriegelung Contention für verschiedene Tabellenmuster

In den folgenden Abschnitten werden Verfahren beschrieben, die dazu verwendet werden können, Leistungsprobleme bei übermäßigen Latchkonflikten zu behandeln oder zu umgehen.

Verwenden eines nicht sequenziellen führenden Indexschlüssels

Eine Methode zur Behandlung von Latchkonflikten ist das Ersetzen eines sequenziellen Indexschlüssels durch einen nicht sequenziellen Schlüssel, um Einfügevorgänge gleichmäßig auf einen Indexbereich zu verteilen.

Dies geschieht in der Regel durch eine führende Spalte im Index, die die Workload proportional verteilt. Verfügbare Optionen:

Option: Verwenden Sie eine Spalte innerhalb der Tabelle, um die Werte über den Indexschlüsselbereich zu verteilen

Bewerten Sie Ihre Arbeitsauslastung, um einen natürlichen Wert zu erhalten, der zum Verteilen der Einfügevorgänge auf den Schlüsselbereich verwendet werden kann. Betrachten Sie zum Beispiel ein Szenario beim Geldautomat-Banking, bei dem ATM_ID ein guter Kandidat für die Verteilung von Einträgen in der Transaktionstabelle für Abhebungen sein könnte, da ein Kunde jeweils nur einen Geldautomaten nutzen kann. Ähnlich in einem Point of Sales System, vielleicht Checkout_ID oder eine Store-ID wäre ein natürlicher Wert, der zur Verteilung von Einfügungen über einen Schlüsselbereich verwendet werden könnte. Zum Erstellen eines zusammengesetzten Indexschlüssels bei dieser Technik ist es erforderlich, dass die führende Schlüsselspalte entweder der Wert der identifizierten Spalte ist oder ein Hash dieses Werts in Kombination mit einer oder mehreren zusätzlichen Spalten eingesetzt wird, um Eindeutigkeit zu gewährleisten. In den meisten Fällen funktioniert ein Hash des Werts am besten, da zu viele unterschiedliche Werte zu einer schlechten physischen Organisation führen. Beispielsweise könnte ein Hashwert in einem POS-System aus der Store-ID erstellt werden, bei der es sich um ein Modulo handelt, das der Anzahl der CPU-Kerne entspricht. Dieses Verfahren würde zu einer relativ kleinen Anzahl von Bereichen in der Tabelle führen, dies würde jedoch ausreichen, um die Einfügevorgänge auf eine Weise zu verteilen, mit der Latchkonflikte vermieden werden würden. Dieses Verfahren wird auf der folgenden Abbildung veranschaulicht.

Wichtig

Dieses Muster widerspricht den herkömmlichen bewährten Methoden für die Indizierung. Diese Technik trägt zwar dazu bei, eine einheitliche Verteilung von Einträgen über den B-Baum hinweg sicherzustellen, doch kann auch eine Schemaänderung auf Anwendungsebene erforderlich sein. Darüber hinaus kann sich dieses Muster negativ auf die Leistung von Abfragen auswirken, die Bereichsscans erfordern, die den gruppierten Index verwenden. Eine Analyse der Arbeitsauslastungsmuster ist erforderlich, um festzustellen, ob dieser Entwurfsansatz gut funktioniert. Dieses Muster sollte implementiert werden, wenn Sie in der Lage sind, einen Teil der sequenziellen Scan-Leistung zu opfern, um einen Einfügedurchsatz und eine Staffelung zu erreichen.

Dieses Muster wurde während einem Leistungslabprojekt implementiert, wodurch Latchkonflikte auf einem System mit 32 physischen CPU-Kernen gelöst wurden. Diese Tabelle wurde dazu verwendet, den Abschlusssaldo nach einer Transaktion zu speichern. Jede Geschäftstransaktion hat einen einzelnen Einfügevorgang in die Tabelle durchgeführt.

Ursprüngliche Tabellendefinition

Bei der ursprünglichen Tabellendefinition sind übermäßige Latchkonflikte im gruppierten Index „pk_table1“ aufgetreten:

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (TransactionID, UserID);
GO

Anmerkung

Die ursprünglichen Werte der Objektnamen in der Tabellendefinition wurden geändert.

Neu geordnete Indexdefinition

Durch die Neuanordnung der Schlüsselspalten des Indexes mit UserID als führender Spalte im Primärschlüssel ergab sich eine nahezu zufällige Verteilung der Einfügungen auf den Seiten. Die sich daraus ergebende Verteilung war nicht 100 % zufällig, da nicht alle Benutzer zur gleichen Zeit online sind, aber die Verteilung war zufällig genug, um übermäßige Verriegelung-Konflikte zu vermeiden. Eine Einschränkung der Neuanordnung der Indexdefinition besteht darin, dass es erforderlich ist, dass alle Auswahlabfragen für diese Tabelle so angepasst werden, dass sie sowohl UserID als auch TransactionID als Gleichheitsprädikate verwenden.

Wichtig

Stellen Sie sicher, dass Sie alle Änderungen gründlich in einer Testumgebung testen, bevor Sie sie in einer Produktionsumgebung verwenden.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (UserID, TransactionID);
GO

Verwendung eines Hash-Werts als führende Spalte im Primärschlüssel

Die folgende Tabellendefinition kann verwendet werden, um ein Modulo zu erzeugen, das mit der Anzahl der CPUs übereinstimmt. HashValue wird mithilfe des sequenziell steigenden Wertes TransactionID generiert, um eine gleichmäßige Verteilung über den B-Baum sicherzustellen.

CREATE TABLE table1
(
    TransactionID BIGINT NOT NULL,
    UserID INT NOT NULL,
    SomeInt INT NOT NULL
);
GO

-- Consider using bulk loading techniques to speed it up
ALTER TABLE table1
    ADD [HashValue] AS (CONVERT (TINYINT, ABS([TransactionID]) % (32))) PERSISTED NOT NULL;

ALTER TABLE table1
    ADD CONSTRAINT pk_table1 PRIMARY KEY CLUSTERED (HashValue, TransactionID, UserID);
GO

Option: Verwendung einer GUID als führende Schlüsselspalte des Indexes

Wenn kein natürliches Trennzeichen vorhanden ist, kann eine GUID-Spalte als führende Schlüsselspalte des Indexes verwendet werden, um eine einheitliche Verteilung von Einfügungen sicherzustellen. Die Verwendung der GUID als führende Spalte im Indexschlüsselansatz ermöglicht zwar die Partitionierung anderer Features, jedoch kann dieses Verfahren auch potenzielle Nachteile mit mehr Seitenteilungen, schlechter physischer Sortierung und niedriger Seitendichte mit sich bringen.

Anmerkung

Die Verwendung von GUIDs als führende Schlüsselspalten von Indizes ist ein umstrittenes Thema. Eine ausführliche Erörterung der Vor- und Nachteile wird in diesem Artikel nicht behandelt.

Verwenden der Hashpartitionierung mit einer berechneten Spalte

Die Tabellenpartitionierung in SQL Server kann dazu verwendet werden, übermäßige Latchkonflikte zu vermeiden. Das Erstellen eines Hashpartitionierungsschemas mit einer berechneten Spalte für eine partitionierte Tabelle ist ein gängiger Ansatz, der mit den folgenden Schritten durchgeführt wird:

1. Erstellen Sie eine neue Dateigruppe, oder verwenden Sie eine vorhandene Dateigruppe zum Speichern der Partitionen.

2. Wenn Sie eine neue Dateigruppe verwenden, sollten Sie die einzelnen Dateien gleichmäßig auf die LUNs (logische Gerätenummer) verteilen. Achten Sie dabei darauf, dass Sie ein optimales Layout verwenden. Wenn das Zugriffsmuster eine hohe Anzahl von Einfügungen involviert, sollten Sie sicherstellen, dass Sie eine Anzahl von Dateien erstellen, die mit der Anzahl der physischen CPU-Kerne im SQL Server-Computer übereinstimmt.

3. Verwenden Sie den CREATE PARTITION FUNCTION Befehl, um die Tabellen in X-Partitionen zu partitionieren, wobei X die Anzahl der physischen CPU-Kerne auf dem SQL Server-Computer ist. (Mindestens 32 Partitionen)

   Anmerkung

   Eine 1:1-Ausrichtung der Anzahl der Partitionen auf die Anzahl der CPU-Kerne ist nicht immer erforderlich. In vielen Fällen kann dies ein Wert kleiner sein als die Anzahl der CPU-Kerne. Wenn Mehr Partitionen vorhanden sind, kann mehr Aufwand für Abfragen entstehen, die alle Partitionen durchsuchen müssen, und in diesen Fällen können weniger Partitionen helfen. In Tests von 64 und 128 logischen CPU-Systemen mit echten Kundenarbeitsauslastungen hat das SQLCAT festgestellt, dass 32 Partitionen ausreichen, um übermäßige Latchkonflikte zu lösen und Skalierungsziele zu erreichen. Letztendlich sollte die ideale Anzahl der Partitionen mithilfe von Tests bestimmt werden.

4. Verwenden Sie den Befehl CREATE PARTITION SCHEME:

   • Binden Sie die Partitionsfunktion an die Dateigruppen.
   • Fügen Sie eine Hashspalte vom Typ „tinyint“ oder „smallint“ zur Tabelle hinzu.
   • Berechnen Sie eine gute Hashverteilung. Verwenden Sie z. B. HASHBYTES mit modulo oder BINARY_CHECKSUM.

Sie können das folgende Beispielskript entsprechend Ihrer Implementierung anpassen:

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Dieses Skript kann zur Hashpartitionierung einer Tabelle verwendet werden, die Probleme aufgrund von Konflikten beim Einfügen der letzten/nachstehenden Seite aufweist. Mit diesem Verfahren wird der Konflikt aus der letzten Seite verschoben, indem die Tabelle partitioniert und die Einfügevorgänge auf die Tabellenpartitionen mit einer Modulo-Berechnung des Hashwerts verteilt werden.

Ergebnisse der Hashpartitionierung mit einer berechneten Spalte

Wie im folgenden Diagramm gezeigt wird der Konflikt durch dieses Verfahren aus der letzten Seite verschoben, indem der Index für die Hashfunktion neu erstellt und so viele Partitionen erstellt werden, wie physische CPU-Kerne im SQL Server-Computer vorhanden sind. Die Einfügevorgänge werden weiterhin am Ende des logischen Bereichs (ein sequenziell steigender Wert) eingefügt, jedoch stellt die Modulo-Berechnung des Hashwerts sicher, dass die Einfügevorgänge auf die verschiedenen B-Strukturen verteilt werden, wodurch der Engpass gemindert wird. Dies wird in den folgenden Abbildungen veranschaulicht:

Nachteile der Hashpartitionierung

Zwar kann die Hashpartitionierung Konflikte bei Einfügungen beheben, jedoch gibt es einige Nachteile, die Sie bei der Entscheidung berücksichtigen sollten, ob Sie dieses Verfahren verwenden möchten:

• Auswahlabfragen müssen in den meisten Fällen geändert werden, um die Hashpartition in das Prädikat einzuschließen und zu einem Abfrageplan zu führen, der keine Partitionslöschung bereitstellt, wenn diese Abfragen ausgegeben werden. Im folgenden Screenshot wird ein schlechter Abfrageplan ohne Partitionsentfernung gezeigt, nachdem die Hashpartitionierung implementiert wurde.

  

• Dadurch entfällt die Möglichkeit, die Partitionsentfernung für bestimmte andere Abfragen wie bereichsbasierte Berichte zu verwenden.

• Wenn Sie eine hashpartitionierte Tabelle mit einer anderen Tabelle verbinden, muss die zweite Tabelle auf demselben Schlüssel hashpartitioniert sein und der Hash-Schlüssel sollte Teil der Verbindungskriterien sein, um eine Partitionseliminierung zu erreichen.

• Die Hashpartitionierung verhindert die Verwendung der Partitionierung für andere Verwaltungsfeatures wie die Archivierung mit gleitenden Fenstern und die Partitionswechselfunktion.

Die Hashpartitionierung ist eine effektive Strategie zum Mindern übermäßiger Latchkonflikte, da der allgemeine Systemdurchsatz erhöht wird, indem Konflikte bei Einfügungen gelöst werden. Da es einige Kompromisse gibt, ist es möglicherweise nicht die optimale Lösung für einige Zugriffsmuster.

Zusammenfassung der Verfahren zum Beheben von Latchkonflikten

In den folgenden zwei Abschnitten finden Sie eine Zusammenfassung der Verfahren, die zur Behandlung von übermäßigen Latchkonflikten verwendet werden können:

Nicht sequenzieller Schlüssel/Index

Vorteile:

• Ermöglicht die Verwendung anderer Partitionierungsfeatures wie die Archivierung von Daten mithilfe eines Schemas mit gleitenden Fenstern oder die Partitionswechselfunktion

Benachteiligungen:

• Es treten möglicherweise Herausforderungen bei der Auswahl eines Schlüssels/Index auf, um immer eine „nahezu“ gleichmäßige Verteilung der Einfügungen zu gewährleisten.
• Die GUID als führende Spalte kann dazu verwendet werden, die gleichmäßige Verteilung zu gewährleisten. Dabei besteht der Nachteil, dass dies zu übermäßigen Seitenteilungsvorgängen führen kann.
• Zufällige Einfügungen in die B-Struktur kann zu übermäßigen Seitenteilungsvorgängen führen, was wiederum zu Latchkonflikten in Seiten führt, die sich nicht am Ende der Struktur befinden.

Hashpartitionierung mit einer berechneten Spalte

Vorteile:

• Transparenz für Einfügungen

Benachteiligungen:

• Die Partitionierung kann nicht für beabsichtigte Verwaltungsfeatures verwendet werden, z. B. für die Archivierung von Daten mithilfe von Partitionswechseloptionen.
• Dieser Ansatz kann zu Partitionsentfernungsproblemen bei Abfragen führen. Dies umfasst einzelne und bereichsbasierte SELECT-/UPDATE-Abfragen und Abfragen, die eine Verknüpfung durchführen.
• Das Hinzufügen einer persistierten berechneten Spalte ist ein Offlinevorgang.

Tipp

Weitere Techniken finden Sie im Blogbeitrag PAGELATCH_EX wartet und fügt viel ein.

Leitfaden: Diagnose einer Verriegelung-Konkurrenz

In dieser folgenden exemplarischen Vorgehensweise werden die in Diagnose von SQL Server-Latchkonflikten und Behandeln von Latchkonflikten für verschiedene Tabellenmuster beschriebenen Tools und Verfahren verwendet, um ein Problem in einem realen Szenario zu lösen. In diesem Szenario wird ein Kundenprojekt zur Durchführung von Lasttests eines Point-of-Sales-Systems beschrieben, das etwa 8.000 Geschäfte simulierte, die Transaktionen in einer SQL Server-Anwendung ausführen, die auf einem System mit 8 Sockets, 32 physischen Kernen und 256 GB Arbeitsspeicher läuft.

Auf der folgenden Abbildung wird die Hardware veranschaulicht, die zum Testen des POS-Systems verwendet wurde:

Symptom: Hot Verriegelungen

In diesem Fall haben wir hohe Wartezeiten bei PAGELATCH_EX beobachtet, wobei wir typischerweise hoch als einen Durchschnittswert von mehr als 1 ms definieren. Im Szenario wurden Wartezeiten über 20 ms festgestellt.

Nachdem bestätigt wurde, dass die Latchkonflikte problematisch waren, musste die Ursache der Latchkonflikte ermittelt werden.

Isolieren Sie das Objekt, das die Verriegelung-Konkurrenz verursacht

Das folgende Skript verwendet die Spalte resource_description, um herauszufinden, welcher Index das Problem verursacht hat. PAGELATCH_EX Behauptung:

Anmerkung

Die von diesem Skript zurückgegebene Spalte resource_description stellt die Ressourcenbeschreibung im Format <DatabaseID,FileID,PageID> bereit, wobei der Name der Datenbank, die DatabaseID zugeordnet ist, durch Übergeben des Werts von DatabaseID an die Funktion DB_NAME() bestimmt werden kann.

SELECT wt.session_id,
       wt.wait_type,
       wt.wait_duration_ms,
       s.name AS schema_name,
       o.name AS object_name,
       i.name AS index_name
FROM sys.dm_os_buffer_descriptors AS bd
     INNER JOIN (SELECT *,
             --resource_description
             CHARINDEX(':', resource_description) AS file_index,
             CHARINDEX(':', resource_description, CHARINDEX(':', resource_description) + 1) AS page_index,
             resource_description AS rd
      FROM sys.dm_os_waiting_tasks AS wt
      WHERE wait_type LIKE 'PAGELATCH%') AS wt
     ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)
        AND bd.file_id = SUBSTRING(wt.rd, wt.file_index + 1, 1) --wt.page_index)
        AND bd.page_id = SUBSTRING(wt.rd, wt.page_index + 1, LEN(wt.rd))
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = au.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id
ORDER BY wt.wait_duration_ms DESC;

Wie hier gezeigt, besteht der Konflikt in Bezug auf die Tabelle LATCHTEST und den Indexnamen CIX_LATCHTEST. Beachten Sie, dass die Namen zur Anonymisierung der Arbeitsauslastung geändert wurden.

Ein erweitertes Skript, das wiederholt Abfragen durchführt und eine temporäre Tabelle verwendet, um die Gesamtwartezeit über einen konfigurierbaren Zeitraum zu ermitteln, finden Sie im Anhang unter Abfragen von Pufferdeskriptoren zum Ermitteln von Objekten als Ursache für Latchkonflikte.

Alternatives Verfahren zum Isolieren der Ursache der Latchkonflikte

Manchmal kann es unpraktisch sein, Folgendes abzufragen sys.dm_os_buffer_descriptors. Wenn der für das System und den Pufferpool verfügbare Arbeitsspeicher steigt, steigt auch die erforderliche Zeit zum Ausführen dieser DMV. Auf einem 256-GB-System kann die Ausführung dieser DMV bis zu 10 Minuten oder mehr dauern. Es gibt auch ein alternatives Verfahren, das im Folgenden grob beschrieben und mit einer anderen Arbeitsauslastung veranschaulicht wird:

1. Fragen Sie die aktuell wartenden Tasks mithilfe des Skripts Abfragen von „sys.dm_os_waiting_tasks“ nach der Wartezeit.

2. Identifizieren Sie die Schlüsselseite, bei dem ein Konflikt vorliegt, der auftritt, wenn mehrere Threads dieselbe Seite beanspruchen möchten. In diesem Beispiel konkurrieren die Threads, die die Einfügung vornehmen, um die hinterste Seite im B-Baum und warten, bis sie eine EX einrasten. Dies wird durch die resource_description in der ersten Abfrage in unserem Fall 8:1:111305angegeben.

3. Aktivieren Sie Trace-Flag 3604, das weitere Informationen über die Seite über DBCC PAGE Mit der folgenden Syntax ersetzen Sie den Wert in Klammern durch den Wert, den Sie über die Ressourcenbeschreibung erhalten haben:

   Aktivieren Sie das Trace-Flag 3604, um die Konsolenausgabe zu aktivieren:

   DBCC TRACEON (3604);
   

   Überprüfen Sie die Details der Seite:

   DBCC PAGE (8, 1, 111305, -1);
   

4. Untersuchen Sie die DBCC-Ausgabe. In unserem Fall 78623323sollte eine zugeordnete Metadata ObjectID vorhanden sein.

   

5. Wir können nun den folgenden Befehl ausführen, um den Namen des Objekts zu ermitteln, das den Konflikt verursacht, der wie erwartet lautet LATCHTEST.

   Anmerkung

   Stellen Sie sicher, dass Sie sich im richtigen Datenbankkontext befinden, andernfalls wird die Abfrage zurückgegeben NULL.

   --get object name
   SELECT OBJECT_NAME(78623323);
   

   

Zusammenfassung und Ergebnisse

Mithilfe des oben genannten Verfahrens konnten Sie bestätigen, dass der Konflikt bei einem gruppierten Index mit einem sequenziell steigenden Schlüsselwert in der Tabelle vorliegt, die bei weitem die höchste Anzahl an Einfügungen empfangen hat. Diese Art der Kontention ist für Indizes mit einem schrittweise steigenden Schlüsselwert wie datetime, identity oder einem anwendungsgenerierten TransactionID nicht ungewöhnlich.

Zum Lösen dieses Problems wurde die Hashpartitionierung mit einer berechneten Spalte verwendet, wodurch eine Leistungsverbesserung von 690 % erzielt wurde. In der folgenden Tabelle wird die Leistung der Anwendung vor und nach Implementierung der Hashpartitionierung mit einer berechneten Spalte zusammengefasst. Die CPU-Auslastung steigt erwartungsgemäß dem Durchsatz entsprechend an, nachdem der Latchkonfliktengpass entfernt wurde:

Messung	Vor der Hashpartitionierung	Nach der Hashpartitionierung
Geschäftstransaktionen/Sekunde	36	249
Durchschnittliche Wartezeit für Seitenlatches	36 Millisekunden	0,6 Millisekunden
Latchwartevorgänge/Sekunde	9.562	2\.873
SQL-Prozessorzeit	24 %	78 %
SQL-Batchanforderungen/Sekunde	12.368	47.045

Wie aus der vorangegangenen Tabelle ersichtlich ist, kann sich die korrekte Identifizierung und Behebung von Leistungsproblemen, die durch übermäßige Seiten-Verriegelung-Konkurrenz verursacht werden, positiv auf die Gesamtleistung der Anwendung auswirken.

Anhang: Alternatives Verfahren

Eine mögliche Strategie zur Vermeidung übermäßiger Seitenverriegelungskonkurrenz ist das Auffüllen von Zeilen mit einem char Spalte, um sicherzustellen, dass jede Zeile eine ganze Seite verwendet. Diese Strategie ist eine Option, wenn die Gesamtdatengröße klein ist und Sie den EX Seitenverriegelungskonflikte werden durch die folgende Kombination von Faktoren verursacht:

• Kleine Zeilengröße
• Flache B-Struktur
• Zugriffsmuster mit vielen zufälligen INSERT-, SELECT-, UPDATE- und DELETE-Vorgängen
• Kleine Tabellen (z. B. temporäre Warteschlangentabellen)

By padding rows bis occupy a full Seite Sie require SQL bis allocate more Seiten, making more Seiten available for inserts und reducing EX Seite Verriegelungskonflikt.

Zeilen auffüllen, um sicherzustellen, dass jede Zeile eine ganze Seite füllt

Ein Skript, das dem folgenden ähnelt, kann zum Auffüllen der Zeilen verwendet werden, damit sie eine ganze Seite beanspruchen:

ALTER TABLE mytable ADD Padding CHAR(5000) NOT NULL DEFAULT ('X');

Anmerkung

Verwenden Sie den kleinsten möglichen CHAR-Wert, der eine Zeile pro Seite erzwingt, um die zusätzliche CPU-Auslastung für den Füllwert und den zusätzlichen erforderlichen Speicherplatz zum Protokollieren der Zeile zu reduzieren. In einem Hochleistungssystem zählt jedes Byte.

Dieses Verfahren wird der Vollständigkeit halber erläutert. In der Praxis hat das SQLCAT dieses Verfahren nur für eine kleine Tabelle mit 10.000 Zeilen in einem einzelnen Leistungstestprojekt verwendet. Dieses Verfahren ist nur begrenzt anwendbar, da es die Arbeitsspeicherauslastung von SQL Server für große Tabellen erhöht und zu Nicht-Pufferlatchkonflikten auf Seiten führen kann, die sich nicht am Ende der Struktur befinden. Der zusätzliche Arbeitsspeicherdruck kann ein erheblicher Begrenzungsfaktor für die Anwendung dieser Technik sein. Aufgrund der Menge des Arbeitsspeichers, der in einem modernen Server verfügbar ist, wird ein großer Anteil des Arbeitssatzes der OLTP-Arbeitsauslastungen in der Regel im Arbeitsspeicher gespeichert. Wenn der Datensatz auf eine Größe ansteigt, die nicht mehr in den Arbeitsspeicher passt, tritt ein erheblicher Rückgang der Leistung auf. Daher kann dieses Verfahren nur für kleine Tabellen genutzt werden. Diese Technik wird von SQLCAT nicht für Szenarien wie Last Page/Trailing Page Insert Contention für große Tabellen verwendet.

Wichtig

Die Verwendung dieser Strategie kann zu einer großen Anzahl von Wartezeiten auf dem Server führen. ACCESS_METHODS_HOBT_VIRTUAL_ROOT Der Typ "Verriegelung" sollte nicht verwendet werden, da diese Strategie zu einer großen Anzahl von Seitenteilungen in den Nicht-Blatt-Ebenen des B-Baums führen kann. Wenn dies geschieht, muss SQL-Server den gemeinsamen (SH) Verriegelungen auf allen Ebenen, gefolgt von exklusiven (EX) hält auf Seiten im B-Baum, auf denen ein Seitensplit möglich ist. Prüfen Sie die sys.dm_os_latch_stats DMV für eine hohe Anzahl von Wartezeiten auf dem ACCESS_METHODS_HOBT_VIRTUAL_ROOT Verriegelungstyp nach Auffüllen von Zeilen.

Anhang: SQL-Server-Verriegelung-Contention-Skripte

Dieser Abschnitt enthält Skripte, die bei der Diagnose und Fehlerbehebung von Verriegelung-Contention-Problemen helfen können.

Abfragen von sys.dm_os_waiting_tasks nach Sitzungs-ID

Die folgende Beispielskriptabfrage sys.dm_os_waiting_tasks, und gibt Latch-Warteschlangen zurück, die nach Sitzungs-ID geordnet sind.

-- WAITING TASKS ordered by session_id
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY session_id;

Abfragen von sys.dm_os_waiting_tasks nach Wartezeit

Die folgende Beispielskript, das sys.dm_os_waiting_tasks abfragt, gibt Latch-Wartezeiten zurück, die nach Wartezeit sortiert sind:

-- WAITING TASKS ordered by wait_duration_ms
SELECT wt.session_id,
       wt.wait_type,
       er.last_wait_type AS last_wait_type,
       wt.wait_duration_ms,
       wt.blocking_session_id,
       wt.blocking_exec_context_id,
       resource_description
FROM sys.dm_os_waiting_tasks AS wt
     INNER JOIN sys.dm_exec_sessions AS es
         ON wt.session_id = es.session_id
     INNER JOIN sys.dm_exec_requests AS er
         ON wt.session_id = er.session_id
WHERE es.is_user_process = 1
      AND wt.wait_type <> 'SLEEP_TASK'
ORDER BY wt.wait_duration_ms DESC;

Berechnen der Wartezeiten in einem Zeitraum

Das folgende Skript berechnet Latchwartevorgänge über einen Zeitraum und gibt diese zurück:

/* Snapshot the current wait 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 AS DATETIME;
DECLARE @previous_snap_time AS DATETIME;

SET @current_snap_time = GETDATE();

IF NOT EXISTS (SELECT name
               FROM tempdb.sys.sysobjects
               WHERE name LIKE '#_wait_stats%')
    CREATE TABLE #_wait_stats
    (
        wait_type VARCHAR (128),
        waiting_tasks_count BIGINT,
        wait_time_ms BIGINT,
        avg_wait_time_ms INT,
        max_wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        avg_signal_wait_time INT,
        snap_time DATETIME
    );

INSERT INTO #_wait_stats (wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms, signal_wait_time_ms, snap_time)
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       max_wait_time_ms,
       signal_wait_time_ms,
       getdate()
FROM sys.dm_os_wait_stats;

--get the previous collection point
SELECT TOP 1 @previous_snap_time = snap_time
FROM #_wait_stats
WHERE snap_time < (SELECT MAX(snap_time)
                   FROM #_wait_stats)
ORDER BY snap_time DESC;

--get delta in the wait stats
SELECT TOP 10 s.wait_type,
              (e.waiting_tasks_count - s.waiting_tasks_count) AS [waiting_tasks_count],
              (e.wait_time_ms - s.wait_time_ms) AS [wait_time_ms],
              (e.wait_time_ms - s.wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_wait_time_ms],
              (e.max_wait_time_ms) AS [max_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) AS [signal_wait_time_ms],
              (e.signal_wait_time_ms - s.signal_wait_time_ms) / ((e.waiting_tasks_count - s.waiting_tasks_count)) AS [avg_signal_time_ms],
              s.snap_time AS [start_time],
              e.snap_time AS [end_time],
              DATEDIFF(ss, s.snap_time, e.snap_time) AS [seconds_in_sample]
FROM #_wait_stats AS e
     INNER JOIN (SELECT *
      FROM #_wait_stats
      WHERE snap_time = @previous_snap_time) AS s
     ON (s.wait_type = e.wait_type)
WHERE e.snap_time = @current_snap_time
      AND s.snap_time = @previous_snap_time
      AND e.wait_time_ms > 0
      AND (e.waiting_tasks_count - s.waiting_tasks_count) > 0
      AND e.wait_type NOT IN ('LAZYWRITER_SLEEP', 'SQLTRACE_BUFFER_FLUSH', 'SOS_SCHEDULER_YIELD',
                              'DBMIRRORING_CMD', 'BROKER_TASK_STOP', 'CLR_AUTO_EVENT',
                              'BROKER_RECEIVE_WAITFOR', 'WAITFOR', 'SLEEP_TASK',
                              'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TO_FLUSH',
                              'XE_DISPATCHER_WAIT', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
ORDER BY (e.wait_time_ms - s.wait_time_ms) DESC;

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

Abfragen von Pufferdeskriptoren zum Ermitteln von Objekten als Ursache für Latchkonflikte

Das folgende Skript gibt Pufferdeskriptoren zurück, um zu ermitteln, welche Objekte im Zusammenhang mit den längsten Latchwartezeiten stehen.

IF EXISTS (SELECT *
           FROM tempdb.sys.objects
           WHERE [name] LIKE '#WaitResources%')
    DROP TABLE #WaitResources;

CREATE TABLE #WaitResources
(
    session_id INT,
    wait_type NVARCHAR (1000),
    wait_duration_ms INT,
    resource_description sysname NULL,
    db_name NVARCHAR (1000),
    schema_name NVARCHAR (1000),
    object_name NVARCHAR (1000),
    index_name NVARCHAR (1000)
);
GO

DECLARE @WaitDelay AS VARCHAR (16), @Counter AS INT, @MaxCount AS INT, @Counter2 AS INT;
SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'; -- 600x.1=60 seconds

SET NOCOUNT ON;

WHILE @Counter < @MaxCount
BEGIN
   INSERT INTO #WaitResources (session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name)
   SELECT wt.session_id,
         wt.wait_type,
         wt.wait_duration_ms,
         wt.resource_description
   FROM sys.dm_os_waiting_tasks AS wt
   WHERE wt.wait_type LIKE 'PAGELATCH%'
         AND wt.session_id <> @@SPID;

   -- SELECT * FROM sys.dm_os_buffer_descriptors;

   SET @Counter = @Counter + 1;
   WAITFOR DELAY @WaitDelay;
END

--SELECT * FROM #WaitResources;

UPDATE #WaitResources
    SET db_name = DB_NAME(bd.database_id),
        schema_name = s.name,
        object_name = o.name,
        index_name = i.name
FROM #WaitResources AS wt
     INNER JOIN sys.dm_os_buffer_descriptors AS bd
         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description))
        AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) - CHARINDEX(':', wt.resource_description) - 1)
        AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) + 1) + 1, LEN(wt.resource_description) + 1)
        -- AND wt.file_index > 0 AND wt.page_index > 0
     INNER JOIN sys.allocation_units AS au
         ON bd.allocation_unit_id = AU.allocation_unit_id
     INNER JOIN sys.partitions AS p
         ON au.container_id = p.partition_id
     INNER JOIN sys.indexes AS i
         ON p.index_id = i.index_id
        AND p.object_id = i.object_id
     INNER JOIN sys.objects AS o
         ON i.object_id = o.object_id
     INNER JOIN sys.schemas AS s
         ON o.schema_id = s.schema_id;

SELECT * FROM #WaitResources
ORDER BY wait_duration_ms DESC;
GO
/*
--Other views of the same information
SELECT wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY wait_type, db_name, schema_name, object_name, index_name;
SELECT session_id, wait_type, db_name, schema_name, object_name, index_name, SUM(wait_duration_ms) [total_wait_duration_ms] FROM #WaitResources
GROUP BY session_id, wait_type, db_name, schema_name, object_name, index_name;
*/

--SELECT * FROM #WaitResources
--DROP TABLE #WaitResources;

Skript für die Hashpartitionierung

Die Verwendung dieses Skripts wurde unter Verwenden der Hashpartitionierung mit einer berechneten Spalte beschrieben. Es sollte gemäß Ihrer Implementierung angepasst werden.

--Create the partition scheme and function, align this to the number of CPU cores 1:1 up to 32 core computer
-- so for below this is aligned to 16 core system
CREATE PARTITION FUNCTION [pf_hash16](TINYINT)
    AS RANGE LEFT
    FOR VALUES (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15);

CREATE PARTITION SCHEME [ps_hash16]
    AS PARTITION [pf_hash16]
    ALL TO ([ALL_DATA]);
-- Add the computed column to the existing table (this is an OFFLINE operation)

-- Consider using bulk loading techniques to speed it up
ALTER TABLE [dbo].[latch_contention_table]
    ADD [HashValue] AS (CONVERT (TINYINT, ABS(BINARY_CHECKSUM([hash_col]) % (16)), (0))) PERSISTED NOT NULL;

--Create the index on the new partitioning scheme
CREATE UNIQUE CLUSTERED INDEX [IX_Transaction_ID]
    ON [dbo].[latch_contention_table]([T_ID] ASC, [HashValue])
    ON ps_hash16 (HashValue);

Verwandte Inhalte

• Werkzeuge zur Leistungsüberwachung und -optimierung