Retrieve execution plan via XML data from sp_WhoIsActive in adhoc process

Question

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?

Answer 1

It's not clear exactly how you are storing the data you get from sp_WhoIsActive.

But if you have the data stored in a table then you can use the following code to get data out of the additional_info column, and pass ut to sys.dm_exec_query_plan

SELECT s.*, pl.query_plan
FROM YourSessionsTable s
CROSS APPLY sys.dm_exec_query_plan(
    CONVERT(
      varbinary(64),
      s.additional_info.value('(plan_handle/text())[1]', 'varchar(256)'),
      1
    )
  ) pl;

You may not get any result if the plan has already been evicted from the cache.

---

I would advise you not to use sp_WhoIsActive on such a frequent basis. It's very heavy, especially if you are retrieving additional information such as query plans, it's mainly designed to be used on a one-off basis when the server is under stress, to see what is going on.

Instead use XEvents and/or Query Store, which is far lighter on resources.