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.