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*/,
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
In each of the cases where I observed a
NULL query plan, the query plan was available shortly afterwards (< 1 second in all my cases) via this method.
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?
This could give you an idea of what the plan might look like, and if the
RetrievedFromCache property of the query plan is
true, you are very likely seeing the same plan that was being used.
Why don't the query plans display in sp_whoisactive?
There are at least a few possible reasons why a plan might not show up, but I haven't observed this with the same frequency that you report, and it will be interesting to see any other answers that fill in the gaps.
Reason #1: sys.dm_exec_requests reports a statement_start_offset of -1
First, let's take a look at a simplified version of the section of
sp_whoisactive that looks up query plans:
query_plan = (
SELECT CONVERT(xml, query_plan)
FROM sys.dm_exec_text_query_plan (
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
And here is a sample result from my server:
Based on the documentation, it does not seem like -1 is an expected value for
sys.dm_exec_requests.statement_start_offset. But it does seem to appear somewhat frequently, and the
sp_whoisactive procedure does not make an attempt to handle this situation.
In my testing, I'm not even sure if it would be possible to do so; even when calling
sys.dm_exec_text_query_plan with the default values (to find the whole plan, not the specific plan based on the offset), I found that no plan was provided when
sys.dm_exec_requests.statement_start_offset was -1.
I think there is the possibility of a situation where the request exists, but the plan and current statement offset are not yet available. I do not know the full explanation for this behavior though. Since a
plan_handle is available (at least in my cases with a
NULL plan), I don't think it's the case that plan compilation is still happening.
Reason #2: It took more than 5ms to find the query plan
The full implementation of
sp_whoisactive actually uses a cursor to look up each plan one at a time. There is a
5ms to avoid delaying the entire procedure too much in case a lock is encountered when looking up a particular plan.
--Wait up to 5 ms for the SQL text, then give up
SET LOCK_TIMEOUT 5;
WHILE @@FETCH_STATUS = 0
However, the output for
sp_whoisactive should show
<timeout_exceeded /> 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.