plan_handle

Uniquely identifies a query plan for a batch that is cached or is currently executing.

plan_handle is varbinary(64). plan_handle can be obtained from the following dynamic management objects:

sys.dm_exec_cached_plans

sys.dm_exec_query_stats

sys.dm_exec_requests

Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_plan:

• If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. For example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_plan.
  
• Some Transact-SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.
  
• If a Transact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.
  

When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. To return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. You can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view. For more information about parameterization see, Simple Parameterization and Forced Parameterization.

Due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_plan cannot return query plans that meet or exceed 128 levels of nested elements. In earlier versions of SQL Server, this condition prevented the query plan from returning and generates error 6335. In SQL Server 2005 Service Pack 2 and later versions, the query_plan column returns NULL. You can use the sys.dm_exec_text_query_plan (Transact-SQL) dynamic management function to return the output of the query plan in text format.

To execute sys.dm_exec_query_plan, a user must be a member of the sysadmin fixed server role or have the VIEW SERVER STATE permission on the server.

The following examples show how to use the sys.dm_exec_query_plan dynamic management view.

To view the XML Showplans, execute the following queries in the Query Editor of SQL Server Management Studio, then click ShowPlanXML in the query_plan column of the table returned by sys.dm_exec_query_plan. The XML Showplan displays in the Management Studio summary pane. To save the XML Showplan to a file, right-click ShowPlanXML in the query_plan column, click Save Results As, name the file in the format <file_name>.sqlplan; for example, MyXMLShowplan.sqlplan.

A. Retrieve the cached query plan for a slow-running Transact-SQL query or batch

Query plans for various types of Transact-SQL batches, such as ad hoc batches, stored procedures, and user-defined functions, are cached in an area of memory called the plan cache. Each cached query plan is identified by a unique identifier called a plan handle. You can specify this plan handle with the sys.dm_exec_query_plan dynamic management view to retrieve the execution plan for a particular Transact-SQL query or batch.

If a Transact-SQL query or batch runs a long time on a particular connection to SQL Server, retrieve the execution plan for that query or batch to discover what is causing the delay. The following example shows how to retrieve the XML Showplan for a slow-running query or batch.

Note:
To run this example, replace the values for session_id and plan_handle with values specific to your server.

First, retrieve the server process ID (SPID) for the process that is executing the query or batch by using the sp_who stored procedure:

USE master;
GO
exec sp_who;
GO

The result set that is returned by sp_who indicates that the SPID is 54. You can use the SPID with the sys.dm_exec_requests dynamic management view to retrieve the plan handle by using the following query:

USE master;
GO
SELECT * FROM sys.dm_exec_requests
WHERE session_id = 54;
GO

The table that is returned by sys.dm_exec_requests indicates that the plan handle for the slow-running query or batch is 0x06000100A27E7C1FA821B10600, which you can specify as the plan_handle argument with sys.dm_exec_query_plan to retrieve the execution plan in XML format as follows. The execution plan in XML format for the slow-running query or batch is contained in the query_plan column of the table returned by sys.dm_exec_query_plan.

USE master;
GO
SELECT * FROM sys.dm_exec_query_plan (0x06000100A27E7C1FA821B10600);
GO

B. Retrieve every query plan from the plan cache

USE master;
GO
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);
GO

C. Retrieve every query plan for which the server has gathered query statistics from the plan cache

USE master;
GO
SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle);
GO

D. Retrieve information about the top five queries by average CPU time

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan 
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO

Reference

Other Resources

Help and Information