Ich dachte, ich hätte einmal irgendwo gelesen, dass das Schreiben in Tempdb schneller ist als eine tatsächliche Tabelle, die nicht in Tempdb enthalten ist. Trifft dies in irgendeiner Form zu? Ich dachte, ich erinnere mich daran, dass ich etwas Besonderes über Tempdb und das Speichern der Daten im Speicher gesagt habe?
Antwort
Das Schreiben in Tempdb ist schneller als eine tatsächliche Tabelle, die nicht in Tempdb enthalten ist.
Es ist wahr. Es gibt zwei E / A-Verbesserungen in TempDb.
Bei Schreibvorgängen in eine Tabelle in einer Benutzerdatenbank müssen die Protokolldatensätze beim Festschreiben auf die Festplatte geleert werden. Bei einer minimal protokollierten Einfügung (wie SELECT … INTO) müssen die Datenbankseiten auf die Festplatte geleert werden Die minimale Protokollierung in einer Benutzerdatenbank besteht darin, dass die Datenbankseiten direkt auf die Festplatte geschrieben werden. Bis zum Abschluss von SELECT … INTO wurden alle neuen Seiten in die physischen Dateien geschrieben.
TempDb-Schreibvorgänge müssen beim Festschreiben nicht auf die Festplatte geschrieben werden, da TempDb nach einem Fehler nie wiederhergestellt wird. Sie sind also einfach nicht „. Ihre Änderungen generieren Protokolldatensätze, aber der Protokollpuffer wird nur dann auf die Festplatte geleert, wenn er voll ist, nicht für jedes Commit.
Und seit SQL Server 2014 werden die minimal protokollierten Einfügungen in TempDb auch nicht immer auf die Festplatte geschrieben. Wenn Sie eine kleine laden, kurzlebige temporäre Tabelle Es wird möglicherweise überhaupt nicht auf die Festplatte geschrieben. Das Protokoll enthält einige Datensätze zu den Seitenzuordnungen und Metadateneinträgen für die Tabelle, aber das ist es.
EG führen Sie Folgendes aus Batch in Tempdb, einer vollständigen Wiederherstellungsdatenbank und einer einfachen Wiederherstellungsdatenbank, um die Unterschiede zu erkennen.
drop table if exists foo go declare @data bigint declare @log bigint select @log = sum(case when type_desc = "LOG" then num_of_bytes_written end) ,@data = sum(case when type_desc = "ROWS" then num_of_bytes_written end) from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs select * into foo from sys.objects select -@log + sum(case when type_desc = "LOG" then num_of_bytes_written end) log_bytes ,-@data + sum(case when type_desc = "ROWS" then num_of_bytes_written end) data_bytes , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model from sys.database_files f cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs und Sie sehen Folgendes:
Für eine einfache Wiederherstellung:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 24576 16384 SIMPLE für eine vollständige Wiederherstellung:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 36864 0 FULL und für tempdb:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 0 0 SIMPLE Manchmal wird für tempdb der Protokollpuffer geleert:
log_bytes data_bytes recovery_model -------------------- -------------------- --------------- 61440 0 SIMPLE Kommentare
- Es gibt einen Fall, in dem das anfängliche Einfügen schneller ist, Sie jedoch später wieder beißen. Diese Demo zeigt eine Abfrage, die Daten in den Puffercache bringt und viel länger dauert, da der Lazy Writer gerade auf Disc-Tempdb-Seiten schreibt, die für eine temporäre Tabelle verschmutzt sind, die nicht mehr existiert. youtube .com / watch? v = X60ipwYv1Ms & feature = youtu.be
- Ja. Es gibt ' eine potenzielle zukünftige Verbesserung, um das Löschen von Pufferpoolseiten zu vermeiden, die keinem Objekt zugeordnet sind. Das Laden einer großen temporären Tabelle führt jedoch immer zu E / A, entweder direkt oder indirekt, indem der für das Caching verfügbare Speicher reduziert wird.
Antwort
Neben dem Schreiben in Tempdb wird häufig nicht jede aufschlagende Festplatte / Netzwerk-E / A angezeigt, wie in der Antwort von David Browne beschrieben, abhängig von Ihrer E / A. Konfiguration Sie können feststellen, dass selbst wenn die Daten groß genug sind, um auf die Festplatte gespoolt zu werden, sie immer noch schneller sind als die Auswahl in einer „normalen“ Tabelle:
-
TempDB kann sich auf einer anderen befinden Laufwerke haben also eine eigene E / A-Bandbreite. Dies ist besonders wichtig bei sich drehenden Laufwerken anstelle von SSDs. Das Lesen von und Schreiben in dieselbe Datenbank (oder eine andere Datenbank auf denselben Laufwerken) erfordert mehr Kopfbewegungen Fügen Sie mehr E / A-Latenz hinzu und drosseln Sie möglicherweise Ihre effektive E / A-Bandbreite. Das Kopieren von Daten zwischen Datenbanken auf verschiedenen Laufwerken / Arrays hat nicht die gleiche zusätzliche Latenz.
-
TempDB ist möglicherweise sogar auf fa ster Medien als Ihr Hauptspeicher. Möglicherweise auf lokalen Laufwerken, auf denen sich der Hauptspeicher im Netzwerk befindet, oder auf NVMe-SSDs, auf denen sich der Hauptspeicher auf herkömmlichen Laufwerken befindet.
Diese beiden Unterschiede können auch innerhalb desselben auftreten Datenbank, wenn Sie mehrere Dateigruppen verwenden, um Teile der Daten auf verschiedene Laufwerke / Arrays zu verteilen.
Das Gegenteil kann auch der Fall sein, wenn Sie mehrere Datenbanken haben, die aktiv verwendet werden. Da TempDB eine gemeinsam genutzte Ressource ist, sind sie und die Laufwerke / Netzwerke, auf denen sie gehostet wird, möglicherweise stärker belastet als die Datendateien für eine einzelne Datenbank.