Sql-server – Execute sp_WhoIsActive as a query

I have found a driver that I can install on a PC that does not have SQL Server installed on it and it makes Excel's data connections connect to SQL Server with cell linked parameters that are dates. The install can be found here: http://www.microsoft.com/en-us/download/details.aspx?id=36434

You then need to change the driver in your connection string to look like:

DRIVER=ODBC Driver 11 for SQL Server;SERVER=blabla.yadayada.eu-west-1.rds.amazonaws.com;UID=MyUserID;PWD=MrPasswordMan;APP=Microsoft Office 2013;WSID=COMPUTER-NAME;

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 (< 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:

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

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 LOCK_TIMEOUT of 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.