Extending sp_WhoIsActive With context_info


If you work with SQL Server you should be familiar with Adam Machanic’s fantastic sp_WhoIsActive stored procedure. This procedure provides high levels of detail about all commands that are currently being executed on a SQL Server instance. I’ve found it immensely helpful for troubleshooting long-running queries and locking/contention issues in live production SQL Server instances. That said, I’ve always wanted to include one extra little bit of information in the dataset that sp_WhoIsActive returns: the id of the user responsible issuing for the database command. Obviously sp_WhoIsActive can report on the SQL or Windows account that was used to initiate the connection to the database and issue the command, but when you utilize connection pooling to let your application talk to SQL Server you end up having all of the connections use the same connection string which means that they all end up using the same SQL/Windows account to connect to the database. I want a way to see the user login from my application (what I’ll call the “application user” for the rest of this post) that is responsible for each of the queries being run. Let’s start by looking at the first problem: making SQL Server aware of the application user that initiated the connection.

Problem 1: Providing the Application User Information To The SQL Connection

In order for sp_WhoIsActive to have a prayer of figuring out what application user owns each database command being executed SQL Server needs to somehow tie that information to each spid that the application uses for running commands. The only reasonable way I’ve found to make this work is to leverage the CONTEXT_INFO SQL function. This function lets associate 128 bytes of data with the current database session. Fortunately this 128 bytes is more than adequate to store a 32 bit integer representing the ID of the application user that is responsible for initiating each database connection from your application. To do this, you’ll need to invoke the CONTEXT_INFO function on each connection that your application uses before you start using it to run commands. If you use a factory or provider pattern for doling out all of the database connections that your application uses (and if you’re not, you should definitely consider doing so) then implementing this CONTEXT_INFO call on each connection is pretty easy. Here’s a snippet of a function that can do this:

private void SetContextInfo(SqlConnection connection, int userId)   
{
    var command = connection.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = "SET CONTEXT_INFO @currentUserId";

    var currentUserIdParameter = command.CreateParameter();
    currentUserIdParameter.ParameterName = "@currentUserId";
    currentUserIdParameter.DbType = DbType.Int32;
    currentUserIdParameter.Size = 4;
    currentUserIdParameter.Value = userId;
    command.Parameters.Add(currentUserIdParameter);
    command.ExecuteNonQuery();
}

This method takes a SqlConnection and user Id integer, then invokes a simple SQL command to call the SET CONTEXT_INFO command passing in the user id value that was provided.  Once this is working, the next step is to surface this user id to the sp_WhoIsActive stored procedure.

Problem 2: Exposing the CONTEXT_INFO data in sp_WhoIsActive Results

As of this writing, the latest version of sp_WhoIsActive (11.11) returns one row per session (SPID) that is actively executing a command. Since context_info is associated with each session, we should be able to see the context_info value for each row that sp_WhoIsActive returns. Unfortunately this info isn’t surfaced by sp_WhoIsActive out-of-the-box. That said there is a way that we can take the output of sp_WhoIsActive and add the context_info for each SPID that is returned.

The sp_WhoIsActive stored procedure exposes a number of optional parameters than affect its behavior. One of these parameters, ‘@destination_table’, lets you specify a name of a table into which you want to insert the dataset that the stored procedure gathers. We can use this to insert the results into a temp table and then join from that temp table to get the context_info for each session. We’ll use the sys.dm_exec_sessions dynamic management view to get the context_info for each session. Here’s how the SQL to do this shapes up:

--only including two columns here for brevity
CREATE TABLE #tempWhoIsActive
(
    [dd hh:mm:ss.mss] [varchar](15) NULL,
    [session_id] [smallint] NOT NULL
)

EXEC sp_WhoIsActive
    @destination_table = '#tempWhoIsActive',
    --define the outout columns to match the temp table definition. See the definition of
    --the sp_WhoIsActive stored procedure for available column names
    @output_column_list = '[dd%][session_id]'

SELECT
    ISNULL(CONVERT(INT, SUBSTRING(s.context_info,1,4)),-1) as [ta_user_id],
    t.[dd hh:mm:ss.mss],
    t.[session_id]
FROM #tempWhoIsActive t
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id

DROP TABLE #tempWhoIsActive

The code above is creating a temp table, invoking the sp_WhoIsActive stored procedure with a pre-defined set of output columns to load that temp table, then joining the temp table to sys.dm_exec_sessions view on the session_id. By joining to the sys.dm_exec_sessions view we can access the context_info value for each session.  Note the use of the SUBSTRING and CONVERT functions to extract the first 4 bytes of the 128 byte context_info value and convert it to an integer. If you were putting some other type of data into context_info you’d need to extract and convert it accordingly. Also note the use of the ‘ISNULL’ function, as any SPIDs being run directly via SSMS, SQL Agents, or other types of applications talking to the database server might not have any context_info value set.

Now that I have the application user id for each SPID, I can join to the appropriate application tables to look up the user login and any other pertinent information I might need about the user. I can also now take this code and create a separate stored procedure to wrap the sp_WhoIsActive call and include the application user information from context_info to make this a bit easier to invoke in a pinch to see what’s going on at the database tier and correlate that activity to actual users of the application.

Leave a comment

Your email address will not be published. Required fields are marked *