.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Finding a cached plan

Posted By:Manning       Posted Date: February 26, 2011    Points: 100    Category: DataBase    URL: http://www.dotnetspark.com  

The author discusses a script for identifying a cached plan to find out the cause of a problematic query in SQl Server

This article is taken from the book SQL Server DMVs in Action.  The author describes how to find the amount of space in tempdb in order to prepare for future space usage.

40% off any version of SQL Server DMVs in Action with the checkout code dnspark40. Offer is only valid through www.manning.com.

When a SQL query (batch or stored procedure) is run, the optimizer determines how it will fulfill its needs. Typically, the optimizer looks at the number of rows in the tables, the indexes, and statistics and determines the best way to access the table's data.

Viewing the cached plan associated with a SQL query is a great way to discover why a query is behaving like it is. Why is the query taking too long? Is it using an index or a table scan? Is an index being used appropriately (an index seek rather than an index scan)?

The script we use to find a cached plan is shown in listing 1.

 Listing 1 How to find a cached plan  

st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, COALESCE(DB_NAME(st.dbid), DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp	#A
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = 'dbid'
AND st.text LIKE '%PartyType%'	#B
#A Join cached plan and SQL text and query plan DMFs
#B Text to search plan for

In the listing, you can see that there is one Dynamic Management View (DMV) and three Dynamic Management
Functions (DMFs) involved in finding a cached plan. A brief description of each is shown in table 1.

Table 1 DMVs/DMFs for finding a cached plan


DMV / table                                       Description


sys.dm_exec_cached_plans          Contains cached plans

sys.dm_exec_sql_text                     DMF that returns the sql text identified by a given plan handle sys.dm_exec_query_plan               DMF that returns the cached plan in XML format, identified by a

given plan handle


sys.dm_exec_plan_attributes          DMF used to obtain the underlying database for ad-hoc or prepared queries (as opposed to stored procedures)


The joining of the DMV and DMFs provides enough information to identify the cached plans for the specified text pattern given by the LIKE clause, across all databases on the server. The DMV sys.dm_exec_cached_plans provides the plan_handle used to CROSS APPLY to all three DMFs. The cached plan's plan_handle is passed to  the  DMF  sys.dm_exec_sql_text to  retrieve  the  text  of  the  SQL  query.  Similarly, the  cached  plan's plan_handle is passed to the DMF sys.dm_exec_query_plan to retrieve the cached plan of the query. Finally, the  cached  plan's  plan_handle is  passed  to  the  DMF  sys.dm_exec_plan_attributes  to  decode  the underlying database name for any ad-hoc or prepared queries.

We use a common pattern to extract the underlying database name for any ad-hoc or prepared queries.

The query we execute selects the first 20 queries that contain the text PartyCode in the SQL query across all databases on the server. This query can be very useful when searching the SQL queries for a given comment; for example, a given change number, change implementer, or date of change.

LIKE pattern matching

There is one thing you should be careful about when searching for names that are wrapped in square brackets or have an underscore, as is sometimes the case in SQL Server function names; for example, [dbo].[Some_Function_Name]. When a LIKE statement is used, it interprets the underscore to mean any single character, and the square brackets to mean any single character within the set.

An example of the type of output for this query is given in figure 1:

Figure 1 Output showing cached plans that contain the text PartyType

The presented script could be amended to filter for cached plans on a given database only. We can also filter on the objtype to look only at non-stored procedure SQL queries, if we are interested in the amount of ad-hoc queries running on the server.


Several SQL scripts can use be used to identify SQL queries that are problematic from different perspectives. Each perspective identifies queries that would benefit from further investigation, with the aim of improving their performance. This article provided a SQL script that can use be used to identify a cached plan.

SQL Server DMVs in Action

Ian W. Stirk
MEAP Release: February 2010
Softbound print: Winter 2010 | 375 pages
ISBN: 9781935182733

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend