Empfehlungen zum Verringern der zuordnungskonflikte in der SQL Server-tempdb-Datenbank

Gilt für: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express

Problembeschreibung


Auf einem Server, auf dem Microsoft SQL Server ausgeführt wird, stellen Sie eine schwerwiegende Blockierung fest, wenn der Server stark ausgelastet 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 Wait-Typ PAGELATCH_UP, und die Wait-Ressource verweist auf Seiten in tempdb. Bei diesen Seiten kann es sich um das Format 2:1:1, 2:1:3 usw. (PFS-und SGAM-Seiten in tempdb) handeln.
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:
  • Repetative-und-Drop-Funktion temporärer Tabellen (lokal oder Global).
  • Tabellenvariablen, die tempdb für den Speicher verwenden
  • Arbeitstabellen, die Cursorn 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 zu Konfliktproblemen führen.

Ursache


Wenn die tempdb -Datenbank stark ausgelastet ist, kann SQL Server Konflikte auftreten, wenn Sie versucht, Seiten zuzuweisen. 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 Extent zugeordnet und dem neuen Objekt zugewiesen werden. Eine Seite ist für die Index Zuordnungs Zuordnung (IAM) und die zweite für die erste Seite des Objekts. SQL Server verfolgt gemischte Blöcke mithilfe der Seite Shared Global Allocation Map (SGAM) nach. Auf jeder SGAM-Seite werden etwa 4 Gigabyte Daten nachverfolgt.Um eine Seite aus dem gemischten Umfang zuzuweisen, muss SQL Server die Seite "Seiten freier Speicherplatz (PFS)" Durchsuchen, um zu ermitteln, welche gemischte Seite freigegeben werden kann. Auf der PFS-Seite wird der auf jeder Seite verfügbare freie Speicherplatz nachverfolgt, und jede PFS-Seite verfolgt Informationen zu 8000-Seiten. Die entsprechende Synchronisierung wird beibehalten, um Änderungen an den PFS-und SGAM-Seiten vorzunehmen. und dadurch können andere Modifikatoren für kurze Zeiträume blockiert werden.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 intensiven Konflikten auf der SGAM-Seite, wenn mehrere Zuteilungen auf gemischter Seite im Gang sind. Dies kann zu Problemen führen, die im Abschnitt "Symptom" dokumentiert sind.Hinweis Durch die dezuweisungs Aktivitäten müssen die Seiten ebenfalls geändert werden. Dies kann zu dem erhöhten Konflikt beitragen.Weitere Informationen zu den unterschiedlichen Zuordnungs Mechanismen, die von SQL Server (SGAM, GAM, PFS, IAM) verwendet werden, finden Sie im Abschnitt "Verweise".

Fehlerbehebung


Probieren Sie die folgenden Methoden aus, um die Parallelität von tempdb zu verbessern:

  • Erhöhen Sie die Anzahl der Datendateien in tempdb , um die Datenträger Bandbreite zu maximieren und Konflikte in Zuordnungs Strukturen zu verringern. Wenn die Anzahl logischer Prozessoren kleiner oder gleich acht (8) ist, verwenden Sie in der Regel die gleiche Anzahl von Datendateien wie logische Prozessoren. Wenn die Anzahl der logischen Prozessoren größer als acht (8) ist, verwenden Sie acht Datendateien. Wenn Konflikte weiterhin bestehen, erhöhen Sie die Anzahl der Datendateien um ein Vielfaches von vier (4) bis zur Anzahl logischer Prozessoren, bis die Konflikte auf akzeptable Ebenen reduziert werden. Sie können aber auch Änderungen an der Arbeitsauslastung oder am Code vornehmen.
  • In diesem TechNet-Thema sollten Sie die Empfehlungen für bewährte Methoden implementieren:  
  • Wenn die vorherigen Schritte die zuordnungskonflikte nicht erheblich verringern und der Konflikt auf SGAM-Seiten liegt, implementieren Sie das Ablaufverfolgungsflag -T1118. Unter diesem Ablaufverfolgungsflag ordnet SQL Server jedem Datenbankobjekt vollständige Blöcke zu, wodurch die Konflikte auf SGAM-Seiten eliminiert werden. Hinweise  
  • Wenden Sie die relevanten Cu für SQL Server 2016 und 2017 an, um die Vorteile des letzten Updates zu nutzen. Es wurden Verbesserungen vorgenommen, die die Konflikte in SQL Server 2016 und SQL Server 2017 weiter reduzieren. Zusätzlich zur Round-Robin-Zuweisung für alle tempdb -Datendateien verbessert der Fix die PFS-Seiten Zuweisung durch Ausführen von Round-Robin-Zuteilungen über mehrere PFS-Seiten in derselben Datendatei. Weitere Informationen finden Sie im folgenden Inhalt:  

Weitere Informationen


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

