September 30, 2010 at 6:24 am
In SQL 2008, how can I get the "plan_handle" value listed below returned as a radable value? (the actual plan name value)?
select top 5
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
0x060009005486401040412E1E060000000000000000000000
September 30, 2010 at 12:46 pm
Express12 (9/30/2010)
In SQL 2008, how can I get the "plan_handle" value listed below returned as a radable value? (the actual plan name value)?select top 5
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
0x060009005486401040412E1E060000000000000000000000
Basically, you don't. What you do is cross apply to the sys.dm_exec_query_plan table like this:
SELECT *
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
You'll get the XML for the estimated plan that is stored in cache which, when you click on it, will open as a graphical plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply