Home > SQL server DBA administration > Retrieving a query’s cached plan and SQL text

Retrieving a query’s cached plan and SQL text


When a query executes, a cached plan is created for it. A cached plan describes, at a granular level, how the query will be executed, for example, how indexes will be used.  A cached plan is a great starting point in determining why a query is behaving as it is, for example, why it’s running slowly. Often it also offers clues on how performance can be improved.

 

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 10

    st.text AS [SQL]

    , DB_NAME(st.dbid) AS DatabaseName

    , cp.usecounts AS [Plan usage]

    , qp.query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp WHERE st.text LIKE ‘%CREATE PROCEDURE%’

ORDER BY cp.usecounts DESC

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: