Freigeben über

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

  • Artikel

Dieser Artikel hilft Ihnen, das Problem zu beheben, bei dem beim Auftreten einer hohen Auslastung des Servers eine schwerwiegende Blockierung festgestellt wird.

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 starke Blockierung, wenn der Server eine hohe Auslastung aufweist. 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 lautet PAGELATCH_UPder Wartetyp , 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).

Notiz

Wenn eine Seite gleichmäßig durch 8088 dividierbar 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 Dropvorgang 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 HASHPLÄNEN zugeordnet sind.

Diese Aktivitäten können Zustrittsprobleme verursachen.

Ursache

Wenn die tempdb-Datenbank stark verwendet wird, kann SQL Server beim Versuch, Seiten zuzuordnen, einen Konflikt erleben. Je nach Inhaltsgrad kann dies dazu führen, dass Abfragen und Anforderungen, die tempdb umfassen, kurz nicht reagiert werden.

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

Um eine Seite aus gemischtem Umfang zuzuweisen, muss SQL Server die Seite "Seitenfreier Speicherplatz (PFS)" überprüfen, um zu bestimmen, welche gemischte Seite frei zugewiesen werden kann. Die PFS-Seite verfolgt auf jeder Seite freien Speicherplatz, und jede PFS-Seite verfolgt etwa 8000 Seiten. Die entsprechende Synchronisierung wird beibehalten, um Änderungen an den PFS- und SGAM-Seiten vorzunehmen; und das kann andere Modifizierer für kurze Zeiträume blockieren.

Wenn SQL Server nach einer gemischten Seite sucht, die zugewiesen werden soll, wird der Scan immer auf derselben Datei- und SGAM-Seite gestartet. Dies führt zu einem intensiven Streit auf der SGAM-Seite, wenn mehrere gemischte Seitenzuweisungen ausgeführt werden. Dies kann zu problemen führen, die im Abschnitt "Symptome" dokumentiert sind.

Notiz

Dezuordnungsaktivitäten müssen auch die Seiten ändern. Dies kann zu einer erhöhten Ansenkung 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 der tempdb-Datendateien mit gleicher Größe

Wenn beispielsweise die Größe der einzelnen Datendatei von tempdb 8 GB beträgt und die Protokolldatei 2 GB groß ist, empfiehlt es sich, die Anzahl der Datendateien auf acht (8) (jede von 1 GB zu erhöhen, um die Größe gleich zu halten) und die Protokolldatei zu belassen. Die unterschiedlichen Datendateien auf separaten Datenträgern würden zusätzliche Leistungsvorteil bieten. Dies ist jedoch nicht erforderlich. Die Dateien können auf demselben Datenträgervolume koexistieren.

Die optimale Anzahl von tempdb-Datendateien hängt vom Grad der Insentreibung 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 höheren Systemen könnte 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) hat einen Fix eingeführt, der einen Roundrobin-Algorithmus für gemischte Seitenzuweisungen verwendet. Aufgrund dieser Verbesserung unterscheidet sich die Startdatei für jede aufeinander folgende gemischte Seitenzuweisung (wenn mehrere Dateien vorhanden sind). 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 in derselben Größe zu erstellen.

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

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

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

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

  • Der Zuordnungsalgorithmus für GAM weist jeweils eine Erweiterung (acht zusammenhängende Seiten) aus der Anzahl der Dateien in einer Roundrobin-Mode zu, während die proportionale Füllung berücksichtigt wird. Wenn Sie also über 10 Dateien mit gleicher Größe verfügen, stammt die erste Zuweisung aus Datei1, der zweite von File2, dem dritten von File3 usw.

  • Der Ressourceninhalt der PFS-Seite wird reduziert, da acht Seiten gleichzeitig als VOLLSTÄNDIG markiert werden, da GAM die Seiten anlastet.

Wie die Implementierung des Ablaufverfolgungskennzeichnungs -T1118 die Inhaltsverknügung reduziert

Notiz

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

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

  • -T1118 ist eine serverweite Einstellung.
  • Schließen Sie das Trace-Flag "-T1118 " in die Startparameter für SQL Server ein, damit das Ablaufverfolgungsflaggen auch nach dem Wiederverwenden von SQL Server wirksam bleibt.
  • -T1118 entfernt fast alle einzelseitigen Zuordnungen auf dem Server.
  • Indem Sie die meisten einzelnen Seitenzuweisungen deaktivieren, reduzieren Sie den Inhalt auf der SGAM-Seite.
  • Wenn -T1118 aktiviert ist, werden fast alle neuen Zuordnungen von einer GAM-Seite (z. B. 2:1:2) erstellt, die jeweils acht (8) Seiten (ein Teil) zu einem Objekt zuordnet, im Gegensatz zu einer einzelnen Seite aus einem Umfang für die ersten acht (8) Seiten eines Objekts, ohne das Ablaufverfolgungszeichen.
  • Die IAM-Seiten verwenden weiterhin die einzelseitigen Zuordnungen 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 der Belastung auf der SGAM-Seite. Informationen zu Platzbedenken finden Sie im nächsten Abschnitt.

Nachteile

Der Nachteil der Verwendung von -T1118 besteht darin, dass möglicherweise eine Erhöhung der Datenbankgröße angezeigt wird, wenn die folgenden Bedingungen zutreffen:

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

Wenn diese Bedingungen zutreffen, können Sie 64 KB (acht Seiten * 8 KB = 64 KB) für ein Objekt zuweisen, das nur 8 KB Speicherplatz erfordert, wodurch 56 KB Speicherplatz fehlt. Wenn das neue Objekt jedoch mehr als 64 KB (acht Seiten) in seiner Lebensdauer verwendet, gibt es keinen Nachteil für die Ablaufverfolgungskennzeichnung. Daher kann SQL Server in einem schlimmsten Fall sieben (7) zusätzliche Seiten während der ersten Zuordnung nur für neue Objekte zuweisen, die nie über eine (1) Seite hinaus wachsen.

References