Empfehlungen, um Zuordnungskonflikte in SQL Server tempdb-Datenbank zu reduzieren

Dieser Artikel hilft Ihnen, das Problem zu beheben, bei dem Sie eine schwerwiegende Blockierung bemerken, wenn der Server stark belastet ist.

Ursprüngliche Produktversion:   SQL Server
Ursprüngliche KB-Nummer:   2154845

Problembeschreibung

Auf einem Server, auf dem Microsoft SQL Server ausgeführt wird, bemerken Sie eine schwerwiegende Blockierung, wenn der Server stark belastet ist. Dynamische Verwaltungsansichten [ sys.dm_exec_request oder sys.dm_os_waiting_tasks ] gibt an, dass diese Anforderungen oder Aufgaben auf tempdb-Ressourcen warten. Darüber hinaus ist der Wartetyp PAGELATCH_UP , und die Warteressource verweist auf Seiten in tempdb. Diese Seiten können das Format 2:1:1, 2:1:3 usw. aufweisen (PFS- und SGAM-Seiten in tempdb).

Hinweis

Wenn eine Seite gleichmäßig durch 8088 teilbar ist, handelt es sich um eine PFS-Seite. Beispielsweise ist Seite 2:3:905856 ein PFS in file_id=3 in tempdb.

Die folgenden Vorgänge verwenden tempdb umfassend:

  • Wiederholter Erstellungs- und Ablagevorgang temporärer Tabellen (lokal oder global).
  • Tabellenvariablen, die tempdb für den Speicher verwenden.
  • Arbeitstabellen, die CURSORS zugeordnet sind.
  • Arbeitstabellen, die einer ORDER BY-Klausel zugeordnet sind.
  • Arbeitstabellen, die einer GROUP BY-Klausel zugeordnet sind.
  • Arbeitsdateien, die HASH-PLÄNEn zugeordnet sind.

Diese Aktivitäten können Konflikte verursachen.

Ursache

Wenn die tempdb-Datenbank häufig verwendet wird, kann SQL Server beim Versuch, Seiten zuzuordnen, zu Einem Konflikt führen. Je nach Dem Grad der Konflikte kann dies dazu führen, dass Abfragen und Anforderungen, die tempdb betreffen, kurz nicht mehr reagieren.

Während der Objekterstellung müssen zwei (2) Seiten aus einem gemischten Umfang zugewiesen und dem neuen Objekt zugewiesen werden. Eine Seite ist für die Indexzuordnung (Index Allocation Map, IAM) und die zweite für die erste Seite für das Objekt. SQL Server verfolgt gemischte Erweiterungen mithilfe der SGAM-Seite (Shared Global Allocation Map). Jede SGAM-Seite verfolgt ca. 4 Gigabyte Daten nach.

Um eine Seite aus dem gemischten Umfang zuzuweisen, müssen SQL Server die Seite "Seitenfreier Speicherplatz( Page Free Space, PFS)" überprüfen, um zu bestimmen, welche gemischte Seite kostenlos zugewiesen werden kann. Auf der PFS-Seite wird der freie Speicherplatz auf jeder Seite nachverfolgt, und auf jeder PFS-Seite werden ca. 8000 Seiten nachverfolgt. Es wird eine geeignete Synchronisierung beibehalten, um Änderungen an den PFS- und SGAM-Seiten vorzunehmen. und dies kann andere Modifizierer für kurze Zeiträume verwischen.

Wenn SQL Server nach einer zuzuweisenden gemischten Seite sucht, wird die Überprüfung immer auf derselben Datei- und SGAM-Seite gestartet. Dies verursacht intensive Konflikte auf der SGAM-Seite, wenn mehrere Zuordnungen mit gemischten Seiten ausgeführt werden. Dies kann die Probleme verursachen, die im Abschnitt "Symptome" dokumentiert sind.

Hinweis

De-Allocation-Aktivitäten müssen auch die Seiten ändern. Dies kann zu dem erhöhten Streit beitragen.

