Displaying query plans in Server Management Studio

Question

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  
from  
    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.

Thanks!

Answer 1

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

Answer 2

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);