Optimize for Ad hoc Workloads

Die Option Optimize for Ad hoc Workloads ist nun wirklich nicht neu.  Dennoch gerate ich immer wieder an SQL Server wo diese Option auf false steht, was vor Allem daran liegt, dass dies die Standardeinstellung des SQL Servers ist. Es gibt jedoch gute Gründe, diese Option auf den meisten SQL Server Instanzen zu aktivieren.

Was sind Ad hoc Workloads?

Unter Ad hoc Workloads werden vor Allem Queries verstanden, die nur sehr selten oder vielleicht auch nur ein einziges mal auf dem SQL Server ausgeführt werden. Dies kann z.B. der Fall sein, wenn ein Administrator eine einzelne Query gegen den SQL Server ausführen muss oder auch wenn Applikationen Queries dynamisch zusammensetzen.

Wenn eine Query zum ersten Mal seit dem letzten SQL Server Neustart auf dem SQL Server ausgeführt wird, so erzeugt der SQL Server einen möglichst optimalen Ausführungsplan und legt diesen Ausführungsplan dann im so genannten Plan Cache ab. Bei Ad Hoc Queries ist das Problem jedoch, dass diese nicht erneut auftreten und daher wertvoller Speicherplatz im Plan Cache verschwendet wird.

Optimize for Ad hoc Workloads

Hier kommt jetzt die Option Optimize for Ad hoc Workloads zum Einsatz. Wenn diese Option auf True gestellt wird, dann wird eine Query, die zum ersten Mal seit dem letzten SQL Server Neustart auf dem System ankommt nicht mehr mit dem kompletten Ausführungsplan im Plan Cache abgelegt. Es wird stattdessen im Plan Cache nur ein Stub abgelegt damit der SQL Server erkennen kann, wenn die gleiche Query ein weiteres mal ausgeführt wird.

Wird die gleiche Query jetzt zum zweiten Mal ausgeführt, wird der SQL Server den kompilierten Ausführungsplan zu dieser Query im Plan Cache ablegen.

Der Nachteil der Option ist dabei, dass der SQL Server etwas länger braucht um den Plan Cache nach einem Neustart zu füllen, da der kompilierte Ausführungsplan einer Query erst nach dem zweiten Auftreten der Query im Plan Cache abgelegt wird. Dieser Nachteil ist meiner Meinung nach aber gut zu verkraften, da SQL Server in der Regel nicht so oft neu gestartet werden.

Normalerweise überwiegen die Vorteile deutlich: Mit Anschalten der Option Optimize for Ad hoc Workloads nehmen Queries die in ihrer Form nur einmal vorkommen keinen unnötigen Speicher mehr im Plan Cache ein, was dazu führt, dass Queries die häufiger vorkommen besser gecached und so auch schneller ausgeführt werden können.

Konfiguration

Die Einstellung Optimize for Ad hoc Workloads findet man direkt in den SQL Server Instanz Einstellungen. Um die Option zu aktivieren kann man folgendes T-SQL Script verwenden:

Um zu testen, ob die Option richtig aktiviert wurde, kann man in die Catalog View sys.configurations sehen:

Hier muss dann der value und value_in_use jeweils auf 1 stehen, damit Optimize for Ad hoc Workloads aktiviert ist.

Fazit

Optimize for Ad hoc Workloads sollte auf den meisten Systemen aktiviert sein, da die Vorteile die Nachteile fast immer überwiegen. Ich würde sogar so weit gehen und sagen, Microsoft sollte eine Best Practice Policy dazu erstellen und in der Standardinstallation diese Option aktivieren.

Neu ist die Option nicht, allerdings ist die Option auf vielen Systemen an die ich komme, immer noch ohne Grund deaktiviert. Generell gilt aber für Produktionssysteme, dass jede Änderung an der Konfiguration zuerst an einem Testsystem getestet werden sollte.