In SQL Server 2005 and SQL Server 2008, when executing a large batched RPC (for eg. tens of thousands of inserts in a single batch), the operation may fail with the following errors reported in SQL Server error log.
2009-07-04 13:30:45.78 spid56 Error: 701, Severity: 17, State: 193.
2009-07-04 13:30:45.78 spid56 There is insufficient system memory to run this query.
If you look at the output of DBCC MEMORYSTATUS that is automatically logged to the error log on 701 error messages, it will have entries similar to the following:
2008-07-04 13:30:45.74 spid56 Failed allocate pages: FAIL_PAGE_ALLOCATION 1
2008-07-04 13:30:45.76 spid58
VM Reserved = 1657936 KB
VM Committed = 66072 KB
AWE Allocated = 2351104 KB ==> ~2.2 GB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
2008-07-04 13:30:45.76 spid56
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 1127848 KB ==> ~1.07 GB
MultiPage Allocator = 0 KB
2008-07-04 13:30:45.78 spid56 Error: 701, Severity: 17, State: 193.
2008-07-04 13:30:45.78 spid56 There is insufficient system memory to run this query.
Note the large allocations for the cache USERSTORE_SXC.
Additionally, if you query the sys.dm_os_memory_clerks dynamic management view (DMV), during the time the batch is getting executed, the single_pages_kb column for the USERSTORE_SXC cache show a continuous growth over a period of time that leads to the 701 error.
For an example application that could potentially exhibit this behavior refer to the More Information section below.
The amount of memory allocated to store a request in SQL Server depends on:
- The batch size (number of RPCs per request)
- Type of parameters.
For certain types of parameters (for example sql_variant), SQL Server can save the requests in memory in a potentially inefficient manner. When a client sends a large batch of requests that use these types of parameters, multiple RPCs can be sent in one request. In this scenario, the server accumulates the whole request in memory before it is executed. This could potentially lead to 701 error discussed in symptoms section.
The issue is far more prevalent in SQL Server 2005 (especially when using sql_variant data type). SQL Server 2008 has some design enhancements which reduce the amount of memory used in certain cases and is more efficient overall.
You can use one of the following workarounds:
- Reduce batch sizes.
- Change parameter types, for example, replace sql_variants with other types.
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.
static void Main(string args)
DataTable t = new DataTable();
// 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"))
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;