Wenn beispielsweise die Datendateigröße von tempdb 8 GB beträgt und die Protokolldateigröße 2 GB beträgt, empfiehlt es sich, 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 vorhanden sind, bietet dies zusätzlichen Leistungsvorteil. Dies ist jedoch nicht erforderlich. Die Dateien können auf demselben Datenträger-Volume koexistieren. Die optimale Anzahl von tempdb - Datendateien hängt vom Grad der Konflikte ab, die in tempdbangezeigt werden. Als Ausgangspunkt können Sie tempdb konfigurieren.mindestens gleich der Anzahl der logischen Prozessoren sein, die für SQL Server zugewiesen sind. Bei High-End-Systemen kann die Anfangsnummer acht (8) sein. Wenn der Konflikt nicht reduziert wird, müssen Sie möglicherweise die Anzahl der Datendateien erhöhen.Wir empfehlen, dass Sie die gleiche Größe von Datendateien verwenden. SQL Server 2000 Service Pack 4 (SP4) hat eine Lösung eingeführt, die einen Round-Robin-Algorithmus für gemischte Seitenzuweisungen verwendet. Aufgrund dieser Verbesserungen unterscheidet sich die Anfangsdatei für jede nachfolgende gemischte Seiten Zuweisung (wenn mehr als eine Datei vorhanden ist). Der neue Zuordnungsalgorithmus für SGAM ist reine Round Robin und berücksichtigt nicht die proportionale Füllung, um die Geschwindigkeit beizubehalten. Wir empfehlen, dass Sie alle tempdb-Datendateien in der gleichen Größe erstellen.  

Verringern der Anzahl von tempdb-Datendateien durch Erhöhen der Konflikte

In der folgenden Liste wird erläutert, wie die Erhöhung der Anzahl von tempdb - Datendateien mit gleicher Größe die Konflikte verringert:
  • Wenn Sie über eine Datendatei für tempdbverfügen, haben Sie nur eine GAM-Seite und eine SGAM-Seite für jeden 4 GB Speicherplatz.
  • Wenn Sie die Anzahl der Datendateien mit den gleichen Größen für tempdb erhöhen, wird eine oder mehrere GAM-und SGAM-Seiten für jede Datendatei erstellt.
  • Der Zuordnungsalgorithmus für GAM ordnet ein Extent (acht zusammenhängende Seiten) von der Anzahl der Dateien in Round-Robin-Manier zu, wobei die proportionale Füllung berücksichtigt wird. Wenn Sie also über 10 Dateien mit gleicher Größe verfügen, ist die erste Zuordnung aus file1, die zweite von Datei2, die dritte von file3 usw.
  • Der Ressourcenkonflikt der PFS-Seite wird reduziert, da acht Seiten gleichzeitig als "voll" markiert sind, da GAM die Seiten reserviert.
 

So wird die Implementierung von Ablaufverfolgungsflag-T1118 reduziert Konflikte

In der folgenden Liste wird erläutert, wie die Verwendung von Ablaufverfolgungsflag -T1118 die 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 der Wiederverwendung von SQL Server in Kraft bleibt.
  • -T1118 entfernt fast alle einzelnen Seitenzuweisungen auf dem Server.
  • Durch Deaktivieren der meisten Einzelseiten Zuweisungen verringern Sie den Konflikt auf der SGAM-Seite.
  • Wenn -T1118 aktiviert ist, werden fast alle neuen Zuordnungen von einer GAM-Seite (beispielsweise 2:1:2) vorgenommen, die acht (8) Seiten (1 Extent) jeweils einem Objekt zuordnet, und zwar im Gegensatz zu einer einzelnen Seite von einem Extent für die ersten acht (8) Seiten eines Objekts ohne das Ablaufverfolgungsflag.
  • Die IAM-Seiten verwenden weiterhin die einzelnen Seitenzuweisungen auf der SGAM-Seite, selbst wenn -T1118 aktiviert ist. Wenn Sie 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 zu Platzgründen finden Sie im nächsten Abschnitt.
Nachteile

Der Nachteil der Verwendung von -T1118 besteht darin, dass Sie möglicherweise eine Vergrößerung der Datenbankgröße sehen, wenn die folgenden Bedingungen zutreffen:

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

Wenn diese Bedingungen erfüllt sind, können Sie 64 KB (8 Seiten * 8 KB = 64 KB) für ein Objekt zuweisen, das nur 8 KB Speicherplatz benötigt, wodurch 56 KB Speicherplatz verschwendet werden. Wenn das neue Objekt jedoch mehr als 64 KB (8 Seiten) in seiner Lebensdauer verwendet, gibt es keinen Nachteil für das Ablaufverfolgungsflag. Daher kann SQL Server in einem Worst-Case-Szenario sieben (7) zusätzliche Seiten während der ersten Zuweisung nur für neue Objekte zuweisen, die nie über eine (1) Seite hinaus wachsen.

Informationsquellen


Weitere Informationen zu den Produkten oder Tools, die automatisch auf diese Bedingung für Ihre Instanz von SQL Server und die Versionen des SQL Server-Produkts überprüft werden, finden Sie in der folgenden Tabelle.

Regel Software Regeltitel Regelbeschreibung Produktversionen, für die die Regel ausgewertet wird
System Center Advisor SQL Server-Datenbankkonfiguration: die Anzahl der tempdb-Datendateien kann zu Blockierungen führen System Center Advisor überprüft die Anzahl der Datendateien, die für die tempdb-Datenbank konfiguriert sind. Wenn nur eine vorhanden ist und auf dem Server, auf dem SQL Server ausgeführt wird, mehr als ein Prozessor verwendet wird, wird diese Warnung generiert. Überprüfen Sie die Informationen in diesem Artikel, und fügen Sie der tempdb-Datenbank weitere Datendateien hinzu. SQL Server 2008 SQL Server 2008 R2 SQL Server 2012