Plan Cache Analysis in SQL Server 2005

  • Article

Plan Cache: The portion of the memory pool allocated to store Query execution Plan

For how much memory is allocated for Plan Cache in different version of SQL server, please refer to my blog: - Understanding Query Plan Cache in SQL 2005 SP2 - why it's changed from SQL 2005 RTM/SP1?

A stored execution plan can be one of the following:

  • Compiled – the actual instructions describing how SQL Server will implement a query
  • Execution Context – run-time information unique to a user:
    • Values of local variables
    • Object ID’s of temporary objects
    • Which statement currently being executed
  • Cursors – execution state of server-side cursors
  • Parse Tree – Aids in parsing the query. Usually not cached.

In SQL Server 2005 there are four major DMVs which can be very useful in Plan Cache Analysis:

1. sys.dm_exec_requests

shows information about all running processes (similar to sysprocesses in SQL 2000) along with sql_handle, and plan_handle which can be utilized as an input in following DMVs

example:

SELECT

    sder.session_id AS [SPID],

       sder.sql_handle as [SQL_Handle],

       sder.plan_handle as [PLAN_Handle],

    sdes.login_name AS [Login],

    sd.name AS [DBName],

    sder.start_time AS [Start Time],

    sder.status AS [Status],

    sder.command AS [Command],

    sdet.text AS [SQL Text],

    sder.percent_complete AS [Pct Cmplt],

    sder.estimated_completion_time AS [Est Cmplt Time],

    sder.wait_type AS [Wait],

    sder.wait_time AS [Wait Time],

    sder.last_wait_type AS [Last Wait],

    sder.cpu_time AS [CPU Time],

    sder.total_elapsed_time AS [Total Elpsd Time],

    sder.reads AS [Reads],

    sder.writes AS [Writes],

    sder.logical_reads AS [Logical Reads]

FROM

    sys.dm_exec_Requests sder

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sdet

    JOIN sys.dm_exec_sessions sdes on sder.session_id = sdes.session_id

    JOIN sys.databases sd on sder.database_id = sd.database_id

WHERE

    sder.session_id <> @@SPID and sder.session_id > 50

 

2. sys.dm_exec_query_plan (plan_handle)

returns the plan in XML format.   You can save the XML format query plan with extension ".sqlplan" and when you open up that file it will show a nice graphical query plan.   

SELECT * FROM sys.dm_exec_query_plan ( < plan_handle> ) --here <plan_handle> is supplied based on the results from Query in Example 1.

3. sys.dm_exec_query_stats 

· The most important DMV – sys.dm_exec_query_stats - returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.   Please note that the statement_start_offset and statement_end_offset columns are zero based, represents number of bytes and the end offset is -1 for last statement in the batch. In addition the text value returned by sys.dm_exec_sql_text is nvarchar(max) instead of text as documented incorrectly in Books Online. Combined with sys_dm_exec_sql_text, it could provide rich information with all the performance related aggregates - IO (logical reads/writes, physical reads/writes), CPU (worker time), Duration (elapsed time), #of executions (execution_count).

SELECT db_name(qt.dbid) as DatabaseName, object_name(qt.objectid,qt.dbid) as ObjectName,

Qt.text as 'statement',

substring(Qt.text, (qs.statement_start_offset/2)+1

                        , ((case qs.statement_end_offset

               when -1 then datalength(Qt.text)

                              else qs.statement_end_offset

                           end - qs.statement_start_offset)/2) + 1) as 'Statement',

  QS.Total_Logical_Reads,

  QS.Total_Worker_Time,

  QS.Total_Elapsed_Time,

  QS.Execution_Count

FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text (QS.sql_handle) AS QT

WHERE plan_handle = <plan_handle> -- <plan_handle> is supplied based on the results from query in example 1.

 

Useful resources:

SQL Programmability & API Development Team Blog - https://blogs.msdn.com/sqlprogrammability/archive/2007/01.aspx

SQL DMVStats – Performance Warehouse - https://www.codeplex.com/sqldmvstats