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