Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Phase out CONTEXT_INFO() in SQL Server 2016 with SESSION_CONTEXT()


By:   |   Read Comments (1)   |   Related Tips: More > SQL Server 2016

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


Problem

Today, there are many reasons why you would want to maintain the equivalent of session variables within SQL Server. Perhaps you need to identify users (e.g. for auditing purposes) even if they all connect using web or middle-tier application and a common, SQL authentication login. Or you want to persist some information that can be accessible within a module that doesn't accept parameters, such as a trigger or view. Or you simply want to maintain session-scoped data that is costly to look up.

The current solution is to use CONTEXT_INFO(), which allows you to persist a binary value throughout the life of a session. There are four significant problems with CONTEXT_INFO():

  1. It is a single, binary value, and therefore cumbersome to work with; not just explicitly converting data to and from binary, but also the potential complication of storing multiple values in a single binary representation.
  2. It is limited to 128 bytes per connection. That is not a typo: 128 bytes, a significant limitation.
  3. The user has the ability to overwrite the data at any time. This can be a monumental security issue depending on how the information is being used - it can lead to escalation of privileges, SQL injection, and auditing fraud. I demonstrated this in SQL Server 2016 Row Level Security Limitations, Performance and Troubleshooting.
  4. It does not work quite the same in Azure SQL Database - if no value has been set, it returns an arbitrary GUID.
Solution

In SQL Server 2016, there is a new built-in function called SESSION_CONTEXT(), matching Oracle's equivalent. It overcomes the above limitations in the following ways:

  1. It supports key-value pairs - the keys are SYSNAME, while the values are SQL_VARIANT. SQL_VARIANT is admittedly not the easiest thing to work with, but it's a far cry better than bit-masking data into a single binary value.
  2. It supports 256 kilobytes - an improvement of 2,000X over CONTEXT_INFO().
  3. Any key can be marked as read only, so that a value cannot be changed once established.
  4. It will work identically in SQL Server 2016 and Azure SQL Database - if you ask for a key that has not been set, you will always receive NULL.

You set key-value pairs using the stored procedure sys.sp_set_session_context - this takes three arguments:

  • @key SYSNAME (required)
  • @value SQL_VARIANT (required, nullable)
  • @read_only BIT (optional, default 0)

You might be curious how the stored procedure actually works by digging into the source code, but there is no way to do this, even under the DAC connection. It is actually an extended procedure under the covers, even though it doesn't have the xp_ prefix:

EXEC sys.sp_helptext @objname = N'sys.sp_set_session_context';
-- returns (server internal)

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_set_session_context'))
-- returns NULL

SELECT [definition] 
  FROM sys.all_sql_modules
  WHERE [object_id] = OBJECT_ID(N'sys.sp_set_session_context');
-- returs 0 rows

You can't even look up the parameters to the procedure; this also returns 0 rows:

SELECT * 
  FROM sys.all_parameters
  WHERE [object_id] = OBJECT_ID(N'sys.sp_set_session_context');

So let's try a few things through experimentation.

Setting a session variable

This shows the simple setting of a single session variable:

DECLARE @ID INT = 255;
EXEC sys.sp_set_session_context @key = N'ID', @value = @ID;

SELECT SESSION_CONTEXT(N'ID');

Result:

255

Setting (and trying to update) a read-only value

We can set a key to be read-only by using the @read_only argument:

DECLARE @ID INT = 255;
EXEC sys.sp_set_session_context @key = N'ID', @value = @ID, @read_only = 1;

Now, if we try to update it:

EXEC sys.sp_set_session_context @key = N'ID', @value = 400;

We get this error:

Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 10
Cannot set key 'ID' in the session context. The key has been set as read_only for this session.

The only way to "change" a read-only value would be to manually disconnect, reconnect, and then re-establish the value from scratch (of course disconnecting would flush *all* session context values before returning the connection to the pool). Since a user could set any value they wanted upon connect/reconnect, it is expected that you would protect from this by setting any session variables you need to guard using @read_only within a logon trigger, your application's login routine, or some other mechanism either during the initial connection or at least before the user has an opportunity to send any ad hoc SQL to the server.

The importance of the N prefix

I've long been a proponent of properly prefixing Unicode string literals with the N prefix, especially when you don't know what they can contain. For the key name you pass to SESSION_CONTEXT(), you need to be explicit even if you know that the name could never contain non-ASCII:

EXEC sys.sp_set_session_context @key = N'foo', @value = 1;
SELECT SESSION_CONTEXT(N'foo'); -- this works
GO
SELECT SESSION_CONTEXT('foo');  -- no N prefix

Resulting error:

Msg 8116, Level 16, State 1
Argument data type varchar is invalid for argument 1 of session_context function.

Concatenating SQL_VARIANT output

Even if two session variables are equivalent strings, you can't concatenate them together. For example:

