THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Which DMV Shows Plan Handles?

Sometimes, knowing which Dynamic Management View (DMV) to use is your biggest challenge when attempting to figure out SQL Server 2005 behavior.  For example, what if you want the cached execution plans in XML format, similar to the output of SET SHOWPLAN_XML?  As it turns out, Gert Draper pointed out that there are a couple of DMVs that expose a plan_handle:

SELECT dm_exec_query_plan(plan_handle),
*
FROM sys.dm_exec_query_stats
WHERE plan_handle IS NOT NULL
GO

SELECT dm_exec_query_plan(plan_handle),
*
FROM sys.dm_exec_requests
WHERE plan_handle IS NOT NULL
GO

SELECT dm_exec_query_plan(plan_handle),
*
FROM sys.dm_exec_cached_plans
WHERE plan_handle IS NOT NULL
GO

Note that the plan_handle is a varbinary(64) data type.  Cheers,

-Kevin

Published Wednesday, July 25, 2007 5:02 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement