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

Article translations Article translations
Close Close
Article ID: 2001221 - View products that this article applies to.
Expand all | Collapse all

Symptoms

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     
Memory Manager
 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      
USERSTORE_SXC (Total)
 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.

Cause

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.

Resolution

 You can use one of the following workarounds:

  • Reduce batch sizes.
  • Change parameter types, for example, replace sql_variants with other types.

More Information

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);
            }
        }
    }
}
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2001221 - Last Review: May 23, 2012 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Express Edition
Keywords: 
KB2001221

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com