Sql Server Session Context Limit

0 投票
最新提问 用户: (820 分)

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?

发表于 用户: (100 分)
have you read the MSDN Documentation- sp_set_session_context about the particular function
发表于 用户: (820 分)
@MethodMan Yes I have.

1个回答

0 投票
最新回答 用户: (180 分)

This is a bug. The following script will reliably reproduce it on SQL Server 2016 SP2 CU1:

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=.\sqlserver2016;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.

The workaround is simple: 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.

I've posted this on Microsoft Connect.

发表于 用户: (820 分)
thanks! hopefully they will address the bug soon
欢迎来到 Security Q&A ,有什么不懂的可以尽管在这里提问,你将会收到社区其他成员的回答。
...