0

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?

1
  • Do you want to collect Estimated plans or Actual plans?
    – Zikato
    Aug 30, 2022 at 9:52

1 Answer 1

2
+25

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.