We have installed a process in which we execute the sp_WhoIsActive
procedure every 1 minute. We combine some additional logic into the same job.
Currently the sp_WhoIsActive
procedure is run every 4-5 seconds in our production environment. We are using the following code:
EXEC [sp_WhoIsActive] @show_sleeping_spids = 0
,@find_block_leaders = 1
,@format_output = 0
,@destination_table = #Temp_Session;
With the parameter @get_plans = 2
the procedure sp_WhoIsActive
code runs around 30 seconds.
We now have a new requirement for for ad hoc analysis to also retrieve the execution plan for some of queries.
My idea is to add the parameter @get_additional_info
, which is supplied in the procedure sp_WhoIsActive
, and then to retrieve from the additional_info
column's XML data the value from the <plan_handle>...</plan_handle>
.
The value(s) for the plan handles would then be stored somewhere and using a scheduled job (for example every 1 hour) retrieve the actual execution plans using sys.dm_exec_query_plan
view.
Executing sp_WhoIsActive
with parameter @get_additional_info
runs almost same time as without this parameter.
Question
Does anybody know how to retrieve plan_handle
from XML column which will be treated as varbinary(64)
to use it for querying sys.dm_exec_query_plan
view?