SQL Server segnala 701 "Memoria insufficiente per l'esecuzione di questa query" durante l'esecuzione di batch di grandi dimensioni

L'articolo illustra l'errore 701 che può verificarsi quando si esegue un batch di operazioni di grandi dimensioni in SQL Server. Per altre cause dell'errore 701, vedere MSSQLSERVER_701.

Versione originale del prodotto: SQL Server
Numero KB originale: 2001221

Sintomi

In SQL Server, quando si esegue un batch elevato di chiamate rpc (Remote Procedure Call) (ad esempio, decine di migliaia di inserimenti in un singolo batch), l'operazione potrebbe non riuscire con gli errori seguenti segnalati nel log degli errori di 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 si controlla l'output di DBCC MEMORYSTATUS registrato automaticamente nel log degli errori nei messaggi di errore 701, si troveranno voci come quelle seguenti:

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. 

Annotazioni

Si notino le allocazioni di grandi dimensioni per la cache USERSTORE_SXC.

Inoltre, se si esegue una query sulla vista a gestione dinamica (DMV) sys.dm_os_memory_clerks durante l'esecuzione del batch, la single_pages_kb colonna per la USERSTORE_SXC cache mostra una crescita continua in un periodo che genera l'errore 701.

Per un esempio di applicazione che potrebbe potenzialmente presentare questo comportamento, vedere Altre informazioni.

Causa

La quantità di memoria allocata per archiviare una richiesta in SQL Server dipende da:

• Dimensioni del batch (numero di RPC per richiesta).
• Numero di parametri.
• Tipo di parametri.

Per determinati tipi di parametri (ad esempio, sql_variant), SQL Server può salvare le richieste in memoria in modo potenzialmente inefficiente. Quando un client invia un batch di richieste di grandi dimensioni che usano questi tipi di parametri, è possibile inviare più CONTROLLER di dominio in una richiesta. In questo scenario, il server accumula l'intera richiesta in memoria prima dell'esecuzione. Questo potrebbe potenzialmente portare all'errore 701 descritto in Sintomi.

Il problema è molto più diffuso nelle versioni precedenti di SQL Server (soprattutto quando si usa sql_variant il tipo di dati). SQL Server 2008 e versioni successive presentano alcuni miglioramenti della progettazione che riducono la quantità di memoria usata in determinati casi e sono più efficienti nel complesso.

Risoluzione

Per risolvere l'errore, usare uno dei metodi seguenti:

• Ridurre le dimensioni dei batch.
• Modificare i tipi di parametro. Ad esempio, sostituire sql_variant con altri tipi.

La USERSTORE_SXC cache viene usata per le allocazioni a livello di gestione delle connessioni, ad esempio i parametri RPC e la memoria associata agli handle preparati. Quando un client invia una richiesta contenente un batch elevato di chiamate RPC, ognuna potenzialmente che usa un numero elevato di determinati tipi di parametri, ad sql_variantesempio , potrebbe comportare allocazioni eccessive da questa cache, esaurendo così tutta la memoria disponibile.

L'applicazione deve anche essere monitorata per assicurarsi di chiudere gli handle preparati in modo tempestivo. Quando non si chiude questi handle, si impedisce a SQL Server di rilasciare memoria per gli oggetti associati sul lato server.

Ulteriori informazioni

Per riprodurre il problema descritto in questo articolo, creare un'applicazione usando il codice seguente e notare che la USERSTORE_SXC cache aumenta e si riduce man mano che viene eseguito il programma.

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