Weitere Informationen zu den verschiedenen Zuordnungsmechanismen, die von SQL Server verwendet werden (SGAM, GIF, PFS, IAM), finden Sie im Abschnitt "Verweise".

Lösung

  • SQL Server 2016 und höher:

    Review

    For more information on these recommendations and other changes that were introduced in SQL 2016 review

  • SQL Server 2014 und frühere Versionen:

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

    • Erhöhen Sie die Anzahl der Datendateien in tempdb, um die Datenträgerbandbreite zu maximieren und den Inhalt der Zuordnungsstrukturen zu verringern. Wenn die Anzahl der logischen Prozessoren kleiner oder gleich acht (8) ist, verwenden Sie in der Regel dieselbe Anzahl von Datendateien wie logische Prozessoren. Wenn die Anzahl der logischen Prozessoren größer als acht (8) ist, verwenden Sie acht Datendateien. Wenn der Inhalt fortgesetzt wird, erhöhen Sie die Anzahl der Datendateien um ein Vielfaches von vier (4) bis zur Anzahl der logischen Prozessoren, bis der Inhalt auf akzeptable Ebenen reduziert wird. Alternativ können Sie Änderungen an der Arbeitsauslastung oder am Code vornehmen.

    • Erwägen Sie die Implementierung der Empfehlungen für bewährte Methoden beim Arbeiten mit tempdb in SQL Server 2005.

    • Wenn die vorherigen Schritte den Zuordnungskonflikt nicht erheblich reduzieren und sich der Inhalt auf SGAM-Seiten befindet, implementieren Sie das Ablaufverfolgungsflags -T1118. Unter diesem Ablaufverfolgungskennzeichen weist SQL Server jedem Datenbankobjekt vollständige Erweiterungen zu, wodurch der Inhalt auf SGAM-Seiten eliminiert wird.

      Hinweis

Erhöhen Sie die Anzahl der tempdb-Datendateien, deren Größe gleich ist

Wenn beispielsweise die Größe der einzelnen Datendatei von tempdb 8 GB beträgt und die Größe der Protokolldatei 2 GB beträgt, wird empfohlen, die Anzahl der Datendateien auf acht (8) (jeweils 1 GB zur Beibehaltung der Größengleichgröße) zu erhöhen und die Protokolldatei wie belassen. Das Vorhandensein der unterschiedlichen Datendateien auf separaten Datenträgern wäre ein zusätzlicher Leistungsvorteil. Dies ist jedoch nicht erforderlich. Die Dateien können auf demselben Datenträgervolume vorhanden sein.

Die optimale Anzahl von tempdb-Datendateien hängt vom Grad der Konflikte in tempdb ab. Als Ausgangspunkt können Sie tempdb so konfigurieren, dass es mindestens der Anzahl logischer Prozessoren entspricht, die für SQL Server zugewiesen sind. Bei Systemen mit höherem Endwert kann die Startnummer acht (8) sein. Wenn der Inhalt nicht reduziert wird, müssen Sie möglicherweise die Anzahl der Datendateien erhöhen.

Es wird empfohlen, die gleiche Größe von Datendateien zu verwenden. SQL Server 2000 Service Pack 4 (SP4) wurde ein Fix eingeführt, der einen Roundrobinalgorithmus für gemischte Seitenzuordnungen verwendet. Aufgrund dieser Verbesserung unterscheidet sich die Startdatei für jede aufeinander folgende gemischte Seitenzuordnung (wenn mehrere Dateien vorhanden sind). Der neue Zuordnungsalgorithmus für SGAM ist reine Roundrobin und berücksichtigt nicht die proportionale Füllung, um die Geschwindigkeit aufrechtzuerhalten. Es wird empfohlen, alle tempdb-Datendateien in derselben Größe zu erstellen.

Wie die Erhöhung der Anzahl von tempdb-Datendateien den Inhalt verringert

