News & Events
DBA-Tipp: Microsoft SQL Server RAM Optimierung durch Plan Cache Reduzierung

Die Information ist ein schnelllebiges Gut. Jeden Tag werden wir mit hunderten Informationen zugemüllt. Deshalb sind wir bestrebt uns auf das Wesentliche zu konzentrieren und nur substantiell nachhaltige Informationen bereitzustellen.

Icon Unternehmen

Auf einem SQL Server teilen sich vor allem der Ausführungsplan Cache und der Buffer Pool (für die Daten der Datenbank) den Arbeitsspeicher. Wenn der Platzbedarf für den Query Plan Cache steigt, sinkt der verfügbare Platz für den Buffer Pool und damit für Daten aus der Datenbank.

Ein Problem tritt auf, wenn sehr viele Pläne erstellt werden, die nur einmalig genutzt werden. Diese belegen unnötig Speicherplatz und verringern die Leistung, da weniger Datenbankdaten im RAM liegen und mehr Zugriffe auf die Festplatten stattfinden müssen.

Seit der Version 2008 bietet der Microsoft SQL Server eine Option zur Optimierung für Ad hoc Workloads. Wenn diese Option aktiviert ist, reduziert sich der Speicherplatz für solche Ausführungspläne auf einen Bruchteil. Durch die Option wird für einen Ad hoc Ausführungsplan nur noch ein "Stub" im Cache gespeichert. Falls der selbe Plan noch einmal generiert werden muss, legt ihn der SQL Server vollständig im Cache ab.

 

Neben dem eben angesprochenen Cache Typ "Ad hoc" gibt es einen weiteren, für die Optimierung interessanten Typen: "Prepared". Das sind alle Ausführungspläne, die parametrisiert sind. Üblicherweise kann es aber ebenfalls viele dieser "Prepared" Pläne geben, die nur einmal benutzt werden.

Es empfiehlt es sich daher, den verbrauchten Speicherplatz für nur einmal genutzte Pläne im Cache zu prüfen.

Mit folgenden Befehl kann der Verbrauch geprüft werden:

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 ist die Option zur Optimierung für Ad hoc Workloads aktiviert und der Speicherbedarf für diesen Cache Typ entsprechend gering. 

Auch für Prepared Pläne gibt es bei diesem Ergebnis noch keinen Handlungsbedarf. Erst wenn der Platzbedarf für einmal genutzte "Prepared" Pläne größer ist als 500 MB, sollten diese mit

DBCC FREESYSTEMCACHE('SQL Plans')

bereinigt werden.

Ein Job, der den Platzbedarf in regelmäßigen Abständen prüft und den Cache bereinigt, falls ein bestimmter Schwellwert übertroffen wird, ist empfehlenswert.

Dieser kann als gespeicherte Prozedur erstellt und entsprechend parametrisiert über den SQL Server Agent regelmäßig ausgeführt werden.

Fazit:

Ein Blick auf die Nutzung des Query Plan Caches kann einen Anhaltspunkt auf eventuelle Performanceengpässe geben. Die Nutzung des SQL Server Features “Optimize for Ad hoc Workloads ” kann aber in vielen Fällen schon für eine signifikante Verbesserung der Cache Nutzung und damit der Performance beitragen.

Wenn weiterhin der Cache regelmäßig überprüft und ggf. zusätzlich bereinigt wird, können anwendungsbedingte Probleme verringert werden. Gern stehen Ihnen unsere Microsoft SQL Server Spezialisten zur Verfügung. Zögern Sie nicht, sich gleich mit uns in Verbindung zu setzen.


Newsletter-Archiv

Auf einem SQL Server teilen sich vor allem der Ausführungsplan Cache und der Buffer Pool (für die Daten der Datenbank) den Arbeitsspeicher. Wenn der Platzbedarf für den Query Plan Cache steigt, sinkt der verfügbare Platz für den Buffer Pool und damit für Daten aus der Datenbank.

Ein Problem tritt auf, wenn sehr viele Pläne erstellt werden, die nur einmalig genutzt werden. Diese belegen unnötig Speicherplatz und verringern die Leistung, da weniger Datenbankdaten im RAM liegen und mehr Zugriffe auf die Festplatten stattfinden müssen.

Seit der Version 2008 bietet der Microsoft SQL Server eine Option zur Optimierung für Ad hoc Workloads. Wenn diese Option aktiviert ist, reduziert sich der Speicherplatz für solche Ausführungspläne auf einen Bruchteil. Durch die Option wird für einen Ad hoc Ausführungsplan nur noch ein "Stub" im Cache gespeichert. Falls der selbe Plan noch einmal generiert werden muss, legt ihn der SQL Server vollständig im Cache ab.

 

Neben dem eben angesprochenen Cache Typ "Ad hoc" gibt es einen weiteren, für die Optimierung interessanten Typen: "Prepared". Das sind alle Ausführungspläne, die parametrisiert sind. Üblicherweise kann es aber ebenfalls viele dieser "Prepared" Pläne geben, die nur einmal benutzt werden.

Es empfiehlt es sich daher, den verbrauchten Speicherplatz für nur einmal genutzte Pläne im Cache zu prüfen.

Mit folgenden Befehl kann der Verbrauch geprüft werden:

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 ist die Option zur Optimierung für Ad hoc Workloads aktiviert und der Speicherbedarf für diesen Cache Typ entsprechend gering. 

Auch für Prepared Pläne gibt es bei diesem Ergebnis noch keinen Handlungsbedarf. Erst wenn der Platzbedarf für einmal genutzte "Prepared" Pläne größer ist als 500 MB, sollten diese mit

DBCC FREESYSTEMCACHE('SQL Plans')

bereinigt werden.

Ein Job, der den Platzbedarf in regelmäßigen Abständen prüft und den Cache bereinigt, falls ein bestimmter Schwellwert übertroffen wird, ist empfehlenswert.

Dieser kann als gespeicherte Prozedur erstellt und entsprechend parametrisiert über den SQL Server Agent regelmäßig ausgeführt werden.

Fazit:

Ein Blick auf die Nutzung des Query Plan Caches kann einen Anhaltspunkt auf eventuelle Performanceengpässe geben. Die Nutzung des SQL Server Features “Optimize for Ad hoc Workloads ” kann aber in vielen Fällen schon für eine signifikante Verbesserung der Cache Nutzung und damit der Performance beitragen.

Wenn weiterhin der Cache regelmäßig überprüft und ggf. zusätzlich bereinigt wird, können anwendungsbedingte Probleme verringert werden. Gern stehen Ihnen unsere Microsoft SQL Server Spezialisten zur Verfügung. Zögern Sie nicht, sich gleich mit uns in Verbindung zu setzen.