SQL Server TempDB Tutorial

Durch: Eric Blinn / Aktualisiert: 2020-07-17 | Bemerkungen (2) | Verwandt: Mehr > Systemdatenbanken

Problem

SQL Server wird standardmäßig mit vier Systemdatenbanken installiert. Sie sind Master, model, msdb und TempDB. Ich muss wissen, was die Systemdatenbank „TempDB“ ist und wofür sie verwendet wird. Diese vorherigen Tutorials behandeln Master, Model undmsdb im Detail.

Lösung

Dieser Tipp behandelt die TempDB-Datenbank. TempDB ist eine Datenbank, die hatviele Funktionen in SQL Server, aber es wird selten explizit aufgerufen. Es hat so viele Funktionen, dass es oft eine der geschäftigsten, wenn nicht die geschäftigste Datenbank auf den meisten SQL Server-Instanzen ist. Lesen Sie weiter, um zu erfahren, was viele dieser Funktionen sind!

SQL Server TempDB Übersicht

Was ist der Zweck von SQL Server TempDB?

Eine der Funktionen von TempDB besteht darin, sich auf Betriebssystemebene wie eine Seiten- oder Auslagerungsdatei zu verhalten. Wenn ein SQL Server-Vorgang zu groß ist, um im Arbeitsspeicher abgeschlossen zu werden, oder wenn die anfängliche Speicherzuweisung für eine Abfrage zu klein ist, kann der Vorgang in TempDB auf die Festplatte verschoben werden.

