Häufig fällt auf, dass Datenbankentwickler ihre Abfragen mit Hilfe von Variablen testen. Die Programmiersprachen, wie z. B. C, C++, Basic und Java, besitzen ihre eigenen Variablen zur Aufnahme von Daten und diesen Lösungsansatz möchte man gerne in der Datenbank wiederverwenden. Der gravierende Unterschied zwischen beiden Lösungsansätzen liegt darin, dass Abfragen in Microsoft SQL Server diese Variablen zur Evaluierung der geschätzten Abfragekosten verwenden. Der folgende Artikel beschreibt, warum Variablen für Tests ungeeignet sind und zeigt Alternativen, wie man trotz der Verwendung von Variablen in Testabfragen zuverlässige Auswertungen durchführen kann.

Inhaltsverzeichnis

Ausführung einer Abfrage

Bevor Microsoft SQL Server eine Abfrage ausführt, müssen verschiedene Schritte bearbeitet werden. Der Abfrageprozessor durchläuft drei Phasen, bevor ein Plan aus einer Abfrage erstellt wird. Zuerst analysiert und normalisiert der Abfrageprozessor die Statements. Dann kompiliert er den Transact SQL (T-SQL) Code. Schließlich optimiert er die SQL-Anweisung.

Parsing

Beim Parsing prüft der Abfrageprozessor die korrekte Syntax einschließlich der korrekten Schreibweise und der Verwendung von Schlüsselwörtern. Die Hauptfunktion besteht darin, zu prüfen, ob Tabellen und Spalten vorhanden sind, Metadaten über die Tabellen und Spalten geladen und Informationen über erforderliche (implizite) Konvertierungen zum Sequenzbaum hinzugefügt werden können.

Kompilierung

In der zweiten Phase sucht der Abfrageprozessor nach Anweisungen und führt die Anweisungen über einen T-SQL-Sprachcompiler aus. Diese Anweisungen (z. B. Variablendeklarationen und Zuweisungen, bedingte Verarbeitung mit IF und Iteration mit WHILE) sind nicht Teil der grundlegenden DML-Syntax (SELECT, INSERT, UPDATE und DELETE). Sie sind zusätzlichen Features, um die Funktionalität des SQL-Codes zu erweitern. Diese Anweisungen brauchen keine Optimierung, aber sie müssen vor der Ausführung kompiliert werden.

Optimierung

Die komplexeste Phase der Abfrageverarbeitung ist die Optimierung. Der Abfrageoptimierer bestimmt den idealen Ausführungsplan für jede SQL-Anweisung in einem Abfrage-Batch oder einer gespeicherten Prozedur. Die Optimierungsphase ist davon geprägt, in kürzester Zeit einen “besten Plan” zu ermitteln Mit den vielen Möglichkeiten – darunter drei Arten von Joins und eine beliebige Anzahl von Indizes auf jeder Tabelle – könnte die Auswertung aller möglichen Ausführungspläne länger dauern als die Ausführung der Abfrage.
Der Kompilierprozess ist der wesentliche Bestandteil VOR der Optimierung. Eine Kompilierung findet immer auf Ebene eines Statements statt, nicht für den gesamten Batch!

Elemente von Statistiken

Der Optimierungsprozess benötigt fundamentale Informationen über die zu erwartende Ergebnismenge. Nur mit einer guten Schätzung er zu erwartenden Datensätze lässt sich ein – aus Sicht von Microsoft SQL Server – optimaler Plan gestalten. Damit Microsoft SQL Server Informationen über die Anzahl und die Verteilung der Datenwerte erhält, werden Statistiken erstellt und gepflegt. Für die Ermittlung von zu erwartenden Datenmengen stehen Microsoft SQL Server zwei Bereiche in den Statistiken zur Verfügung.

Density Vector

Der Abfrageoptimierer verwendet den Density Vector, um Kardinalitätsschätzungen für Abfragen zu erweitern. Der Density Vector enthält einen Verdichtungskoeffizienten für jedes Präfix von Spalten im Statistikobjekt. Der Wert für eine Verdichtung berechnet sich aus 1 / Anzahl verschiedener Werte.

Histogramm

Das Histogramm berechnet die Häufigkeit des Vorkommens eines unterschiedlichen Wertes in einer Tabelle / Index. Der Abfrageoptimierer berechnet ein Histogramm für die Spaltenwerte in der ersten Schlüsselspalte des Statistikobjekts und wählt die Spaltenwerte aus, indem statistische Zeilenstichproben entnommen werden oder indem ein vollständiger Scan aller Zeilen in der Tabelle oder Sicht ausgeführt wird. Das Histogramm kann nur verwendet werden, wenn dem Abfrageprozessor zum Zeitpunkt der Kompilierung der zu suchende Wert bekannt ist!

Testumgebung

Für die nachfolgenden Tests ist eine Tabelle mit ca. 100.000 Datensätzen erstellt worden.