In der folgenden Liste wird erläutert, wie die Erhöhung der Anzahl von tempdb-Datendateien mit gleicher Größe den Inhalt verringert:

  • Wenn Sie über eine Datendatei für die tempdb verfügen, haben Sie nur eine OLE-Seite und eine SGAM-Seite für jeweils 4 GB Speicherplatz.

  • Durch das Erhöhen der Anzahl von Datendateien mit den gleichen Größen für tempdb wird effektiv eine oder mehrere OLE- und SGAM-Seiten für jede Datendatei erstellt.

  • Der Zuordnungsalgorithmus für SIS weist einen Umfang nach dem anderen (acht zusammenhängende Seiten) aus der Anzahl der Dateien in einer Roundrobin-Weise zu, während die proportionale Füllung berücksichtigt wird. Wenn Sie also 10 Dateien mit gleicher Größe haben, stammt die erste Zuordnung aus "Datei1", die zweite aus "Datei2", die dritte aus "Datei3" usw.

  • Der Ressourcenkonflikt der PFS-Seite wird reduziert, da jeweils acht Seiten als VOLLSTÄNDIG gekennzeichnet sind, da DIE Seiten von MOF zugewiesen werden.

Implementieren des Ablaufverfolgungsflags -T1118 reduziert den Inhalt

Hinweis

Dieser Abschnitt gilt nur für SQL Server 2014 und frühere Versionen.

In der folgenden Liste wird erläutert, wie die Verwendung von Ablaufverfolgungsflags -T1118 den Inhalt reduziert:

  • -T1118 ist eine serverweite Einstellung.
  • Schließen Sie das Ablaufverfolgungskennzeichen "-T1118" in die Startparameter für SQL Server ein, damit das Ablaufverfolgungskennzeichen auch nach der Wiederverwendung von SQL Server wirksam bleibt.
  • -T1118 entfernt fast alle Einzelseitenzuordnungen auf dem Server.
  • Durch Deaktivieren der meisten Einzelseitenzuweisungen reduzieren Sie den Inhalt auf der SGAM-Seite.
  • Wenn -T1118 aktiviert ist, werden fast alle neuen Zuordnungen von einer SIS-Seite (z. B. 2:1:2) vorgenommen, die einem Objekt jeweils acht (8) Seiten (eine Erweiterung) zuordnet, im Gegensatz zu einer einzelnen Seite aus einer Erweiterung für die ersten acht (8) Seiten eines Objekts, ohne das Ablaufverfolgungskennzeichen.
  • Die IAM-Seiten verwenden weiterhin die Einzelseitenzuordnungen von der SGAM-Seite, auch wenn -T1118 aktiviert ist. Wenn es jedoch mit Hotfix 8.00.0702 und erhöhten tempdb-Datendateien kombiniert wird, ist der Nettoeffekt eine Verringerung des Inhalts auf der SGAM-Seite. Informationen zu Platzbedenken finden Sie im nächsten Abschnitt.

Nachteile

Der Nachteil der Verwendung von -T1118 besteht darin, dass die Datenbankgröße möglicherweise zunimmt, wenn die folgenden Bedingungen erfüllt sind:

  • Neue Objekte werden in einer Benutzerdatenbank erstellt.
  • Jedes der neuen Objekte belegt weniger als 64 KB Speicherplatz.

Wenn diese Bedingungen erfüllt sind, können Sie einem Objekt, das nur 8 KB Speicherplatz benötigt, 64 KB (acht Seiten * 8 KB = 64 KB) zuweisen, wodurch 56 KB Speicherplatz belegt wird. Wenn das neue Objekt jedoch mehr als 64 KB (acht Seiten) in seiner Lebensdauer verwendet, gibt es keinen Nachteil für das Ablaufverfolgungszeichen. Daher können SQL Server im schlechtesten Fall sieben (7) zusätzliche Seiten während der ersten Zuordnung nur für neue Objekte zuordnen, die nie über eine Seite (1) hinausgehen.

References