Sql Server Session Context Limit

Question

We are having issues setting and clearing out session context values.

We receive the following error: The value was not set for key X because the total size of keys and values in the session context would exceed the 1 MB limit

We are using asp.net core and dapper for our data access.

When opening a connection we execute sp_set_session_context and send 4 keys. 3 which are integers and 1 which is a string.

In testing the string is null and the integers are less than 10.

After executing a sql command we then set the session context values to null, close, and dispose of the connection.

We are using the following query to view memory usage: SELECT SUM([pages_kb]) FROM [sys].[dm_os_memory_cache_counters] WHERE [type] = 'CACHESTORE_SESSION_CONTEXT'

That query has yet to exceed 1MB.

Does anyone know why we are receiving this error?

Answer 1

This is a known bug, which has been fixed in SQL Server 2016 and 2017. The following script will reliably reproduce it:

bool unset = true;
using (var command = new SqlCommand()) {
  command.CommandText = "sp_set_session_context";
  command.CommandType = CommandType.StoredProcedure;
  command.Parameters.Add("@key", SqlDbType.NVarChar, 128);
  command.Parameters.Add("@value", SqlDbType.Variant, -1);
  for (int cycles = 0; cycles != 10; ++cycles) { 
    ++cycles;
    using (var connection = 
      new SqlConnection(@"Data Source=(localdb)\MSSqlLocalDB;Integrated Security=SSPI")
    ) {
      connection.Open();
      // Set as many values as we can
      int keys = 0;
      while (true) {
        command.Connection = connection;
        command.Parameters["@key"].Value = keys.ToString();
        command.Parameters["@value"].Value = new String(' ', 8000);
        try {
          command.ExecuteNonQuery();
          ++keys;
        } catch (SqlException e) {
          Console.WriteLine("Failed setting at {0}: {1}", keys, e.Message);
          break;
        }
      }
      if (unset) {
        // Now unset them
        for (; keys >= 0; --keys) {
          command.Connection = connection;
          command.Parameters["@key"].Value = keys.ToString();
          command.Parameters["@value"].Value = DBNull.Value;
          try {
            command.ExecuteNonQuery();
          } catch (SqlException e) {
            Console.WriteLine("Failed unsetting at {0}: {1}", keys, e.Message);
            break;
          }
        }
      }
    }
  }
}

Output:

Failed setting at 125: The value was not set for key '125' because the total size of keys and values in the session context would exceed the 1 MB limit.
Failed setting at 120: The value was not set for key '120' because the total size of keys and values in the session context would exceed the 1 MB limit.
Failed setting at 115: The value was not set for key '115' because the total size of keys and values in the session context would exceed the 1 MB limit.
Failed setting at 110: The value was not set for key '110' because the total size of keys and values in the session context would exceed the 1 MB limit.
Failed setting at 105: The value was not set for key '105' because the total size of keys and values in the session context would exceed the 1 MB limit.

The available context size decreases with every cycle. If continued for long enough, it will drop to some minimal value (not necessarily 0). At this point, querying sys.dm_os_memory_cache_counters shows that there is far less than 1 MB in use, but even so, no more session context can be set.

This bug has been fixed in SQL Server 2016 SP1 CU8 and SQL Server 2017 CU6. (The fix mentions clearing up the memory issue if you use a NULL value for the key parameter, but using a NULL value for the key isn't allowed in the first place and will produce an error even in older versions. According to my tests it's been fixed for unsetting values.)

For prior versions, there are at least two workarounds:

• don't set the values to NULL when you're done, leave the engine to clear them out. In the above script, if you set unset to false, you will observe no session context getting leaked.
• use a non-NULL sentinel value to mark "removed" values, like an empty string.