Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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

share|improve this answer
add comment

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);
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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