Database Reference
In-Depth Information
select pi.sql_handle, pi.plan_handle, pi.set_options, b.text
from
PlanInfo pi cross apply
sys.dm_exec_sql_text(convert(varbinary(64),pi.sql_handle)) b
option (recompile)
Figure
26-17
shows two different plans for the same SQL batch due to the difference in
SET
options.
Figure 26-17.
Plan_handle
and
sql_handle
You can obtain an XML representation of the execution plan with the
sys.dm_exec_query_plan
function, which
accepts
plan_handle
as a parameter. However, it does not return a query plan if the XML plan has more than 128
nested levels due to XML data type limitations. In that case, you can use the
sys.dm_exec_text_query_plan
function,
which returns a text representation of the XML plan instead.
You can retrieve the information about currently executed requests by using the
sys.dm_exec_requests
view.
Listing 26-32 shows the query, which returns the data on currently running requests from user sessions sorted by their
running time in descending order.
Listing 26-32.
Using
sys.dm_exec_requests
select
er.session_id
,er.user_id
,er.status
,er.database_id
,er.start_time
,er.total_elapsed_time
,er.logical_reads
,er.writes
,substring(qt.text, (er.statement_start_offset/2)+1,
((
case er.statement_end_offset
when -1 then datalength(qt.text)
else er.statement_end_offset
end - er.statement_start_offset)/2)+1) as [SQL],
qp.query_plan, er.*
from
sys.dm_exec_requests er with (nolock)
cross apply sys.dm_exec_sql_text(er.sql_handle) qt
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where
er.session_id > 50 and /* Excluding system processes */
er.session_id <> @@SPID
order by
er.total_elapsed_time desc
option (recompile)
