SQL Server Query Tuning mit Statistics Time und Statistics IO

Es gibt sehr viele Mittel und Wege die Leistung von SQL Server T-SQL Code zu optimieren. Doch wie kann man sich sicher sein, dass gewisse Änderungen den Code tatsächlich effizienter und schneller machen und nicht etwa das Gegenteil bewirken, den Code langsamer machen oder gar beschädigen?

Natürlich kann die Ausführungszeit einer Abfrage überprüft werden, doch reicht dies wirklich aus, um festzustellen ob eine Verbesserung vorliegt? Ist eine Abfrage beispielsweise nach 1-2 Sekunden beendet, so reicht die Genauigkeit der Zeitangabe für eine tatsächliche Verifizierung einer Verbesserung nicht aus. Dinge wie Caching und Parallelität können die Statistik der Ausführungszeiten von Abfragen durchaus irreführen.

In diesem Beitrag wollen wir erläutern, wie die Ausführungszeit einer Abfrage mit weitaus größerer Genauigkeit untersucht und die Verwendung von Systemressourcen besser nachvollzogen werden kann, sodass sich eine wesentlich verlässlichere Aussage über eine tatsächliche Optimierung treffen lässt.

Wir verwenden in diesem Beispiel die WideWorldImporters Datenbank und einen SQL Server 2019.

Messen der Ausführungszeit von SQL Server Abfragen mit Time Precision
Betrachten wir einmal folgende Abfrage:

Führen wir diese Abfrage aus, meldet uns SSMS eine Ausführungszeit von 00:00:00. Prinzipiell ist bei einer so geringen Ausführungszeit eine Optimierung nicht sinnvoll. Anders ist es aber, wenn diese Abfrage 1000 mal pro Sekunde ausgeführt werden soll. In diesem Fall währe eine Optimierung natürlich hilfreich, doch bei einer Ausführungszeit von 0 Sekunden kann eine Leistungsverbesserung durch bloßes Überprüfen dieser nicht wirklich festgestellt werden.

Hierfür bietet SQL Server eine Methode zum Abrufen von Ausführungszeiten auf Millisekundenebene, die 1000 mal genauer ist, als der Timer am rechten unteren Bildschirmrand. Diese Methode heißt STATISTICS TIME und kann auf zwei Arten aktiviert werden.

Ein Weg STATISTICS TIME zu aktivieren, ist über die Abfrageoptionen (engl. Query Options). Hierfür auf den Einstellungsreiter Abfrage (engl. Query) im linken oberen Bereich von SSMS klicken und anschließend Abfrageoptionen wählen. Unter dem Reiter Ausführung (engl. Execution) Advanced auswählen. Hier kann nun ein Haken bei SET STATISTICS TIME gesetzt werden.

Weiterhin kann die Methode auch mit folgendem T-SQL Code aktiviert werden:

Nachdem wir nun die Funktion aktiviert haben, können wir die Abfrage erneut durchführen und erhalten hierbei zusätzliche Angaben. Die Ausgabe von STATISTICS TIME ist auf der Registerkarte Nachrichten (engl. Messages) in SSMS zu finden.

Jetzt können wir sehen, dass die Abfrage 73 Millisekunden gebraucht hat. Mithilfe dieser Angaben könnten wir nun damit beginnen unsere Abfrage zu optimieren und eine potentielle Verbesserung verifizieren.

Messen der Ressourcennutzung von SQL Server bei Abfragen
Neben der Methode STATISTICS TIME bietet SQL Server auch eine Methode, um die verwendeten Ressourcen beim Ausführen einer Abfrage zu messen. Diese Methode heißt STATISTICS IO und lässt sich sehr ähnlich wie STATISTICS TIME aktivieren.

So kann zum einen folgender T-SQL Code ausgeführt werden:

oder eine Aktivierung über die Query Options erfolgen. Hierbei befindet sich das Kästchen für die Aktivierung von STATISTICS IO genau unter dem für STATISTICS TIME (siehe Screenshot). Aktivieren wir nun auch diese Methode und führen die Abfrage erneut aus, erhalten wir noch weitere Ausgaben auf der Nachrichten Registerkarte.

