Bereits mit der Version SQL Server 2008 kam eine neue Option, die sich positiv auf die Performance auswirken sollte, es aber noch nicht so richtig in die Köpfe geschafft hat. Das liegt mit Sicherheit auch daran, dass sich die Online-Doku auf wenige Informationen hierzu beschränkt.

Auch der Artikel mit dem Titel Zwischenspeichern und Wiederverwenden von Ausführungsplänen läßt sich zu dem Thema nicht besonders aus. Allerdings gibt es hier einige Hinweise zur Verwaltung des Prozedurcache, die aber nicht so richtig gut zu verstehen sind, oder?

Zitat: Wenn nicht ausreichend Speicherplatz zur Verfügung steht, werden von Database Engine (Datenbankmodul) Ausführungspläne aus dem Prozedurcache gelöscht. Um zu ermitteln, welche Pläne entfernt werden sollen, überprüft Database Engine (Datenbankmodul) wiederholt den Status sämtlicher Ausführungspläne. Die Pläne, deren aktuelle Kosten 0 (null) betragen, werden entfernt. Ein Ausführungsplan, dessen aktuelle Kosten 0 (null) betragen, wird bei unzureichendem Speicher nicht automatisch entfernt. Der Ausführungsplan wird nur bei einer Überprüfung durch Database Engine (Datenbankmodul) entfernt, wenn die aktuellen Kosten 0 (null) betragen. Wird ein Ausführungsplan derzeit nicht von einer Abfrage verwendet, werden bei der Überprüfung des Plans die aktuellen Kosten von Database Engine (Datenbankmodul) durch Reduzieren dieser Kosten gegen 0 (null) gesenkt.

Alles verstanden? Wenn nicht ausreichend Speicherplatz zur Verfügung steht, werden also die Pläne im Prozedurcache von der Database Engine überprüft. Die Kosten werden auf 0 gesenkt, wenn der Plan aktuell nicht verwendet wird. Pläne mit Kosten 0 werden bei der nächsten Überprüfung entfernt.

Wie kann man nun aber dafür sorgen, dass sich im Prozedurcache keine unnötigen Pläne ansammeln, die sowieso kein Mensch mehr braucht? Kann man das eigentlich mal vorab analysieren, über welche Zahlen wir uns hier gerade Gedanken machen wollen?

Quellen

Die folgenden Informationen und Statements habe ich ausgehend von dem Artikel von Kimberley Tripp zusammengestellt. Sehr empfehlenswert ist auch das White-Paper von Greg Low: Plan Caching in SQL Server 2008
Kalen Delaney geht ebenfalls in ihrem Buch "SQL Server 2008 Internals" auf die Zwischenspeicherung von Ad-Hoc-Abfragen ein.
Glenn Berry geht bei seinen Analyse-Skripten auch immer wieder auf diese Option ein!

Analyse vorher

Schauen wir uns zuerst einmal an, wie die aktuelle Einstellung auf dem Server ist:

SELECT name, value, value_in_use, [description]
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';

Das Ergebnis dürfte ungefähr so aussehen:

Was habe ich denn nun so in meinem Prozedurcache?

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;

Hier erwarte ich dann eine Tabelle die ungefähr der folgenden entspricht:

Wir sehen also hier ca. 24.000 Ad-Hoc-Abfragen, die nur ein einziges Mal verwendet wurden und im Prozedurcache immerhin 1,2 GB belegen. Das ist mehr als die Hälfte dessen, was die Ad-Hoc-Abfragen insgesamt belegen.
Das sollte man sich mal genauer anschauen. Das folgende Statement zeigt also die ersten hundert Einträge aus dem Prozedurcache (die nur einmal verwendet wurden) absteigend sortiert nach der Größe des belegten Speicherplatzes.

SELECT TOP(100) [text], cp.size_in_bytes
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = 'Compiled Plan'
AND cp.objtype = 'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

Sicher wird einem das ein oder andere Statement bekannt vorkommen und man findet zumindest schnell die Anwendungen heraus, die dafür verantwortlich sind. Der beste Weg wäre jetzt sicher alle Statements mit Parametern zu versehen und die Anwendungen sauber arbeiten zu lassen. Leider bauen Entwickler all zu oft SQL-Statements im Baukastensystem zusammen und da werden dann eben auch mal die Teile der Where-Bedingung "dran gestringt", welche eigentlich Parameter sind und bei sp_executesql übergeben werden könnten.
In der Folge gibt es unzählige Statements, die ähnlich aber nicht gleich sind, da sie sich lediglich in wenigen Zeichen unterscheiden. Jedesmal muss so ein Statement kompiliert werden, was auch Zeit kostet. Anwendungen, die intensiv Ad-Hoc-Abfragen zusammenbauen, können schon mal um 20% schneller werden, wenn sie durch eine Umstellung auf parametrisierte Statements bereits vorhandene Pläne wiederverwenden. Wir haben hier also nicht nur einen Gewinn an Speicherplatz für wichtigere Objekte, sondern auch eine Reduzierung von Rechnerlast durch Verzicht auf unnötige Kompilationen.

Nicht immer hat man Einfluss auf die Entwicklung, auch diverse Produkte externer Anbieter kommen mit Statements daher, die eigentlich parametrisiert gehörten. Was können wir hier tun, bzw. was kann der SQL Server für uns tun?

Arbeitsweise

Wenn wir den SQL Server dazu veranlassen, diese Ad-Hoc-Abfrage nur rudimentär zu beachten, legt er lediglich eine Art Platzhalter (Stub=Stummel) von ca. 300 Byte vor SQL Server 2012 oder ca. 200 Byte ab SQL Server 2012 im Prozedurcache ab. Falls dieses Statement erneut verwendet wird, wird der Platzhalter durch den richtigen Plan ersetzt und die Art des Objektes von "Compiled Plan Stub" auf "Compiled Plan" geändert.

