Empfehlungen zur Verringerung der Zuordnungskonflikte in der SQL Server tempdb-Datenbank

Ursprüngliche Produktversion: SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022
Ursprüngliche KB-Nummer: 2154845

Zusammenfassung

In diesem Artikel wird erklärt, wie Sie Zuordnungskonflikte in der SQL Server-Datenbank tempdb diagnostizieren und reduzieren. Es behandelt PAGELATCH_UP Wartezeiten bei Page Free Space (PFS)-, Global Allocation Map (GAM)- und Shared Global Allocation Map (SGAM)-Seiten. Es beschreibt, wie Sie mehrere gleich große tempdb Datendateien verwenden und in älteren Versionen die Traceflags 1118 und 1117 einsetzen, um die Leistung von SQL Server bei hoher gleichzeitiger Auslastung zu verbessern.

Symptome

Auf einem Server, auf dem Microsoft SQL Server ausgeführt wird, bemerken Sie eine starke Blockierung, wenn der Server stark belastet wird. Die dynamischen Verwaltungsansichten sys.dm_exec_requests und sys.dm_os_waiting_tasks zeigen, dass die Anforderungen oder Aufgaben auf tempdb Ressourcen warten. Der Wartetyp ist PAGELATCH_UP, und die Warteressource verweist auf Seiten in tempdb. Diese Seiten befinden sich möglicherweise im Format 2:1:1, 2:1:3 usw. (PFS- und SGAM-Seiten in tempdb).

Die folgenden Vorgänge verwenden tempdb umfassend:

• Wiederholte Erstellungs- und Dropvorgänge für temporäre Tabellen (lokal oder global).
• Tabellenvariablen, die tempdb zum Speichern verwenden.
• Arbeitstabellen, die Cursorn zugeordnet sind.
• Arbeitstabellen, die einer ORDER BY Klausel zugeordnet sind.
• Arbeitstabellen, die einer GROUP BY Klausel zugeordnet sind.
• Arbeitsdateien, die Hashplänen zugeordnet sind.

Diese Aktivitäten können Konflikte verursachen.

Ursache

Wenn tempdb intensiv genutzt wird, kann es in SQL Server beim Zuweisen von Seiten zu Konkurrenz kommen. Je nach Grad der Ressourcenkonkurrenz könnten Abfragen und Anfragen, die tempdb verwenden, kurzzeitig nicht mehr reagieren.

Während der Objekterstellung in älteren Versionen von SQL Server weist das System zwei Seiten aus gemischtem Umfang zu und weist sie dem neuen Objekt zu. Eine der Seiten ist die Index Allocation Map-Seite (IAM), und die zweite Seite ist die erste Seite des Objekts. SQL Server verfolgt gemischte Extents mithilfe der Seite „Shared Global Allocation Map (SGAM)“. Jede SGAM-Seite verfolgt ca. 4 GB Daten.

Um eine Seite aus einem gemischten Umfang zuzuweisen, durchsucht SQL Server die Seite "Seitenfreier Speicherplatz (PFS)", um eine kostenlose gemischte Seite zu finden. Die PFS-Seite erfasst den freien Speicherplatz jeder Seite, und jede PFS-Seite erfasst etwa 8.000 Seiten. Eine Synchronisierung ist erforderlich, wenn SQL Server PFS- und SGAM-Seiten ändert, und diese Synchronisierung kann andere Änderungsvorgänge für kurze Zeit blockieren.

Wenn SQL Server nach einer gemischten Seite sucht, die zugewiesen werden soll, wird der Scan immer auf derselben Datei- und SGAM-Seite gestartet. Dieses Verhalten führt zu intensiven Auseinandersetzungen auf der SGAM-Seite, wenn viele gemischte Seitenzuordnungen gleichzeitig ausgeführt werden, was die zuvor beschriebenen Symptome erzeugt.

Weitere Informationen zu den zuordnungsmechanismen, die SQL Server verwendet (SGAM, GAM, PFS und IAM), finden Sie im Abschnitt "Verwandte Inhalte".

Lösung

SQL Server 2014 hat am 9. Juli 2024 das Ende des erweiterten Supports erreicht, sodass die meisten Leser den Anleitungen zu SQL Server 2016 und höheren Versionen folgen sollten.

SQL Server 2016 und höher

