Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset:

select top 10  
    sum(qs.total_worker_time) as total_cpu_time,  
    sum(qs.execution_count) as total_execution_count, 

    qs.plan_handle, st.text  
    sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.plan_handle) as st
group by qs.plan_handle, st.text 
order by sum(qs.total_worker_time) desc

Question 1: What exactly is the plan_handle? It doesn't appear to be a hash of the plan, like it is in Oracle. I ask because I want to be able to detect the situation in which the plan of a statement has changes.

Question 2: Once I have a plan_handle, I am interested in the actual plan. So I do, for example:

select * from sys.dm_exec_query_plan (0x060006001F176406B8413043000000000000000000000000)

In the query_plan column I get a link that when I click displays an XML document. If I save it on disk as whatever.sqlplan, I can double-click it in Windows and it displays correctly in Management Studio. Surely there must be a way to avoid this step?!

Question 3: Is there a way to convert the XML back into a textual format, like in the old days of SET SHOWPLAN_TEXT? I want to be able to view them graphically, but also automate diffing them in some meaningful way.



I found the following tip in the SQL Server 2008 Internals and Troubleshooting book by Wrox Press:

If you use SQL 2008 SSMS connected to a SQL 2005 Server, when you click on the XML query plan it will load the graphic query plan for you automatically.

I verified it worked using the following query from the same book:

select session_id, text, query_plan
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle)
cross apply sys.dm_exec_query_plan(plan_handle)

Also, if you haven't looked at it yet, check out SQL Sentry's free Plan Explorer. You'll have to save the XML to disk as a .sqlplan first, but it offers a much easier to use view of a SQL Execution Plan. Hopefully someone will come up with an Visual Execution Plan Diff tool someday. :D


A1: The plan_handle is a hash for a group of statements, or batch.

A2: No, the dm_exec_query_plan returns the query plan in XML format, so you need to click on that in order to see the graph.

A3. Try this:

SELECT query_plan 
FROM sys.dm_exec_text_query_plan (0x06000100A27E7C1FA821B10600,0,-1);
  • 1
    I get back <ShowPlanXML xmlns="http://schemas.microsoft.com... but there's still no way to display it directly - I need to save it to disk as a .sqlplan file first. Weird. Thanks tho' :) – Gaius Mar 16 '11 at 12:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.