In diesem Artikel werden die Best Practices erläutert, die befolgt werden sollten, um die TempDB-Datenbank in einem fehlerfreien Zustand zu halten und unerwartetes Wachstum der Datenbank zu verhindern.
TempDB-Übersicht
Die TempDB-Datenbank ist eine der wichtigsten SQL Server-Systemdatenbanken, die zum Speichern temporärer Benutzerobjekte verwendet wird, z. B. temporäre Tabellen, die vom Benutzer definiert oder von der Ausführung von Tabellenwertfunktionen zurückgegeben werden, temporäre gespeicherte Prozeduren, Tabellenvariablen oder Indizes.
Zusätzlich zu den Benutzerobjekten wird TempDB zum Speichern interner Objekte verwendet, die vom SQL Server-Datenbankmodul während der verschiedenen internen Vorgänge erstellt werden, z. B. Zwischensortierung, Spooling, Aggregation oder Cursoroperationen.
Die TempDB-Systemdatenbank wird auch zum Speichern der Zeilenversionen verwendet, um die Funktionen zu unterstützen, die die Verfolgung der Änderungen erfordern, die an den Tabellenzeilen vorgenommen werden, z. B. die Snapshot-Isolationsstufe der Transaktionen, Online-Indexwiederherstellungen oder die Funktion Mehrere aktive Ergebnismengen.
Die TempDB-Datenbank wird jedes Mal gelöscht und neu erstellt, wenn der SQL Server-Dienst neu gestartet wird, beginnend mit einer neuen sauberen Kopie der Datenbank. Basierend auf dieser Tatsache werden alle Benutzer- und internen Datenbankobjekte, die in dieser Datenbank gespeichert sind, automatisch gelöscht, wenn der SQL Server-Dienst neu gestartet wird oder wenn die Sitzung, in der diese Objekte erstellt wurden, getrennt wird. Daher sind die Sicherungs- und Wiederherstellungsvorgänge für die TempDB nicht verfügbar.
Warum TempDB
Die TempDB-Systemdatenbank spielt eine wichtige Rolle bei der Leistungsoptimierung von SQL Server. Weil es als Caching-Speicher zum Speichern verschiedener Arten von Benutzerdatenbankobjekten und zum Speichern der systeminternen Objekte verwendet wird, um die SQL Server-Datenbankmodul-bezogenen Prozesse zu beschleunigen.
Aufgrund dieser wichtigen Rolle, die die TempDB-Systemdatenbank bei der Verbesserung der Gesamtleistung der SQL Server-Instanz spielt, ist es sehr wichtig, die Best Practices für Anfangsgröße, automatisches Wachstum und Speicherort auf die TempDB-Datenbankdaten und -Protokolldateien anzuwenden. Wenn Sie diese Best Practices auf eine Weise anwenden, die zu Ihrer SQL Server-Instanzarbeitslast passt, wird verhindert, dass die TempDB-Datenbankdaten und Protokolldateien sehr häufig erweitert werden, wobei zu berücksichtigen ist, dass der Dateierweiterungsprozess ein teurer Prozess ist, bei dem SQL Server Engine zusätzlichen Speicherplatz vom Betriebssystem anfordert, der auf diesen Speicherplatz zurückgesetzt wird, bevor er SQL Server Engine zugewiesen wird. Sie können sich vorstellen, wie viel Zeit und Ressourcen für diesen Zuweisungsprozess erforderlich sind.
Wenn Sie bereits alle Best Practices anwenden und ein unerwarteter Zuweisungsvorgang aufgetreten ist, können Sie den Zuweisungsprozess beschleunigen, indem Sie die Option database Instant file initialization verwenden, bei der das Betriebssystem dem SQL Server-Dienst vertraut und den erforderlichen Speicherplatz zuweist, ohne den Zeit- und Ressourcenverschwendungsvorgang für den zugewiesenen Speicherplatz auszuführen.
Best Practices
Es gibt eine Reihe von Best Practices für die Konfiguration von TempDB-Systemdatenbanken, die empfohlen werden, um die beste Leistung dieser freigegebenen Ressource zu erzielen und Ihre SQL Server-Instanz vor unerwartetem Wachstum der TempDB-Datenbankdaten oder Protokolldateien zu schützen, die den zugrunde liegenden Speicherplatz füllen können.
Diese Konfigurationen umfassen:
- Die Anzahl der TempDB-Datendateien sollte der Anzahl der logischen Prozessoren (bis zu acht Dateien) auf dem Computer entsprechen, auf dem die SQL Server-Instanz installiert ist. Wenn die Anzahl der logischen Prozessoren auf diesem Computer größer als acht ist, legen Sie die Anzahl der Datendateien auf acht fest, mit der Möglichkeit, sie um ein Vielfaches von 4 zu erweitern, falls Sie ein TempDB-Konfliktproblem haben
- Stellen Sie sicher, dass die TempDB-Dateien innerhalb derselben Dateigruppe die gleiche Größe haben, um aufgrund des proportionalen Füllschreibmechanismus, der zum Schreiben der Daten in die Datendateien verwendet wird, die höchstmögliche Effizienz paralleler Operationen zu erzielen. Weitere Informationen finden Sie unter SQL Server Proportional Fill algorithm verstehen
- Konfigurieren Sie die TempDB-Datenbankdaten und -Protokolldateien mit einer angemessenen Anfangsgröße und automatisch wachsenden Mengen, die der erwarteten Datenmenge entsprechen, die in diese Dateien geschrieben wird, und die verschiedenen Arten von Arbeitslasten, die in Ihrer SQL Server-Instanz ausgeführt werden, einschließlich Benutzeraktivitäten und Indexwartungsvorgänge, um die häufigen kleinen Inkrementbeträge zu vermeiden, die sich auf die Gesamtleistung der Instanz auswirken
- Suchen Sie die TempDB-Dateien auf einem schnellen E / A-Subsystem, um E/A-Engpässe
- Suchen Sie die TempDB auf einem separaten Laufwerk, entfernt von den Benutzerdatenbanken und anderen Systemdatenbanken. Auf diese Weise wirken sich die umfangreichen Lese- und Schreibvorgänge, die für TempDB ausgeführt werden, nicht auf die Arbeitslast der Benutzerdatenbanken aus oder werden davon beeinflusst, wodurch die Gesamtleistung der SQL Server-Instanz verbessert wird
Weitere Informationen zu den neuen Verbesserungen der TempDB-Leistung finden Sie im TempDB-Datenbankdokument.
Anwendungshinweise
Vor der SQL Server 2016-Version kann die TempDB-Größenzuweisung nach der Installation der SQL Server-Instanz auf der Seite Datenbankeigenschaften ausgeführt werden. Hier können Sie die Anzahl der Datenbankdatendateien steuern, die für die aktuelle SQL Server-Instanz geeignet sind, die anfängliche Größe für jede Datenbankdaten- und Protokolldatei, den richtigen Wert für die Autogrowth-Option und den Speicherort der Datenbankdaten und Protokolldateien.
Um diese Änderungen durchzuführen, erweitern Sie den Knoten Datenbanken in SQL Server Management Studio, erweitern Sie den Knoten Systemdatenbanken, klicken Sie mit der rechten Maustaste auf die Datenbank tempdb und wählen Sie die Option Eigenschaften wie folgt aus:
Durchsuchen Sie im geöffneten Fenster Datenbankeigenschaften die Seite Dateien und überprüfen oder ändern Sie die unten gezeigten Einstellungen für verschiedene Datenbankdaten und Protokolldateien:
Ab der SQL Server 2016-Version können alle diese Änderungen mit den Best Practices, die zu den aktuellen Serverressourcen passen, die bereits vom Installationsassistenten vorgeschlagen wurden, während des Installationsprozesses der SQL Server-Instanz mithilfe des SQL Server-Installationsassistenten unter Datenbankmodulkonfigurationen | TempDB überprüft und ausgeführt werden, wie unten gezeigt:
Verfolgen des TempDB-Wachstums
Das Verfolgen des Wachstums der TemDB-Datenbankdateien und der Abfragen, die die TempDB-Ressourcen verbrauchen, kann mithilfe der Leistungsmonitorindikatoren ausgeführt werden, die den TempDB-Dateien zugeordnet sind, und des SQL Profiler-Tools, das einen bestimmten Datenbank-Workload-Typ überwacht.
Eine datenbankadministratorfreundlichere Möglichkeit, das Wachstum der TempDB-Datenbankdateien zu verfolgen, besteht darin, die folgenden dynamischen Verwaltungsansichten abzufragen:
- sys.dm_db_file_space_usage, das die Informationen zur Speicherplatznutzung für jede Datei in der Datenbank zurückgibt, ohne anzuzeigen, welche Sitzung oder Aufgabe diesen Speicherplatz verbraucht hat
- sys.dm_db_session_space_usage, das die Anzahl der zugewiesenen und freigegebenen Seiten pro Sitzung zurückgibt
- sys. dm_db_task_space_usage, die die Anzahl der zugewiesenen und freigegebenen Seiten pro Aufgabe zurückgibt
Beachten Sie, dass das von sys zurückgegebene Ergebnis.dm_db_session_space_usage DMV ist das von sys zurückgegebene Ergebnis.dm_db_task_space_usage DMV wenn alle Aufgaben erfolgreich abgeschlossen sind, da mehrere Aufgaben im Zusammenhang mit derselben Abfrage in derselben Sitzung ausgeführt werden. Ein weiterer Vorteil dieser DMVs besteht darin, dass der Objekttyp angezeigt wird, der die TempDB-Ressourcen verbraucht, und ob es sich bei diesem Objekt um ein Benutzerobjekt handelt, z. B. eine temporäre oder variable Tabelle, oder um ein internes Objekt, z. B. eine Indexpflege oder eine Sortieroperation. Basierend auf der Anzahl der Seiten, die von jedem Objekt verbraucht werden, können Sie angeben, ob TempDB von einem internen oder einem Benutzerobjekt verbraucht wird, und diesen Verbraucher optimieren.
sys.dm_db_file_space_usage
Mit dem DMV sys.dm_db_file_space_usage kann der in den TempDB-Datenbankdateien verwendete Speicherplatz überwacht werden. Die folgenden Abfragen zeigen den nicht zugewiesenen Speicherplatz, den zum Speichern von Zeilenversionen verwendeten Speicherplatz, den von den internen Objekten belegten Speicherplatz und schließlich den von den Benutzerobjekten belegten Speicherplatz wie folgt:
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT (SUM(unallocated_extent_page_count)*1.0/128) AS TempDB_FreeSpaceAmount_InMB
FROM sys.dm_db_file_space_usage;
SELECT (SUM(version_store_reserved_page_count)*1.0/128) AS TempDB_VersionStoreSpaceAmount_InMB
VON sys.dm_db_file_space_usage;
SELECT (SUMME (internal_object_reserved_page_count)*1.0/128) ALS TempDB_InternalObjSpaceAmount_InMB
VON sys.dm_db_file_space_usage;
SELECT (SUM(user_object_reserved_page_count)*1.0/128) ALS TempDB_UserObjSpaceAmount_InMB
VON sys.dm_db_file_space_usage;
|
Aus dem Ergebnis, das von meiner lokalen SQL Server-Instanz zurückgegeben wird, können Sie ersehen, dass in den TempDB-Dateien 60 MB nicht verwendet werden, wobei nur 0,5 MB für interne Objekte und 1,5 MB für die Benutzerobjekte verwendet werden, wie unten gezeigt:
sys.dm_db_task_space_usage und sys.dm_db_session_space_usage
sys.dm_db_task_space_usage und sys.dm_db_session_space DMVs werden verwendet, um die Anzahl der zugewiesenen Seiten wird von jeder Aufgabe oder Sitzung in der TempDB-Datenbank freigegeben. Auf diese Weise können Sie sehen, welcher Benutzer oder ein internes Objekt den TempDB-Speicherplatz belegt.
Das folgende Ergebnis wird beispielsweise von der Abfrage des sys zurückgegeben.dm_db_task_space_usage DMV unter TempDB-Datenbank, wo die Anzahl der zugewiesenen und freigegebenen Seiten für jeden Benutzer oder jede interne Aufgabe wie folgt zurückgegeben wird:
Der DBA benötigt jedoch nicht so viele Details, um die Abfrage zu verfolgen, die die TempDB-Ressourcen verbraucht. Daher müssen wir das vorherige Ergebnis für jede Sitzung mithilfe der folgenden Abfrage zusammenfassen:
|
1
2
3
4
5
6
7
8
|
SELECT session_id,
SUMME(internal_objects_alloc_page_count) ALS NumOfPagesAllocatedInTempDBforInternaltask,
SUMME(internal_objects_dealloc_page_count) ALS NumOfPagesDellocatedInTempDBforInternaltask,
SUMME( user_objects_alloc_page_count) ALS NumOfPagesAllocatedInTempDBforUsertask,
SUMME(user_objects_dealloc_page_count) ALS NumOfPagesDellocatedInTempDBforUsertask
VON sys.dm_db_task_space_usage
GRUPPIEREN NACH session_id
SORTIEREN NACH NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC
|
Das Ergebnis des zusammengefassten Seitenverbrauchs pro Sitzung lautet wie folgt:
Das gleiche Ergebnis kann durch Abfragen des sys zurückgegeben werden.dm_db_session_space_usage DMW, das die Seitenzuweisung und -freigabe für alle Aufgaben zusammenfasst, die unter derselben Sitzung ausgeführt werden, wie unten gezeigt:
Lassen Sie uns einige Aktionen in der TempDB-Datenbank ausführen, indem wir sowohl interne als auch Benutzerobjekte wie folgt verwenden:
Führen Sie dann die folgende konsolidierte Abfrage aus, die die Anzahl der zugewiesenen und freigegebenen Seiten nach internen und Benutzerobjekten zurückgibt und beide abgeschlossenen Operationen anzeigt (mit sys.dm_db_session_space_usage DMV) und die nicht abgeschlossenen Operationen (mit sys.dm_db_task_space_usage DMV):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
WÄHLEN SIE SpacePerTask.session_id,
SpacePerSession.internal_objects_alloc_page_count
+ SUMME(SpacePerTask.internal_objects_alloc_page_count) ALS NumOfPagesAllocatedInTempDBforInternalTask,
SpacePerSession .internal_objects_dealloc_page_count
+ SUMME(SpacePerTask.internal_objects_dealloc_page_count) ALS NumOfPagesDellocatedInTempDBforInternalTask,
SpacePerSession .User_objects_alloc_page_count
+ SUMME(SpacePerTask.User_objects_alloc_page_count) ALS NumOfPagesAllocatedInTempDBforUserTask,
SpacePerSession .User_objects_dealloc_page_count
+ SUMME(SpacePerTask.User_objects_dealloc_page_count) ALS NumOfPagesDellocatedInTempDBforUserTask
VON sys.dm_db_session_space_usage ALS SpacePerSession
INNER JOIN sys.dm_db_task_space_usage ALS SpacePerTask AUF SpacePerSession.session_id = SpacePerTask.session_id
GRUPPIEREN NACH SpacePerTask.session_id, SpacePerSession.internal_objects_alloc_page_count, SpacePerSession.internal_objects_dealloc_page_count, SpacePerSession.User_objects_alloc_page_count,SpacePerSession.User_objects_dealloc_page_count
SORTIEREN NACH NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC
|
Das Ergebnis der vorherigen Operationen spiegelt die Abfrageausgabe wider und zeigt die Anzahl der zugewiesenen und freigegebenen Seiten an, indem die temporäre Benutzertabelle erstellt und die Daten während des Indexwiederherstellungsprozesses sortiert werden (siehe unten:
Um den Text der Abfrage abzurufen, die die TempDB-Ressourcen verbraucht, können Sie Ihre SQL-Entwicklungsfähigkeiten verwenden, indem Sie das Ergebnis der DMV ys.dm_db_task_space_usage mit anderen DMVs wie sys.dm_exec_requests und sys . dm_exec_sessions mit der dynamischen Verwaltungsfunktion sys.dm_exec_sql_text, in der die Abfragen angezeigt werden, die jetzt den TempDB-Speicherplatz belegen, wie in dieser Abfrage.
Das Ergebnis ähnelt dem folgenden:
- Autor
- Neueste Beiträge
Er ist Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate und Microsoft Certified Trainer.
Außerdem trägt er mit seinen SQL-Tipps in vielen Blogs bei.
Alle Beiträge von Ahmad Yaseen anzeigen
- Fragen und Antworten zum Azure Data Factory-Interview – 11. Februar 2021
- Überwachen von Azure Data Factory – 15. Januar 2021
- Verwenden der Quellcodeverwaltung in Azure Data Factory – Januar 12, 2021