Im Forum eines von mir sehr geschätzten MVP-Kollegen wurde eine Frage bezüglich der Verwendung von Variablen anstelle von Literalen gestellt (hier). Das Problem war, dass die Abfrage sich deutlich verlangsamte, wenn Variablen statt Literale verwendet wurden. Warum dieses Verhalten für Microsoft SQL Server jedoch korrekt ist, soll der folgende Artikel zeigen.

Ausgangsfrage

“Ich grübele über einem Select, der wesentlich langsamer wird, wenn Variablen als Parameter verwendet werden. Setze ich feste Werte als Parameter, ist der Select ca. 3mal schneller. Die Problematik bewegt sich leider nicht im 10tel-Sekunden-Bereich...”

Testszenario

Für den Test wird aus der Tabelle [person].[Person] der AdventureWorks2012Datenbank  ein paar Attribute des Datenbestandes in eine neue Tabelle [dbo].[Person] kopiert und neu indiziert.

SELECT  BusinessEntityID        AS    Id,

        PersonType,

        FirstName,

        LastName

INTO    dbo.Person

FROM    person.person;

GO

-- Eintragen eines dedizierten neuen PersonType

INSERT INTO dbo.Person (Id, PersonType, FirstName, LastName)

VALUES (30000, 'UR', 'Uwe', 'Ricken');

GO

-- Clustered Index auf Id

CREATE UNIQUE CLUSTERED INDEX ix_Person_Id ON dbo.Person (Id);

GO

-- Index auf PersonType

CREATE INDEX ix_Person_PersonType ON dbo.Person (PersonType);

GO

Um das Problem noch etwas zu verschärfen, wurde ein neuer Datensatz hinzugefügt, dessen [PersonType] ein Alleinstellungsmerkmal besitzt; dabei handelt es sich um meiner Einer…

Nachdem die Tabelle mit allen Indexen erstellt wurde, werden zwei Abfragen mit der gleichen Ergebnismenge ausgeführt:

-- Anzeige von IO aktivieren

SET STATISTICS IO ON;

GO

-- Demoabfrage 1

SELECT * FROM dbo.Person WHERE PersonType = N'UR';

GO

-- Demoabfrage 2

DECLARE @PersonType NCHAR(2) = N'UR'

SELECT * FROM dbo.Person WHERE PersonType = @PersonType;

GO

-- Anzeige von IO deaktivieren

SET STATISTICS IO OFF;

GO

Obwohl beide Abfragen – scheinbar – identisch sind, zeigt ein erster Blick auf das IO, dass Abfrage 2 deutlich mehr IO produzieren muss als Abfrage 1; beide Abfragen verwenden unterschiedliche Ausführungspläne:

-- Abfrage 1

Person-Tabelle. Scananzahl 1, logische Lesevorgänge 4, physische Lesevorgänge 0...

-- Abfrage 2:

Person-Tabelle. Scananzahl 1, logische Lesevorgänge 116, physische Lesevorgänge 0...

Wird für die erste Abfrage von einem Kostenverhältnis von 6% bei der Ausführung ausgegangen, so fallen die restlichen 94% der Ausführungskosten auf die 2. Abfrage (das sind vom Query Optimizer geschätzte Kosten!). Abfrage 1 verwendet den Index [ix_Person_PersonType] für einen INDEX SEEK während Abfrage 1 einen – deutlich teureren – INDEX SCAN unter Verwendung des Clustered Index verwendet. Das Geheimnis dieses Verhaltens liegt – wie meistens – in den Statistiken. Bevor die Statistiken näher untersucht werden, solle ein Blick auf die Eigenschaften der Ausführungsoperatoren geworfen werden.

