Sql server session context limit

Question

We are having problems setting up and clearing session context values.

We get 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 use the core asp.net and dapper for our data access.

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

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

After executing the sql command, we set the session context values ​​to null, close and delete the connection.

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

This request has not yet exceeded 1 MB.

Does anyone know why we get this error?

Answer 1

This is mistake. The following script will reliably reproduce it (from the moment of writing) SQL Server 2017 CU3:

 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 to set value 125: value was not set for the key "125", because the total size of the keys and values ​​in the context of the session would exceed 1 MB.
Error 120: the value was not set for the key "120", because the total size of the keys and values ​​in the context of the session would exceed 1 MB.
Error 115: the value was not set for the key "115" because the total size of the keys and values ​​in the context of the session would exceed 1 MB.
Failed to set to 110: the value was not set for the key "110", because the total size of the keys and values ​​in the context of the session would exceed the 1 MB limit.
Failed to set the value 105: the value was not set for the key "105", because the total size of the keys and values ​​in the context of the session would exceed 1 MB.

The available context size decreases with each cycle. If you continue long enough, it will drop to some minimum value (not necessarily 0). At this stage the inquiry sys.dm_os_memory_cache_counters shows that is used much less than 1 MB, but even this can not be specified more context session.

The workaround is simple: do not set NULL values ​​when you are done, leave the engine to clear them. In the above script, if you set unset to false , you will not notice a session context leak.

I posted this on Microsoft Connect , which was deleted - the problem is now on User Voice , at least until they decide to move things again. Unfortunately, they have reduced all the details from the error report, but the link to this question remains.