Umstellung

Damit sich die Arbeitsweise des SQL Servers ändert, müssen wir die entsprechende Option in der Konfiguration ändern. Da es sich hier um eine erweiterte Konfiguration handelt, müssen wir erst mal konfigurieren, dass sie uns auch angezeigt wird. Danach setzen wir den Wert auf 1.

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO

Die Konfiguration ist auch über die GUI im Management Studio möglich und hier über die Servereigenschaften zu setzen. Im Abschnitt "Erweitert" finden wir die Option "Für Ad-Hoc-Arbeitsauslastungen optimieren" und ändern die Konfiguration von "False" auf "True".

Hierdurch zeigt sich der Prozedurcache erst mal ziemlich unbeeindruckt, denn diese Einstellung gilt nur für alle neu ermittelten Ad-Hoc-Pläne. Um erst einmal Platz im Prozedurcache zu schaffen und die alten Ad-Hoc-Pläne zu entfernen, können wir alle Pläne aus dem Cache entfernen. Dies sollte man natürlich auf einem produktiven System nicht gerade zu Zeiten großer Last machen. Alternativ kann man auch den nächsten Serverneustart abwarten. Ansonsten leert dieses Statement den Prozedurcache:

DBCC FREEPROCCACHE;

Analyse nachher

Schauen wir uns nun noch einmal an, wie die aktuelle Einstellung auf dem Server ist:

SELECT name, value, value_in_use, [description]
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';

Das Ergebnis sollte jetzt so aussehen:

Was ist jetzt mit unserem Prozedurcache?

Dazu müssen wir erst mal wieder etwas abwarten, bis sich der Cache neu gefüllt hat, die ersten Anwendungen tätig geworden sind. Wir setzen dann wieder das Statement ab, welches uns die Verteilung im Prozedurcache anzeigt. Diesmal ist es ergänzt um die dedizierte Angabe der nur als Stub vorliegenden Pläne und deren Speicherverbrauch:

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]
        , SUM(CAST((CASE WHEN usecounts = 1 AND cacheobjtype = 'Compiled Plan Stub' THEN size_in_bytes ELSE 0 END) AS DECIMAL(18,2)))/1024/1024 AS [Total MBs - Stub USE Count 1]
        , SUM(CASE WHEN usecounts = 1 AND cacheobjtype = 'Compiled Plan Stub' THEN 1 ELSE 0 END) AS [Total Plans - Stub USE Count 1]                
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC;


Die Werte beim Cachetype "Adhoc" sollten sich dahingehend verändert haben, dass zumindest die Menge an verschwendetem Arbeitsspeicher ("Total MBs - USE Count 1") deutlich geringer geworden ist.

Nach einiger Zeit kann man einmal zählen, wieviele Ad-Hoc-Abfragen als Stub (Stummel) vorliegen und wieviele bereits mehrfach verwendet wurden:

SELECT cp.usecounts, SUM(CASE WHEN cp.cacheobjtype = 'Compiled Plan' THEN 1 ELSE 0 END) AS #Distinct_Plans
, SUM(CASE WHEN cp.cacheobjtype = 'Compiled Plan Stub' THEN 1 ELSE 0 END) AS #Distinct_Plans_Stub
FROM sys.dm_exec_cached_plans AS cp
WHERE cp.cacheobjtype LIKE 'Compiled Plan%'
AND cp.objtype = 'Adhoc'
GROUP BY cp.usecounts
ORDER BY cp.usecounts;

Mit Usecounts=1 sehen wir nun also eine große Zahl von Stubs und auch noch einige Ad-Hoc-Abfragen, die nun aber schon zum zweiten mal verwendet wurden. Runde 1 haben sie ja als Stub verbracht.
In den höheren Rängen tummeln sich fast ausschließlich Ad-hoc-Abfragen, für die ein vollständiger Plan im Cache vorliegt. Es gibt aber auch immer wieder einige Statements, die als Stub vorliegen, obwohl sie bereits mehrfach verwendet wurden:

SELECT cp.usecounts, cp.cacheobjtype, TEXT
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = 'Compiled Plan Stub'
AND cp.objtype = 'Adhoc'
AND cp.usecounts > 1
ORDER BY cp.usecounts;

Wie kann das sein? Nun, die Antwort ist ganz einfach. Es sind zwar Ad-Hoc-Abfragen, die mehrfach verwendet wurden, aber eigentlich keinen Ausführungsplan besitzen, der im Prozedurcache gespeichert werden könnte. Ein Beispiel hierfür ist dieses Statement:

SELECT CONVERT(SYSNAME, SERVERPROPERTY(N'servername'))

Führt man es im Management Studio aus und läßt sich dabei den tatsächlichen Ausführungsplan anzeigen, erhält man lediglich das Ergebnis des Statements, aber keinen Ausführungsplan!

Die Anzahl der Ad-Hoc-Abfragen reduziert man damit natürlich nicht, aber so belegen die 24.000 Abfragen, die vorher ca. 1,2 GB verschwendeten jetzt nur noch ca. 7 MB. Damit bleibt deutlich mehr Platz für andere Objekte über, die ihn sicherlich sinnvoller verwenden.

Rückmeldung

Sicher wird der ein oder andere sagen, dass machen wir schon lange so. Andere werden aber überrascht auf die Zahlen schauen, die sie bei der ersten Analyse des Prozedurcache finden werden. Ich würde mich über einige Kommentare mit Zahlen über bisher verschwendeten Arbeitsspeicher freuen und rechne damit, dass wir hier schnell einige GB zusammen bekommen, die gesammelt einem einzelnen SQL Server sehr gut zu Gesicht stehen würden.