Posts Tagged ‘Performance’

SQL Server Plan Cache

21. Oktober 2011

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.

Artikel zur SQL Server Performance

15. März 2011

Zwei ältere Artikel zur SQL Server Performance sind:

Aus ersterem stammt auch das folgende SQL Statement zur Analyse von Performance Problemen:

 SELECT
 COALESCE(DB_NAME(t.[dbid]),'Unknown') AS [DB Name],
 ecp.objtype AS [Object Type],
 t.[text] AS [Adhoc Batch or Object Call],
             SUBSTRING(t.[text], (qs.[statement_start_offset]/2) + 1,
             ((CASE qs.[statement_end_offset]
                         WHEN -1 THEN DATALENGTH(t.[text]) ELSE qs.[statement_end_offset] END
                                     - qs.[statement_start_offset])/2) + 1) AS [Executed Statement]
             , qs.[execution_count] AS [Counts]
             , qs.[total_worker_time] AS [Total Worker Time], (qs.[total_worker_time] /
 qs.[execution_count]) AS [Avg Worker Time]
             , qs.[total_physical_reads] AS [Total Physical Reads],
  (qs.[total_physical_reads] / qs.[execution_count]) AS [Avg Physical Reads]
             , qs.[total_logical_writes] AS [Total Logical Writes],
 (qs.[total_logical_writes] / qs.[execution_count]) AS [Avg Logical Writes]
             , qs.[total_logical_reads] AS [Total Logical Reads],
 (qs.[total_logical_reads] / qs.[execution_count]) AS [Avg Logical Reads]
             , qs.[total_clr_time] AS [Total CLR Time], (qs.[total_clr_time] /
 qs.[execution_count]) AS [Avg CLR Time]
             , qs.[total_elapsed_time] AS [Total Elapsed Time], (qs.[total_elapsed_time]
 / qs.[execution_count]) AS [Avg Elapsed Time]
             , qs.[last_execution_time] AS [Last Exec Time], qs.[creation_time] AS [Creation Time]
 FROM sys.dm_exec_query_stats AS qs
     JOIN sys.dm_exec_cached_plans ecp ON qs.plan_handle = ecp.plan_handle
             CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
 --    ORDER BY [Total Worker Time] DESC
 --    ORDER BY [Total Physical Reads] DESC
 --    ORDER BY [Total Logical Writes] DESC
 --    ORDER BY [Total Logical Reads] DESC
 --    ORDER BY [Total CLR Time] DESC
 --    ORDER BY [Total Elapsed Time] DESC
 ORDER BY [Counts] DESC

Links zum Thema SQL Server Performance Tuning

16. September 2010

Hat Buck Woody in seinem Post Presentation Links: SQL Server Performance Tuning (Quest) zusammengetragen.
Die werde ich mir wohl mal in einer stillen Stunde zu Gemüte führen müssen