次の方法で共有

SQL Server - Errore 701 e Memory Clerk Userstore_sxc

  • [アーティクル]

In questo post descriverò una possibile causa dell'errore 701 ed individuazione della sua origine.

Problema

  • In maniera sporadica alcune query falliscono con il seguente errore
    Error: 701, Severity: 17, State: 123.
    There is insufficient system memory to run this query.
  • Server a 32 bit:
    Microsoft SQL Server 2005 - 9.00.3200.00 (Intel X86)
    Windows NT 5.2 (Build 3790: Service Pack 2)

Dati raccolti

Per questa particolare occorrenza dell’errore 701 sono stati raccolti ed analizzati solo i dati sotto riportati.

  • Informazioni relative a Dbcc Memorystatus (INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage) registrate nell’error log di Sql server al momento della generazione dell’errore 701
  • Memory Dump di Sql server prodotto in modo automatico al verificarsi dell’errore 701 tramite l’abilitazione dei seguenti trace flag:
    • .8004 per generare un dump al verificarsi dell’errore 701
    • .8026 per eliminare automaticamente il trace flag 8004 una volta che un primo dump è già stato prodotto per l’errore 701. Questo eviterà la successiva produzione di dump nel caso in cui l’errore 701 avvenisse numerose volte
    • .2551 per generare un filtered dump
      I trace flag potranno essere abilitati utilizzando dbcc traceon o l’opzione di startup -T come indicato in Trace Flags
  • Informazioni sulla configurazione del server quali output di Sp_configure per Sql server e memoria fisica del server

Analisi dei Dati

Quando si verifica l’errore 701, la prima attività, necessaria a determinarne la causa, è controllare lo stato della memoria di Sql server scritto nell’Error log subito dopo il verificarsi dell’errore.
Di seguito un estratto dell’Error log e informazioni sullo stato della memoria di Sql server:

2008-12-21 14:22:33.61 spid54 Buffer Distribution: Stolen=5097 Free=680 Cached=162318
Database (clean)=831513 Database (dirty)=100
I/O=0, Latched=4
2008-12-21 14:22:33.61 spid54 Buffer Counts: Committed=999712 Target=1048576 Hashed=831617
Internal Reservation=33587 External Reservation=0
Stolen Potential=-7875
Min Free=256 Visible=167936
Available Paging File=34149490688
2008-12-21 14:22:33.61 spid54 Procedure Cache: TotalProcs=3 TotalPages=63 InUsePages=15

Come si può verificare lo Stolen Potential è negativo. Stolen Potential indica il numero massimo di pagine che possono essere sottratte alla Buffer Pool (How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005)

 

In queste condizioni di Stolen Potential negativo, Sql server inizia ad impedire successive richieste di allocazione nell'ambito della Stolen Memory, per cui si possono generare errori 701 o di mancanza di memoria durante l'esecuzione di una query.
La Stolen Memory indica buffers di 8Kb utilizzati per diversi tipi di allocazioni di memoria escluso le allocazioni per le pagine dati. Esempi di Stolen Memory possono essere allocazioni della procedure cache, metadata cache, memoria dell'optimizer o memoria per esecuzione di query.

Esamindano l'output di dbcc memorystatus in corrispondenza della memoria utilizzata dai vari Memory Clerk, è stato possibile individuare un Memory Clerk utilizzante 1.3 Gb per single page allocator (Per una descrizione della Buffer Pool e Memory Clerk riferirsi al post di Slava Oks SQLOS's memory manager and SQL Server's Buffer Pool)

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 = 1309464 KB
MultiPage Allocator = 0 KB

Essendo il sistema a 32 bit, la dimensione della Stolen Memory è limitata dalla dimensione del Virtual Address space ( 2 GB di memoria per processo utente su windows-32 bit non utilizzanti il /3GB Virtual Address Space ), perciò, la quantità di memoria utilizzata dallo User Store USERSTORE_SXC (1,3GB ), ha determinato una diminuzione della parte di memoria utilizzabile per Stolen Memory e pagine dati, determinando l'errore 701.

 

Per verificare su quale esatta allocazione l’errore 701 venisse prodotto, abbiamo analizzato il dump ottenuto tramite i trace flag sopra menzionati

Il dump, contenente le chiamate generanti l’errore 701, mostrava, il tentativo di allocazioni per USERSTORE_SXC (sqlservr!SOS_UserStore::CreateMemoryObject à sqlservr!SQLSinglePageAllocator::AllocatePages ), scaturito da chiamate RPC (sqlservr!processRPC)e fallito per condizione di mancanza di memoria (sqlservr!BPool::RecordFailure).

66f7f5ac 0158fefb sqlservr!BPool::RecordFailure+0x168
66f7f63c 0101890a sqlservr!BPool::Steal+0x15a
66f7f65c 01018a4f sqlservr!SQLSinglePageAllocator::AllocatePages+0x24
66f7f8b0 01018b4b sqlservr!MemoryNode::AllocatePagesInternal+0xd3
66f7f8d4 01042534 sqlservr!MemoryClerkInternal::AllocatePages+0x50
66f7f93c 01301643 sqlservr!MemoryObjectFactory::CreateMemObject+0x319
66f7f974 013015b4 sqlservr!SOS_UserStore::CreateMemoryObject+0x136
66f7f998 01058e69 sqlservr!CCacheableSessionExecState::CreateMemoryObjectPxp+0x82
66f7f9c4 01058947 sqlservr!srv_rpcinfo::PxpAllocate+0x39
66f7fa00 01058784 sqlservr!processRPC<3>+0x31e
66f7fa1c 0103d94c sqlservr!CRPCExecEnv::GetCommandInput+0x70
66f7fb94 0103d690 sqlservr!process_request+0x267
66f7fd4c 01008d68 sqlservr!process_commands+0x2e0

Da notare che una volta prodotta una condizione di mancanza di memoria , l’errore 701 potrebbe prodursi anche su allocazioni diverse da quella che ha causato il problema, in questo caso il dump ha semplicemente rinforzato il concetto di un errore 701 avvenuto durante il tentativo di allocazioni per USERSTORE_SXC

Soluzione

Il Memory Clerk USERSTORE_SXC ha, tra i suoi utilizzi, la gestione della memoria per immagazzinare le richieste RPC e relativi parametri.
Numerose chiamate RPC possono essere inviate al server in un'unica richiesta.
Se il client invia al server numerosi batch RCP di grandi dimensioni in un'unica richiesta, Sql server 2005 accumula tale richiesta in memoria prima di iniziare l’esecuzione.

La memoria utilizzata per immagazzinare questa richiesta, può diventare molto grande a seconda dei tipo di dati utilizzati per i parametri RPC e dalla dimensione dei batch (numero di RPC per richiesta).

La soluzione a questo problema consiste nel

  • ridurre la dimensione dei batch (numero di RPC per richiesta) oppure
  • riconsiderare il tipo di dati dei parametri RPC , ad esempio evitando di utilizzare sql_variant.

Una soluzione temporanea potrebbe essere quella di liberare ad intervalli regolari la memoria associata a USERSTORE_SXC tramite esecuzione di

DBCC FREESYSTEMCACHE ('sxcCacheStore') WITH MARK_IN_USE_FOR_REMOVAL

 

Raffaella Canobbio
Senior Support Escalation Engineer
Microsoft Enterprise SQL Support