In dieser Ausgabe wird für jede Tabelle, auf die in der Abfrage verwiesen wird, eine neue Zeile angezeigt. Da es sich in unserem Fall nur um eine Tabelle handelt, wird auch nur eine neue Zeile aufgelistet. Diese enthält sehr viele nützliche Informationen – mehr als auf dem Screenshot zu sehen sind. Der Schwerpunkt liegt hier allerdings auf dem Abschnitt Logische Lesevorgänge (engl. logical reads). Diese Statistik zeigt die Anzahl der 8-KB-Datenseiten, die insgesamt auf dieser Tabelle gelesen wurden. Der Abschnitt physical reads zeigt, wie viele dieser Lesevorgänge direkt auf der Festplatte ausgeführt wurden. Das hier eine Null gemeldet wird, bedeutet, dass alle Seiten im Pufferpool gefunden und aus dem Speicher gelesen wurden.

Da wir beide Methoden, also STATISTICS TIME und STATISTICS IO aktiviert haben, wird auch weiterhin die Ausführungszeit zurückgegeben. Die Ausgabe der Ausführungszeit enthält auch die von der Abfrage verwendete CPU-Zeit, die auch als eine hervorragende Metrik bei der Optimierung der Abfrage verwendet werden kann.

In Kombination bietet die CPU-Zeit, die Anzahl der gelesenen Datenseiten (von der Festplatte oder dem Speicher) und die auf Millisekunden gemessene Ausführungszeit, eine hervorragende Grundlage für den Vergleich zukünftiger Ausführungen zur Leistungsverbesserung.

Vergleich mehrerer SQL Server Abfrage-Ausführungen
SSMS bietet ein Tool, mit dessen Hilfe wir mehrere Abfrageausführungen vergleichen und deren Leistung auf einfache Weise vergleichen können. Diese Funktion wird als Client-Statistik bezeichnet. Um diese Funktion zu nutzen, gibt es drei Möglichkeiten:

• durch einen Button
• eine Tastenkombination
• oder ein Kontextmenü

Die Tastenkombination zum Aktivieren der Funktion lautet Shift+Alt+S. Auf folgendem Screenshot ist der Button abgebildet:

Führen wir nun erneut die Abfrage aus, erscheint für die Ausgabe noch eine dritte Registerkarte mit dem Namen Client Statistics. Aus Demo-Zwecken haben wir die Abfrage nach dem Aktivieren der Client Statistics zusätzliche 3 Male ausgeführt:

Diese Ausgabe zeigt nun die Ausführungszeit zusammen mit einigen nützlichen Statistiken für jede der 3 frischen Ausführungen, sodass diese einfach miteinander verglichen werden können.

SQL Server Abfragen optimieren
Um nun die Abfrage etwas schneller zu machen, wollen wir einen Index für die Spalte Sales.invoices erstellen. Dies tun wir mit folgendem T-SQL Code:

Nachdem wir den Index erstellt haben (in einer separaten Sitzung, um die Ausgabe nicht zu verfälschen) führen wir ein letztes Mal unsere Abfrage aus. Wir lassen STATISTICS TIME, STATISTICS IO und Client Statistics aktiviert, sodass wir das Verhalten vor- und nach der Indexerstellung genau untersuchen können. Um das Muster besser darzustellen führen wir die Abfrage nach dem Erstellen des Index weitere 3 Male aus.

Wir können sehen, dass die Ausführungszeit von 72 und 67 Millisekunden auf 25 Millisekunden und die Anzahl der logischen Lesevorgänge von 11400 auf 56 gefallen ist. Auch bei der CPU Zeit ist ein Abfall von 31 auf unter 1 Millisekunde zu beobachten. Dies zeigt, dass das Erstellen eines Index einen deutlichen Einfluss auf die Leistung der Abfrage genommen hat. Schauen wir uns nun die Ausgabe der Client Statistics an: