In der letzten Woche bekam ich während eines Kurzprojekts eine Anfrage auf den Schreibtisch, in dem sich der betroffene Geschäftsbereich bei den SQL Server Administratoren beschwert, dass eine Abfrage mit unterschiedlichen Parameterwerten mal sehr schnell lief und dann wieder sehr langsam die Daten verarbeitete. Zunächst dachte ich an “Parameter Sniffing” jedoch war die Ursache viel komplexer als gedacht.

CREATE TABLE dbo.OrderDemo

(

    Id    int         NOT NULL    IDENTITY (1, 1),

    c1    int         NOT NULL,

    c2    char(200)   NOT NULL,

    c3    char(200)   NOT NULL,

    c4    char(200)   NOT NULL,

    c5    date        NOT NULL

);

CREATE UNIQUE CLUSTERED INDEX uix_OrderDemo_Id ON dbo.OrderDemo (Id);

GO

SET NOCOUNT ON;

GO

DECLARE    @i int = 1;

WHILE @i <= 20000

BEGIN

    INSERT INTO dbo.OrderDemo(c1, c2, c3, c4, c5)

    VALUES    (

                CAST(RAND() * 20000 AS int),

                'RANDOM STUFF ' + CAST(@i % 1000 AS varchar(20)),

                'another stuff ' + CAST(@i % 5000 AS varchar(20)),

                'more stuff: ' + CAST(@i % 100 AS varchar(20)),

                DATEADD(dd, CAST(RAND() * 1000 AS int) * -1, getdate())

            );

    IF @i % 10000 = 0

        RAISERROR ('%i Datensätze eingetragen...', 0, 1, @i);

    SET    @i += 1;

END

GO

UPDATE STATISTICS dbo.OrderDemo WITH FULLSCAN;

Die Tabelle wird mit 20.000 Datensätzen gefüllt und abschließend werden die Statistiken zur Sicherheit noch einmal mit einem FULLSCAN (alle Daten) aktualisiert. Für die nachfolgenden ermittelten Zahlen (die natürlich für jeden Computer unterschiedlich ausfallen können) wurden auf einem LENOVO W530 mit 8 Cores (Hyperthreading) und 32 GB RAM ermittelt. Die Datenbank TEMPDB hat eine Größe von 200 MB und liegt – über 4 Datenfiles verteilt - auf einem SSD-Laufwerk. Die verwendete Version von Microsoft SQL Server - für diesen Artikel - ist “Microsoft SQL Server 2012 Enterprise Edition (64-bit)”.

Problemstellung

Für die Demonstration des Problems werden zwei Abfragen ausgeführt, die sich nur durch ihre Datenmenge unterscheiden, um zu sehen, wie viel Zeit die Ausführung der beiden Abfragen benötigt, werden die Ausführungszeiten ebenfalls ausgegeben.

-- Ausgabe von Ausführungszeiten für die nachfolgend

-- auszuführenden Abfragen

SET STATISTICS TIME ON;

GO

-- die Ergebnisse werden in Variablen geschrieben,

-- um die Ausgabe von Datenzeilen zu unterdrücken

DECLARE @Id int,

        @c1 int,

        @c2 char(200),

        @c3 char(200),

        @c4 char(200),

        @c5 date

-- Abfrage 1: schnell!

SELECT  @Id = Id,

        @c1 = c1,

        @c2 = c2,

        @c3 = c3,

        @c4 = c4,

        @c5 = c5

FROM    dbo.OrderDemo

WHERE   Id <= 9544

ORDER BY c1;

-- Abfrage 2: Langsam!

SELECT  @Id = Id,

        @c1 = c1,

        @c2 = c2,

        @c3 = c3,

        @c4 = c4,

        @c5 = c5

FROM    dbo.OrderDemo

WHERE   Id <= 9545

ORDER BY c1;

Beide Abfragen sind vollkommen identisch und unterscheiden sich ausschließlich durch ihre Werte in der WHERE-Klausel. Obwohl Abfrage 2 nur einen (!!!) Datensatz mehr liefert als Abfrage 1, unterscheiden sich die Ausführungszeiten gewaltig.

