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 di memoria corrente di 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 errori specifici 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 DBCC MEMORYSTATUS comando include sezioni per la gestione della memoria, l'utilizzo della memoria, le informazioni di memoria aggregate, le informazioni sul pool di buffer e le informazioni sulla cache delle procedure. Descrive inoltre l'output di oggetti di memoria globale, oggetti di memoria di query, ottimizzazione e broker di memoria.

Note

Monitor prestazioni (PerfMon) e Gestione attività non riguardano l'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 forniti sono soggetti a modifiche tra i Service Pack e le 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 visualizzati avvisi aggiuntivi prima che questo comando venga modificato o rimosso. Pertanto, le applicazioni che usano questo comando potrebbero interrompersi senza avvisi.

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

Come usare DBCC MEMORYSTATUS

DBCC MEMORYSTATUS viene 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 potrebbe essere causata dal motore di database di SQL Server o da altri componenti eseguiti all'interno del processo (ad esempio server collegati, XP, SQLCLR, protezione delle intrusioni o software antivirus). Per altre informazioni su come risolvere i problemi di utilizzo elevato della memoria, vedere Risolvere i problemi di memoria insufficiente o memoria insufficiente in SQL Server.

Ecco i passaggi generali per l'uso del comando e l'interpretazione dei risultati. È possibile che scenari specifici richiedano un approccio leggermente diverso all'output, ma l'approccio complessivo è descritto qui.

  1. Eseguire il comando DBCC MEMORYSTATUS.
  2. Usare le sezioni Conteggi processo/sistema e Gestione memoria per stabilire se è presente un utilizzo elevato di memoria esterna( ad esempio, il computer è insufficiente nella memoria fisica o virtuale o il working set di SQL Server viene sottoposto a paging). Usare anche queste sezioni per determinare la quantità di memoria allocata dal motore di database di SQL Server rispetto alla memoria complessiva nel sistema.
  3. Se si stabilisce che è presente una pressione di memoria esterna, provare a ridurre l'utilizzo della memoria da parte di altre applicazioni e dal sistema operativo o aggiungere più RAM.
  4. Se si stabilisce che il motore di SQL Server usa la maggior parte della memoria (utilizzo di memoria interna), è possibile usare le sezioni rimanenti di DBCC MEMORYSTATUS per identificare quali componenti (clerk di memoria, Cachestore, UserStore o Objectstore) sono il principale collaboratore a questo utilizzo della memoria.
  5. Esaminare ogni componente: MEMORYCLEARK, CACHESTORE, USERSTOREe OBJECTSTORE. Esaminare il valore Pages Allocate per determinare la quantità di memoria utilizzata 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 Dei tipi clerk di memoria.
    1. In rari casi, l'allocazione è un'allocazione virtuale diretta anziché passare attraverso la gestione della memoria di SQL Server. In questi casi, esaminare il valore VM Committed nel componente specifico anziché pagine allocate.
    2. Se il computer usa NUMA, alcuni componenti di memoria vengono suddivisi per ogni nodo. Ad esempio, è possibile osservare , OBJECTSTORE_LOCK_MANAGER (node 1), e così via e infine osservare OBJECTSTORE_LOCK_MANAGER (node 0)un valore sommato di ogni nodo in OBJECTSTORE_LOCK_MANAGER (Total)OBJECTSTORE_LOCK_MANAGER (node 2). Il punto migliore per iniziare è la sezione che segnala il valore totale e quindi esaminare la scomposizione, in base alle esigenze. Per altre informazioni, vedere Utilizzo della memoria con nodi NUMA.
  6. Alcune sezioni di DBCC MEMORYSTATUS forniscono informazioni dettagliate e specializzate su specifici allocatori di memoria. È possibile usare queste sezioni per comprendere dettagli aggiuntivi e visualizzare un'ulteriore suddivisione delle allocazioni all'interno di un clerk di memoria. Esempi di queste sezioni includono il pool di buffer (cache dei dati e degli indici), la cache delle procedure/la cache dei piani, gli oggetti di memoria query (concessioni di memoria), la coda di ottimizzazione e i gateway di piccole e medie e grandi dimensioni (memoria ottimizzata). Se si sa già che un particolare componente di memoria in SQL Server è la fonte di utilizzo elevato della memoria, è consigliabile passare direttamente a quella sezione specifica. Ad esempio, se è stato stabilito in un altro modo in cui è presente un utilizzo elevato delle concessioni di memoria che causa 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 libera nel computer. Nell'esempio la memoria libera è 5.060.247.552 byte.
  • Memoria virtuale disponibile: questo valore mostra la quantità complessiva di memoria virtuale disponibile per il processo di SQL Server è 140.710.048.014.336 byte (128 TB). Per altre informazioni, vedere Limiti di spazio di memoria e 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(Working Set): questo valore mostra la quantità complessiva di memoria virtuale che il processo di SQL Server ha in RAM (non viene eseguito il paging) è 430.026.752 byte.
  • Percentuale di memoria di cui è stato eseguito il commit in WS: questo valore indica la percentuale di memoria virtuale allocata di SQL Server nella RAM (o è working set). Il valore del 100% indica che tutta la memoria di cui è stato eseguito il commit viene archiviata in RAM e il 0% di esso viene eseguito il paging.
  • Errori di pagina: questo valore mostra la quantità complessiva di errori di pagina hard e soft per 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. Questo è il motivo per cui il valore di Memoria fisica di sistema è 0, ovvero nessuna memoria insufficiente. La logica simile viene applicata a Elaborazione memoria fisica insufficiente e Elaborazione memoria virtuale insufficiente, dove 0 indica che è false e 1 significa che è true. In questo esempio, entrambi i valori sono 0, il che significa che per il processo di SQL Server è disponibile molta memoria fisica e virtuale.

Memory Manager

Questa sezione fornisce un output di esempio di Gestione memoria che mostra l'utilizzo complessivo della memoria da parte di 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 Reserved (Vm Reserved): questo valore mostra la quantità complessiva di spazio indirizzi virtuale (VAS) o di memoria virtuale (VM) riservata da 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 macchina virtuale: questo valore mostra la quantità complessiva di memoria virtuale di cui è stato eseguito il commit (in KB) di SQL Server. 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; che 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 queste 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 dal motore di database di SQL Server.

  • Pagine bloccate allocate: questo valore rappresenta la quantità di memoria, in kilobyte (KB), che SQL Server ha allocato e bloccato nella RAM fisica usando l'API AWE. Indica la quantità di memoria usata attivamente da SQL Server e ha richiesto di mantenere in memoria per ottimizzare le prestazioni. Bloccando le pagine in memoria, SQL Server garantisce che le pagine critiche del database siano facilmente disponibili e non scambiate su disco. Per altre informazioni, vedere Address Windows Extensions (AWE) memory.For more information, see Address Windows Extensions (AWE) memory. 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 grandi allocate: questo valore rappresenta la quantità di memoria allocata da SQL Server tramite pagine di grandi dimensioni. Pagine di grandi dimensioni è una funzionalità di gestione della memoria fornita dal sistema operativo. Anziché usare le dimensioni della pagina standard (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 Virtual Alloc(), MEM_LARGE_PAGES.

  • Target Committed: questo valore indica la quantità di memoria di destinazione di cui SQL Server ha lo scopo di eseguire 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 (allocato nell'archivio fisico) del gestore di memoria di SQL Server. 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 ancora allocata da Gestione memoria virtuale del sistema operativo e verrà riflessa come macchina virtuale di cui è stato eseguito il commit.

  • Fase di crescita NUMA: questo valore indica se SQL Server è attualmente in una fase di crescita NUMA. Per altre informazioni su questa rampa iniziale di memoria quando sono presenti nodi NUMA nel computer, vedere How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks).For more information about this initial ramp up of memory when NUMA nodes exist on the machine, see How It Works: SQL Server (NUMA Local, Foreign and Away Memory Blocks).

  • Ultimo errore del sistema operativo: questo valore mostra l'ultimo errore del sistema operativo che si è verificato quando si è verificato un utilizzo elevato della memoria nel sistema. SQL Server registra l'errore del sistema operativo e lo visualizza nell'output. Per un elenco completo degli errori del sistema operativo, vedere Codici 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 abilitato per l'accesso non uniforme alla memoria (NUMA) è presente una voce corrispondente del nodo di memoria 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

Note

  • 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 per il nodo NUMA.
  • Le somme dei valori riservati della macchina virtuale e dei valori VM Committed in tutti i nodi di memoria saranno leggermente inferiori ai valori corrispondenti riportati nella tabella Memory Manager.
  • Il nodo NUMA 64 (nodo 64) è riservato per l'applicazione livello dati ed è raramente di interesse per l'analisi della memoria perché questa connessione usa risorse di memoria limitate. Per altre informazioni sulla connessione amministrativa 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 Reserved (Vm Reserved): mostra lo spazio indirizzi virtuale (VAS) riservato dai thread in esecuzione in questo nodo.
  • Macchina virtuale sottoposta a commit: mostra il vas di cui è stato eseguito il commit dai thread in esecuzione in questo nodo.

Aggregare la memoria

La tabella seguente contiene informazioni di memoria aggregate per ogni tipo di clerk e nodo NUMA. Per un sistema abilitato per NUMA, è possibile che venga 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 (clerk di memoria, userstore, objectstore o archivio cache).

Note

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 sopra i nodi NUMA hardware o sopra un sistema SMP. Per trovare il mapping tra ID nodo e CPU per ogni nodo, vedere ID evento di informazioni 17152. Questo evento viene registrato nel log applicazioni in Visualizzatore eventi all'avvio di SQL Server.

