Ich hatte am 22.03.2014 in Nürnberg während der SNEK (SQL Server + .NET Entwickler Konferenz) die Gelegenheit, nach meinem Vortrag ein interessantes Gespräch führen können, in dem unter anderen behauptet wurde, dass Tabellenvariablen Objekte seien, deren Datenoperationen (INSERT/UPDATE/DELETE)  im Buffer Pool – und somit im RAM – stattfinden und daher ein Grund für die bevorzugte Wahl von Tabellenvariablen sei. Diese Aussage ist nicht richtig wie der nachfolgende Artikel zeigen soll.

Was sind Tabellenvariablen?

Tabellenvariablen wurden mit Microsoft SQL Server 2000 als Alternative zu temporären Tabellen eingeführt. Tabellenvariablen haben Vor- und Nachteile gegenüber temporären Tabellen, die aber in diesem Beitrag nicht thematisiert werden sollen. Ein besonderer Vorteil einer Tabellenvariablen ist die Deklaration ihrer Struktur mit einem DECLARE-Statement, wie es auch für andere Variablen gilt. Der Mythos der Verarbeitung aller Daten, die in einer Tabellenvariablen gespeichert werden, im Buffer Pool hält sich – aus welchen Gründen auch immer – beständig. Das folgende Beispiel zeigt, dass Tabellenvariablen – wie auch temporäre Tabellen – ihre DML-Aktivitäten in der TEMPDB ausführen!

Testszenario

Im folgenden Beispiel wird eine Tabellenvariable @t deklariert, die eine feste Datensatzläge von 4.000 Bytes besitzt. Anschließend werden 1.000 Datensätze in diese Tabellenvariable eingetragen. Um die Schreib- und Leseaktivitäten zu messen, werden vor als auch nach dem Schreiben der Daten das aktuelle I/O der TempDB-Datenbank gemessen.

Um das Testergebnis nicht zu verfälschen, wird der Test natürlich in einer isolierten Umgebung ausgeführt, in der keine weiteren Benutzerprozesse die Messungen – deutlich – beeinflussen können!

DECLARE    @sum_of_reads    decimal (18, 0);

DECLARE    @sum_of_writes   decimal (18, 0);

-- Messung des IO der TEMPDB vor der Ausführung des Codes

SELECT  @sum_of_reads  = SUM(num_of_bytes_read),

        @sum_of_writes = SUM(num_of_bytes_written)

FROM    sys.dm_io_virtual_file_stats(2, NULL)

-- Deklaration einer Tabellenvariablen

DECLARE    @t TABLE

(

    c1 int        NOT NULL  PRIMARY KEY CLUSTERED,

    c2 char(3996) NOT NULL  DEFAULT ('just stuff')

);

-- 1.000 Datensätze in Tabellenvariable eintragen

DECLARE    @i int = 1;

WHILE @i <= 1000

BEGIN

    INSERT INTO @t (c1) VALUES (@i);

    SET    @i += 1;

END

-- Berechnung des IO-Delta für TEMPDB

;WITH io_analysis

AS

(

    SELECT  SUM(num_of_bytes_read)      AS sum_of_reads,

            SUM(num_of_bytes_written)   AS sum_of_writes

    FROM    sys.dm_io_virtual_file_stats(2, NULL)

)

SELECT  @sum_of_reads = sum_of_reads - @sum_of_reads,

        @sum_of_writes = sum_of_writes - @sum_of_writes

FROM    io_analysis;

-- Ausgabe des IO-Delta für TEMPDB

SELECT  @sum_of_reads / POWER(1024, 2)  AS diff_read_MB,

        @sum_of_writes / POWER(1024, 2) AS diff_write_MB;

Um zu analysieren, wie viele Schreib- und Lesevorgänge in einer Datenbankdatei vorgenommen werden, wird die Funktion [sys].[dm_io_virtual_file_stats] verwendet. TEMPDB besitzt die Datenbank-Id 2. In meinem Testsystem ist TempDB auf mehrere Dateien verteilt und das I/O muss daher aggregiert werden. Wird das Skript ausgeführt, wird man erkennen, dass insgesamt ca. 4 MB in TEMPDB geschrieben werden, der Wert kann auf Grund von Hintergrundaktivitäten variieren – die Differenz ist jedoch vernachlässigbar.

Fügt man am Ende des obigen Skripts noch die folgenden Anweisungen hinzu, kann man deutlich erkennen, dass auch eine Tabellenvariable – im Hintergrund – nichts anderes ist, als eine temporäre Tabelle (aber mit weniger Funktionalität):

-- SELECT Statement für Ausgabe von I/O

SET STATISTICS IO ON;

SELECT * FROM @t WHERE c2 = 'irgendwas';

SET STATISTICS IO OFF;

#BDB0B5EA-Tabelle. Scananzahl 1, logische Lesevorgänge 501, physische Lesevorgänge 0,

Der “Mythos” der Verarbeitung von Daten einer Tabellenvariablen im RAM ist definitiv FALSCH!

Herzlichen Dank fürs Lesen!