SQL Server Plan Cache

Dass SQL Server (in meinem Fall 2008 R2) sich Ausführungspläne im (endlichen) Speicher merkt, ist wohl den meisten DB-Administratoren und -Entwicklern bekannt.
Ebenso leuchtet ein, dass Adhoc Abfragen diesen Cache vollmüllen und dadurch die Leistung des Systems nicht gerade verbessert wird.
Wenn dann noch die ungünstige Situation eintritt, dass man häufig direkt auf dem ProduktivSystem entwickeln muss, dann ist spätestens das ein Grund, sich mal mit dem Thema zu beschäftigen.

Das Blog von Pinal Dave war mit dem Post SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script Ausgangspunkt meiner Recherche.

Zunächst einmal kann man sich mit folgender Abfrage einmal alle gespeicherten Pläne im Cache anzeigen lassen, die nur einmal benutzt wurden:

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

Einzelne Pläne können mit dem Verwaltungsbefehl DBCC FREEPROCCACHE aus dem Cache entfernt werden, indem man das plan_handle aus der obigen Abfrage dem Befehl in Klammern hinten anstellt, wie im folgenden Beispiel:

DBCC FREEPROCCACHE(0x06000100804AD30040219A86000000000000000000000000)
WITH NO_INFOMSGS;
GO

Wenn ansonsten kein plan_handle angegeben wird, werden alle Pläne aller DBs auf dem Server gelöscht.
Das ist auf dem Produktivserver nicht unbedingt best practice.

Da das Finden und Löschen eines Plans die Entwicklerproduktivität negativ beeinflusst, ist es hilfreich, dass man die Erstellung der Pläne schon relativ einfach im Vorfeld verhindern kann.
Dazu kann man dem jeweiligen SQL Befehl wie im folgenden Beispiel den Query Hint OPTION (RECOMPILE) hinzufügen:

SELECT * FROM Customers
OPTION (RECOMPILE);
GO

Ein Hinweis von Klaus Aschenbrenner und der Blogpost Clearing your ad-hoc SQL plans while keeping your SP plans intact von Maciej Pilecki führten mich zu folgendem Befehl, der wohl nur die Adhoc Pläne, nicht aber die von Prozeduren löscht:

DBCC FREESYSTEMCACHE('SQL Plans');
GO

Allerdings hat das Teil den Nachteil, dass es auch parameterisierte Abfragen (z.B. von LinqToSQL und Entity Framework) wegputzt.

Übrigens gibt es auch beim Zugriff mit .Net-Anwendungen über das Entity Framework das Query Plan Caching eine Property System.Data.EntityClient.EntityCommand.EnablePlanCaching.
Die habe ich allerdings noch nicht genauer untersucht.

Evtl. könnte man mit Hilfe der obigen Abfrage in Kombination mit DBCC FREEPROCCACHE automatisch in bestimmten Zeitzyklen einfach alle Pläne löschen, die nur einmal verwendet wurden.

Advertisements

Schlagwörter: , ,

Eine Antwort to “SQL Server Plan Cache”

  1. rewrite Says:

    It’s actually a nice and helpful piece of info. I am satisfied that
    you just shared this useful information with us.
    Please keep us up to date like this. Thanks for sharing.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s


%d Bloggern gefällt das: