Condividi tramite

Usare il comando DBCC MEMORYSTATUS per monitorare l'utilizzo della memoria in SQL Server

  • Articolo

Questo articolo descrive come usare il DBCC MEMORYSTATUS comando per monitorare l'utilizzo della memoria.

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

Introduzione

Il DBCC MEMORYSTATUS comando fornisce uno snapshot dello stato corrente della memoria microsoft SQL Server e del sistema operativo. Fornisce uno degli output più dettagliati della distribuzione e dell'utilizzo della memoria in SQL Server. È possibile usare l'output per risolvere i problemi di utilizzo della memoria in SQL Server o per risolvere specifici errori di memoria insufficiente. Molti errori di memoria insufficiente generano automaticamente questo output nel log degli errori. Se si verifica un errore correlato a una condizione di memoria insufficiente, è possibile eseguire il DBCC MEMORYSTATUS comando e fornire l'output quando si contatta supporto tecnico Microsoft.

L'output del comando include sezioni per la gestione della DBCC MEMORYSTATUS memoria, l'utilizzo della memoria, le informazioni di aggregazione della memoria, le informazioni sul pool di buffer e le informazioni sulla cache delle procedure. Descrive anche l'output di oggetti di memoria globali, oggetti di memoria di query, ottimizzazione e broker di memoria.

Nota

Monitor prestazioni (PerfMon) e Gestione attività non tiene conto dell'utilizzo completo della memoria se l'opzione Pagine bloccate in memoria è abilitata. Non sono presenti contatori delle prestazioni che mostrano l'utilizzo della memoria dell'API AWE (Address Windowing Extensions).

Importante

Il DBCC MEMORYSTATUS comando deve essere uno strumento di diagnostica per supporto tecnico Microsoft. Il formato dell'output e il livello di dettaglio fornito sono soggetti a modifiche tra Service Pack e versioni del prodotto. La funzionalità fornita dal DBCC MEMORYSTATUS comando potrebbe essere sostituita da un meccanismo diverso nelle versioni successive del prodotto. Pertanto, nelle versioni successive del prodotto, questo comando potrebbe non funzionare più. Non verranno forniti avvisi aggiuntivi prima che questo comando venga modificato o rimosso. Pertanto, le applicazioni che usano questo comando potrebbero interrompersi senza avviso.

L'output del DBCC MEMORYSTATUS comando è stato modificato rispetto alle versioni precedenti di SQL Server. Attualmente contiene diverse tabelle non disponibili nelle versioni precedenti del prodotto.

Come usare DBCC MEMORYSTATUS

DBCC MEMORYSTATUSviene in genere usato per analizzare i problemi di memoria insufficiente segnalati da SQL Server. La memoria insufficiente può verificarsi se si verifica una pressione di memoria esterna dall'esterno del processo di SQL Server o una pressione interna che ha origine all'interno del processo. La pressione interna può essere causata dal motore di database SQL Server o da altri componenti eseguiti all'interno del processo,ad esempio server collegati, indirizzi XP, SQLCLR, protezione dalle intrusioni o software antivirus. Per altre informazioni su come risolvere la pressione della memoria, vedere Risolvere i problemi di memoria insufficiente o memoria insufficiente in SQL Server.

Di seguito sono riportati i passaggi generali per l'uso del comando e l'interpretazione dei risultati. Scenari specifici potrebbero richiedere un approccio leggermente diverso all'output, ma l'approccio generale è descritto qui.

  1. Eseguire il comando DBCC MEMORYSTATUS.
  2. Usare le sezioni Processi/Conteggi di sistema e Gestione memoria per stabilire se è presente una pressione di memoria esterna, ad esempio se la memoria fisica o virtuale del computer è insufficiente o se il working set SQL Server viene estratto. Usare anche queste sezioni per determinare la quantità di memoria allocata dal motore di database SQL Server rispetto alla memoria complessiva del sistema.
  3. Se si verifica una pressione di memoria esterna, provare a ridurre l'utilizzo della memoria da parte di altre applicazioni e dal sistema operativo oppure aggiungere più RAM.
  4. Se si stabilisce che il motore di SQL Server usa la maggior parte della memoria (pressione interna della memoria), è possibile usare le sezioni rimanenti di DBCC MEMORYSTATUS per identificare quali componenti (Memory clerk, Cachestore, UserStore o Objectstore) sono i principali collaboratori a questo utilizzo della memoria.
  5. Esaminare ogni componente: MEMORYCLEARK, CACHESTORE, USERSTOREe OBJECTSTORE. Esaminare il valore Pages Allocated per determinare la quantità di memoria usata dal componente all'interno di SQL Server. Per una breve descrizione della maggior parte dei componenti di memoria del motore di database, vedere la tabella Tipi di clerk di memoria .
    1. In rari casi, l'allocazione è un'allocazione virtuale diretta anziché passare attraverso il gestore di memoria SQL Server. In questi casi, esaminare il valore Commit della macchina virtuale nel componente specifico anziché Pagine allocate.
    2. Se il computer usa NUMA, alcuni componenti di memoria vengono suddivisi per nodo. Ad esempio, è possibile osservare OBJECTSTORE_LOCK_MANAGER (node 0), OBJECTSTORE_LOCK_MANAGER (node 1), OBJECTSTORE_LOCK_MANAGER (node 2)e così via e infine osservare un valore sommato di ogni nodo in OBJECTSTORE_LOCK_MANAGER (Total). Il punto di partenza migliore è la sezione che segnala il valore totale e quindi esamina la suddivisione, se necessario. Per altre informazioni, vedere Utilizzo della memoria con i nodi NUMA.
  6. Alcune sezioni di DBCC MEMORYSTATUS forniscono informazioni dettagliate e specializzate su specifici allocatori di memoria. È possibile usare queste sezioni per comprendere altri dettagli e visualizzare un'ulteriore suddivisione delle allocazioni all'interno di un impiegato di memoria. Esempi di queste sezioni includono pool di buffer (cache di dati e indici), cache delle procedure/piano, oggetti di memoria query (concessioni di memoria), coda di ottimizzazione e gateway di piccole e medie e grandi dimensioni (memoria optimizer). Se si sa già che un componente specifico della memoria in SQL Server è l'origine della pressione della memoria, potrebbe essere preferibile passare direttamente a tale sezione specifica. Ad esempio, se si è stabilito in un altro modo che esiste un utilizzo elevato delle concessioni di memoria che causano errori di memoria, è possibile esaminare la sezione Query memory objects (Oggetti di memoria query).

Nella parte restante di questo articolo vengono descritti alcuni dei contatori utili nell'output DBCC MEMORYSTATUS che consentono di diagnosticare i problemi di memoria in modo più efficace.

Conteggi di processi/sistemi

Questa sezione fornisce un output di esempio in formato tabulare e ne descrive i valori.

Process/System Counts                Value
------------------------------------ ------------
Available Physical Memory            5060247552
Available Virtual Memory             140710048014336
Available Paging File                7066804224
Working Set                          430026752
Percent of Committed Memory in WS    100
Page Faults                          151138
System physical memory high          1
System physical memory low           0
Process physical memory low          0
Process virtual memory low           0

L'elenco seguente illustra i valori e le relative descrizioni:

  • Memoria fisica disponibile: questo valore mostra la quantità complessiva di memoria disponibile nel computer. Nell'esempio la memoria libera è di 5.060.247.552 byte.
  • Memoria virtuale disponibile: questo valore mostra che la quantità complessiva di memoria virtuale disponibile per SQL Server processo è 140.710.048.014.336 byte (128 TB). Per altre informazioni, vedere Limiti di memoria e spazio degli indirizzi.
  • File di paging disponibile: questo valore mostra lo spazio disponibile del file di paging. Nell'esempio il valore è 7.066.804.224 byte.
  • Working Set: questo valore mostra la quantità complessiva di memoria virtuale che il processo SQL Server ha nella RAM (non è impostato su paging) è di 430.026.752 byte.
  • Percentuale della memoria con commit in WS: questo valore mostra la percentuale di memoria virtuale allocata SQL Server risiede nella RAM (o è working set). Il valore del 100% indica che tutta la memoria di cui è stato eseguito il commit è archiviata nella RAM e lo 0% di essa viene eseguita una pagina.
  • Errori di pagina: questo valore mostra la quantità complessiva di errori di pagina hard e soft per il SQL Server. Nell'esempio il valore è 151.138.