-- Ausführungszeiten für Abfrage 1

SQL Server Execution Times:

  CPU time = 16 ms,  elapsed time = 12 ms.

-- Ausführungszeiten für Abfrage 2

SQL Server Execution Times:

  CPU time = 15 ms,  elapsed time = 64 ms.

Für die Ausführung von Abfrage 1 benötigte Microsoft SQL Server 12 ms während für die zweite Abfrage fast fünf mal so viel Zeit benötigt wurde (auf dem Originalserver war der Unterschied teilweise bis zu 30 mal höher!). Die Analyse des Ausführungsplans für beide Abfragen zeigt schnell, wo das Problem liegt (hier sei darauf hingewiesen, dass ein “SORT-SPILL” erst mit Microsoft SQL Server 2012 visuell in die Ausführungspläne Einzug gehalten hat – mit älteren Versionen von Microsoft SQL Server ist das – mit SSMS – nicht möglich).

Der Ausführungsplan trügt – er zeigt, dass beide Ausführungen mit jeweils 50% der Gesamtlaufzeit zu Buche schlagen. Das entspricht, wie weiter oben bereits gezeigt, nicht der Realität. Eine Möglichkeit für die unterschiedlichen Ausführungsoperationen können falsche oder veraltete Statistiken sein; das kann jedoch ausgeschlossen werden, da die Statistiken unmittelbar nach dem Hinzufügen von Datenzeilen vollständig aktualisiert wurden. Das beweisen auch die Ausführungsstatistiken für die jeweiligen SORT-Operationen:

Die linke Abbildung zeigt die “geschätzte” Anzahl von Zeilen, die der “tatsächlichen” Datenmenge nahezu entspricht. Gleiches gilt auch für die Ausführung der Abfrage nach Werten <= 9545.

Was ist ein SORT-Spill?

Microsoft SQL Server führt – wenn möglich – alle Abfragen im Arbeitsspeicher aus. Dadurch werden teure Schreib- und Lesevorgänge im I/O-System vermieden. Vor der Ausführung einer Abfrage kalkuliert Microsoft SQL Server den benötigten Speicher, um die Abfrageoperationen im Speicher ausführen zu können. Hierbei verlässt sich Microsoft SQL Server auf die Statistiken, der dem abzufragenden Objekt zu Grunde liegen; somit einer von vielen Gründen, Statistiken immer aktuell zu halten. Stimmen die Kalkulationen von Microsoft SQL Server nicht (entweder Statistiken sind veraltet oder aber es werden mehr Daten ermittelt als “vorhergesehen”), kann der allozierte Arbeitsspeicher nachträglich nicht erweitert werden. Ein weiterer Grund kann sein, dass Microsoft SQL Server den benötigten Speicher nicht allozieren kann, weil bereits andere Abfragen den Speicher belegen (das konnte aber für das betroffene System bei 192 GB RAM ebenso ausgeschlossen werden, wie für das für diesen Artikel verwendete System mit 32 GB RAM!).

Hat Microsoft SQL Server nicht genügend Speicher für die Ausführung der Abfrage im RAM, müssen Elemente in TEMPDB ausgelagert werden (z. B. SORT / HASH MATCH / EXCHANGE). Bei den drei genannten Iteratoren (Zeiger, die eine Menge von Elementen durchlaufen) handelt es sich um eine abschließende Liste von Objekten, die zusätzlichen Speicher für die Ausführung ihrer Operationen benötigen.

Messung des Datenvolumens in TEMPDB

Steht nicht ausreichend Speicher zur Verfügung, müssen die Operationen in TEMPDB verlagert werden. Diese Schreib- und Leseoperationen können sehr teuer sein, wie der nächste Test eindrucksvoll belegt:

-- Tabellenvariable für die Speicherung von Zwischenergebnissen

DECLARE @Result TABLE

(

    Operation               varchar(20)    NOT NULL,

    num_of_reads            bigint,

    num_of_bytes_read       bigint,

    num_of_writes           bigint,

    num_of_bytes_written    bigint

);

