sp_whoisactive: Commands, Batches, and the Mysteries of Text

Home ... Documentation ... Downloads
Prior: Deciding What (Not) To See .... Next: "Planning" For Success

Remember the saga of SPID 54? In case you’ve forgotten its sad tale, let’s revisit:

F1_02_Activity_Monitor

There it is. 54. Doing something, as usual. And of course, that something is not exactly apparent from the above sp_who2 output.

This is such a major defect that when I wrote Who is Active I decided to show query text by default even if it slightly slowed things down in some cases. This can be disabled—we’ll get to that in a later post—but I don’t think anyone does that very often.

To understand the differences between the options, consider the following batch:

SELECT *
FROM sys.tables

WAITFOR DELAY '10:10:10'

SELECT *
FROM sys.databases
GO

Running this batch on session 53, then running sp_WhoIsActive on another session, the following is output:

F10_01_default

The [sql_text] column, by default, outputs the text of the statement that is currently running. We can instead decide to see the entire batch, by setting @get_full_inner_text:

EXEC sp_WhoIsActive
    @get_full_inner_text = 1

F10_01_full_text

Why is this option called “get full inner text” rather than “get full batch?” It’s assumed that in most systems (hopefully) the majority of code running will be stored procedure calls rather than ad hoc batches. Let’s see what happens in that case:

USE tempdb
GO

CREATE PROC test_proc
AS
BEGIN
    SELECT *
    FROM sys.tables

    WAITFOR DELAY '10:10:10'

    SELECT *
    FROM sys.databases
END
GO

EXEC test_proc
GO

Running Who is Active with the default mode outputs pretty much the same exact thing as before:

F10_03_default

Setting the @get_full_inner_text option now returns the entire text of the stored procedure—or the “full inner text.”

F10_03_full_text

If you’re wondering what <?query means and why the results are returned in XML format, consider a nicely laid-out piece of code like the following:

SELECT
    *
FROM sys.databases
WHERE
    database_id IN
    (
        SELECT
            dbid
        FROM sys.sysprocesses
        WHERE
            loginame = 'Adam'
    )

If I were to query the various DMVs to get this text without formatting it as XML, the results would be rather ugly once I copied and pasted it out of the SSMS grid:

F10_04_bad_paste

XML in SQL Server Management Studio has the nice property of maintaining the formatting of text nodes. Clicking on the hyperlinked XML in the [sql_text] column, rather than attempting to copy and paste, has a much nicer result:

F10_04_xml

One other consideration is what happens when dealing with nested modules. Consider:

USE tempdb
GO

--Create two "inner" procs

CREATE PROC inner_test_proc1
AS
BEGIN
    WAITFOR DELAY '10:10:10'
END
GO

CREATE PROC inner_test_proc2
AS
BEGIN
    WAITFOR DELAY '20:20:20'
END
GO

--Create one "outer" proc to call the others

CREATE PROC outer_test_proc
    @x INT
AS
BEGIN
    IF @x = 1
        EXEC inner_test_proc1
    ELSE
        EXEC inner_test_proc2
END
GO

--Call the “outer” test proc
EXEC outer_test_proc 2
GO

If you run this batch, then run Who is Active, you’ll see output like the following:

F10_05_outer1

The [sql_text] column shows the WAITFOR command currently running. Its value is always scoped to the innermost scope—the currently running batch or module. But what if we want to get a bit more information about what call the application made to get us to this point? To get more information, use the @get_outer_command option:

EXEC sp_WhoIsActive
    @get_outer_command = 1

F10_05_outer2

This option introduces a new column called [sql_command], which reflects the outer batch or stored procedure call that was issued by the application or user. Like the [sql_text] column, it’s clickable XML, so that you can retrieve the original format of the batch.

Two other considerations for the [sql_text] column:


Prior: Deciding What (Not) To See .... Next: "Planning" For Success
(C) 2017 Adam Machanic