O SQL Server relata 701 "Não há memória suficiente para executar esta consulta" ao executar lotes grandes

O artigo discute o erro 701 que pode ocorrer quando você executa um grande lote de operações no SQL Server. Para outras causas do erro 701, consulte MSSQLSERVER_701.

Versão original do produto: SQL Server
Número original do KB: 2001221

Sintomas

No SQL Server, quando você executa um grande lote de RPC (chamadas de procedimento remoto) (por exemplo, dezenas de milhares de inserções em um único lote), a operação pode falhar com os seguintes erros relatados no log de erros do 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.

Se você verificar a saída de DBCC MEMORYSTATUS que é registrada automaticamente no log de erros em mensagens de erro 701, encontrará entradas como as seguintes:

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.

Observação

Observe as grandes alocações para o cache USERSTORE_SXC.

Além disso, se você consultar a DMV (exibição de gerenciamento dinâmico) sys.dm_os_memory_clerks enquanto o lote estiver sendo executado, a single_pages_kb coluna do USERSTORE_SXC cache mostrará um crescimento contínuo durante um período que gera o erro 701.

Para obter um exemplo de um aplicativo que pode exibir esse comportamento, consulte Mais informações.

Causa

A quantidade de memória alocada para armazenar uma solicitação no SQL Server depende de:

  • O tamanho do lote (número de RPCs por solicitação).
  • O número de parâmetros.
  • O tipo de parâmetros.

Para determinados tipos de parâmetros (por exemplo, sql_variant), o SQL Server pode salvar as solicitações na memória de maneira potencialmente ineficiente. Quando um cliente envia um grande lote de solicitações que usam esses tipos de parâmetros, vários RPCs podem ser enviados em uma solicitação. Nesse cenário, o servidor acumula toda a solicitação na memória antes de ser executada. Isso pode levar ao erro 701 discutido em Sintomas.

O problema é muito mais prevalente em versões mais antigas do SQL Server (especialmente ao usar sql_variant o tipo de dados). O SQL Server 2008 e versões posteriores têm alguns aprimoramentos de design que reduzem a quantidade de memória usada em determinados casos e são mais eficientes em geral.

Solução

Para resolver o erro, use um dos seguintes métodos:

  • Reduza o tamanho dos lotes.
  • Altere os tipos de parâmetros. Por exemplo, substitua sql_variant por outros tipos.

O USERSTORE_SXC cache é usado para alocações de nível de gerenciamento de conexão, como parâmetros RPC e a memória associada a identificadores preparados. Quando um cliente envia uma solicitação contendo um grande lote de chamadas RPC, cada uma potencialmente usando um grande número de determinados tipos de parâmetros, como sql_variant, isso pode resultar em alocações excessivas desse cache, esgotando assim toda a memória disponível.

O aplicativo também deve ser monitorado para garantir que você esteja fechando identificadores preparados em tempo hábil. Quando você não fecha esses identificadores, isso impedirá que o SQL Server libere memória para os objetos associados no lado do servidor.

Mais informações

Para reproduzir o problema discutido neste artigo, crie um aplicativo usando o código a seguir e observe que o USERSTORE_SXC cache aumenta e diminui à medida que o programa é executado.

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);
            }
        }
    }
}
  • Last updated on