Article ID: 2001221 - View products that this article applies to.
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.
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 spid56
2008-07-04 13:30:45.78 spid56 Error: 701, Severity: 17, State: 193.
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:
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:
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.
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2001221 - Last Review: May 23, 2012 - Revision: 3.0