Eine weitere Funktion von TempDB ist storetemporary Tabellen. Jeder, der eine temporäre Tabelle in T-SQL mit einem Pound- oder Hash-Präfix (#) oder dem Doublepound / Hash-Präfix (##) erstellt hat, hat ein Objekt in TempDB erstellt, da hier diese gespeichert sind.

CREATE TABLE #MSSQLTips (RowID int);

Wenn atrigger ausgeführt wird, werden die eingefügten und gelöschten virtuellen Tabellen in TempDB gespeichert.

Alle Datenbanken, die RCSI (COMMITTED SNAPSHOT ISOLATION) verwenden, haben ihre eigenen Versionsinformationen in TempDB gespeichert.

Jede der oben genannten Funktionen von TempDB wird abgeschlossen, ohne jemals namentlich erwähnt zu werdentempdb und kann daher überraschend für TempDB verwendet werden.

TempDB kann auch auf verschiedene Arten explizit aufgerufen werden. Tabellen können in TempDB generiert werden, indem die Datenbank in einer create-Anweisung referenziert wird. Der Code sieht genau wie eine normale DDL-Operation aus, aber wenn er in TempDB ausgeführt wird, ist die Tabelle per Definition eine temporäre Tabelle.

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

TempDB bei jedem Start der SQL Server-Instanz neu generiert wird. Objekte, die während einer vorherigen Sitzung in TempDB erstellt wurden, bleiben beim Neustart des Dienstes nicht bestehen. TempDB erhält seine anfängliche Objektliste von Dermodelldatenbank, die im Allgemeinen leer oder fast leer sein wird.

Die Indexpflege kann optional dazu aufgefordert werden, Sortierungen in TempDB durchzuführen, anstatt zu versuchen, dies mit freiem Speicherplatz in der Benutzerdatenbank zu tun.

ALTER INDEX ON . REBUILD WITH (SORT_IN_TEMPDB = ON);

Kann ich SQL Server ohne TempDB-Datenbank ausführen?

SQL Server kann ohne TempDB nicht ausgeführt werden (außer in sehr extremen Wiederherstellungsszenarien und für sehr kurze Zeiträume).

Ist die Version der TempDB-Datenbank für Version, Edition und Patchlevel von SQL Server eindeutig?

TempDB ist eine größtenteils leere Datenbank und es gibt nichts besonders Einzigartiges an jeder Edition oder Patch-Ebene von SQL Server.

Es gab in den letzten Jahren nur 2 sinnvolle Änderungen an TempDB auf Versionsebene. Das erste ist, dass ab SQL Server 2016 das Verhalten von TEMPDB geändert wurde, sodass das Aktivieren der Ablaufverfolgungsflags T1117 und T1118 nicht mehr als bewährte Methode angesehen wird. Das zweite ist, dass ab SQL Server 2019 einige stark verwendete Systemobjekte in TempDB auf In-Memory-Tabellen aktualisiert wurden, um Konflikte zu reduzieren und die allgemeine Serverleistung zu verbessern.

Können Sie die TempDB-Datenbank löschen oder umbenennen und sollten Sie dies aus irgendeinem Grund tun?

TempDB kann nicht gelöscht, getrennt, offline geschaltet oder umbenannt werden. Attemptingany dieser Operationen gibt einen Fehler zurück. Es gibt keinen Grund, dass dies jemals versucht werden sollte, da dies eine kritische Systemdatenbank ist.

SQL Server TempDB-Datenbankspeicherort

Welche physischen Dateien und Namen unterstützen die TempDB-Datenbank?

Die logischen Standarddateinamen sind tempdev für data und templog für log.Sie können auf der Festplatte als tempdb gefunden werden.mdf und templog.ldf bzw. TempDBcommonly hat viele Datendateien.

Wie viele TempDB-Datendateien sollte es geben?

Es sollte eine TempDB-Datendatei für jeden Thread / Kern / vCPU in der Instanz mit maximal 8 sein.

Wo finde ich die TempDB-Datenbank auf der Festplatte und in SSMS?

Die Dateien können durch Abfragen von sys gefunden werden.sysfiles dmv oder der Dateibereich im Fenster databaseproperties.

SELECT * FROM TempDB.sys.sysfiles;

Macht es einen Unterschied, wo sich die TempDB-Datenbankdateien befinden (müssen sie sich auf C: \, SAN usw. befinden.)?

Best Practices schreiben vor, dass sich Ihre TempDB-Datendateien aufgrund ihrer Aktivität auf dem schnellsten verfügbaren Speicher befinden sollten. In einigen Fällen kann dies bedeuten, dass sie mit den Benutzerdatendateien der Datenbank gespeichert werden, aber ein separates Volume auf einer separaten Festplatte wird bevorzugt, falls verfügbar.

Es gibt unterschiedliche Meinungen zur TempDB-Protokolldatei, aber dieser Autor zieht es vor, diese Datei mit den anderen Transaktionsprotokolldateien abzulegen.

Kann die TempDB-Datenbank verschoben werden?

Die TempDB-Datenbank kann verschoben werden. Die Methode zum Verschieben von TempDB ist einfachimplementieren. Ändern Sie einfach die Eigenschaft FILENAME der gewünschten Datei (en).Es müssen keine Dateien verschoben werden, wie wenn andere Systemdatenbanken verschoben werden. SQLServer erstellt die Datei (en) mit dem neuen Namen und / oder am neuen Speicherort beim nächsten Dienststart. Vergessen Sie nicht, zurück zu gehen und die jetzt nicht verwendeten TempDB-Dateien zu löschen, die zurückgelassen werden.

ALTER DATABASE MODIFY FILE ( NAME = N'tempdev', FILENAME = N'G:\DATA\tmp.mdf');GOALTER DATABASE MODIFY FILE ( NAME = N'templog', FILENAME = N'H:\LOG\tmplog.ldf');GO

SQL Server TempDB-Datenbankgröße und -wachstum

Wie sollten die Einstellungen für das Datenbank- und Transaktionslogwachstum für die TempDB-Datenbank konfiguriert werden?

Autogrowth-Einstellungen sollten auf eine bestimmte Anzahl von MB festgelegt werden, nicht auf einen Prozentsatz.Jede Datendatei sollte die gleiche Anzahl von MB für das Wachstum haben.

Die richtige Größe der Dateien für eine Datenbank ist im Allgemeinen eine Best Practice, aber niemals mehr als für TempDB. Überwachen Sie die Instanz – insbesondere wenn dies der Fall istneu – und stellen Sie fest, ob die Dateien während des normalen Betriebs wachsen.Wenn ja, ändern Sie die Startgröße so, dass SQL Server nicht gezwungen wird, die Dateien nach jedem Dienststart konsistent zu vergrößern.

Was ist die typische Größe der TempDB-Datenbank und wie groß kann sie werden?

Auf diese Frage gibt es keine gute Antwort. Was für eine Instanz typisch ist, kannnicht typisch für eine andere sein. Dieser Autor hat Fälle gesehen, in denen die TempDB basicallyunused ist durchschnittlich nur ein paar MB Speicherplatz zu einem bestimmten Zeitpunkt verwendet. Andere werden jedoch ständig abgefragt und verwenden Hunderte von GB Speicherplatz – sogar Intospace, gemessen in TB –, insbesondere während eines Indexwartungsfensters.

Wenn Sie entscheiden, wie viel Speicherplatz TempDB auf einer neuen Instanz zugewiesen werden soll, können Sie nur wenige Dinge tun. Ersetzt die neue Instanz eine ältere, vorhandene Instanz? Verwenden Sie in diesem Fall eine Perfmon-Ablaufverfolgung oder eine geplante DMV-Erfassung, um den verwendeten Speicherplatz in TempDB über mehrere Tage hinweg zu ermitteln. Stellen Sie sicher, dass Sie ein Wartungsfenster in diese Erfassung aufnehmen. Versuchen Sie, auf der neuen Instanz genügend Speicherplatz zuzuweisen, um den größten während der Ablaufverfolgung erfassten Speicherplatz mit zusätzlichem Spielraum zu berücksichtigen. Passen Sie dann die richtige Größe der Dateien an diese Speicherplatzzuweisung an.

Die 2 folgenden Abfragen können helfen. Die erste wird eine Momentaufnahme der Größe der Daten- und Protokolldateien sowie des in den Dateien verwendeten Speicherplatzes erstellen. Dies kann regelmäßig ausgeführt werden, um die Nutzung im Laufe der Zeit zu verfolgen. Die zweite Abfrage gibt an, wie oft die Protokolldatei seit dem letzten Neustart der Instanz gewachsen ist.

Wenn keine Workload vorhanden ist, die als Leitfaden verwendet werden kann, und keine andere Anleitung, möglicherweise von einem Softwareanbieter, vorhanden ist, müssen Sie nur sicherstellen, dass in TempDB Platz vorhanden ist, um die größten erwarteten einzelnen Objekte zu berücksichtigen, die während einer Indexwartung in TempDB sortiert werden könnenwindows. Überwachen Sie von dort aus die Verwendung und passen Sie sie entsprechend an.

USE TempDB;GOSELECT GETUTCDATE() AS SnapshotDateTime, groupid --0 = data, 1 = log, SUM(size/128.) SizeOnDiskInMB, SUM(FILEPROPERTY(name, 'spaceused')/128.) MBUsedWithinFile FROM TempDB.sys.sysfilesGROUP BY groupid; SELECT * FROM sys.dm_os_performance_countersWHERE counter_name = 'Log Growths' AND instance_name = 'tempdb';

Server TempDB-Datenbankwartung

Können Sie die Datenbank und das Transaktionslog für die TempDB-Datenbank verkleinern?

Technisch gesehen ja, aber dies ist keine Operation, die jemals abgeschlossen werden sollte.Das Ziel sollte immer die richtige Größe von TempDB sein. Wenn eine einmalige Operation dazu führt, dass diese Dateien unerklärlicherweise wachsen, werden sie beim nächsten Neustart des Dienstes wieder normal.

Sollten Sie Indizes neu erstellen?

TempDB ist nicht Teil von indexbezogenen Wartungsplänen. Dies bedeutet, dass Indizes nicht neu erstellt, neu organisiert oder Statistiken aktualisiert werden müssen.

Sollten Sie Integritätsprüfungen durchführen Was ist der Code?

Es gibt unterschiedliche Meinungen darüber, obtempdb auf Integrität überprüft werden sollte.Dieser Autor wird das Argument darlegenfür und gegen und lassen Sie den Leser entscheiden.

Gegen: Eine Integritätsprüfung kann TempDB nicht mit der gleichen Gesamtheit überprüfen wie alle anderen Datenbanken.Die in TempDB vorhandenen Daten sind sehr vorübergehend, sodass die Integritätsprüfung zu keinem Zeitpunkt viel zu überprüfen ist.Aus dem gleichen Grund sind alle Daten, die beschädigt werden, wahrscheinlich aktive Daten, die eine Warnung verursachen, wenn sie von der Festplatte gelesen werden, lange bevor eine Integritätsprüfung aufgerufen wird.

Für: Während eine Integritätsprüfung nicht alles in TempDB überprüfen kann, was in anderen Datenbanken überprüft wird, bedeutet dies nicht, dass es keinen Wert hat.TempDB ist eine integrale Datenbank undWenn es Korruption gibt, ist es unerlässlich, darüber Bescheid zu wissen, damit Korrekturmaßnahmen ergriffen werden können.

Der Code zum Ausführen einer Integritätsprüfung befindet sich unten.

DBCC CHECKDB ('TempDB'); 

SQL Server TempDB-Datenbankobjekte

Was sind die wichtigsten Tabellen und Prozeduren in der TEMPDB-Datenbank, die für SQL Server-Profis wichtig sind?

Es gibt keine.Diese Datenbank dient zur temporären Speicherung von objects.It kommt nicht mit anytables oder Prozeduren installiert.

Kann ich meine eigenen Objekte in der TEMPDB-Datenbank speichern?

Objekte können zwar in TempDB erstellt werden, bleiben jedoch nach dem nächsten Start des SQL Server-Dienstes nicht bestehen.

SQL Server TempDB-Datenbankberechtigungen

Wer hat Zugriff auf die TempDB-Datenbank und gibt es unterschiedliche Berechtigungsstufen?

Jeder hat Zugriff und den gleichen Zugriff auf die TempDB.

SQL Server TempDB-Datenbanksicherung

Muss ich die TempDB-Datenbank sichern?

TempDB kann nicht gesichert werden. Es speichert nur vorübergehende Daten und wird bei jedem Start des Dienstes neu erstellt, sodass im Katastrophenfall nichts wiederhergestellt werden kann.

SQL Server TempDB Database Recovery Model

Welche Wiederherstellungs-TempDB sollte die TempDB-Datenbank haben und kann das Wiederherstellungsmodell geändert werden?

TempDB befindet sich in der EINFACHEN Wiederherstellung und kann nicht geändert werden.

SQL Server TempDB-Datenbank wiederherstellen

Muss die TempDB-Datenbank jemals wiederhergestellt werden?

TempDB kann nicht wiederhergestellt werden. Es sollten keine Daten und kein Code in TempDBto wiederhergestellt werden. Wenn TempDB beschädigt wird, löschen Sie einfach die Daten und Protokolldateien, damit beim nächsten Dienststart neue generiert werden können.

Nächste Schritte

• Dieser Tipp zeigt, wie man Warnungen für Probleme innerhalb der TempDB-Größen und des Festplattenspeichers erstellt
• Dieser Tipp zeigt, wie man die sofortige Dateiinitialisierung aktiviert, eine wichtige Funktion für alle DBs, insbesondere jedoch für TempDB, wenn sie nicht die richtige Größe hat.

Zuletzt aktualisiert: 2020-07-17

Über den Autor

Eric Blinn ist der Sr. Datenarchitekt für Squire Patton Boggs. Er ist auch SQL-Autor und lokaler Gruppenleiter.
Alle meine Tipps anzeigen

Verwandte Ressourcen

• Weitere SQL Server DBA-Tipps…