Montag, 10. Dezember 2012

Stored Procedures und nondeterministische Variablen

Ein gemütlicher Sonntag sollte es werden, jedoch konnten wir eine geplante Autofahrt wegen Schnee nicht antreten und so habe ich die Zeit genutzt, um das Datenmodell einer von mir erstellten Datenbank auf den Prüfstand zu stellen. Bei den Tests bin ich dann auf eine – für mich – sehr interessante Entdeckung gestossen, über deren Zusammenhänge ich im Internet leider nicht sofort etwas gefunden habe. Mir ist aufgefallen, dass bei offensichtlich gleichen Abfragen unterschiedliche “estimated rows” ausgegeben werden. Warum das so ist und welche Konsequenzen das für die Ausführung einer Prozedur haben kann, soll der nachfolgende Artikel beschreiben.

Problembeschreibung

Bei der Durchsicht der Relationen ist mir für eine Relation aufgefallen, dass die Indexe nicht ideal sind. Also wurden die Indexe neu angelegt und optimiert. Anschließend sollte mittels Abfragen die Performance in Bezug auf IO getestet werden. Gleichwohl sollte auch im Ausführungsplan geprüft werden, ob die Indexe ideal genutzt werden. Als Basis für den nachfolgenden Artikel dient eine Relation mit dem Namen dbo.tbl_Stammdaten sowie zwei weitere Objekte, die für die Ausführung benötigt wurden. Das vollständige Script kann unter dem am Ende aufgeführten Link zu Testzwecken herunter geladen werden.

Die Relation [dbo].[tbl_Stammdaten] hat insgesamt 290 Tuples. Auf dem Attribut [Category] liegt ein Index. Das Histogramm der Relation für diesen Index sieht wie folgt aus:

DBCC SHOW_STATISTICS ('dbo.tbl_Stammdaten', 'ix_tbl_Stammdaten_Category') WITH HISTOGRAM;

Histogram - ix_tbl_Stammdaten_Category

Aus dem Histogram ist die Anzahl der Kategorien sowie deren Gewichtung in der Relation erkennbar. Insgesamt gibt es vier Kategorien mit jeweils unterschiedlicher Kardinalität (Selektivität). Basierend auf den obigen Ergebnissen wurden die folgenden Abfragen ausgeführt und untersucht.

DECLARE @condition nvarchar(5) = system.fn_Get_ConfigurationValue('FormShortCut');

SET STATISTICS IO ON;

-- First query with usage of parameter
SELECT  SId, SName1
FROM    data.tbl_Stammdaten
WHERE   Category = @condition
GO
-- Second query with usage of fixed value
SELECT  SId, SName1
FROM    data.tbl_Stammdaten
WHERE   Category = N'FRM'
GO

SET STATISTICS IO OFF;

Beide Abfragen liefern identische Ergebnisse und ich war etwas überrascht, als ich den Abfrageplan für die Ausführung gesehen habe.

Abfrageplan - No-1

Obwohl es – scheinbar – identische Abfragen sind, gibt es einen erheblichen Unterschied beim Laufzeitverhalten. Insgesamt ist die Abfrage mit der Variablen fast 5% langsamer als die Abfrage mit einem festen Wert. Das IO für beide Abfrage ist identisch – wo liegt also der Unterschied? Eine genauere Untersuchung der Plandetails hat dann den Unterschied offenbart.

Abfrage 1 Abfrage 2
Plan-details - nondeterministic variables Plan-details - deterministic values

Das Ergebnis hatte mich zunächst überrascht aber das Ergebnis ist logisch. Variablen in einem SQL-Batch sind nondeterministisch. Das bedeutet, dass beim Start des Batches SQL Server bei der Ausführung der Abfrage noch nicht weiß, wieviele Werte zurückgeliefert werden. Die Variable wird VOR der Ausführung der Abfrage initialisiert und ihr wird ein Wert zugewiesen. Für die Abfrage ist das aber irrelevant, da erst NACH der Ausführung der Abfrage klar bestimmbar ist, wieviele Datensätze zurückgeliefert werden [Tatsächliche Anzahl von Zeilen].

Der kuriose Wert von 72,5 [Geschätzte Anzahl von Zeilen] errechnet sich aus der Gesamtzahl der Daten / Anzahl der Schlüsselwerte. 290 Datensätze / 4 verschiedene Kategorien = 72,5 Datensätze (Licht ins Dunkel hat hier der von mir sehr geschätzte Olaf Helper gebracht – dafür noch einmal ein herzliches “Danke” an dieser Stelle.).

Da die Anwendung ausschließlich mit Prozeduren arbeiten, ist interessant, wie sich dieses Verhalten auf Prozeduren auswirkt, wenn der Suchwert an die Prozedur übergeben wird und wenn der Wert innerhalb der Prozedur in eine Variable gelesen wird.

