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.
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
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
sp_WhoIsActive with parameter
@get_additional_info runs almost same time as without this parameter.
Does anybody know how to retrieve
plan_handle from XML column which will be treated as
varbinary(64) to use it for querying