-- die Ergebnisse werden in Variablen geschrieben,

-- um die Ausgabe von Datenzeilen zu unterdrücken

DECLARE @Id int,

        @c1 int,

        @c2 char(200),

        @c3 varchar(200),

        @c4 char(200),

        @c5 date

INSERT INTO @Result

SELECT  'Intialmessung'              AS Operation,

        SUM(num_of_reads)            AS num_of_reads,

        SUM(num_of_bytes_read)       AS num_of_bytes_read,

        SUM(num_of_writes)           AS num_of_writes,

        SUM(num_of_bytes_written)    AS num_of_bytes_written

FROM    sys.dm_io_virtual_file_stats(db_id('tempdb'), NULL)

WHERE    file_id != 2;

SELECT  @Id = Id,

        @c1 = c1,

        @c2 = c2,

        @c3 = c3,

        @c4 = c4,

        @c5 = c5

FROM    dbo.OrderDemo

WHERE   Id <= 9544

ORDER BY c1;

INSERT INTO @Result

SELECT  'Ausführung <= 9544'        AS Operation,

        SUM(num_of_reads)           AS num_of_reads,

        SUM(num_of_bytes_read)      AS num_of_bytes_read,

        SUM(num_of_writes)          AS num_of_writes,

        SUM(num_of_bytes_written)   AS num_of_bytes_written

FROM    sys.dm_io_virtual_file_stats(db_id('tempdb'), NULL)

WHERE   file_id != 2;

SELECT  @Id = Id,

        @c1 = c1,

        @c2 = c2,

        @c3 = c3,

        @c4 = c4,

        @c5 = c5

FROM    dbo.OrderDemo

WHERE   Id <= 9545

ORDER BY c1;

INSERT INTO @Result

SELECT  'Ausführung <= 9545'        AS    Operation,

        SUM(num_of_reads)           AS    num_of_reads,

        SUM(num_of_bytes_read)      AS    num_of_bytes_read,

        SUM(num_of_writes)          AS    num_of_writes,

        SUM(num_of_bytes_written)   AS    num_of_bytes_written

FROM    sys.dm_io_virtual_file_stats(db_id('tempdb'), NULL)

WHERE    file_id != 2;

SELECT * FROM @Result;

Bei Ausführung der Abfrage wird zunächst eine Tabellenvariable erstellt, in der die Zwischenergebnisse der Aktivitäten in TEMPDB gespeichert werden. In meinem System muss ich die Werte aggregieren, da vier Dateien für TEMPDB konfiguriert sind. Die WHERE-Klausel klammert Aktivitäten im Transaktionsprotokoll aus!

Die Abbildung zeigt, dass bei Ausführung der ersten Abfrage keine Aktionen in TEMPDB ausgeführt werden während bei Ausführung der zweiten Abfrage Aktivitäten zu verzeichnen sind. Insgesamt wurden 6.029.312 Bytes in TEMPDB geschrieben und die gleiche Datenmenge gelesen. 9545 Datensätze á 618 Bytes = 5.898.810, das entspricht in etwa der geschriebenen und gelesenen Datenmenge. Die zweite Abfrage hat ein Datenvolumen von ca. 6 MB in TEMPDB generiert und musste diese Datenmenge explizit aus TEMPDB lesen. Da im Testsystem die TEMPDB auf einer SSD abgelegt ist, sind die Unterschiede marginal – auf dem tatsächlichen System waren Unterschiede messbar, die bis zu 30 mal höher waren.

Zugewiesener Speicher für die Ausführung der Abfrage

Ein wesentlicher Aspekt bei der Ausführung von Abfragen ist die Bereitstellung von Speicher aus dem Buffer Pool. Das Thema ist SEHR komplex und würde den Rahmen dieses Artikels vollständig sprengen. Hier möchte ich für die Interessierten einen sehenswerten Vortrag von Adam Machanic (SQL Server MVP) empfehlen, den er auf dem PASS SUMMIT 2011 gehalten hat. Keine leichte Kost für einen Anfänger aber dennoch ein MUSS, um zu verstehen, wie Microsoft SQL Server den benötigten Workspace Memory berechnet und alloziert.