Als Testprozeduren werden zwei Prozeduren erstellt, die identische Daten abrufen sollen. Prozedur 1 erhält das Suchkriterium als Parameter übergeben. In Prozedur 2 wird eine Variable deklariert und ihr wird der zu suchende Wert in der Prozedur zugewiesen.

dbo.proc_app_GetData_WithParms

CREATE PROC dbo.proc_app_GetData_WithParms
    @Category nvarchar(5)
AS
    SET NOCOUNT ON
;

    SELECT SId,
           SName1
    FROM   data.tbl_Stammdaten
    WHERE  Category = @Category;

    SET NOCOUNT OFF;
GO

Die Prozedur proc_app_GetData_WIthParms erhält das Suchkriterium als Parameter dediziert übergeben.
Dieser Paramter wird anschliessend für die Abfrage verwendet.
dbo.proc_app_GetData_WithoutParms

CREATE PROC dbo.proc_app_GetData_WithoutParms
AS
    SET NOCOUNT ON;

    DECLARE @Category nvarchar(5) = dbo.fn_Get_ConfigurationValue('FormShortCut');

    SELECT SId,
           SName1
    FROM  dbo
.tbl_Stammdaten
    WHERE Category = @Category;

    SET NOCOUNT OFF;
GO

In dieser Prozedur wird die Variable innerhalb des Prozedurblocks deklariert und ihr wird ein Wert zugewiesen.
Ansclhießend wird dieser initialisierte Parameter für die Abfrage verwendet

Das Ergebnis der Ausführung ist ernüchternd und – leider – wie erwartet. Folgende Abfrage wurde ausgeführt und der Abfrageplan wurde analysiert:

DECLARE @Category nvarchar(5) = dbo.fn_Get_ConfigurationValue('FormShortCut')
EXEC    dbo.proc_app_GetData_WithParms @Category = @Category;
EXEC    dbo.proc_app_GetData_WithoutParms;

Abfrageplan - No-2

Obwohl scheinbar beide Abfragen laut Ausführungsplan identisch sind, benötigt die Abfrage der Prozedur [dbo].[proc_app_GetData_WithoutParms] 4% mehr Zeit für die Ausführung als die Prozedur [dbo].[proc_app_GetData_WithParms].

Auch hier ist die Erklärung relativ schnell gefunden. Wird einer Prozedur ein Parameter übergeben, ist er zur Laufzeit der Prozedur determiniert und SQL Server kann anhand der Statistiken basierend auf dem determinierten Wert bereits die voraussichtlichen Datensätze ermitteln. Wird jedoch eine Variable erst in der Prozedur deklariert und ihr ein Wert zugewiesen, kann sie auch erst zur Laufzeit bestimmt werden. Somit muss die Abfrage in der Prozedur erst ausgeführt werden, bevor eine Anzahl von Datensätzen bestimmt werden kann. SQL Server kann also die “geschätzten Datenzeilen” nur auf Basis der Informationen des Histogramms ermitteln.

Konsequenzen

“Schätzt” SQL Server die zu erwartende Anzahl von Zeilen falsch, kann dies zu einer signifikanten Verschlechterung durch eine schlecht gewählte Abfragestrategie führen. In dem gezeigten Beispiel mit wenigen Datensätzen ist die Kardinalität der Datensätze “ausgewogen”. Stimmt jedoch das Verhältnis nicht mehr, werden die Abfragestrategien falsch gewählt und es kommt zu den angesprochenen Verschlechterungen.

Fazit

Die Verwendung von Variablen in Stored Procedures birgt Nachteile, da in diesem Fall die Variable nicht deterministisch ist. SQL Server muß also auf Basis der Statistiken für den zu verwendenden Index einen Mittelwert finden, um die vorausichtliche Menge der zu liefernden Daten zu errechnen. Ist dieser Wert deutlich höher als die tatsächlich gelieferte Datenmenge, kann es passieren, dass eine falsche Abfragestrategie verwendet wird.

Stellen Sie ihre Strategie für die Verwendung von Variablen in einer Prozedur auf den Prüfstand und schauen analysieren Sie im Vorfeld die in der Prozedur verwendete Abfrage und deren Statistiken.

Welche Strategien man für die Optimierung solcher Prozeduren verwenden kann, möchte ich im nächsten Artikel wieder an Hand von Beispielen zeigen.

Herzlichen Dank für’s Lesen

 

Links  
Beispielscript http://www.db-berater.de/Files/executionplan_demo.zip
Tuples http://de.wikipedia.org/wiki/Relation_(Datenbank)
nondeterministic variables http://msdn.microsoft.com/de-de/library/ms175933(v=sql.105).aspx

Keine Kommentare :

Kommentar veröffentlichen