Empfehlungen zum Reduzieren von Zuordnungskonflikten in SQL Server tempdb-Datenbank

  • Artikel

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

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

Symptome

Auf einem Server, auf dem Microsoft SQL Server ausgeführt wird, stellen Sie eine schwerwiegende Blockierung fest, wenn auf dem Server eine hohe Auslastung auftritt. Dynamische Verwaltungssichten [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 PAGELATCH_UPder Wartetyp , und die Wait-Ressource verweist auf Seiten in tempdb. Diese Seiten haben möglicherweise das Format 2:1:1, 2:1:3 usw. (PFS- und SGAM-Seiten in tempdb).

Hinweis

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

In den folgenden Vorgängen wird tempdb umfassend verwendet:

  • Wiederholter Vorgang zum Erstellen und Löschen temporärer Tabellen (lokal oder global).
  • Tabellenvariablen, die tempdb als 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 HASHPLÄNEN zugeordnet sind.

Diese Aktivitäten können Zu Konflikten führen.

Ursache

Wenn die tempdb-Datenbank stark genutzt wird, kann es bei SQL Server zu Konflikten kommen, wenn versucht wird, Seiten zuzuordnen. Je nach Konfliktgrad 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 Block zugeordnet und dem neuen Objekt zugewiesen werden. Eine Seite ist für die Indexzuordnungszuordnung (IAM) und die zweite für die erste Seite für das -Objekt. SQL Server verfolgt gemischte Blöcke mithilfe der Seite Shared Global Allocation Map (SGAM) nach. Jede SGAM-Seite verfolgt etwa 4 Gigabyte an Daten nach.

Um eine Seite aus dem gemischten Bereich zuzuordnen, müssen SQL Server die Seite Freier Speicherplatz (Page Free Space, PFS) überprüfen, um zu bestimmen, welche gemischte Seite frei zugeordnet werden kann. Auf der PFS-Seite wird der freie Speicherplatz auf jeder Seite nachverfolgt, und jede PFS-Seite verfolgt etwa 8000 Seiten nach. Die entsprechende Synchronisierung wird beibehalten, um Änderungen an den Seiten PFS und SGAM vorzunehmen. und das kann andere Modifizierer für kurze Zeiträume blockieren.

Wenn SQL Server nach einer gemischten Seite sucht, die zugeordnet werden soll, wird die Überprüfung immer auf derselben Datei- und SGAM-Seite gestartet. Dies führt zu intensiven Konflikten auf der SGAM-Seite, wenn mehrere gemischte Seitenzuordnungen ausgeführt werden. Dies kann zu den Problemen führen, die im Abschnitt Symptome dokumentiert sind.

Hinweis

Aktivitäten zur Aufhebung der Zuordnung müssen auch die Seiten ändern. Dies kann zu den erhöhten Konflikten beitragen.

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

Lösung

Erhöhen der Anzahl von tempdb-Datendateien mit gleicher Größe

Wenn beispielsweise die Einzelne Datendateigröße von tempdb 8 GB und die Protokolldateigröße 2 GB beträgt, wird empfohlen, die Anzahl der Datendateien auf acht (8) zu erhöhen (jeweils 1 GB, um die gleiche Größe beizubehalten) und die Protokolldatei unverändert zu lassen. Wenn die verschiedenen Datendateien auf separaten Datenträgern gespeichert sind, bietet dies einen zusätzlichen Leistungsvorteil. Dies ist jedoch nicht erforderlich. Die Dateien können gleichzeitig 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 sie mindestens der Anzahl der logischen Prozessoren entspricht, die für SQL Server zugewiesen sind. Bei Übergeordneten Systemen kann die Startnummer acht (8) sein. Wenn der Konflikt 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 eine Korrektur eingeführt, die einen Roundrobin-Algorithmus für gemischte Seitenzuordnungen verwendet. Aufgrund dieser Verbesserung unterscheidet sich die Startdatei für jede aufeinanderfolgende gemischte Seitenzuordnung (wenn mehr als eine Datei vorhanden ist). Der neue Zuordnungsalgorithmus für SGAM ist reines Roundrobin und berücksichtigt nicht die proportionale Füllung, um die Geschwindigkeit aufrechtzuerhalten. Es wird empfohlen, alle tempdb-Datendateien mit der gleichen Größe zu erstellen.

Verringern der Anzahl von tempdb-Datendateien zur Verringerung von Konflikten

In der folgenden Liste wird erläutert, wie das Erhöhen der Anzahl von tempdb-Datendateien mit gleicher Größe Konflikte reduziert:

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

  • Wenn Sie die Anzahl der Datendateien erhöhen, die die gleiche Größe für tempdb aufweisen, werden effektiv eine oder mehrere GAM- und SGAM-Seiten für jede Datendatei erstellt.

  • Der Zuordnungsalgorithmus für GAM ordnet die Anzahl der Dateien nacheinander (acht zusammenhängende Seiten) in Roundrobin-Weise zu, wobei die proportionale Füllung berücksichtigt wird. Wenn Sie also über 10 gleich große Dateien verfügen, erfolgt die erste Zuordnung aus Datei1, die zweite aus Datei2, die dritte aus Datei3 usw.

  • Der Ressourcenkonflikt der PFS-Seite wird reduziert, da acht Seiten gleichzeitig als VOLLSTÄNDIG gekennzeichnet sind, da GAM die Seiten zuteilt.

Wie die Implementierung des Ablaufverfolgungsflags -T1118 Konflikte reduziert

Hinweis

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

In der folgenden Liste wird erläutert, wie die Verwendung des Ablaufverfolgungsflags -T1118 Konflikte reduziert:

  • -T1118 ist eine serverweite Einstellung.
  • Fügen Sie das Ablaufverfolgungsflag -T1118 in die Startparameter für SQL Server ein, damit das Ablaufverfolgungsflag auch nach dem Wiederverwendung SQL Server wirksam bleibt.
  • -T1118 entfernt fast alle Einzelseitenzuordnungen auf dem Server.
  • Indem Sie die meisten Einzelseitenzuordnungen deaktivieren, verringern Sie die Konflikte auf der SGAM-Seite.
  • Wenn -T1118 aktiviert ist, werden fast alle neuen Zuordnungen von einer GAM-Seite (z. B. 2:1:2) erstellt, die einem Objekt jeweils acht (8) Seiten (ein Bereich) zuordnet, im Gegensatz zu einer einzelnen Seite aus einem Bereich für die ersten acht (8) Seiten eines Objekts ohne das Ablaufverfolgungsflag.
  • Die IAM-Seiten verwenden weiterhin die Einzelseitenzuordnungen der SGAM-Seite, auch wenn -T1118aktiviert ist. Wenn es jedoch mit Hotfix 8.00.0702 und erhöhten tempdb-Datendateien kombiniert wird, ist der Nettoeffekt eine Verringerung der Konflikte auf der SGAM-Seite. Informationen zum Speicherplatz finden Sie im nächsten Abschnitt.

Nachteile

Der Nachteil der Verwendung von -T1118 besteht darin, dass die Datenbankgröße möglicherweise erhöht wird, 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 64 KB (acht Seiten * 8 KB = 64 KB) für ein Objekt zuordnen, das nur 8 KB Speicherplatz benötigt, wodurch 56 KB Speicherplatz verbitten. Wenn das neue Objekt jedoch mehr als 64 KB (acht Seiten) in seiner Lebensdauer verwendet, hat das Ablaufverfolgungsflag keinen Nachteil. Daher können SQL Server im schlimmsten Fall während der ersten Zuordnung sieben (7) zusätzliche Seiten nur für neue Objekte zuordnen, die nie über eine (1) Seite hinaus wachsen.

References