Mist - hier kann man kein YOUTUBE-Video verlinken :(
Wer Interesse hat, suche nach "Query Tuning Mastery: ZEN and the art of Workgroup Memory" von Adam Machanic!

Grundsätzlich gibt es die Möglichkeit der Überprüfung, wie viel Speicher eine Abfrage zugewiesen bekommt und wie viel Speicher vom allozierten Speicher benötigt wird. Zunächst wird überprüft, wie viel Arbeitsspeicher die erste Abfrage erhält und wie viel sie tatsächlich benötigt. Dazu wird die Abfrage in einer Endlosschleife ausgeführt, während in einem zweiten Fenster von SQL Server Management Studio die Analyse des Arbeitsspeichers durchgeführt wird.

Ausführung der Abfrage

Der folgende Code wird in einem separaten Fenster von SSMS ausgeführt:

DECLARE @Id int,

        @c1 int,

        @c2 char(200),

        @c3 varchar(200),

        @c4 char(200),

        @c5 date

WHILE 1 = 1

    SELECT  @Id = Id,

            @c1 = c1,

            @c2 = c2,

            @c3 = c3,

            @c4 = c4,

            @c5 = c5

    FROM    dbo.OrderDemo

    WHERE   Id <= 9544

    ORDER BY c1;

Während die obige Abfrage ausgeführt wird, wird in einem zweiten Fenster der folgende Code ausgeführt:

SELECT  session_id,

        requested_memory_kb,

        granted_memory_kb,

        required_memory_kb,

        used_memory_kb,

        max_used_memory_kb

FROM    sys.dm_exec_query_memory_grants

Mit dieser Abfrage wird für jede ausgeführte Abfrage die Speichernutzung ermittelt. Hierzu wird die dmv [sys].[dm_exec_query_memory_grants] abgefragt. Die Werte können je nach Status der Abfrage immer wieder leicht variieren; auffällig ist jedoch, dass [max_used_memory_kb] nie größer ist als [granted_memory_kb].

Das Ergebnis zeigt, dass für die Ausführung der zweiten Abfrage der komplette zugewiesene Arbeitsspeicher verwendet wird. Microsoft SQL Server benötigt mehr Speicher für eine Ausführung aller Operationen im Arbeitsspeicher. Die Speicherzuweisung erfolgte auf Basis von Kostenschätzungen. Ein weiterer Datensatz erforderte – gem. Berechnung – nur weitere 16 Bytes für die Ausführung.  Wichtig hierbei ist, dass der Query Optimizer eine “optimale Zuordnung” von Arbeitsspeicher ermittelt. Für den SORT-Operator scheint diese kostenoptimierte Schätzung jedoch nicht ausreichend zu sein.

Lösungen

Eindeutig fehlt es der Abfrage an Arbeitsspeicher, wenn Iteratoren für ihre Aufgaben die Daten in die TEMPDB auslagern müssen. Das Ziel muss also sein, für die Abfrage ausreichenden Arbeitsspeicher zur Verfügung zu stellen.

Generelle Anpassung des minimalen Workload-Speichers

Ein Lösungsansatz besteht darin, den serverweiten Parameter “min memory per query” anzupassen. Der Minimalwert liegt bei 512 KB; standardmäßig werden 1.024 KB zur Verfügung gestellt. Um zum Beispiel generell 16 MB für JEDE Abfrage zur Verfügung zu stellen, reicht die folgende Anweisung:

EXEC sp_configure 'min memory per query', 16384;

RECONFIGURE;

Diese Variante ist jedoch überhaupt nicht praktikabel, wenn hauptsächlich weniger speicherintensive Abfragen ausgeführt werden oder aber der Server nicht mit all zu viel Speicher ausgestattet ist. Des weiteren sollte für serverweite Anpassungen immer erst eine Bewertung des Gesamtworkloads durchgeführt werden! Diese Lösung fällt eher unter den Begriff “Holzhammer-Methode”!

Überlistung des Query Optimizers

Eine andere – von mir bevorzugte - Alternative besteht darin, dem Query Optimizer vorzugaukeln, er benötige mehr Speicher. Der Trick besteht darin, die “geschätzte Zeilengröße” so zu erweitern, dass die Kalkulation des benötigten Arbeitsspeichers für die Abfrage so viel Arbeitsspeicher anfordert, dass der SORT-Iterator seine Arbeit vollständig im Arbeitsspeicher ausführt. Um die Lösung zu verstehen, zunächst ein kleines Beispiel mit einer anderen Tabelle.

CREATE TABLE dbo.Employee

(

    Id         int         NOT NULL,

    FirstName  varchar(64) NOT NULL,

    LastName   varchar(64) NOT NULL,

    CONSTRAINT pk_Employees_Id PRIMARY KEY CLUSTERED (Id)

);

Schaut man sich die Eigenschaften der nachfolgenden Abfrage an, ist die “geschätzte Zeilengröße” interessant.

SELECT  Id, LastName, FirstName

FROM    dbo.Employees

WHERE   Id = 1

Microsoft SQL Server nimmt bei Attributen mit variabler Datenlänge nicht die maximale Länge für die Berechnung der Zeilengröße sondern 50% der maximalen Zeilengröße. Diese Berechnung gilt jedoch nur für Datentypen mit variabler Länge. Um den Query Optimizer zu überlisten, wird einfach die zu erwartende Zeilenlänge basierend auf der 50%-Regel verändert. Zunächst wird die eigentliche Abfrage wie folgt geändert:

DECLARE @Id int,

        @c1 int,

        @c2 varchar(400),

        @c3 varchar(400),

        @c4 varchar(400),

        @c5 date

SELECT  @Id = Id,

        @c1 = c1,

        @c2 = CAST(c2 AS varchar(400)),

        @c3 = CAST(c3 AS varchar(400)),

        @c4 = CAST(c4 AS varchar(400)),

        @c5 = c5

FROM    dbo.OrderDemo

WHERE   Id <= 9545

ORDER BY c1;

Die obige Abfrage ändert die feste Datenlänge CHAR(200) zunächst zu VARCHAR(400). Damit ist – basierend auf der ursprünglichen – Zeilenlänge der Zustand bis auf wenige Bytes (hängt mit der Datenstruktur für “variable length attributes” zusammen) zunächst identisch und der SORT-Operator führt seine Operation in der TEMPDB aus.

Um die “geschätzte Zeilengröße” zu vergrößern, wird die Datenlänge von einem oder mehreren Variablen mit variabler Datenlänge so lange manipuliert, bis der angeforderte Speicher so weit angewachsen ist, dass die vollständige Operation im Arbeitsspeicher ausgeführt werden kann.

DECLARE @Id int,

        @c1 int,

        @c2 varchar(475),

        @c3 varchar(475),

        @c4 varchar(475),

        @c5 date

SELECT  @Id = Id,

        @c1 = c1,

        @c2 = CAST(c2 AS varchar(475)),

        @c3 = CAST(c3 AS varchar(475)),

        @c4 = CAST(c4 AS varchar(475)),

        @c5 = c5

FROM    dbo.OrderDemo

WHERE   Id <= 9545

ORDER BY c1;

Wie die obigen Abbildungen zeigen, werden zunächst die Zeilen mit ihrer Originalgröße von 618 Bytes aus der Tabelle gelesen. Anschließend unterliegen sie einer Typenkonvertierung. Durch diese Konvertierung vergrößert sich die Zeilengröße von 618 Bytes auf 737 Bytes. Diese Zeilengröße wurde für den SORT-Iterator bereits in der Optimierungsphase berücksichtigt. Basierend auf diesen Informationen hat der Query Optimizer von Microsoft SQL Server deutlich mehr Arbeitsspeicher angefordert – und erhalten. Eine Sortierung findet im Arbeitsspeicher statt.

Herzlichen Dank fürs Lesen!