1. Überprüfen Sie die tempdb-Datenbank auf aktuelle Leistungsanleitungen.
2. Stellen Sie sicher, dass tempdb über mehrere gleich große Datendateien verfügt. Verwenden Sie als Ausgangspunkt eine Datendatei pro logischem Prozessor bis zu acht Dateien. Wenn weiterhin Ressourcenkonflikte bestehen, fügen Sie Dateien in Vierergruppen hinzu, bis die Anzahl der logischen Prozessoren erreicht ist.
3. Halten Sie die SQL Server Instanz mit dem neuesten kumulativen Update auf dem neuesten Stand, um weitere Zuordnungsverbesserungen zu erhalten. Siehe beispielsweise KB 4099472: Verbesserung des PFS-Seiten-Roundrobin-Algorithmus in SQL Server 2014, 2016 und 2017.
4. Wenn Konflikte unter SQL Server 2019 oder einer höheren Version weiterhin bestehen, lesen Sie die Hinweise zur tempdb-Datenbank zu gleichzeitigen PFS-Updates und speicheroptimierten Metadatentempdb.

SQL Server 2014 und ältere Versionen

Um die Parallelität von tempdbzu verbessern, probieren Sie die folgenden Methoden aus:

• Erhöhen Sie die Anzahl der Datendateien in tempdb, um die Datenträgerbandbreite zu maximieren und Konflikte bei den Zuordnungsstrukturen zu verringern. Wenn die Anzahl der logischen Prozessoren acht oder weniger beträgt, verwenden Sie in der Regel die gleiche Anzahl von Datendateien wie logische Prozessoren. Wenn die Anzahl der logischen Prozessoren größer als acht ist, beginnen Sie mit acht Datendateien. Wenn die Ressourcenkonflikte weiterhin bestehen, fügen Sie Datendateien in Gruppen zu je vier hinzu, bis die Anzahl der logischen Prozessoren erreicht ist, oder ändern Sie die Arbeitslast oder den Code.

• Überprüfen Sie die Bewährten Methoden in der Arbeit mit tempdb in SQL Server 2005.

• Wenn die vorherigen Schritte die Zuordnungskonflikte nicht erheblich verringern und die Konflikte auf SGAM-Seiten auftreten, aktivieren Sie das Traceflag -T1118. Unter diesem Trace-Flag weist SQL Server jedem Datenbankobjekt volle Extents zu, wodurch die Konkurrenz um SGAM-Seiten erheblich reduziert wird.

  Notiz

  • Dieses Ablaufverfolgungsflag wirkt sich auf jede Datenbank auf der SQL Server-Instanz aus. Informationen dazu, ob Zuordnungskonflikte auf SGAM-Seiten auftreten, finden Sie unter Überwachen von Konflikten durch DML-Vorgänge.
  • Installieren Sie für SQL Server 2014 Service Pack 3, um die in KB 4099472 enthaltene Korrektur zu erhalten, die Konflikte durch Round-Robin-Zuweisungen von PFS-Seiten über mehrere PFS-Seiten in derselben Datendatei hinweg weiter reduziert.

Weitere Informationen zur tempdb-Zuordnung

Standardverhalten von tempdb in SQL Server 2016 und höheren Versionen

Ab SQL Server 2016 werden tempdb-Zuordnungskonflikte durch mehrere Verbesserungen standardmäßig reduziert, sodass Sie in der Regel die Traceflags 1118 oder 1117 nicht aktivieren müssen:

• Zuordnungen gemischter Extents sind für tempdb standardmäßig deaktiviert (das Verhalten, das zuvor durch Traceflag 1118 aktiviert wurde). Alle neuen Mittelzuweisungen stammen aus einheitlichen Ausmaßen.
• Alle tempdb Datendateien werden automatisch zusammenwachsen (das zuvor durch Ablaufverfolgungskennzeichnung 1117 aktivierte Verhalten).
• SQL Server Setup empfiehlt mehrere gleich große tempdb Datendateien basierend auf der Anzahl der logischen Prozessoren bis zu acht.
• Gleichzeitige PFS-Updates reduzieren Latchkonflikte auf PFS-Seiten in SQL Server 2019 und späteren Versionen weiter.

Wenn Sie SQL Server 2016 oder eine höhere Version ausführen, konzentrieren Sie sich auf die Größe und Die Anzahl der tempdb Dateien. Verwenden Sie Traceflags nur in SQL Server 2014 oder älter.

Beispiel für gleiche tempdb-Datendateigröße

Wenn die einzelne tempdb Datendatei beispielsweise 8 GB beträgt und die Protokolldatei 2 GB beträgt, erhöhen Sie die Anzahl der Datendateien auf acht (jeweils 1 GB, um die Größe gleich zu halten), und lassen Sie die Protokolldatei unverändert. Das Platzieren der Datendateien auf separaten Datenträgern kann einen zusätzlichen Leistungsvorteil bieten, ist jedoch nicht erforderlich. Die Dateien können auf demselben Datenträgervolume koexistieren.