Per un sistema SMP, viene visualizzata una sola tabella per ogni tipo di clerk, senza contare il nodo = 64 usato dall'applicazione livello dati. 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 sono relative alla memoria condivisa:

  • SM Riservato: mostra il VAS riservato da tutti i impiegati di questo tipo che usano l'API dei file mappati alla memoria. Questa API è nota anche come memoria condivisa.
  • SM Committed: mostra il VAS di cui è stato eseguito il commit da tutti i impiegati di questo tipo che usano l'API dei 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 vista a gestione dinamica sys.dm_os_memory_clerks (DMV). 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 diversi stati dati e pagine di indice all'interno del pool di buffer, noto 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 (numero di buffer). Vedere Memoria con commit di destinazione nelle sezioni precedenti di questo articolo.
  • Dirty: mostra le pagine con contenuto del database e che sono state modificate. Questi buffer contengono modifiche che devono essere scaricate su disco in genere dal processo di 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 all'I/O (questo non indica necessariamente un problema).
  • Aspettativa di vita 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 del database usando la sys.dm_os_buffer_descriptors DMV. Tuttavia, usare questa DMV con cautela perché può eseguire molto tempo e produrre un output enorme se il server basato su SQL Server è autorizzato ad avere un sacco di RAM a sua disposizione.

Pianificare la cache

In questa sezione viene illustrata la cache dei piani precedentemente denominata cache di 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 gli oggetti memorizzati nella cache cache delle procedure totali. Questo valore corrisponde al numero di voci nella sys.dm_exec_cached_plans DMV.

    Note

    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, procedure e oggetti ad hoc.

  • TotalPages: mostra le pagine cumulative utilizzate per archiviare tutti gli oggetti memorizzati nella cache dei piani o delle procedure. È 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 rimosse.

Oggetti di memoria globale

Questa sezione contiene informazioni sui vari oggetti memoria globale 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 specifica del motore di archiviazione per thread che usa l'archiviazione locale del thread (TLS). Per altre informazioni, vedere Archiviazione locale del thread.
  • Allocatori subpDesc: mostra la memoria usata per la gestione di sottoprocessi per query parallele, operazioni di backup, operazioni di ripristino, operazioni di database, operazioni di 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 server globale usato in modo generico da diversi sottosistemi.
  • XP Global: mostra la memoria utilizzata dalle stored procedure estese.
  • SortTables: mostra la memoria usata dalle tabelle di ordinamento.

Eseguire query sugli oggetti memoria

In questa sezione vengono descritte le informazioni sulle concessioni di memoria query. Include anche uno snapshot dell'utilizzo della memoria di query. La memoria delle 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 di query "small", la query viene inserita in una piccola coda di query. Questo comportamento impedisce che le query più piccole vengano ritardate dietro query di dimensioni maggiori già presenti nella coda.

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

  • Grant: 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 da usare come area di lavoro hash e ordinare l'area di lavoro. Il Available valore viene aggiornato periodicamente.
  • Richiesta successiva: mostra le dimensioni della richiesta di memoria, nei buffer, per la query in attesa successiva.
  • In 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, espresso 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 relativi a prestazioni lente o a 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 l'analisi, l'algebra e l'ottimizzazione. Le query vengono classificate in base alla memoria utilizzata da ogni query durante il processo di compilazione.

Note

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

All'avvio di una query non è previsto alcun limite per il numero di query che è possibile compilare. Man mano che l'utilizzo della memoria aumenta e raggiunge una soglia, la query deve passare un gateway per continuare. È previsto un limite progressivamente 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 finché 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 passa 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 informazioni dettagliate sulle attese di memoria che si verificano a causa di memoria insufficiente per l'ottimizzazione delle query. Gli account di memoria interna per la memoria ottimizzata usata dalle query di sistema, mentre la memoria predefinita segnala la memoria di ottimizzazione per le query dell'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 piccolo (impostazione predefinita), otto query simultanee dal gateway medio e una query dal gateway Big. Come accennato in precedenza, se una query richiede più memoria rispetto a quella che il gateway di piccole dimensioni può allocare, passa al gateway medio e tale query viene conteggiata per avere acquisito un'unità in entrambi i gateway. Maggiore è la quantità di memoria di compilazione necessaria per una query, meno unità configurate in un gateway.
  • Unità disponibili: indica il numero di slot o unità disponibili per le query simultanee da compilare dall'elenco di unità configurate. Ad esempio, se sono disponibili 32 unità, ma tre query usano attualmente la memoria di compilazione, Available Units saranno 32 meno 3 o 29 unità.
  • Acquisisci : indica il numero di unità o slot acquisiti dalle query da compilare. Se tre query usano attualmente la memoria da un gateway, acquisisce = 3.
  • Waiters : indica il numero di query in attesa di memoria di compilazione in un gateway. Se tutte le unità in un gateway vengono 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 la posizione in cui una query ottiene la memoria o il gateway in cui rimane. 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 è necessaria 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 di 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 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 modificata perché altri consumer di memoria di SQL Server richiedono memoria, anche i fattori di soglia causerebbero la regolazione dinamica delle soglie.
  • Timeout: indica il valore in minuti che definisce per quanto tempo una query attende la memoria dell'utilità di ottimizzazione. Se viene raggiunto questo valore di timeout, la sessione smette di attendere e generare 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 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