EXEC sys.sp_set_session_context @key = N'a', @value = N'blat';
EXEC sys.sp_set_session_context @key = N'b', @value = N'fung';
SELECT SESSION_CONTEXT(N'a') + SESSION_CONTEXT(N'b');
Msg 402, Level 16, State 1
The data types sql_variant and sql_variant are incompatible in the add operator.

If you try CONCAT:

EXEC sys.sp_set_session_context @key = N'a', @value = N'blat';
EXEC sys.sp_set_session_context @key = N'b', @value = N'fung';
SELECT CONCAT(SESSION_CONTEXT(N'a'), SESSION_CONTEXT(N'b'));

You just get a different error message:

Msg 257, Level 16, State 3
Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query.

So, you have to put implicit CONVERT statements anywhere where you're going to use the output for anything other than direct display:

EXEC sys.sp_set_session_context @key = N'a', @value = N'blat';
EXEC sys.sp_set_session_context @key = N'b', @value = N'fung';
SELECT CONCAT(
  CONVERT(NVARCHAR(4000),SESSION_CONTEXT(N'a')), 
  CONVERT(NVARCHAR(4000),SESSION_CONTEXT(N'b'))
);

Result:

blatfung

Overflowing the @key name

Since I suspect @key names could come from all over the place, I wanted to test a name that exceeded the limits of the SYSNAME datatype (128 Unicode characters):

DECLARE @SessionKey NVARCHAR(129) = REPLICATE(N'x', 129);
EXEC sys.sp_set_session_context @key = @SessionKey, @value = N'x';

Error:

Msg 15666, Level 16, State 1, Procedure sp_set_session_context, Line 1
Cannot set key 'xxx[...]xxx' in the session context. The size of the key cannot exceed 256 bytes.

You also can't fake it out by using a VARCHAR parameter instead. This yields the same error:

DECLARE @SessionKey VARCHAR(129) = REPLICATE('x', 129);
EXEC sys.sp_set_session_context @key = @SessionKey, @value = N'x';

Overflowing the SESSION_CONTEXT() buffer

I also wanted to see what would happen if I exceeded the 256kb limitation. So I wrote this code:

DECLARE 
  @fill NCHAR(4000) = REPLICATE(N'X', 4000),
  @i TINYINT = 1,
  @SessionKey SYSNAME;

WHILE @i <= 40
BEGIN
  SET @SessionKey = N'Key' + CONVERT(VARCHAR(11),@i);
  EXEC sys.sp_set_session_context @key = @SessionKey, @value = @fill;
  SET @i += 1;
END

With this example, I was able to store 29 session values, before I got caught with this error:

Msg 15665, Level 16, State 1, Procedure sp_set_session_context, Line 1
The value was not set for key 'Key30' because the total size of keys and values in the session context would exceed the 256 kilobyte limit.

How many keys you will be able to use will fluctuate wildly depending on the data types of your values and the actual contents. That said, don't go out of your way to try to fill this buffer; I suspect in most cases you won't come anywhere near needing 256kb.

MARS support

There are two additional error messages I came across in sys.messages that lead me to believe MARS (Multiple Active Result Sets) will not be supported, even though it is supported with CONTEXT_INFO():

Msg 15667
Session context is not supported with MARS connections.

Msg 15668
Another batch in the session is using session context, new batch is not allowed to start.

In current builds, I could not generate these errors, but it is likely something that will kick in as we get closer to release, or just that I don't have an easy way to simulate a real set of MARS batches using just Management Studio.

Summary

SESSION_CONTEXT() provides the ability to maintain session variables within SQL Server, without many of the limitations inherent in CONTEXT_INFO(). This is another feature that should appear in all editions of SQL Server 2016 and, even as a developer productivity feature only (ignoring the security implication), will serve as another motivator for customers to upgrade. In reality, though, the real use case for this feature is Row-Level Security, and I will explore that in more detail in a future tip.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, November 19, 2015 - 2:54:51 PM - jeff_yao Back To Top

Thanks @Aaron for this tip, which actually I was interested in as well as I wrote about using context_info in my tip https://www.mssqltips.com/sqlservertip/3285/detect-and-automatically-kill-low-priority-blocking-sessions-in-sql-server/

When I saw sql 2016 CTP3 containing this new session_context feature, I immediately played with it, I think it is much better than context_info. However, there is still a big problem with session_context. The problem is:

How can I know what session_context are there with a given session_id?

For context_info, with session_id, we can find out a sessions's context_info via sys.dm_exec_requests or sys.dm_exec_sessions etc 

Most of the time, for a monitoring session to effectively manage existing sessions, we want to know what happens inside those monitored sessions, but currently, session_context is not exposed to outside yet.

I hope MS will solve this issue in RTM version. I put my note here so when other readers read this tip, they may understand the limitation of this new session_context feature.


Learn more about SQL Server tools