Die optimale Anzahl von tempdb Datendateien hängt vom Grad der auftretenden Konflikte ab. Legen Sie als Ausgangspunkt die Dateianzahl auf die Anzahl der logischen Prozessoren fest, die SQL Server zugewiesen sind. Für höhere Systeme beginnen Sie mit acht Dateien. Wenn der Ressourcenkonflikt nicht verringert wird, fügen Sie weitere Datendateien hinzu.

Verwenden Sie die gleiche Größe für alle Datendateien. SQL Server 2000 Service Pack 4 (SP4) hat einen Fix eingeführt, der einen Roundrobin-Algorithmus für gemischte Seitenzuordnungen verwendet. Mit dieser Verbesserung ändert sich die Startdatei für jede aufeinander folgende gemischte Seitenzuordnung, wenn mehr als eine Datei vorhanden ist. Der SGAM-Zuordnungsalgorithmus ist ein reiner Round-Robin-Algorithmus und berücksichtigt keine proportionale Füllung. Erstellen Sie daher alle tempdb Datendateien mit derselben Größe.

Wie mehr tempdb-Datendateien Konflikte verringern

Das Hinzufügen gleich großer tempdb Datendateien verringert Ressourcenkonflikte auf folgende Weise:

• Wenn Sie eine Datendatei für tempdb haben, haben Sie pro 4 GB Speicherplatz eine GAM-Seite und eine SGAM-Seite.
• Mehr Datendateien derselben Größe erstellen effektiv eine oder mehrere GAM- und SGAM-Seiten für jede Datendatei.
• Der GAM-Zuordnungsalgorithmus weist jeweils eine Erweiterung (acht zusammenhängende Seiten) aus den Dateien in Roundrobin-Mode zu, während die proportionale Füllung berücksichtigt wird. Wenn Sie z. B. über 10 Dateien mit gleicher Größe verfügen, stammt die erste Zuweisung aus Datei1, der zweite von File2, dem dritten von File3 usw.
• Konflikte um PFS-Seiten werden verringert, weil jeweils acht Seiten gleichzeitig als FULL markiert werden, während GAM die Seiten allokiert.

Wie das Traceflag -T1118 Ressourcenkonflikte reduziert

Das Traceflag -T1118 reduziert Ressourcenkonflikte auf folgende Weise:

• -T1118 ist eine serverweite Einstellung.
• Fügen Sie -T1118 zu den Startparametern von SQL Server hinzu, damit das Traceflag nach einem Neustart von SQL Server wirksam bleibt.
• -T1118 entfernt fast alle einzelseitigen Zuordnungen auf dem Server.
• Durch das Deaktivieren der meisten Einzelseitenzuweisungen werden Konflikte bei SGAM-Seiten reduziert.
• Wenn -T1118 aktiviert ist, stammen fast alle neuen Zuweisungen von einer GAM-Seite (z. B. 2:1:2), die einem Objekt jeweils acht Seiten (ein Extent) auf einmal zuweist, anstatt für die ersten acht Seiten eines Objekts einzelne Seiten aus einem Extent zuzuweisen.
• IAM-Seiten verwenden weiterhin Einzelseitenzuordnungen von der SGAM-Seite, auch wenn -T1118 aktiviert ist. Aber in Kombination mit dem Hotfix 8.00.0702 und mehr tempdb Datendateien besteht der Gesamteffekt in einer Verringerung der SGAM-Seitenkonflikte. Informationen zu Platzbedenken finden Sie im nächsten Abschnitt.

Ablaufverfolgungskennzeichnung 1118 im Vergleich zum Standardverhalten in modernen SQL Server

Nachteile von Traceflag -T1118

Der Nachteil der Verwendung -T1118 besteht darin, dass die Datenbankgröße wachsen kann, wenn beide der folgenden Bedingungen zutreffen:

• Sie erstellen neue Objekte in einer Benutzerdatenbank.
• Jedes neue Objekt belegt weniger als 64 KB Speicherplatz.

Unter diesen Bedingungen kann SQL Server 64 KB (acht Seiten * 8 KB) für ein Objekt zuweisen, das nur 8 KB benötigt, was 56 KB Lagerung verschwendet. Wenn das neue Objekt während seiner Lebensdauer mehr als 64 KB (acht Seiten) belegt, hat das Trace-Flag keine Nachteile. Im schlimmsten Fall ordnet SQL Server während der ersten Zuordnung sieben zusätzliche Seiten zu, sondern nur für neue Objekte, die niemals über eine Seite hinaus wachsen.

Verwandte Inhalte

• tempdb-Datenbank
• DBCC TRACEON: Traceflags (Transact-SQL)