Add a value on their SESSION_CONTEXT for all sys.dm_exec_sessions

Question

When consulting sys.dm_exec_sessions is it possible to attach them their session_context?

I have a backend with a pool of 100 connections. When a connection is assigned to a new incoming request I store the user making that request to the connection's session_context.

exec sp_set_session_context @Key = N'User', @Value = @user_id

Now when I face a deadlock I would like to identify the users involved getting their session_context's 'User' value of each sys.dm_exec_sessions.

Is it possible? Thank you.

Answer 1

While it's normally recommended to use the newer sp_set_session_context, it's impossible to get that information from a different connection, and it's not available in XEvents or in any of the DMVs.

Instead you can use the older CONTEXT_INFO. Do note that it only allows a maximum of 128 bytes, and you may want to check that no-one else is using it, as there are no keys.

DECLARE @userBinary varbinary(128) = CAST(@user_id AS varbinary(128));
SET CONTEXT_INFO @userBinary;

You can use SELECT CONTEXT_INFO(); to view it, or get it from the context_info column in both dm_exec_sessions and dm_exec_requests. It's also available in XEvent sessions, under the context_info field.