Obwohl die zweite Abfrage auch nur einen Datensatz liefert, geht Microsoft SQL Server von vollständig anderen Voraussetzungen bei der Ausführung der Abfrage aus. In der nebenstehenden Abbildung ist zu erkennen, dass Microsoft SQL Server nicht – wie zuvor – von einem Datensatz ausgeht sondern von 2.853,29 Datensätzen. Diese Schätzung liegt im Verhältnis zum Ergebnis vollkommen daneben und aus diesem Grund entscheidet sich der Query Optimizer von Microsoft SQL Server für einen Index Scan. Die Frage, die sich in diesem Zusammenhang stellt, ist natürlich, WARUM weichen die geschätzten Zahlen so weit voneinander ab? Im direkten Zusammenhang steht auf jeden Fall die Verwendung von Parametern statt Literale. Wird ein Literal verwendet, so kann der Query Optimizer diesen Umstand sofort in die Berechnung eines möglichen Ausführungsplans einbeziehen. Bei der Verwendung einer Variablen kann der Query Optimizer von Microsoft SQL Server den möglichen Wert dieser Variablen nicht einbeziehen – Microsoft SQL Server muss also “schätzen”, wie viele Datensätze durch die Abfrage unter Verwendung einer Variablen zurückgeliefert werden. Wie genau Microsoft SQL Server intern dabei vorgeht, zeigt die nachfolgende Erläuterung

Die erste Abbildung zeigt die Eigenschaften des INDEX SEEK Operators der ersten Abfrage. Man kann erkennen, dass der Operator den Index [ix_Person_PersonType] verwendet. Um den [SEEK]-Vorgang durchführen zu können, wird das Literal N’UR von Microsoft SQL Server parametrisiert (wegen Wiederverwendbarkeit des Ausführungsplans) und der Indexschlüssel [PersonType] durchsucht. Der wichtigste Punkt für die Ursache des genannten Problems ist die Information über die [geschätzte Anzahl von Zeilen] und die [tatsächliche Anzahl von Zeilen]. Microsoft SQL Server überprüft vor Beginn der Ausführung der Abfrage zunächst die Statistiken und erhält als Information, dass der Wert “UR” im Index nur 1 Mal vorkommt. Basierend auf diesen Informationen entscheidet sich Microsoft SQL Server für einen Index Seek, da das zu erwartende IO deutlich geringer ist als bei einem Index Scan! Ausgehend von einem zu erwartenden Datensatz wird die Abfrage ausgeführt und tatsächlich wird auch nur ein Datensatz an den Client geliefert. Das Verhältnis zwischen geschätzten Datensätzen und tatsächlichen Datensätzen ist in Ordnung.

Vollkommen anders jedoch sieht es bei der zweiten Abfragen aus. Obwohl die zweite Abfrage auch nur einen Datensatz liefert, geht Microsoft SQL Server von vollständig anderen Voraussetzungen bei der Ausführung der Abfrage aus. In der nebenstehenden Abbildung ist zu erkennen, dass Microsoft SQL Server nicht – wie zuvor – von einem Datensatz ausgeht sondern von 2.853,29 Datensätzen. Diese Schätzung liegt im Verhältnis zum Ergebnis vollkommen daneben und aus diesem Grund entscheidet sich der Query Optimizer von Microsoft SQL Server für einen Index Scan. Die Frage, die sich in diesem Zusammenhang stellt, ist natürlich, WARUM weichen die geschätzten Zahlen so weit voneinander ab? Im direkten Zusammenhang steht auf jeden Fall die Verwendung von Parametern statt Literale. Wird ein Literal verwendet, so kann der Query Optimizer diesen Umstand sofort in die Berechnung eines möglichen Ausführungsplans einbeziehen. Bei der Verwendung einer Variablen kann der Query Optimizer von Microsoft SQL Server den möglichen Wert dieser Variablen nicht einbeziehen – Microsoft SQL Server muss also “schätzen”, wie viele Datensätze durch die Abfrage unter Verwendung einer Variablen zurückgeliefert werden. Wie genau Microsoft SQL Server intern dabei vorgeht, zeigt die nachfolgende Erläuterung

Statistiken – der Schlüssel für optimale Abfragen

Wie bereits oben erwähnt, benötigt der Query Optimizer von Microsoft SQL Server Statistiken, um einen effizienten Ausführungsplan zu erstellen. Wie wichtig aktuelle Statistiken für Microsoft SQL Server sind, habe ich bereits im Artikel “Bedeutung von aktuellen Statistiken für Indexe” beschrieben. Für das vorliegende Problem sind die Statistiken des Index [ix_Person_PersonType] interessant und werden mit dem folgenden Befehl ausgegeben:

DBCC SHOW_STATISTICS('dbo.Person', 'ix_Person_PersonType');

