Wie messe ich eigentlich den Erfolg der Optimierung einer SQL Abfrage?

Hier ein Thema, welches mir immer wieder in Coachings von Einsteigern und Juniorberatern begegnet. Daher speziell für diese Zielgruppe ein paar Grundlagen.

Wie messe ich eigentlich, ob dank einer Maßnahme eine Query “schneller” geworden ist?

Der eine oder andere mag denken, dass das doch eine überflüssige Frage ist. Die Optimierung war erfolgreich, wenn eine Abfrage schneller fertig ist. Wo ist also das Problem? Und schließlich hat doch das Management Studio in der rechten untern Ecke extra dafür einen Timer. Und im Profiler kann ich mir doch auch die Dauer einer Abfrage anschauen. Damit sollte das Thema doch eigentlich durch sein, oder? Und bei vielen Queries steht da unten doch eh nur 00:00:00, die können also doch gar nicht weh tun.

Nun ja, ganz so einfach ist es dann doch nicht. Die Zeit, welche eine Query benötigt, ist immer relativ und kann von vielem abhängig sein. Die wesentlich entscheidendere Größe beim Tuning ist die Anzahl der für die Beantwortung notwendigen Datenbankseiten! Und dabei ist es fast egal, ob die Seiten gerade warm und trocken im Cache liegen oder gar erst vom Plattensystem gelesen werden müssen. Seite bleibt Seite. Daher sollte ein Ziel von Optimierungsmaßnahmen immer sein, dass die Anzahl der für ein Resultset notwendigen Datenbankseiten minimiert wird! Auch bei Abfragen, welche gefühlt flott laufen, kann es Sinn machen diese entsprechend zu optimieren, wenn diese auf einem Server vielleicht viele tausend Mal am Tag ausgeführt wird.

Ok, nur wie kommen wir nun an diese Anzahl der notwendigen Seiten und wie reduzieren wir diese?

Für den Einstieg in das Thema ist es nicht immer notwendig gleich den SQL Profiler mitlaufen zu lassen. Auch Querypläne müssen nicht immer gleich zerlegt werden, auch wenn das einen bereits deutlich nach vorne bringen würde.

Mit folgender Anweisung schalten wir die Darstellung einer Statistik ein, welche uns die Anzahl der Datenbankenseiten pro verwendeter Tabelle zeigen wird:

set statistics io on;

Und nun noch eine kleine Abfrage für die gute alte AdventureWorksDW2008:

select SUM(SalesAmount), COUNT(*)
from dbo.FactInternetSales fis
left join dbo.DimCustomer dc on dc.CustomerKey = fis.CustomerKey
where dc.YearlyIncome > 50000;

Und schon sehen wir im Management Studio unter Meldungen folgendes:

(1 Zeile(n) betroffen)
Worktable-Tabelle. Scananzahl 0, logische Lesevorgänge 0, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.
FactInternetSales-Tabelle. Scananzahl 1, logische Lesevorgänge 1030, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.
DimCustomer-Tabelle. Scananzahl 1, logische Lesevorgänge 978, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

Das “Ziel” habe ich mal markiert. Diese Werte gilt es im Rahmen einer Optimierung zu reduzieren. Denn je weniger Seiten benötigt werden, um so schneller wird die Abfrage und, kleiner Nebeneffekt, um so geringer sind Probleme durch Sperren. Performanceschwierigkeiten kann so viele Gründe haben, aber das würde hier zu weit gehen.

Aber wie kommen wir nun zu einer Optimierung? Es gibt zwei grundsätzliche Wege:

• durchdachtere SQL Abfragen (weites Feld, würde den Rahmen dessen hier für den Moment sprengen)
• bessere Indizierung (wird von viel zu vielen unterschätzt und von einigen tatsächlich auch überschätzt)

Am Ende geht es um weniger Seiten oder wie der Profi sagt: Weniger IO Operationen.

Wir versuchen es mal mit der Erzeugung von zwei Indizes, um die Lesevorgänge deutlich reduzieren. Für Details einfach mal einen Blick in die BooksOnline des SQL Server werfen.

CREATE NONCLUSTERED INDEX [ix_DimCustomer_YearlyIncome] ON [dbo].[DimCustomer]
(
    [CustomerKey] ASC
)
INCLUDE ( [YearlyIncome]) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ix_FactInternetSales_CustomerKey_SalesAmount] ON [dbo].[FactInternetSales]
(
    [CustomerKey] ASC
)
INCLUDE ( [SalesAmount]) ON [PRIMARY]

So, identische Query nochmal starten und schon sehen wir, dass unsere Bemühungen erste Erfolge zeigen. Ach ja, natürlich bleibt das Ergebnis der SQL Abfrage identisch!

(1 Zeile(n) betroffen)
FactInternetSales-Tabelle. Scananzahl 1, logische Lesevorgänge 257, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.
DimCustomer-Tabelle. Scananzahl 1, logische Lesevorgänge 44, physische Lesevorgänge 0, Read-Ahead-Lesevorgänge 0, logische LOB-Lesevorgänge 0, physische LOB-Lesevorgänge 0, Read-Ahead-LOB-Lesevorgänge 0.

Was passiert da? Kurzgefasst kann gesagt werden, dass der Queryoptimizer nun nur noch unsere beiden Indizes verwendet, welche genau die Spalten bereitstellen, die unsere Query benötigt. Und damit wäre normalerweise noch nicht Schluss mit der Optimierung, aber für den Moment soll das reichen, um das Konzept zu verdeutlichen wie wir den Erfolg einer Maßnahme messen können. Denn darum ging es mir hier.

Also, nochmal wiederholt: Erfolgreiche Optimierung heißt u. a. Reduzierung von Seitenzugriffen!

Und messen könnt Ihr das u. a. mittels set statistics io on.