patternsqlModerate

When does an execution plan not show in sp_WhoIsActive?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

showsp_whoisactiveplandoeswhennotexecution

Problem

I use Adam Machanic's excellent sp_WhoIsActive procedure to see activity and to log to a table to troubleshoot long running queries.

Why does the execution plan not show all the time in the results? Most of the time, the query_plan value is NULL. If I look at the SQL_TEXT column in the results, I do see the query with parameters, but no values, like below:

(@P0 nvarchar(4000),@P1 nvarchar(4000))
select blah from foo where a = @P0 and b = @P1

How do I get the execution plans when they don't display here? Why don't they display? Must I use Profiler or Extended Events? Would putting in dummy values into these parameters and running the query with the actual execution plan included generate the same plan that was actually used when I was logging?

I have verified with the vendor that they are using Hibernate 3.5.

Solution

How do I get the execution plans when they don't display here?

One option could be to capture the plan_handle and then look up the query plan afterwards for that plan_handle using the following query:

SELECT CONVERT(XML, query_plan) from sys.dm_exec_text_query_plan(
    0x0600050059E32C0/*Truncated for brevity, replace with your full plan_handle*/,
    DEFAULT,
    DEFAULT
)

You can capture the plan_handle using the query in the Reason #1 section below or you could make a small edit to sp_whoisactive to expose it in the results. (It's already captured in the intermediate #sessions table that is used within sp_whoisactive).

In each of the cases where I observed a NULL query plan, the query plan was available shortly afterwards ( in the query_plan field in this case. I've seen this a few times, so I think that a NULL

 value is more likely reason #1 in your case.

Reason #3: The query plan XML is too complex

This doesn't seem likely in your case if the query text is as simple as your example. However, it looks like there is also the possibility of a

NULL

 query plan being shown due to the XML being too complex.

However, from looking through

sp_whoisactive, it looks like this case is handled with a message Could not render showplan due to XML data type limitations.` and instructions to convert the raw XML into a .sqlplan file.

Code Snippets

SELECT CONVERT(XML, query_plan) from sys.dm_exec_text_query_plan(
    0x0600050059E32C0/*Truncated for brevity, replace with your full plan_handle*/,
    DEFAULT,
    DEFAULT
)

SELECT t.text,
    r.plan_handle,
    r.statement_start_offset,
    r.statement_end_offset,
    query_plan = (
        SELECT CONVERT(xml, query_plan)
        FROM sys.dm_exec_text_query_plan (
            r.plan_handle, 
            r.statement_start_offset,
            r.statement_end_offset
        )
    )
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.is_user_process = 1

--Wait up to 5 ms for the SQL text, then give up
SET LOCK_TIMEOUT 5;
WHILE @@FETCH_STATUS = 0
...

Context

StackExchange Database Administrators Q#120220, answer score: 16

Revisions (0)

No revisions yet.