I quattro valori rimanenti sono binari o booleani.

  • Il valore elevato della memoria fisica di sistema pari a 1 indica che SQL Server considera elevata la memoria fisica disponibile nel computer. Ecco perché il valore di Memoria fisica di sistema bassa è 0, il che significa che non c'è memoria insufficiente. Logica simile viene applicata a Elaborazione memoria fisica insufficiente e Elaborazione memoria virtuale bassa, dove 0 significa che è false e 1 significa che è vero. In questo esempio entrambi i valori sono 0, il che significa che è disponibile una quantità sufficiente di memoria fisica e virtuale per il processo di SQL Server.

Gestione memoria

In questa sezione viene fornito un output di esempio di Gestione memoria che mostra l'utilizzo complessivo della memoria per SQL Server.

Memory Manager             KB
-------------------------- --------------------
VM Reserved                36228032
VM Committed               326188
Locked Pages Allocated     0
Large Pages Allocated      0
Emergency Memory           1024
Emergency Memory In Use    16
Target Committed           14210416
Current Committed          326192
Pages Allocated            161904
Pages Reserved             0
Pages Free                 5056
Pages In Use               286928
Page Alloc Potential       15650992
NUMA Growth Phase          0
Last OOM Factor            0
Last OS Error              0

L'elenco seguente illustra i valori nell'output e le relative descrizioni:

  • VM riservata: questo valore mostra la quantità complessiva di spazio di indirizzi virtuali (VAS) o di memoria virtuale (VM) riservata SQL Server. La prenotazione di memoria virtuale non usa effettivamente la memoria fisica; significa semplicemente che gli indirizzi virtuali vengono messi da parte all'interno del vas di grandi dimensioni. Per altre informazioni, vedere VirtualAlloc(), MEM_RESERVE.

  • Commit della macchina virtuale: questo valore mostra la quantità complessiva di memoria virtuale (VM) di cui SQL Server ha eseguito il commit (in KB). Ciò significa che la memoria usata dal processo è supportata dalla memoria fisica o meno frequentemente dal file di pagina. Gli indirizzi di memoria precedentemente riservati sono ora supportati da una risorsa di archiviazione fisica; ovvero sono allocati. Se le pagine bloccate in memoria sono abilitate, SQL Server usa un metodo alternativo per allocare memoria, l'API AWE e la maggior parte della memoria non viene riflessa in questo contatore. Per tali allocazioni, vedere [Pagine bloccate allocate](#Locked pagine allocate). Per altre informazioni, vedere VirtualAlloc(), MEM_COMMIT.

  • Pagine allocate: questo valore mostra il numero totale di pagine di memoria allocate da SQL Server motore di database.

  • Pagine bloccate allocate: questo valore rappresenta la quantità di memoria, espressa in kilobyte (KB), che SQL Server ha allocato e bloccato nella RAM fisica usando l'API AWE. Indica la quantità di memoria che SQL Server sta attivamente usando e ha richiesto di rimanere in memoria per ottimizzare le prestazioni. Bloccando le pagine in memoria, SQL Server garantisce che le pagine critiche del database siano immediatamente disponibili e non siano scambiate su disco. Per altre informazioni, vedere Memoria AWE (Address Windows Extensions). Il valore zero indica che la funzionalità "pagine bloccate in memoria" è attualmente disabilitata e SQL Server usa invece la memoria virtuale. In questo caso, il valore VM Committed rappresenta la memoria allocata a SQL Server.

  • Pagine di grandi dimensioni allocate: questo valore rappresenta la quantità di memoria allocata da SQL Server usando pagine di grandi dimensioni. Pagine di grandi dimensioni è una funzionalità di gestione della memoria fornita dal sistema operativo. Invece di usare le dimensioni standard della pagina (in genere 4 KB), questa funzionalità usa dimensioni di pagina maggiori, ad esempio 2 MB o 4 MB. Il valore zero indica che la funzionalità non è abilitata. Per altre informazioni, vedere Alloc virtuali(), MEM_LARGE_PAGES.

  • Commit di destinazione: questo valore indica la quantità di memoria di destinazione di cui SQL Server mira ad avere eseguito il commit, una quantità ideale di memoria che SQL Server potrebbe utilizzare, in base al carico di lavoro recente.

  • Commit corrente: questo valore indica la quantità di memoria del sistema operativo (in KB) di cui è attualmente stato eseguito il commit in Gestione memoria SQL Server (allocato nell'archivio fisico). Questo valore include "pagine bloccate in memoria" (API AWE) o memoria virtuale. Di conseguenza, questo valore è vicino o uguale a VM Committed o Locked Pages Allocate. Si noti che quando SQL Server usa l'API AWE, una parte della memoria viene allocata da Gestione memoria virtuale del sistema operativo e verrà riflessa come VM Committed.

  • Fase di crescita NUMA: questo valore indica se SQL Server si trova attualmente in una fase di crescita NUMA. Per altre informazioni sull'aumento iniziale della memoria quando nel computer sono presenti nodi NUMA, vedere Funzionamento: SQL Server (blocchi di memoria NUMA locali, esterni e esterni).

  • Ultimo errore del sistema operativo: questo valore mostra l'ultimo errore del sistema operativo che si è verificato quando si è verificata una pressione di memoria sul sistema. SQL Server registra l'errore del sistema operativo e lo mostra nell'output. Per un elenco completo degli errori del sistema operativo, vedere Codici di errore di sistema.

Utilizzo della memoria con nodi NUMA

La sezione Gestione memoria è seguita da un riepilogo dell'utilizzo della memoria per ogni nodo di memoria. In un sistema con accesso alla memoria non uniforme (NUMA) è presente una voce del nodo di memoria corrispondente per ogni nodo NUMA hardware. In un sistema SMP è presente una singola voce del nodo di memoria. Lo stesso modello viene applicato ad altre sezioni di memoria.

Memory node Id = 0      KB
----------------------- -----------
VM Reserved             21289792
VM Committed            272808
Locked Pages Allocated  0
Pages Allocated         168904
Pages Free              3040
Target Committed        6664712
Current Committed       272808
Foreign Committed       0
Away Committed          0
Taken Away Committed    0

Nota

  • Il Memory node Id valore potrebbe non corrispondere all'ID del nodo hardware.
  • Questi valori mostrano la memoria allocata dai thread in esecuzione in questo nodo NUMA. Questi valori non sono la memoria locale del nodo NUMA.
  • Le somme dei valori riservati della macchina virtuale e dei valori di commit della macchina virtuale in tutti i nodi di memoria saranno leggermente inferiori ai valori corrispondenti riportati nella tabella Gestione memoria.
  • Il nodo NUMA 64 (nodo 64) è riservato all'applicazione livello dati ed è raramente interessante nell'analisi della memoria perché questa connessione usa risorse di memoria limitate. Per altre informazioni sulla connessione amministratore dedicata, vedere Connessione di diagnostica per gli amministratori di database.

L'elenco seguente illustra i valori nella tabella di output e le relative descrizioni:

  • VM riservata: mostra lo spazio di indirizzi virtuali riservato dai thread in esecuzione in questo nodo.
  • Commit della macchina virtuale: mostra il valore VAS di cui viene eseguito il commit dai thread in esecuzione in questo nodo.

Memoria aggregata

La tabella seguente contiene informazioni di memoria aggregate per ogni tipo di clerk e nodo NUMA. Per un sistema abilitato per NUMA, potrebbe essere visualizzato un output simile al seguente:

MEMORYCLERK_SQLGENERAL (node 0) KB
------------------------------  --------------------
VM Reserved                     0
VM Committed                    0
Locked Pages Allocated          0
SM Reserved                     0
SM Commited                     0
Pages Allocated                 5416

MEMORYCLERK_SQLGENERAL (node 1) KB
------------------------------- --------------------
VM Reserved                     0
VM Committed                    0
Locked Pages Allocated          0
SM Reserved                     0
SM Commited                     0
Pages Allocated                 136

MEMORYCLERK_SQLGENERAL (Total)  KB
------------------------------- --------------------
VM Reserved                     0
VM Committed                    0
Locked Pages Allocated          0
SM Reserved                     0
SM Commited                     0
Pages Allocated                 5552

Il valore di Pages Allocated mostra il numero complessivo di pagine di memoria allocate a da un componente specifico (impiegato di memoria, userstore, objectstore o archivio cache).

Nota

Questi ID nodo corrispondono alla configurazione del nodo NUMA del computer che esegue SQL Server. Gli ID nodo includono i possibili nodi NUMA software definiti in cima ai nodi NUMA hardware o in un sistema SMP. Per trovare il mapping tra ID nodo e CPU per ogni nodo, vedere Information Event ID 17152. Questo evento viene registrato nel registro applicazioni in Visualizzatore eventi quando si avvia SQL Server.

Per un sistema SMP, viene visualizzata una sola tabella per ogni tipo di impiegato, senza contare node = 64 usato da APPLICAZIONE. Questa tabella è simile all'esempio seguente.

MEMORYCLERK_SQLGENERAL (Total)     KB
--------------------------------- --------------------
VM Reserved                        0
VM Committed                       0
AWE Allocated                      0
SM Reserved                        0
SM Commited                        0
Pages Allocated                    2928

Altre informazioni in queste tabelle riguardano la memoria condivisa:

  • SM riservato: mostra il servizio di archiviazione con mapping alla memoria riservato da tutti gli impiegati di questo tipo che usano l'API file mappati alla memoria. Questa API è nota anche come memoria condivisa.
  • SM Committed: mostra il valore VAS di cui viene eseguito il commit da tutti gli impiegati di questo tipo che usano l'API file mappati alla memoria.

Come metodo alternativo, è possibile ottenere informazioni di riepilogo per ogni tipo di clerk per tutti i nodi di memoria usando la sys.dm_os_memory_clerks DMV (Dynamic Management View). A tale scopo, eseguire la query seguente:

SELECT
  TYPE,
  SUM(virtual_memory_reserved_kb) AS [VM Reserved],
  SUM(virtual_memory_committed_kb) AS [VM Committed],
  SUM(awe_allocated_kb) AS [AWE Allocated],
  SUM(shared_memory_reserved_kb) AS [SM Reserved],
  SUM(shared_memory_committed_kb) AS [SM Committed],
  -- SUM(multi_pages_kb) AS [MultiPage Allocator],          /*Applies to: SQL Server 2008   (10.0.x) through SQL Server 2008 R2 (10.50.x).*/
  -- SUM(single_pages_kb) AS [SinlgePage Allocator],        /*Applies to: SQL Server 2008   (10.0.x) through SQL Server 2008 R2 (10.50.x).*/
  SUM(pages_kb) AS [Page Allocated]                      /*Applies to: SQL Server 2012 (11.  x) and later.*/
FROM sys.dm_os_memory_clerks
GROUP BY TYPE

Dettagli del pool di buffer

Si tratta di una sezione importante che fornisce una suddivisione dei dati di stati diversi e delle pagine di indice all'interno del pool di buffer, nota anche come cache dei dati. Nella tabella di output seguente sono elencati i dettagli sul pool di buffer e altre informazioni.

Buffer Pool                                       Pages
------------------------------------------------- ---------
Database                                          5404
Simulated                                         0
Target                                            16384000
Dirty                                             298
In IO                                             0
Latched                                           0
IO error                                          125
In Internal Pool                                  0
Page Life Expectancy                              3965

L'elenco seguente illustra i valori nell'output e le relative descrizioni:

  • Database: mostra il numero di buffer (pagine) con contenuto del database (dati e pagine di indice).
  • Destinazione: mostra le dimensioni di destinazione del pool di buffer (conteggio buffer). Vedere Memoria con commit di destinazione nelle sezioni precedenti di questo articolo.
  • Dirty: mostra le pagine che hanno contenuto del database e sono state modificate. Questi buffer contengono modifiche che devono essere scaricate su disco in genere dal processo del checkpoint.
  • In I/O: mostra i buffer in attesa di un'operazione di I/O in sospeso. Ciò significa che il contenuto di queste pagine viene scritto o letto dalla risorsa di archiviazione.
  • Latched: mostra i buffer latch. Un buffer viene latch quando un thread legge o modifica il contenuto di una pagina. Un buffer viene anche latch quando la pagina viene letta dal disco o scritta su disco. Un latch viene usato per mantenere la coerenza fisica dei dati nella pagina durante la lettura o la modifica. Al contrario, viene usato un blocco per mantenere la coerenza logica e transazionale.
  • Errore di I/O: mostra il numero di buffer che potrebbero aver rilevato errori del sistema operativo correlati a I/O (questo non indica necessariamente un problema).
  • Aspettativa di vita della pagina: questo contatore misura la quantità di tempo in secondi in cui la pagina meno recente è rimasta nel pool di buffer.

È possibile ottenere informazioni dettagliate sul pool di buffer per le pagine di database usando la sys.dm_os_buffer_descriptors DMV. Tuttavia, usare questa DMV con cautela perché può essere eseguita a lungo e produrre un output enorme se al server basato su SQL Server è consentito disporre di una grande quantità di RAM.

Cache dei piani

In questa sezione viene illustrata la cache dei piani definita in precedenza cache delle procedure.

Procedure Cache         Value
----------------------- -----------
TotalProcs              4
TotalPages              25
InUsePages              0

L'elenco seguente illustra i valori nell'output e le relative descrizioni:

  • TotalProcs: questo valore mostra il totale degli oggetti memorizzati nella cache attualmente nella cache delle procedure. Questo valore corrisponde al numero di voci nella sys.dm_exec_cached_plans DMV.

    Nota

    A causa della natura dinamica di queste informazioni, la corrispondenza potrebbe non essere esatta. È possibile usare PerfMon per monitorare l'oggetto SQL Server: Plan Cache e la sys.dm_exec_cached_plans DMV per informazioni dettagliate sul tipo di oggetti memorizzati nella cache, ad esempio trigger, routine e oggetti ad hoc.

  • TotalPages: mostra le pagine cumulative usate per archiviare tutti gli oggetti memorizzati nella cache del piano o della routine. È possibile moltiplicare questo numero per 8 KB per ottenere il valore espresso in KB.

  • InUsePages: mostra le pagine nella cache delle procedure che appartengono a procedure attualmente attive. Queste pagine non possono essere eliminate.

Oggetti memoria globale

Questa sezione contiene informazioni sui vari oggetti di memoria globali e sulla quantità di memoria usata.

Global Memory Objects               Buffers
----------------------------------  ----------------
Resource                            576
Locks                               96
XDES                                61
DirtyPageTracking                   52
SETLS                               8
SubpDesc Allocators                 8
SE SchemaManager                    139
SE Column Metadata Cache            159
SE Column Metadata Cache Store      2
SE Column Store Metadata Cache      8
SQLCache                            224
Replication                         2
ServerGlobal                        1509
XP Global                           2
SortTables                          3

L'elenco seguente illustra i valori nell'output e le relative descrizioni:

  • Risorsa: mostra la memoria usata dall'oggetto Resource. Viene usato dal motore di archiviazione per varie strutture a livello di server.
  • Blocchi: mostra la memoria usata da Gestione blocchi.
  • XDES: mostra la memoria usata da Gestione transazioni.
  • SETLS: mostra la memoria usata per allocare la struttura per thread specifica del motore di archiviazione che usa LLS (Thread Local Storage). Per altre informazioni, vedere Archiviazione locale thread.
  • SubpDesc Allocators: mostra la memoria usata per la gestione di sottoprocessi per query parallele, operazioni di backup, operazioni di ripristino, operazioni di database, operazioni sui file, mirroring e cursori asincroni. Questi sottoprocessi sono noti anche come "processi paralleli".
  • SE SchemaManager: mostra la memoria usata da Schema Manager per archiviare i metadati specifici del motore di archiviazione.
  • SQLCache: mostra la memoria usata per salvare il testo di istruzioni ad hoc e preparate.
  • Replica: mostra la memoria usata dal server per i sottosistemi di replica interni.
  • ServerGlobal: mostra l'oggetto memoria del server globale usato in modo generico da diversi sottosistemi.
  • XP Globale: mostra la memoria usata dalle stored procedure estese.
  • SortTables: mostra la memoria usata dalle tabelle di ordinamento.

Eseguire query su oggetti di memoria

Questa sezione descrive le informazioni sulla concessione di memoria query. Include anche uno snapshot dell'utilizzo della memoria di query. La memoria di query è nota anche come "memoria dell'area di lavoro".

Query Memory Objects (default)           Value
---------------------------------------- -------
Grants                                    0
Waiting                                   0
Available                                 436307
Current Max                               436307
Future Max                                436307
Physical Max                              436307
Next Request                              0
Waiting For                               0
Cost                                      0
Timeout                                   0
Wait Time                                 0

Se le dimensioni e il costo di una query soddisfano le soglie di memoria delle query "piccole", la query viene inserita in una piccola coda di query. Questo comportamento impedisce che le query più piccole vengano ritardate rispetto alle query di dimensioni maggiori già presenti nella coda.

L'elenco seguente illustra i valori nell'output e le relative descrizioni:

  • Concessioni: indica il numero di query in esecuzione con concessioni di memoria.
  • In attesa: mostra il numero di query in attesa di ottenere concessioni di memoria.
  • Disponibile: mostra i buffer disponibili per le query per l'uso come area di lavoro hash e l'area di lavoro di ordinamento. Il Available valore viene aggiornato periodicamente.
  • Richiesta successiva: mostra le dimensioni della richiesta di memoria, nei buffer, per la query in attesa successiva.
  • Attesa: mostra la quantità di memoria che deve essere disponibile per eseguire la query a cui fa riferimento il valore Della richiesta successiva. Il valore Waiting For è il Next Request valore moltiplicato per un fattore headroom. Questo valore garantisce in modo efficace che una quantità specifica di memoria sarà disponibile quando viene eseguita la query in attesa successiva.
  • Costo: mostra il costo della query in attesa successiva.
  • Timeout: mostra il timeout, espresso in secondi, per la query in attesa successiva.
  • Tempo di attesa: mostra il tempo trascorso, in millisecondi, dal momento che la query in attesa successiva è stata inserita nella coda.
  • Max corrente: mostra il limite di memoria complessivo per l'esecuzione di query. Questo valore è il limite combinato sia per la coda di query di grandi dimensioni che per la coda di query di piccole dimensioni.

Per altre informazioni sulle concessioni di memoria, sul significato di questi valori e su come risolvere i problemi relativi alle concessioni di memoria, vedere Risolvere i problemi di prestazioni lente o di memoria insufficiente causati dalle concessioni di memoria in SQL Server.

Memoria di ottimizzazione

Le query vengono inviate al server per la compilazione. Il processo di compilazione include analisi, algebrizzazione e ottimizzazione. Le query vengono classificate in base alla memoria utilizzata da ogni query durante il processo di compilazione.

Nota

Questa quantità non include la memoria necessaria per eseguire la query.

All'avvio di una query non esiste alcun limite per il numero di query che è possibile compilare. Man mano che il consumo di memoria aumenta e raggiunge una soglia, la query deve passare un gateway per continuare. Esiste un limite decrescente di query compilate contemporaneamente dopo ogni gateway. Le dimensioni di ogni gateway dipendono dalla piattaforma e dal carico. Le dimensioni del gateway vengono scelte per ottimizzare la scalabilità e la velocità effettiva.

Se la query non riesce a passare un gateway, attende fino a quando la memoria non è disponibile o restituisce un errore di timeout (errore 8628). Inoltre, la query potrebbe non acquisire un gateway se si annulla la query o se viene rilevato un deadlock. Se la query supera più gateway, non rilascia i gateway più piccoli fino al completamento del processo di compilazione.

Questo comportamento consente di eseguire contemporaneamente solo alcune compilazioni a elevato utilizzo di memoria. Inoltre, questo comportamento ottimizza la velocità effettiva per le query più piccole.

La tabella successiva fornisce i dettagli delle attese di memoria che si verificano a causa di memoria insufficiente per l'ottimizzazione delle query. La memoria interna rappresenta la memoria dell'utilità di ottimizzazione usata dalle query di sistema, mentre l'impostazione predefinita segnala la memoria di ottimizzazione per le query utente o dell'applicazione.

Optimization Queue (internal)      Value
---------------------------------- ----------------
Overall Memory                     4013162496
Target Memory                      3673882624
Last Notification                  1
Timeout                            6
Early Termination Factor           5

Small Gateway (internal)           Value
---------------------------------- ----------------
Configured Units                   32
Available Units                    32
Acquires                           0
Waiters                            0
Threshold Factor                   380000
Threshold                          380000

Medium Gateway (internal)          Value
---------------------------------- ----------------
Configured Units                   8
Available Units                    8
Acquires                           0
Waiters                            0
Threshold Factor                   12
Threshold                          -1

Big Gateway (internal)             Value
---------------------------------- ----------------
Configured Units                   1
Available Units                    1
Acquires                           0
Waiters                            0
Threshold Factor                   8
Threshold                          -1

Optimization Queue (default)       Value
---------------------------------- ----------------
Overall Memory                     4013162496
Target Memory                      3542319104
Last Notification                  1
Timeout                            6
Early Termination Factor           5

Small Gateway (default)            Value
---------------------------------- ----------------
Configured Units                   32
Available Units                    32
Acquires                           0
Waiters                            0
Threshold Factor                   380000
Threshold                          380000

Medium Gateway (default)           Value
---------------------------------- ----------------
Configured Units                   8
Available Units                    8
Acquires                           0
Waiters                            2
Threshold Factor                   12
Threshold                          -1

Big Gateway (default)              Value
---------------------------------- ----------------
Configured Units                   1
Available Units                    1
Acquires                           0
Waiters                            0
Threshold Factor                   8
Threshold                          -1

Ecco una descrizione di alcuni di questi valori:

  • Unità configurate : indica il numero di query simultanee che possono usare la memoria di compilazione dal gateway. Nell'esempio, 32 query simultanee possono usare la memoria del gateway Small (impostazione predefinita), otto query simultanee dal gateway medio e una query dal gateway Big. Come accennato in precedenza, se una query richiede più memoria di quella che il gateway Small può allocare, passa a Gateway medio e tale query viene conteggiata per avere preso un'unità in entrambi i gateway. Maggiore è la quantità di memoria di compilazione necessaria per una query, minore è il numero di unità configurate in un gateway.
  • Unità disponibili : indica il numero di slot o unità disponibili per le query simultanee da compilare dall'elenco delle unità configurate. Ad esempio, se sono disponibili 32 unità, ma tre query usano attualmente la memoria di compilazione, sarà Available Units 32 meno 3 o 29 unità.
  • Acquisisce : indica il numero di unità o slot acquisiti dalle query da compilare. Se tre query usano attualmente la memoria da un gateway, acquisisce = 3.
  • Camerieri : indica il numero di query in attesa di memoria di compilazione in un gateway. Se tutte le unità in un gateway sono esaurite, il valore waiters è diverso da zero che mostra il numero di query in attesa.
  • Soglia : indica un limite di memoria del gateway che determina da dove una query ottiene la memoria o il gateway in cui si trova. Se una query non richiede più del valore soglia, rimane nel gateway di piccole dimensioni (una query inizia sempre con il gateway di piccole dimensioni). Se ha bisogno di più memoria per la compilazione, passerebbe a quella media e, se tale soglia è ancora insufficiente, passa al gateway grande. Per il gateway di piccole dimensioni, il fattore soglia è di 380.000 byte (potrebbe essere soggetto a modifiche nelle versioni future) per la piattaforma x64.
  • Fattore soglia: determina il valore soglia per ogni gateway. Per il gateway di piccole dimensioni, poiché la soglia è predefinita, anche il fattore viene impostato sullo stesso valore. I fattori di soglia per il gateway medio e grande sono le frazioni della memoria totale dell'ottimizzatore (memoria complessiva nella coda di ottimizzazione) e sono impostati rispettivamente su 12 e 8. Pertanto, se la memoria complessiva viene regolata perché altri consumer di memoria SQL Server richiedono memoria, anche i fattori di soglia causerebbero la regolazione dinamica delle soglie.
  • Timeout: indica il valore in minuti che definisce il tempo di attesa di una query per la memoria dell'utilità di ottimizzazione. Se viene raggiunto questo valore di timeout, la sessione interrompe l'attesa e genera l'errore 8628 - A time out occurred while waiting to optimize the query. Rerun the query.

Broker di memoria

Questa sezione fornisce informazioni sui broker di memoria che controllano la memoria memorizzata nella cache, la memoria rubata e la memoria riservata. È possibile usare le informazioni contenute in queste tabelle solo per la diagnostica interna. Di conseguenza, queste informazioni non sono dettagliate.

MEMORYBROKER_FOR_CACHE (internal)       Value
--------------------------------------- -------------
Allocations                             20040
Rate                                    0
Target Allocations                      3477904
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_STEAL (internal)       Value
--------------------------------------- -------------
Allocations                             129872
Rate                                    40
Target Allocations                      3587776
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_RESERVE (internal)     Value
--------------------------------------- -------------
Allocations                             0
Rate                                    0
Target Allocations                      3457864
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_CACHE (default)        Value
--------------------------------------- -------------
Allocations                             44592
Rate                                    8552
Target Allocations                      3511008
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_STEAL (default)        Value
--------------------------------------- -------------
Allocations                             1432
Rate                                    -520
Target Allocations                      3459296
Future Allocations                      0
Overall                                 3919104
Last Notification                       1

MEMORYBROKER_FOR_RESERVE (default)      Value
--------------------------------------- -------------
Allocations                             0
Rate                                    0
Target Allocations                      3919104
Future Allocations                      872608
Overall                                 3919104
Last Notification                       1