SQL Server сообщает 701 "Недостаточно памяти для выполнения этого запроса" при выполнении больших пакетов

В этой статье рассматривается ошибка 701, которая может возникнуть при выполнении большого пакета операций в SQL Server. Другие причины ошибки 701 см. в разделе MSSQLSERVER_701.

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 2001221

Симптомы

В SQL Server при выполнении большого пакета удаленных вызовов процедур (RPC) (например, десятки тысяч вставок в одном пакете) операция может завершиться ошибкой со следующими ошибками, указанными в SQL Server журнале ошибок:

2020-07-04 13:30:45.78 spid56 Error: 701, Severity: 17, State: 193. 
2020-07-04 13:30:45.78 spid56 There is insufficient system memory to run this query.

Если вы проверка выходные данные DBCC MEMORYSTATUS, которые автоматически регистрируются в журнале ошибок в сообщениях об ошибках 701, вы увидите следующие записи:

2020-07-04 13:30:45.74 spid56       Failed allocate pages: FAIL_PAGE_ALLOCATION 1 
2020-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 

2020-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 

2020-07-04 13:30:45.78 spid56      Error: 701, Severity: 17, State: 193. 
2020-07-04 13:30:45.78 spid56      There is insufficient system memory to run this query. 

Кроме того, при запросе sys.dm_os_memory_clerks динамического административного представления (DMV) во время выполнения single_pages_kb пакета в столбце USERSTORE_SXC кэша отображается непрерывное увеличение в течение периода, в результате чего возникает ошибка 701.

Пример приложения, которое может потенциально проявлять такое поведение, см. в разделе Дополнительные сведения.

Причина

Объем памяти, выделенной для хранения запроса в SQL Server, зависит от:

• Размер пакета (количество RPC на запрос).
• Количество параметров.
• Тип параметров.

Для некоторых типов параметров (например, sql_variant) SQL Server могут сохранять запросы в памяти потенциально неэффективным способом. Когда клиент отправляет большой пакет запросов, использующих эти типы параметров, в одном запросе может быть отправлено несколько RPC. В этом сценарии сервер накапливает весь запрос в памяти перед его выполнением. Это может привести к ошибке 701, описанной в разделе Симптомы.

Эта проблема гораздо чаще встречается в более ранних версиях SQL Server (особенно при использовании sql_variant типа данных). SQL Server 2008 и более поздних версиях имеют некоторые усовершенствования, которые сокращают объем используемой памяти в определенных случаях и более эффективны в целом.

Разрешение

Чтобы устранить эту ошибку, используйте один из следующих методов:

• Уменьшение размеров пакетов.
• Изменение типов параметров. Например, замените sql_variant другими типами.

Кэш USERSTORE_SXC используется для распределения уровней управления подключениями, таких как параметры RPC и память, связанная с подготовленными дескрипторами. Когда клиент отправляет запрос, содержащий большой пакет вызовов RPC, каждый из которых может использовать большое количество определенных типов параметров, таких как sql_variant, это может привести к чрезмерному выделению из этого кэша, что приведет к исчерпанию всей доступной памяти.

Приложение также должно отслеживаться, чтобы вы своевременно закрывали подготовленные дескрипторы. Если не закрыть эти дескрипторы, SQL Server не смогут освободить память для связанных объектов на стороне сервера.

Дополнительная информация

Чтобы воспроизвести проблему, описанную в этой статье, создайте приложение с помощью следующего кода и обратите внимание, что USERSTORE_SXC кэш увеличивается и сжимается по мере выполнения программы.

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);
            }
        }
    }
}