„Optimize for ad-hoc workloads“ – warum diesen Parameter aktivieren?

Heute wurde mir die Frage gestellt, ob ich den Parameter „Optimize for ad-hoc workloads“ kenne bzw ob ich wüsste wofür dieser da ist.
Ja, ich kenne diesen Parameter und ich weiß auch, dass wir ihn in unseren Best-Practises aktivieren. Also muss ich mich schon einmal damit beschäftigt haben… scheint aber schon lange her zu sein, denn ich brachte die Fakten einfach nicht mehr zusammen. Dieser Parameter ist auch schon recht alt, in den Dokumentation des SQL Server fand ich den Parameter erstmalig für den SQL Server 2008, da kann man mal die Details „vergessen“.

„Optimize for ad-hoc workloads“

Aber vielleicht sollte ich erst einmal klären, was ad-hoc in diesem Zusammenhang bedeutet… Man findet diesen Begriff in Verbindung mit dem SQL Server relativ häufig, aber macht man sich dann Gedanken was im Detail damit gemeint sein könnte, dabei ist es relativ einfach.

Ad-hoc Queries sind einfache Abfragen, also zum Beispiel Abfragen die ein Benutzer „einfach mal so“ in seinem Abfrage-Tool erstellt und ausführt. Also Abfragen die irgendwie immer anders aussehen, auch wenn sich vielleicht nur Werte oder die Sortierung geändert haben, somit kann der Ausführungsplan für diese Query erst bei Ausführung ermittelt bzw erstellt werden.
Als Gegensatz zu ad-hoc Queries sind Stored-Procedures und Prepared-Statements zu nennen, da die Abfrage bereits zum Zeitpunkt der Erstellung bekannt ist und entsprechend frühzeitig in Ausführungsplan erstellt wurde. Man hat hier eine fest definierte Abfrage/Funktion erstellt, welche nur (grob gesagt) einem verallgemeinerten Zweck dient, um zum Beispiel aus dieser Tabelle eine Auswahl erstellt und an welche nur die ID als Variable übergeben wird. Bei Ausführung der Stored Procedure wird dann immer der bereits bekannte Ausführungsplan herangezogen.

Was passiert bei der Ausführung von ad-hoc Queries?

Jedesmal wenn eine ad-hoc Query ausgeführt wird, sucht der SQL Server (die Datenbank Engine – der Optimizer) in seinem Plan Cache ob es zu dieser Query bereits einen Ausführungsplan gibt. Gibt es keinen gespeicherten Ausführungsplan so wird ein neuer für diese Abfrage berechnet und im Plan Cache gespeichert. Wenn nun eine Applikation immer wieder ein und dieselbe Abfrage ausführt, zum Beispiel für jede Kategorie in einem Kategorienbaum alle zugehörigen Produkte sucht:

Select * from Products where category_id = 1 and stock_value > 0
Select * from Products where category_id = 2 and stock_value > 0
Select * from Products where category_id = 3 and stock_value > 0
...

Da die Anfrage immer „anders“ ist, als die vorherige Abfrage, wird für jedes dieser Statements ein eigener Ausführungsplan im Plan Cache erstellt und belegt dort Platz. Im Idealfall kann man die Applikation anpassen und die Verarbeitung anpassen bzw optimieren, was aber tun wenn man dies nicht kann?

Beispiel eines Autoteile-Händlers: Gewisse Produkte gibt es für gewisse Marken => Ermittel alle Teile aller Kategorien für die Marke XYZ.
Jede Ergebniszeile nimmt einen gewissen Bereich im Speicher des SQL Servers ein, bei ~100.000 Kategorien/Teilen pro Automarke…
Irgendwann ist der Plan Cache voll und der SQL Server benötigt neuen Platz. Da der Plan Cache aber begrenzt ist, bleibt nur die Möglichkeit des „Aufräumens“. Aber woran orientiert sich der SQL Server beim Aufräumen im Plan Cache?

Wie optimiert der Parameter die Ausführung der ad-hoc Queries?

Durch die Aktivierung des Parameters „Optimize for ad-hoc workloads“ (von 0 nach 1) ändert sich die Art der Kompilierung eben dieser ad-hoc Queries bzw deren Speicherung im Plan Cache. Während vorher für jede Abfrage ein Ausführungplan kompiliert wurde und anschließend gespeichert wurde, wird jetzt nur noch ein sogenannter Plan Stub gespeichert, dadurch reduziert sich der Speicherbedarf enorm. In Beispielen die ich hierzu gefunden habe, werden Reduzierungen von ~90% erreicht. (Variiert von System/Abfrage – kann mit Statements ermittelt bzw überprüft werden) Diese Einsparung wird dadurch erreicht, dass nur noch ein Hash-Wert für diese Ad-hoc Queries ermittelt / gespeichert wird und kein vollständiger Ausführungsplan. Wird jetzt eine Abfrage ein zweites Mal ausgeführt, so wird auch hier erst einmal nur der Hash-Wert für die Ausführung berechnet und dann im Plan Cache gesucht. Ist ein gesuchter Ausführungsplan bereits vorhanden so wird nun der endgültige Ausführungsplan vollständig kompiliert und gespeichert. Durch diese optimierte Speicherung und Verarbeitung von Abfragen bzw Ausführungsplänen wird a.) Speicher im Plan Cache für relevantere Abfragen frei gehalten und gleichzeitig die Kompellierungslast von den Prozessoren genommen.

Fazit:

Wenn eine (oder mehrere) Applikation immer wieder eine Vielzahl von ad-hoc Queries ausführt, dann sollte man diesen Parameter „Optimize for ad-hoc workloads“ definitiv aktivieren. Im Grunde sollte dieser Parameter bei allen SQL Servern (es gibt auch Ausnahmen 😉 ) aktivieren, da die meisten Applikationen eine Vielzahl von immer wieder minimal andere Abfragen ausführen. Hier unterstützt diese Speicher-Optimierung den SQL Server bzw die Performance erheblich.

Wer sich selber mal ein Bild machen möchte, sollte sich die Abfragen von Kimberly Tripp anschauen, um sich einen Überblick über die jeweilige Auslastung bzw Mengenverhältnisse seines SQL Servers zu machen (Beispiel das Beitragsbild – hier ist der Unterschied zwischen ad-hoc und prepared Statements nicht so sonderlich – ich habe aber auch keinen Vorher/Nachher-Vergleich)

SELECT objtype AS [CacheType],
    COUNT_BIG(*) AS [Total Plans],
    SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs],
    AVG(usecounts) AS [Avg Use Count],
    SUM(CAST((CASE WHEN usecounts = 1 THEN size_in_bytes
        ELSE 0
        END) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs – USE Count 1],
    SUM(CASE WHEN usecounts = 1 THEN 1
        ELSE 0
        END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC
GO