SQL Server reports 701: "There is insufficient memory to run this query" when executing large batches

The USERSTORE_SXC cache is used for connection management level allocations such as RPC parameters and the memory that is associated with prepared handles. When a client sends a request containing a large batch of RPC calls, each potentially using a large number of certain types of parameters like sql_variant, it could result in large number of allocations from this cache thereby exhausting all the available memory.

The application should also be monitored to ensure we are closing prepared handles in a timely fashion. When you do not close these handles in a timely manner, it will prevent SQL Server from releasing memory for the associated objects on the server side.

Example Application: To reproduce the problem discussed in this article create an application using the code below in a C# project and notice that the USERSTORE_SXC cache grows and shrinks as the program is executed.

Code snippet:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
 
 
namespace RPCBatching
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable t = new DataTable();
            t.Columns.Add("a", typeof(int));
           
            for(int i=0;i<100000;i++)
                t.Rows.Add(1);
            
            // pre-create the table with "CREATE TABLE t (a sql_Variant)" in a database named as test
            using (SqlConnection conn = new SqlConnection("server=tcp:localhost; integrated security=true; database=test"))
            {
               conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(null, conn);
                da.InsertCommand = new SqlCommand("INSERT INTO t VALUES (@a)", conn);
                da.InsertCommand.Parameters.Add("@a", SqlDbType.Variant, 0, "a");
                da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                da.UpdateBatchSize = 100000;
                da.InsertCommand.CommandTimeout = 12000;
                da.Update(t);
            }
        }
    }
}