CREATE TABLE CustomerData
(
    Id     INT           NOT NULL    IDENTITY (1, 1),
    [Name] VARCHAR(100)  NOT NULL,
    CCode  CHAR(3)       NOT NULL,
    Street VARCHAR(100)  NOT NULL,
    ZIP    CHAR(5)       NOT NULL,
    City   VARCHAR(100)  NOT NULL
);
GO

CREATE UNIQUE CLUSTERED INDEX cuix_CustomerData_Id ON dbo.CustomerData (Id);
CREATE NONCLUSTERED INDEX nix_CustomerData_CCode ON dbo.CustomerData (CCode);
GO

Während der gruppierte Index hoch selektiv ist, ist der Index auf dem Ländercode eher nicht selektiv. Ein Blick in das Histogramm des Index zeigt die Verteilung der unterschiedlichen Ländercodes in der Tabelle.

DBCC SHOW_STATISTICS (N’dbo.CustomerData’, N’nix_CustomerData_CCode’);

Aus dem Histogramm ist ersichtlich, dass die Datenverteilung sehr verzerrt ist. Gibt es nur ca. 200 Kunden aus Australien so sind es für Deutschland über 90.000 Kundenbeziehungen.

Testabfrage

Schaut man den Entwicklern über die Schulter, findet man relativ häufig Statements der folgenden Art:

DECLARE @CCode CHAR(3) = 'AU';

SELECT * FROM dbo.CustomerData
WHERE  CCode = @CCode
ORDER BY
       Name;
GO

Die Intention es Programmierers ist bereits in der ersten Zeile offensichtlich – durch einfaches Austauschen des Wertes für die Variable möchte er schnell erkennen, wie performant sich die Abfrage bei unterschiedlichen Abfragewerten verhält. Der Ausführungsplan für die obige Abfrage stellt sich wie folgt dar:

Auf den ersten Blick scheint der Ausführungsplan für die Abfrage ideal. Bedingt durch das “ORDER BY” muss ein SORT-Operator verwendet werden, der seine Daten aus dem gruppierten Index bezieht. Fraglich ist jedoch, warum Microsoft SQL Server für die Evaluierung der Daten einen SCAN über die vollständige Tabelle durchführen muss, obwohl lediglich 217 Datensätze zu erwarten sind. Das Ausführungsprofil zeigt den Grund für dieses Verhalten.

Obwohl das Histogramm 217 Datensätze ausweist, geht der Abfrageoptimierer von einer deutlich höheren Anzahl von Datensätzen aus; seine Schätzung betreffen 33% der gesamten Datenmenge. Zum Vergleich dazu wird die Abfrage mit einem explizit definierten Literal ausgeführt:

SELECT * FROM dbo.CustomerData
WHERE  CCode = 'AU'
ORDER BY
       Name;

Auch das Ausführungsprofil zeigt deutliche Unterschiede zur vorherigen Version!

Vergleicht man die geschätzten Zeilen, ist auffällig, dass bei der Ausführung der Abfrage mit einem in der Abfrage definierten Literal die Schätzungen deutlich akkurater sind, als im vorherigen Beispiel. Doch woher kommen diese immensen Unterschiede. Die Erklärung liegt in der Vorgehensweise der Kompilierung einer Abfrage.
Im ersten Abfragebatch wird zunächst eine Variable deklariert und gekennzeichnet. Diese Anweisung wird von Microsoft SQL Server kompiliert und anschließend gleich wieder vergessen! Wird der zweite Teil des Batches ausgeführt, kennt Microsoft SQL Server den Wert der Variablen (für die Kompilierung des Statements) nicht mehr. Damit kann Microsoft SQL Server nicht nach einem geeigneten Wert im Histogramm des Statistikobjekts schauen und kann sich nur am Density Vector der Statistik orientieren.

Der abgebildete Workflow verdeutlicht das Problem bei der Verwendung von Variablen in einem Befehlsbatch. Die zweite Befehlszeile verwendet “eine” Variable @CCode, die zuvor initialisiert wurde. In der Kompilierphase betrachtet Microsoft SQL Server jedoch das Statement isoliert und somit weiß Microsoft SQL Server während des Kompilierens nicht, welchen Wert die Variable zur Laufzeit besetzt. Da kein Wert für die Variable vorhanden ist, kann Microsoft SQL Server nicht auf das Histogramm zugreifen – nach welchem Wert soll Microsoft SQL Server dann suchen? Vielmehr muss als Alternative der Density Vector verwendet werden. Die Abfrage verwendet ausschließlich das Attribut [CCode] als Prädikat. Für dieses Prädikat wird eine Verteilungsdichte von 1/3 = 0,33333 berechnet. Diese Dichte wird mit der Anzahl der vorhandenen Datensätze (ca. 10.0000) multipliziert und das Resultat ergibt die geschätzten Datensätze für die Ausgabe.

