Was tut STATISTICS IO?
“Duuuhuuu, Ninja, ich hab da so ne Abfrage, die ist total langsam, gestern war das noch nicht so, ganz sicher”. Mit Anfragen wie dieser wird jeder Ninja früher oder später überrascht und das beweist, dass auch die anderen User gewisse Ninja-Skills haben, denn auf diese Situation vorbereitet ist man mit Sicherheit nie. Was also tun? Natürlich mal schauen, was die Abfrage so macht. Zuerst im Execution Plan nach Opfern suchen, doch wieviele Daten werden überhaupt gelesen? Das Katana, das diese Frage zerlegt, ist STATISTICS IO.
Wie könnt ihr mit STATISTICS IO arbeiten?
Wenn ihr sehen wollt, wie viele Zugriffe auf die Festplatte eine Abfrage verursacht, dann könnt ihr diese Information erlangen, indem ihr vor der Abfrage ein SET STATISTICS IO ON abschickt. Was ihr dann seht ist eine Reihe von Informationen, die wir im Folgenden ein wenig untersuchen möchten. Fragen wir dafür nun mit den Statistiken die StockItems-Tabelle in der Wide World Importers Datenbank ab:
SET STATISTICS IO ON
SELECT
[StockItemID]
,[StockItemName]
,[UnitPrice]
FROM [Warehouse].[StockItems]
Neben den Resultaten erhalten wir im Messages-Reiter noch Informationen über die Ausführung:
Table 'StockItems'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Zerlegen wir nun diese Informationen, so sehen wir, dass die Tabelle einmal gescannt wurde, was angesichts der Tatsache, dass wir die gesamte Tabelle abfragen wenig überraschend ist. Darüber hinaus erfahren wir, dass 16 logische Readsstattgefunden haben. Damit ist gemeint, dass es 16 Datenseiten in der Abfrage gab, die der Server aus dem Cache gelesen hat. Die Physical Reads sind Datenseiten, die für die Abfrage von der Festplatte gelesen wurden, also nicht im Cache vorhanden waren. Schränken wir nun die Ergebnismenge etwas ein:
SET STATISTICS IO ON
SELECT
[StockItemID]
,[StockItemName]
,[UnitPrice]
FROM [Warehouse].[StockItems]
WHERE UnitPrice > 50.0
Auch hier sehen wir die Information, dass 16 Datenbankseiten gelesen wurden:
Table 'StockItems'. Scan count 1, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Warum ist das so, wo wir nun doch nur noch 23 Zeilen zurückbekommen? Das liegt daran, dass der UnitPrice keine Index-Spalte ist, deshalb muss noch immer jede Datenbankseite gelesen werden, um herauszufinden, ob eine Zeile in der Ergebnismenge enthalten ist oder nicht. Anders sieht das aus, wenn wir auf eine Index-Spalte einschränken:
SET STATISTICS IO ON
SELECT
[StockItemID]
,[StockItemName]
,[UnitPrice]
FROM [Warehouse].[StockItems]
WHERE StockItemID = 150
Dieses Mal mussten, um den Eintrag zu finden für den wir uns interessieren nicht alle Datenseiten der Tabelle gelesen werden, sondern nur 2:
Table 'StockItems'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Dementsprechend liegt hier auch kein Scan mehr vor und wir sehen einen Scan count von 0. Die zwei Seiten, die gelesen werden mussten, sind dabei die beiden Index-Ebenen des Primärschlüssels. Doch wann sehen wir nun mehr als 1 Scan? Dafür sehen wir uns folgende Abfrage einmal an:
SET STATISTICS IO ON
SELECT
[StockItemID]
,[StockItemName]
,[UnitPrice]
FROM [Warehouse].[StockItems]
WHERE StockItemID = 150 or StockItemID = 120
Hier muss zweimal über den Primärschlüssel die richtige Zeile gesucht werden. Und während der Ausführungsplan hier einen Index Seek zeigt, sehen wir in Statistics IO zwei Scan-Vorgänge.
Dieser scheinbare Wiederspruch ist einer, der nur im reinen Naming begründet ist, da die Scan-Anzahl laut der Doku wie folgt definiert ist: “Die Anzahl von Suchen oder Scans, die nach Erreichen der Blattebene in beliebiger Richtung gestartet wurden, um alle Werte zum Erstellen des letzten Datasets für die Ausgabe abzurufen.”
Zum Schluss kommen wir noch einmal zur Suche nach dem Preis zurück, legen wir hier jetzt einen Index an:
BEGIN TRY DROP INDEX idx_StockItemUnitPrice ON [Warehouse].[StockItems] END TRY BEGIN CATCH END CATCH CREATE NONCLUSTERED INDEX idx_StockItemUnitPrice ON [Warehouse].[StockItems] (UnitPrice) INCLUDE ([StockItemID], [StockItemName])
Da dieser Index alle zur Beantwortung der Abfrage benötigten Spalten enthält, muss hier nur noch auf den Index zugegriffen werden. Das sehen wir natürlich auch in unseren IO Statistiken:
Table 'StockItems'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Wir haben für diese einfache Abfrage mit unserem Index die Anzahl der Reads also von 16 auf 2 Datenbankseiten, also um 87{cd57dc5c024b062e80cf443c8eab5674adc0a8508c08485cd20648099fe1d76c} reduziert. Die Anzahl der Reads für verschiedene Abfragen zu optimieren ist allerdings eine Wissenschaft für sich, die einen einfachen Ninja-Beitrag sprengen würde. Hier verneigt der Ninja sich in Erfurcht in Richtung des großen Ninja-Meisters Uwe Ricken-San, in dessen Blog sich reihenweise tiefergehende Informationen zu diesem Thema befinden.