Die Abbildung zeigt alle Informationen zur Statistik des Index [ix_Person_PersonType]. Insgesamt werden durch die Ausführung des Befehls drei Informationsbereiche ausgegeben:

• Statistik-Information (Header):
• Density Vektor
• Histogramm

Verwendung von Literalen

Wenn eine Abfrage mit einem Literal ausgeführt wird, verwendet der Query Optimizer von Microsoft SQL Server das Histogramm. Bezugnehmend auf die erste Abfrage sucht der Query Optimizer im Histogramm in [RANGE_HI_KEY] – immer sortiert - nach dem Literal “UR” und – sofern dieser Eintrag vorhanden ist – kann im Attribut [EQ_ROWS] die Anzahl der zu erwartenden Datensätze ausgelesen werden. Ist der Wert “UR” nicht zu finden, verwendet Microsoft SQL Server [AVG_RANGE_ROWS] als Wert. Aus der obigen Abbildung geht hervor, dass für den Literal “UR” 1 Datensatz zu erwarten ist – somit wendet der Query Optimizer einen INDEX-SEEK als Ausführungsoperator an.

Verwendung von Variablen

Das Verhalten des Query Optimizer ändert sich jedoch, wenn Variablen ins Spiel kommen. Da Variablen in der Kompilierungsphase nicht vom Query Optimizer verwendet werden können, muss die Berechnung anders erfolgen – durch Verwendung des DENSITY Vektors. Der DENSITY Vektor gibt die Dichte eines Wertes im Verhältnis zur Gesamtmenge aller eindeutigen Datensätze an. Die Formel für die Ermittlung dieses Wertes lautet 1 / Anzahl eindeutiger Werte. Insgesamt gibt es 7 unterschiedliche Werte im Schlüsselattribut des Index bei insgesamt 19.973 Datensätzen. 1 / 7 = 0,1428571 ! Diese “Dichte” verwendet der Query Optimizer, wenn eine – zur Laufzeit der Abfrage – unbekannte Variable verwendet wird. 19.973 * 0,1428571 = 2.853,2848583Microsoft SQL Server geht – basierend auf der Dichte – davon aus (geschätzt), dass 2.853 Datensätze durch die Abfrage geliefert werden. Diese hohe Anzahl von Datensätzen würde – auf Grund des Key Lookup – dazu führen, dass ein deutlich höheres IO produziert würde; ein INDEX SCAN produziert ein deutlich geringeres IO!

Frage – Antwort – Fehler

Bei der Beantwortung der gestellten Frage wurden – leider – falsche Antworten gegeben, die den Sachverhalt nicht wirklich berücksichtigt haben. Warum die Antworten verkehrt sind, möchte ich kurz erläutern: “... weil es auf jeden Fall noch langsamer wäre, weil nicht auf einen gespeicherten Ausführungsplan zurückgegriffen werden kann.” Nein, das ist nicht richtig. Abfragepläne werden – generell - nur dann nicht gespeichert, wenn die Option RECOMPILE für die Abfrage verwendet wird. Ansonsten wird – bis auf ganz wenige Ausnahmen – IIMMER ein Ausführungsplan gespeichert; sonst wäre obige Auswertung nicht möglich gewesen. “Ansonsten sollten weder der Datentyp noch der Umstand, dass Parameter verwendet werden, einen spürbaren Unterschied bei der Ausführung machen.” Nein, das ist auf jeden Fall falsch wie das oben beschriebene Verfahren belegt. “Wir haben über Nacht die Ausführungspläne aktualisieren lassen, das hat am Ende das Problem gelöst.“ Ganz bestimmt nicht! Oder anders ausgedrückt: Nicht das oben beschriebene Problem war die Ursache für die langsame Ausführung sondern “Parameter Sniffing”. Mehr Informationen zu Parameter Sniffing habe ich in diesem Artikel beschrieben “Tücken bei der Verwendung von sp_executeSQL”. Würde tatsächlich ein extremes Ungleichgewicht in den Indexschlüsseln vorhanden sein und die Tabelle sehr groß sein, kann das von mir beschriebene Verhalten sicherlich auf die Problembeschreibung angewendet werden.

Herzlichen Dank fürs Lesen!