Problematischer bei der vorliegenden Beispiel-Abfrage ist jedoch der SORT-Operator. Bei einem SORT-Operator handelt es sich um einen sogenannten “STOP-Operator”. Ein STOP-Operator leitet die Daten nach Erhalt nicht automatisch weiter, sondern muss sie vor der Weiterleitung zwischenspeichern, um mit ihnen zu arbeiten. Der SORT-Operator speichert die Daten zunächst zwischen, um sie anschließend zu sortieren und dann an den vorherigen Operator weiterzuleiten. Dazu benötigen solche STOP-Operatoren aber eine Speicherreservierung. Die Höhe der Reservierung von Speicher hängt von den geschätzten Datenzeilen ab. Für das obige Beispiel wurden ca. 15 MB reserviert.

15 MB mögen auf den ersten Blick nicht als besonders viel erscheinen – aber was passiert, wenn 1.000 Anforderungen an den SQL Server gerichtet werden, die jeweils 15 MB benötigen. Unabhängig von der Anzahl der Anforderungen sollte Microsoft SQL Server schonend mit den Ressourcen umgehen und nur so viel Ressourcen binden, wie auch tatsächlich genutzt werden. Ein Blick auf die Abfrage mit einem Literal zeigt den deutlichen Unterschied.

Der Ausführungsplan für die Abfrage mit Literal mag komplexer aussehen; ist sie aber nicht. Die Abfrage ist höchst effizient, da sie – anders als bei Verwendung einer Variable – während der Kompilierphase den Wert für das Prädikat kennt und gezielt im Histogramm nach dem Wert suchen kann.

Da Microsoft SQL Server nun weiß, wie viele Datensätze an den Client zurückgeliefert werden müssen, kann von einer präziseren Planung bei der Erstellung des Ausführungsplans ausgegangen werden. Das spiegelt sich – neben einer optimaleren Strategie – auch bei der Berechnung der zu verwendenden Ressourcen wider.

Lösung trotz Variablen?

Die meisten Programmierer möchten nicht gerne auf Variablen verzichten. Das Beispiel für diesen Artikel ist sehr übersichtlich; aber was ist mit einem SQL-Statement, dass über 50, 60 oder mehr Zeilen geht. Da möchte man nur ungern im Code nach den Variablen suchen und sie durch Literale ersetzen. Was also tun? Das Geheimnis liegt in der Ausführung des Statements mit Hilfe von sp_executesql! Mit Hilfe von sp_executesql ist es möglich, das Statement zu kapseln und MIT Variablen an den Parser von Microsoft SQL Server zu übergeben. Das Statement wird ähnlich behandelt wie eine Stored Procedure, der ein Parameterwert übergeben wird.

DECLARE @CCode CHAR(3) = 'AU';
DECLARE @stmt NVARCHAR(1000) = N'SELECT * FROM dbo.CustomerData
WHERE  CCode = @CCode
ORDER BY
       Name;';

DECLARE @vars NVARCHAR(64) = N'@CCode CHAR(3)';

EXEC sp_executesql @stmt, @vars, @CCode;
GO

Das obige Code-Beispiel generiert ein abschließendes SQL-Statement mit einer Variablen, die erst während des Kompilierens von sp_executesql übergeben wird. Dadurch kann Microsoft SQL Server den Wert “sniffen” und in der Kompilierphase verwenden. Der Wert ist bekannt und Microsoft SQL Server kann auf das Histogramm zugreifen. Aber VORSICHT ist hier angeraten – mit sp_executesql schaffen wir uns andere Probleme, die unter Umständen gravierender sein können: Parameter Sniffing. Über dieses Problem habe ich in meinem Artikel “Parameter Sniffing – Lösungsansätze” sehr ausführlich geschrieben.
Für das Testen eines SQL-Statements mit Hilfe von Variablen wäre es empfehlenswert, die Abfrage mit Hilfe von RECOMPILE jedes Mal neu kompilieren zu lassen. Dann wird auch der Parameter korrekt gelesen.

DECLARE @CCode CHAR(3) = 'AU';

SELECT * FROM dbo.CustomerData
WHERE  CCode = @CCode
ORDER BY
       Name
OPTION (RECOMPILE);
GO

Mehr Informationen?

Statistiken sind ein wichtiger Faktor bei der Optimierung von Abfragen. Sie zu verstehen, Ergebnisse zu analysieren und zu bewerten sind ein wesentlicher Bestandteil für das Verständnis der Operationen von Microsoft SQL Server. Das Thema Statistiken mit Fallbeispielen aus der täglichen Praxis ist zu einer meiner Lieblingssessions geworden, die ich überall in Europa vortrage. Unter anderem bin ich mit diesem Vortrag am 16.02.2017 um 10:45 auf der SQLKonferenz anzutreffen. Wer also Lust, Zeit und Interesse hat, darf sich sehr gerne hier für die größte 3-tägige deutsche SQL Server Konferenz in Darmstadt anmelden.

Herzlichen Dank fürs Lesen!

Abfragen nicht mit Variablen testen