Risolvere i problemi di prestazioni lente o di memoria insufficiente causati da concessioni di memoria in SQL Server
Che cosa sono le concessioni di memoria?
Le concessioni di memoria, dette anche prenotazioni di esecuzione query (QE), memoria di esecuzione query, memoria dell'area di lavoro e prenotazioni di memoria, descrivono l'utilizzo della memoria in fase di esecuzione delle query. SQL Server alloca questa memoria durante l'esecuzione di query per uno o più degli scopi seguenti:
- Operazioni di ordinamento
- Operazioni hash
- Operazioni di copia bulk (non un problema comune)
- Creazione di indici, incluso l'inserimento in indici COLUMNSTORE perché i dizionari/tabelle hash vengono usati in fase di esecuzione per la compilazione di indici (non un problema comune)
Per fornire un contesto, durante la sua durata, una query può richiedere memoria a diversi allocatori o clerk di memoria a seconda di ciò che deve fare. Ad esempio, quando una query viene analizzata e compilata inizialmente, utilizza la memoria di compilazione. Una volta compilata la query, tale memoria viene rilasciata e il piano di query risultante viene archiviato nella memoria della cache dei piani. Dopo aver memorizzato nella cache un piano, la query è pronta per l'esecuzione. Se la query esegue operazioni di ordinamento, operazioni di corrispondenza hash (JOIN o aggregazioni) o inserimenti in indici COLUMNSTORE, usa la memoria dell'allocatore di esecuzione delle query. Inizialmente, la query richiede la memoria di esecuzione e, in seguito, se viene concessa questa memoria, la query usa tutta o parte della memoria per i risultati di ordinamento o i bucket hash. Questa memoria allocata durante l'esecuzione di query è quella definita concessione di memoria. Come si può immaginare, una volta completata l'operazione di esecuzione della query, la concessione di memoria viene rilasciata di nuovo a SQL Server da usare per altre operazioni. Di conseguenza, le allocazioni di concessioni di memoria sono di natura temporanea, ma possono comunque durare a lungo. Ad esempio, se un'esecuzione di query esegue un'operazione di ordinamento su un set di righe molto grande in memoria, l'ordinamento potrebbe richiedere molti secondi o minuti e la memoria concessa viene usata per la durata della query.
Esempio di query con una concessione di memoria
Ecco un esempio di query che usa la memoria di esecuzione e il relativo piano di query che mostra la concessione:
SELECT *
FROM sys.messages
ORDER BY message_id
Questa query seleziona un set di righe di oltre 300.000 righe e lo ordina. L'operazione di ordinamento induce una richiesta di concessione della memoria. Se si esegue questa query in SSMS, è possibile visualizzarne il piano di query. Quando si seleziona l'operatore più SELECT a sinistra del piano di query, è possibile visualizzare le informazioni sulla concessione di memoria per la query (premere F4 per visualizzare Proprietà):
Inoltre, se si fa clic con il pulsante destro del mouse sullo spazio vuoto nel piano di query, è possibile scegliere Mostra XML piano di esecuzione e individuare un elemento XML che mostra le stesse informazioni sulla concessione di memoria.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
Diversi termini richiedono una spiegazione qui. Una query può richiedere una certa quantità di memoria di esecuzione (DesiredMemory) e in genere richiede tale quantità (RequestedMemory). In fase di esecuzione, SQL Server concede tutta o parte della memoria richiesta a seconda della disponibilità (GrantedMemory). Alla fine, la query può usare più o meno della memoria richiesta inizialmente (MaxUsedMemory). Se Query Optimizer ha sovrastimato la quantità di memoria necessaria, usa meno delle dimensioni richieste. Ma tale memoria viene sprecata perché potrebbe essere stata usata da un'altra richiesta. D'altra parte, se l'ottimizzatore ha sottovalutato le dimensioni della memoria necessaria, le righe in eccesso possono essere riversate su disco per eseguire il lavoro in fase di esecuzione. Invece di allocare più memoria rispetto alle dimensioni richieste inizialmente, SQL Server esegue il push delle righe aggiuntive sul disco e le usa come area di lavoro temporanea. Per altre informazioni, vedere Workfiles and Worktables in Memory Grant Considerations.For more information, see Workfiles and Worktables in Memory Grant Considerations.
Terminologia
Verranno ora esaminati i diversi termini che è possibile riscontrare per questo consumer di memoria. Anche in questo caso, tutti questi concetti descrivono i concetti correlati alle stesse allocazioni di memoria.
Memoria di esecuzione query (memoria QE): Questo termine viene usato per evidenziare il fatto che viene usata la memoria di ordinamento o hash durante l'esecuzione di una query. In genere la memoria QE è il maggior consumer di memoria durante la durata di una query.
Prenotazioni di esecuzione query (QE) o di memoria: Quando una query richiede memoria per operazioni di ordinamento o hash, esegue una richiesta di prenotazione per la memoria. La richiesta di prenotazione viene calcolata in fase di compilazione in base alla cardinalità stimata. In seguito, quando viene eseguita la query, SQL Server concede la richiesta parzialmente o completamente a seconda della disponibilità della memoria. Alla fine, la query può usare una percentuale della memoria concessa. Esiste un impiegato di memoria (contabile della memoria) denominato "MEMORYCLERK_SQLQERESERVATIONS" che tiene traccia di queste allocazioni di memoria (vedere DBCC MEMORYSTATUS o sys.dm_os_memory_clerks).
Concessioni di memoria: Quando SQL Server concede la memoria richiesta a una query in esecuzione, si dice che si sia verificata una concessione di memoria. Esistono alcuni contatori delle prestazioni che usano il termine "concessione". Questi contatori,
Memory Grants OutstandingeMemory Grants Pending, visualizzano il conteggio delle concessioni di memoria soddisfatte o in attesa. Non tiene conto delle dimensioni delle concessioni di memoria. Una sola query avrebbe potuto utilizzare, ad esempio, 4 GB di memoria per eseguire un ordinamento, ma questo non si riflette in nessuno di questi contatori.Memoria dell'area di lavoro è un altro termine che descrive la stessa memoria. Spesso questo termine può essere visualizzato nel contatore
Granted Workspace Memory (KB)Perfmon , che riflette la quantità complessiva di memoria attualmente usata per operazioni di ordinamento, hash, copia bulk e creazione di indici, espresse in KB. IlMaximum Workspace Memory (KB), un altro contatore, tiene conto della quantità massima di memoria dell'area di lavoro disponibile per tutte le richieste che potrebbero dover eseguire operazioni di hash, ordinamento, copia bulk e creazione di indici. Il termine Memoria dell'area di lavoro viene rilevato raramente al di fuori di questi due contatori.
Impatto sulle prestazioni dell'utilizzo di memoria QE di grandi dimensioni
Nella maggior parte dei casi, quando un thread richiede memoria all'interno di SQL Server per eseguire un'operazione e la memoria non è disponibile, la richiesta ha esito negativo con un errore di memoria insufficiente. Esistono tuttavia alcuni scenari di eccezione in cui il thread non ha esito negativo, ma attende che la memoria non diventi disponibile. Uno di questi scenari sono le concessioni di memoria e l'altro è la memoria di compilazione delle query. SQL Server usa un oggetto di sincronizzazione dei thread denominato semaforo per tenere traccia della quantità di memoria concessa per l'esecuzione di query. Se SQL Server viene eseguita dall'area di lavoro QE predefinita, invece di non riuscire con un errore di memoria insufficiente, la query rimane in attesa. Dato che la memoria dell'area di lavoro può richiedere una percentuale significativa della memoria complessiva SQL Server, l'attesa della memoria in questo spazio ha gravi implicazioni sulle prestazioni. Un numero elevato di query simultanee ha richiesto memoria di esecuzione e, insieme, hanno esaurito il pool di memoria QE oppure alcune query simultanee hanno richiesto concessioni molto grandi. In entrambi i casi, i problemi di prestazioni risultanti possono presentare i sintomi seguenti:
- È probabile che le pagine di dati e indici da una cache del buffer siano state scaricate per creare spazio per le richieste di concessione di memoria di grandi dimensioni. Ciò significa che le letture di pagina provenienti da richieste di query devono essere soddisfatte dal disco (un'operazione significativamente più lenta).
- Le richieste per altre allocazioni di memoria possono avere esito negativo con errori di memoria insufficiente perché la risorsa è associata a operazioni di ordinamento, hash o compilazione di indici.
- Le richieste che richiedono memoria di esecuzione sono in attesa che la risorsa diventi disponibile e richiedono molto tempo per il completamento. In altre parole, per l'utente finale, queste query sono lente.
Pertanto, se si osservano attese sulla memoria di esecuzione delle query in Perfmon, viste a gestione dinamica (DMV) o DBCC MEMORYSTATUS, è necessario agire per risolvere questo problema, in particolare se il problema si verifica di frequente. Per altre informazioni, vedere Cosa può fare uno sviluppatore per le operazioni di ordinamento e hash.
Come identificare le attese per la memoria di esecuzione delle query
Esistono diversi modi per determinare le attese per le prenotazioni QE. Scegli quelli che ti servono meglio per vedere l'immagine più grande a livello di server. Alcuni di questi strumenti potrebbero non essere disponibili,ad esempio Perfmon non è disponibile nel database Azure SQL. Dopo aver identificato il problema, è necessario eseguire il drill-down a livello di singola query per vedere quali query devono essere ottimizzazioni o riscritture.
A livello di server, usare i metodi seguenti:
- Sys.dm_exec_query_resource_semaphores DMV del semaforo delle risorse Per altre informazioni, vedere sys.dm_exec_query_resource_semaphores.
- Monitor prestazioni contatori Per altre informazioni, vedere SQL Server oggetto Gestione memoria.
- DBCC MEMORYSTATUS Per altre informazioni, vedere DBCC MEMORYSTATUS.
- Sys.dm_os_memory_clerks DMV dei clerk di memoria Per altre informazioni, vedere sys.dm_os_memory_clerks.
- Identificare le concessioni di memoria tramite eventi estesi (XEvents) Per altre informazioni, vedere Eventi estesi (XEvents).For more information, see Extended Events (XEvents).
A livello di singola query, usare i metodi seguenti:
- Identificare query specifiche con sys.dm_exec_query_memory_grants: query attualmente in esecuzione. Per altre informazioni, vedere sys.dm_exec_query_memory_grants.
- Identificare query specifiche con sys.dm_exec_requests: query attualmente in esecuzione. Per altre informazioni, vedere sys.dm_exec_requests.
- Identificare query specifiche con sys.dm_exec_query_stats: statistiche cronologiche sulle query. Per altre informazioni, vedere sys.dm_exec_query_stats.
- Identificare query specifiche usando Query Store (QDS) con sys.query_store_runtime_stats: statistiche cronologiche sulle query con QDS. Per altre informazioni, vedere sys.query_store_runtime_stats.
Statistiche di utilizzo della memoria aggregate
Sys.dm_exec_query_resource_semaphores DMV del semaforo delle risorse
Questa DMV suddivide la memoria della prenotazione delle query in base al pool di risorse (interno, predefinito e creato dall'utente) e resource_semaphore (richieste di query regolari e di piccole dimensioni). Una query utile può essere:
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
L'output di esempio seguente mostra che circa 900 MB di memoria di esecuzione delle query vengono usati da 22 richieste e altre 3 sono in attesa. Ciò avviene nel pool predefinito (pool_id = 2) e nel semaforo di query regolare (resource_semaphore_id = 0).
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
contatori Monitor prestazioni
Informazioni simili sono disponibili tramite contatori Monitor prestazioni, in cui è possibile osservare le richieste attualmente concesse (Memory Grants Outstanding), le richieste di concessione in attesa (Memory Grants Pending) e la quantità di memoria usata dalle concessioni di memoria (Granted Workspace Memory (KB)). Nell'immagine seguente le concessioni in sospeso sono 18, le concessioni in sospeso sono 2 e la memoria dell'area di lavoro concessa è di 828.288 KB. Il Memory Grants Pending contatore Perfmon con un valore diverso da zero indica che la memoria è esaurita.
Per altre informazioni, vedere SQL Server oggetto Gestione memoria.
- SQLServer, Gestione memoria: memoria massima dell'area di lavoro (KB)
- SQLServer, Gestione memoria: concessioni di memoria in sospeso
- SQLServer, Gestione memoria: Concessioni di memoria in sospeso
- SQLServer, Gestione memoria: Memoria dell'area di lavoro concessa (KB)
DBCC MEMORYSTATUS
Un'altra posizione in cui è possibile visualizzare i dettagli sulla memoria della prenotazione delle query è DBCC MEMORYSTATUS (sezione Oggetti memoria query). È possibile esaminare l'output Query Memory Objects (default) per le query utente. Se è stato abilitato Resource Governor con un pool di risorse denominato PoolAdmin, ad esempio, è possibile esaminare sia che Query Memory Objects (default)Query Memory Objects (PoolAdmin).
Ecco un output di esempio da un sistema in cui a 18 richieste è stata concessa la memoria di esecuzione delle query e 2 richieste sono in attesa di memoria. Il contatore disponibile è zero, che indica che la memoria dell'area di lavoro non è più disponibile. Questo fatto spiega le due richieste in attesa. Mostra Wait Time il tempo trascorso in millisecondi da quando una richiesta è stata inserita nella coda di attesa. Per altre informazioni su questi contatori, vedere Eseguire query sugli oggetti memoria.
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS visualizza anche informazioni sul clerk di memoria che tiene traccia della memoria di esecuzione delle query. L'output seguente mostra che le pagine allocate per le prenotazioni di esecuzione di query (QE) superano gli 800 MB.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
Sys.dm_os_memory_clerks DMV degli impiegati di memoria
Se sono necessari più set di risultati tabulari, diversi da quelli basati su DBCC MEMORYSTATUSsezioni, è possibile usare sys.dm_os_memory_clerks per informazioni simili. Cerca l'impiegato di MEMORYCLERK_SQLQERESERVATIONS memoria. Gli oggetti di memoria query non sono tuttavia disponibili in questa DMV.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Ecco un output di esempio:
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identificare le concessioni di memoria tramite eventi estesi (XEvents)
Sono presenti più eventi estesi che forniscono informazioni sulla concessione di memoria e consentono di acquisire queste informazioni tramite una traccia:
- sqlserver.additional_memory_grant: si verifica quando una query tenta di ottenere più concessioni di memoria durante l'esecuzione. Se non si ottiene questa concessione di memoria aggiuntiva, è possibile che la query venga rallentata.
- sqlserver.query_memory_grant_blocking: si verifica quando una query blocca altre query in attesa di una concessione di memoria.
- sqlserver.query_memory_grant_info_sampling: si verifica alla fine delle query campionate in modo casuale che forniscono informazioni sulla concessione di memoria (può essere usata, ad esempio, per i dati di telemetria).
- sqlserver.query_memory_grant_resource_semaphores: si verifica a intervalli di cinque minuti per ogni pool di risorse di Resource Governor.
- sqlserver.query_memory_grant_usage: si verifica al termine dell'elaborazione delle query per le query con concessioni di memoria superiori a 5 MB per comunicare agli utenti le inesattezze delle concessioni di memoria.
- sqlserver.query_memory_grants: si verifica a intervalli di cinque minuti per ogni query con una concessione di memoria.
Eventi estesi per il feedback delle concessioni di memoria
Per informazioni sulle funzionalità di feedback delle concessioni di memoria per l'elaborazione delle query, vedere Commenti e suggerimenti sulle concessioni di memoria.
- sqlserver.memory_grant_feedback_loop_disabled: si verifica quando il ciclo di feedback delle concessioni di memoria è disabilitato.
- sqlserver.memory_grant_updated_by_feedback: si verifica quando la concessione di memoria viene aggiornata tramite feedback.
Avvisi di esecuzione di query correlati a concessioni di memoria
- sqlserver.execution_warning: si verifica quando un'istruzione T-SQL o una stored procedure attende più di un secondo per una concessione di memoria o quando il tentativo iniziale di recupero della memoria ha esito negativo. Usare questo evento in combinazione con eventi che identificano le attese per risolvere i problemi di contesa che influiscono sulle prestazioni.
- sqlserver.hash_spill_details: si verifica alla fine dell'elaborazione hash se la memoria non è sufficiente per elaborare l'input di compilazione di un hash join. Usare questo evento insieme a uno qualsiasi degli
query_pre_execution_showplaneventi oquery_post_execution_showplanper determinare quale operazione nel piano generato causa la propagazione dell'hash. - sqlserver.hash_warning: si verifica quando la memoria non è sufficiente per elaborare l'input di compilazione di un hash join. Ciò comporta una ricorsione hash quando l'input di compilazione viene partizionato o un bailout hash quando il partizionamento dell'input di compilazione supera il livello di ricorsione massimo. Usare questo evento insieme a uno qualsiasi degli
query_pre_execution_showplaneventi oquery_post_execution_showplanper determinare quale operazione nel piano generato causa l'avviso hash. - sqlserver.sort_warning: si verifica quando l'operazione di ordinamento in una query in esecuzione non rientra nella memoria. Questo evento non viene generato per le operazioni di ordinamento causate dalla creazione dell'indice, ma solo per le operazioni di ordinamento in una query. Ad esempio, in un'istruzione
Order BySelect. Usare questo evento per identificare le query che si eseguono lentamente a causa dell'operazione di ordinamento, in particolare quandowarning_type= 2, che indica che sono stati necessari più passaggi sui dati per l'ordinamento.
Pianificare la generazione di eventi che contengono informazioni sulla concessione di memoria
Il piano di query seguente che genera eventi estesi contiene campi granted_memory_kb e ideal_memory_kb per impostazione predefinita:
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
Compilazione dell'indice dell'archivio colonne
Una delle aree coperte tramite XEvents è la memoria di esecuzione usata durante la compilazione dell'archivio colonne. Questo è un elenco di eventi disponibili:
- sqlserver.column_store_index_build_low_memory: il motore di archiviazione ha rilevato una condizione di memoria insufficiente e le dimensioni del rowgroup sono state ridotte. In questo caso sono presenti diverse colonne di interesse.
- sqlserver.column_store_index_build_memory_trace: consente di tracciare l'utilizzo della memoria durante la compilazione dell'indice.
- sqlserver.column_store_index_build_memory_usage_scale_down: motore di archiviazione ridotto.
- sqlserver.column_store_index_memory_estimation: mostra il risultato della stima della memoria durante la compilazione del rowgroup COLUMNSTORE.
Identificare query specifiche
Quando si esamina il livello di richiesta individuale, è possibile trovare due tipi di query. Query che utilizzano una grande quantità di memoria di esecuzione delle query e quelle in attesa della stessa memoria. Quest'ultimo gruppo può essere costituito da richieste con esigenze modeste per le concessioni di memoria e, in tal caso, è possibile concentrare l'attenzione altrove. Ma potrebbero anche essere i colpevoli se richiedono dimensioni di memoria enormi. Concentrarsi su di essi se si ritiene che sia il caso. Può essere comune scoprire che una particolare query è il trasgressore, ma molte istanze di esso vengono generati. Le istanze che ottengono le concessioni di memoria causano l'attesa della concessione da parte di altre istanze della stessa query. Indipendentemente da circostanze specifiche, in definitiva, è necessario identificare le query e le dimensioni della memoria di esecuzione richiesta.
Identificare query specifiche con sys.dm_exec_query_memory_grants
Per visualizzare le singole richieste e le dimensioni di memoria richieste e concesse, è possibile eseguire query sulla sys.dm_exec_query_memory_grants visualizzazione a gestione dinamica. Questa DMV mostra informazioni sull'esecuzione di query, non informazioni cronologiche.
L'istruzione seguente ottiene i dati dalla DMV e recupera di conseguenza anche il testo della query e il piano di query:
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Di seguito è riportato un output di esempio abbreviato della query durante l'utilizzo attivo della memoria QE. La maggior parte delle query ha la memoria concessa, come illustrato da granted_memory_kb e used_memory_kb essendo valori numerici non NULL. Le query che non hanno ricevuto la richiesta concessa sono in attesa della memoria di esecuzione e di granted_memory_kb = NULL. Vengono inoltre inseriti in una coda di attesa con = queue_id 6. Indica wait_time_ms circa 37 secondi di attesa. La sessione 72 è la successiva in linea per ottenere una concessione come indicato da wait_order = 1, mentre la sessione 74 arriva dopo con wait_order = 2.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
Identificare query specifiche con sys.dm_exec_requests
È presente un tipo di attesa in SQL Server che indica che una query è in attesa della concessione RESOURCE_SEMAPHOREdi memoria . È possibile osservare questo tipo di attesa in sys.dm_exec_requests per le singole richieste. Quest'ultima DMV è il punto di partenza migliore per identificare quali query sono vittime di memoria di concessione insufficiente. È anche possibile osservare l'attesa RESOURCE_SEMAPHORE in sys.dm_os_wait_stats come punti dati aggregati a livello di SQL Server. Questo tipo di attesa viene visualizzato quando non è possibile concedere una richiesta di memoria di query a causa di altre query simultanee che hanno utilizzato la memoria. Un numero elevato di richieste in attesa e tempi di attesa lunghi indica un numero eccessivo di query simultanee che usano memoria di esecuzione o grandi dimensioni delle richieste di memoria.
Nota
Il tempo di attesa per le concessioni di memoria è limitato. Dopo un'attesa eccessiva (ad esempio, oltre 20 minuti), SQL Server il timeout della query e genera l'errore 8645, "Si è verificato un timeout durante l'attesa delle risorse di memoria per eseguire la query. Eseguire di nuovo la query." È possibile che il valore di timeout sia impostato a livello di server esaminando timeout_sec in sys.dm_exec_query_memory_grants. Il valore di timeout può variare leggermente tra le versioni SQL Server.
Con l'uso di sys.dm_exec_requests, è possibile visualizzare le query a cui è stata concessa memoria e le dimensioni di tale concessione. È anche possibile identificare le query attualmente in attesa di una concessione di memoria cercando il RESOURCE_SEMAPHORE tipo di attesa. Ecco una query che mostra sia le richieste concesse che le richieste in attesa:
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
Un output di esempio mostra che a due richieste è stata concessa memoria e altre due dozzine sono in attesa di concessioni. La granted_query_memory colonna segnala le dimensioni in pagine da 8 KB. Ad esempio, un valore pari a 34.709 indica 34.709 * 8 KB = 277.672 KB di memoria concessa.
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
Identificare query specifiche con sys.dm_exec_query_stats
Se il problema di concessione della memoria non si verifica in questo momento, ma si vuole identificare le query in errore, è possibile esaminare i dati cronologici delle query tramite sys.dm_exec_query_stats. La durata dei dati è associata al piano di query di ogni query. Quando un piano viene rimosso dalla cache dei piani, le righe corrispondenti vengono eliminate da questa visualizzazione. In altre parole, la DMV mantiene le statistiche in memoria che non vengono mantenute dopo un riavvio SQL Server o dopo che la pressione della memoria causa un rilascio della cache del piano. Detto questo, è possibile trovare le informazioni qui preziose, in particolare per le statistiche di query aggregate. È possibile che qualcuno abbia recentemente segnalato la visualizzazione di grandi concessioni di memoria dalle query, ma quando si esamina il carico di lavoro del server, è possibile che il problema non sia più presente. In questo caso, sys.dm_exec_query_stats può fornire le informazioni dettagliate che altri DVM non possono ottenere. Ecco una query di esempio che consente di trovare le prime 20 istruzioni che hanno utilizzato le quantità maggiori di memoria di esecuzione. Questo output visualizza singole istruzioni anche se la struttura di query è la stessa. Ad esempio, SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 è una riga separata da SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (solo il valore del predicato del filtro varia). La query ottiene le prime 20 istruzioni con una dimensione massima di concessione maggiore di 5 MB.
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
È possibile ottenere informazioni ancora più dettagliate esaminando le query aggregate da query_hash. In questo esempio viene illustrato come trovare le dimensioni medie, massime e minime delle concessioni per un'istruzione di query in tutte le istanze dal momento che il piano di query è stato memorizzato per la prima volta nella cache.
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
La Sample_Statement_Text colonna mostra un esempio della struttura di query che corrisponde all'hash della query, ma deve essere letta senza considerare valori specifici nell'istruzione . Ad esempio, se un'istruzione contiene WHERE Id = 5, è possibile leggerla nel formato più generico: WHERE Id = @any_value.
Di seguito è riportato un output di esempio abbreviato della query con solo le colonne selezionate visualizzate:
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
Identificare query specifiche usando Query Store (QDS) con sys.query_store_runtime_stats
Se è stato abilitato Query Store, è possibile sfruttare le statistiche cronologica persistenti. Contrariamente ai dati di sys.dm_exec_query_stats, queste statistiche sopravvivono a un riavvio SQL Server o a una pressione di memoria perché vengono archiviate in un database. QDS ha anche limiti di dimensioni e criteri di conservazione. Per altre informazioni, vedere le sezioni Impostare la modalità di acquisizione Query Store ottimale e Mantenere i dati più rilevanti in Query Store sezioni Procedure consigliate per la gestione dei Query Store.
Identificare se i database sono Query Store abilitati usando questa query:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Eseguire la query di diagnostica seguente nel contesto di un database specifico da analizzare:
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)I principi sono gli stessi di . Le statistiche di aggregazione per le istruzioni sono le stesse
sys.dm_exec_query_stats. Tuttavia, una differenza è che con QDS si esaminano solo le query nell'ambito di questo database, non l'intero SQL Server. Potrebbe quindi essere necessario conoscere il database in cui è stata eseguita una richiesta di concessione di memoria specifica. In caso contrario, eseguire questa query di diagnostica in più database fino a trovare le concessioni di memoria di dimensioni considerevoli.Ecco un output di esempio abbreviato:
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
Una query di diagnostica personalizzata
Ecco una query che combina i dati di più visualizzazioni, incluse le tre elencate in precedenza. Fornisce una visualizzazione più approfondita delle sessioni e delle relative concessioni tramite sys.dm_exec_requests e sys.dm_exec_query_memory_grants, oltre alle statistiche a livello di server fornite da sys.dm_exec_query_resource_semaphores.
Nota
Questa query restituirà due righe per sessione a causa dell'uso di sys.dm_exec_query_resource_semaphores (una riga per il semaforo di risorse normale e un'altra per il semaforo delle risorse con query di piccole dimensioni).
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
Nota
L'hint LOOP JOIN viene usato in questa query di diagnostica per evitare una concessione di memoria da parte della query stessa e non viene usata alcuna ORDER BY clausola. Se la query di diagnostica finisce in attesa di una concessione, lo scopo della diagnosi delle concessioni di memoria verrà annullato. L'hint LOOP JOIN potrebbe potenzialmente rallentare la query di diagnostica, ma in questo caso è più importante ottenere i risultati della diagnostica.
Di seguito è riportato un output di esempio abbreviato di questa query di diagnostica con solo le colonne selezionate.
| Session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
L'output di esempio illustra chiaramente come una query inviata da session_id = 60 abbia ottenuto correttamente la concessione di memoria di 9 MB richiesta, ma solo 7 MB sono stati necessari per avviare correttamente l'esecuzione della query. Alla fine, la query ha usato solo 1 MB dei 9 MB ricevuti dal server. L'output mostra anche che le sessioni 75 e 86 sono in attesa di concessioni di memoria, quindi .RESOURCE_SEMAPHOREwait_type Il tempo di attesa è stato superiore a 1.300 secondi (21 minuti) e il relativo granted_memory_mb è NULL.
Questa query di diagnostica è un esempio, quindi è possibile modificarla in qualsiasi modo in base alle proprie esigenze. Una versione di questa query viene usata anche negli strumenti di diagnostica usati dal supporto di Microsoft SQL Server.
Strumenti di diagnostica
Sono disponibili strumenti di diagnostica usati da Microsoft SQL Server supporto tecnico per raccogliere i log e risolvere i problemi in modo più efficiente. Sql LogScout e Pssdiag Configuration Manager (insieme a SQLDiag) raccolgono gli output delle DMV e dei contatori di Monitor prestazioni descritti in precedenza che consentono di diagnosticare i problemi di concessione della memoria.
Se si esegue SQL LogScout con scenari LightPerf, GeneralPerf o DetailedPerf , lo strumento raccoglie i log necessari. È quindi possibile esaminare manualmente il YourServer_PerfStats.out e cercare -- dm_exec_query_resource_semaphores -- gli output e -- dm_exec_query_memory_grants -- . In alternativa, invece dell'esame manuale, è possibile usare SQL Nexus per importare l'output proveniente da SQL LogScout o PSSDIAG in un database SQL Server. SQL Nexus crea due tabelle, tbl_dm_exec_query_resource_semaphores e tbl_dm_exec_query_memory_grants, che contengono le informazioni necessarie per diagnosticare le concessioni di memoria. Sql LogScout e PSSDIAG raccolgono anche i log di Perfmon sotto forma di . File BLG, che possono essere usati per esaminare i contatori delle prestazioni descritti nella sezione Monitor prestazioni contatori.
Perché le concessioni di memoria sono importanti per uno sviluppatore o un amministratore di database
In base all'esperienza di supporto Microsoft, i problemi di concessione della memoria tendono ad essere alcuni dei problemi più comuni correlati alla memoria. Le applicazioni spesso eseguono query apparentemente semplici che possono causare problemi di prestazioni nel SQL Server a causa di operazioni di ordinamento o hash elevate. Tali query non solo consumano molta memoria SQL Server, ma causano anche l'attesa di memoria da parte di altre query, quindi il collo di bottiglia delle prestazioni.
Usando gli strumenti descritti qui (DMV, contatori Perfmon e piani di query effettivi), è possibile identificare quali query sono consumer con concessione di grandi dimensioni. È quindi possibile ottimizzare o riscrivere queste query per risolvere o ridurre l'utilizzo della memoria dell'area di lavoro.
Cosa può fare uno sviluppatore per le operazioni di ordinamento e hash
Dopo aver identificato query specifiche che utilizzano una grande quantità di memoria di prenotazione delle query, è possibile eseguire i passaggi necessari per ridurre le concessioni di memoria riprogettando queste query.
Cause delle operazioni di ordinamento e hash nelle query
Il primo passaggio consiste nel prendere in considerazione le operazioni in una query che possono portare a concessioni di memoria.
Motivi per cui una query usa un operatore SORT:
ORDER BY (T-SQL) determina l'ordinamento delle righe prima di essere trasmesse come risultato finale.
GROUP BY (T-SQL) può introdurre un operatore di ordinamento in un piano di query prima del raggruppamento se non è presente un indice sottostante che ordina le colonne raggruppate.
DISTINCT (T-SQL) si comporta in modo analogo a
GROUP BY. Per identificare righe distinte, i risultati intermedi vengono ordinati e quindi vengono rimossi i duplicati. L'utilità di ottimizzazione usa unSortoperatore prima di questo operatore se i dati non sono già ordinati a causa di una ricerca o di un'analisi dell'indice ordinato.L'operatore Merge Join , se selezionato da Query Optimizer, richiede l'ordinamento di entrambi gli input aggiunti. SQL Server può attivare un ordinamento se un indice cluster non è disponibile nella colonna join in una delle tabelle.
Motivi per cui una query usa un operatore del piano di query HASH:
Questo elenco non è esaustivo, ma include i motivi più comuni per le operazioni hash. Analizzare il piano di query per identificare le operazioni di corrispondenza hash.
JOIN (T-SQL): quando si uniscono tabelle, SQL Server può scegliere tra tre operatori fisici,
Nested Loop,Merge JoineHash Join. Se SQL Server finisce per scegliere un join hash, è necessaria memoria QE per archiviare ed elaborare i risultati intermedi. In genere, la mancanza di indici validi può causare questo operatore di join più costoso per le risorse,Hash Join. Per esaminare il piano di query per identificareHash Match, vedere Riferimento agli operatori logici e fisici.DISTINCT (T-SQL):è possibile usare un
Hash Aggregateoperatore per eliminare i duplicati in un set di righe. Per cercare (Hash MatchAggregate) nel piano di query, vedere Riferimento agli operatori logici e fisici.UNION (T-SQL):simile a
DISTINCT. È possibile usare unHash Aggregateoggetto per rimuovere i duplicati per questo operatore.SUM/AVG/MAX/MIN (T-SQL): qualsiasi operazione di aggregazione potrebbe essere eseguita come .
Hash AggregatePer cercare (Hash MatchAggregate) nel piano di query, vedere Riferimento agli operatori logici e fisici.
Conoscere questi motivi comuni può aiutare a eliminare il più possibile le richieste di concessione di memoria di grandi dimensioni che arrivano a SQL Server.
Modi per ridurre le operazioni di ordinamento e hash o le dimensioni della concessione
- Mantenere aggiornate le statistiche . Questo passaggio fondamentale, che migliora le prestazioni per le query su molti livelli, garantisce che Query Optimizer disponga delle informazioni più accurate quando si selezionano i piani di query. SQL Server determina le dimensioni da richiedere per la concessione di memoria in base alle statistiche. Le statistiche non aggiornate possono causare una sovrastima o una sottostima della richiesta di concessione e quindi, rispettivamente, una richiesta di concessione inutilmente elevata o la propagazione dei risultati su disco. Assicurarsi che le statistiche di aggiornamento automatico siano abilitate nei database e/o mantenere aggiornate le statistiche statiche con UPDATE STATISTICS o sp_updatestats.
- Ridurre il numero di righe provenienti dalle tabelle. Se si usa un filtro WHERE più restrittivo o un join e si riduce il numero di righe, un ordinamento successivo nel piano di query ottiene l'ordine o l'aggregazione di un set di risultati più piccolo. Un set di risultati intermedio più piccolo richiede meno memoria del working set. Si tratta di una regola generale che gli sviluppatori possono seguire non solo per il salvataggio della memoria del working set, ma anche per ridurre CPU e I/O (questo passaggio non è sempre possibile). Se sono già state eseguite query ben scritte ed efficienti in base alle risorse, questa linea guida è stata soddisfatta.
- Creare indici nelle colonne join per facilitare i join di merge. Le operazioni intermedie in un piano di query sono interessate dagli indici nella tabella sottostante. Ad esempio, se in una tabella non è presente alcun indice in una colonna join e un join di merge risulta essere l'operatore di join più conveniente, tutte le righe di tale tabella devono essere ordinate prima dell'esecuzione del join. Se invece esiste un indice nella colonna, è possibile eliminare un'operazione di ordinamento.
- Creare indici per evitare operazioni hash. In genere, l'ottimizzazione delle query di base inizia con il controllo se le query hanno indici appropriati per ridurre le letture e ridurre al minimo o eliminare gli ordinamenti o le operazioni hash di grandi dimensioni, ove possibile. I join hash vengono in genere selezionati per elaborare input di grandi dimensioni, non ordinati e non indicizzati. La creazione di indici può modificare questa strategia di Ottimizzazione e velocizzare il recupero dei dati. Per assistenza nella creazione di indici, vedere Ottimizzazione guidata motore di database e Ottimizzare gli indici non cluster con suggerimenti sugli indici mancanti.
- Usare gli indici COLUMNSTORE, se appropriato, per le query di aggregazione che usano
GROUP BY. Le query di analisi che gestiscono set di righe molto grandi ed eseguono in genere aggregazioni "raggruppa per" possono richiedere blocchi di memoria di grandi dimensioni per svolgere il lavoro. Se non è disponibile un indice che fornisce risultati ordinati, viene introdotto automaticamente un ordinamento nel piano di query. Una sorta di risultato molto grande può portare a una concessione di memoria costosa. - Rimuovere se
ORDER BYnon è necessario. Nei casi in cui i risultati vengono trasmessi a un'applicazione che ordina i risultati in modo autonomo o consente all'utente di modificare l'ordine dei dati visualizzati, non è necessario eseguire un ordinamento sul lato SQL Server. È sufficiente trasmettere i dati all'applicazione nell'ordine in cui il server lo produce e consentire all'utente finale di ordinarli autonomamente. Le applicazioni di creazione di report come Power BI o Reporting Services sono esempi di tali applicazioni che consentono agli utenti finali di ordinare i dati. - Si consideri, anche se con cautela, l'uso di un hint LOOP JOIN quando esistono join in una query T-SQL. Questa tecnica può evitare join hash o merge che usano concessioni di memoria. Tuttavia, questa opzione viene suggerita solo come ultima risorsa perché forzare un join potrebbe causare una query significativamente più lenta. Testare lo stress del carico di lavoro per assicurarsi che si tratta di un'opzione. In alcuni casi, un join a ciclo annidato potrebbe non essere nemmeno un'opzione. In questo caso, SQL Server potrebbe non riuscire con l'errore MSSQLSERVER_8622, "Query processor could not produce a query plan because of the hints defined in this query".
Hint per la query di concessione della memoria
Da SQL Server 2012 SP3, esiste un hint di query che consente di controllare le dimensioni della concessione di memoria per ogni query. Ecco un esempio di come è possibile usare questo hint:
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
È consigliabile usare valori conservativi qui, soprattutto nei casi in cui si prevede che molte istanze della query vengano eseguite contemporaneamente. Assicurarsi di testare lo stress test del carico di lavoro in modo che corrisponda all'ambiente di produzione e determinare quali valori usare.
Per altre informazioni, vedere MAX_GRANT_PERCENT e MIN_GRANT_PERCENT.
Resource Governor
QE Memory è la memoria che Resource Governor effettivamente limita quando vengono usate le impostazioni MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT. Dopo aver identificato le query che causano concessioni di memoria di grandi dimensioni, è possibile limitare la memoria usata dalle sessioni o dalle applicazioni. Vale la pena ricordare che il default gruppo di carico di lavoro consente a una query di richiedere fino al 25% di memoria che può essere concessa in un'istanza di SQL Server. Per altre informazioni, vedere Resource Governor Pool di risorse e CREATE WORKLOAD GROUP.
Elaborazione di query adattive e feedback sulle concessioni di memoria
SQL Server 2017 ha introdotto la funzionalità di feedback delle concessioni di memoria. Consente al motore di esecuzione delle query di modificare la concessione concessa alla query in base alla cronologia precedente. L'obiettivo è ridurre le dimensioni della concessione quando possibile o aumentarla quando è necessaria più memoria. Questa funzionalità è stata rilasciata in tre ondate:
- Feedback delle concessioni di memoria in modalità Batch in SQL Server 2017
- Feedback delle concessioni di memoria in modalità riga in SQL Server 2019
- Feedback sulle concessioni di memoria sulla persistenza su disco usando la concessione Query Store e percentile in SQL Server 2022
Per altre informazioni, vedere Feedback sulle concessioni di memoria. La funzionalità di concessione della memoria può ridurre le dimensioni delle concessioni di memoria per le query in fase di esecuzione e quindi ridurre i problemi derivanti da richieste di concessione di grandi dimensioni. Con questa funzionalità, in particolare in SQL Server 2019 e versioni successive, in cui è disponibile l'elaborazione adattiva in modalità riga, è possibile che non si notano problemi di memoria derivanti dall'esecuzione di query. Tuttavia, se questa funzionalità è attiva (attivata per impostazione predefinita) e viene comunque visualizzato un consumo elevato di memoria QE, applicare i passaggi descritti in precedenza per riscrivere le query.
Aumentare SQL Server o memoria del sistema operativo
Dopo aver eseguito i passaggi per ridurre le concessioni di memoria non necessarie per le query, se si verificano ancora problemi correlati a memoria insufficiente, il carico di lavoro richiede probabilmente più memoria. È quindi consigliabile aumentare la memoria per SQL Server usando l'impostazione max server memory se nel sistema è presente memoria fisica sufficiente. Seguire le indicazioni su come lasciare circa il 25% della memoria per il sistema operativo e altre esigenze. Per altre informazioni, vedere Opzioni di configurazione della memoria del server. Se nel sistema non è disponibile memoria sufficiente, prendere in considerazione l'aggiunta di RAM fisica o, se si tratta di una macchina virtuale, aumentare la RAM dedicata per la macchina virtuale.
Concessioni di memoria interne
Per altre informazioni su alcuni elementi interni nella memoria di esecuzione delle query, vedere il post di blog Informazioni sulla concessione di memoria di SQL Server .
Come creare uno scenario di prestazioni con un utilizzo elevato delle concessioni di memoria
Infine, l'esempio seguente illustra come simulare un consumo elevato di memoria di esecuzione delle query e come introdurre query in attesa su RESOURCE_SEMAPHORE. È possibile eseguire questa operazione per informazioni su come usare gli strumenti e le tecniche di diagnostica descritti in questo articolo.
Avviso
Non usarlo in un sistema di produzione. Questa simulazione consente di comprendere meglio il concetto e di impararlo meglio.
In un server di test installare utilità RML e SQL Server.
Usare un'applicazione client come SQL Server Management Studio per ridurre l'impostazione max server memory del SQL Server a 1.500 MB:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREAprire un prompt dei comandi e modificare la directory nella cartella utilità RML:
cd C:\Program Files\Microsoft Corporation\RMLUtilsUsare ostress.exe per generare più richieste simultanee sulla SQL Server di test. Questo esempio usa 30 sessioni simultanee, ma è possibile modificare tale valore:
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30Usare gli strumenti di diagnostica descritti in precedenza per identificare i problemi di concessione della memoria.
Riepilogo dei modi per gestire le concessioni di memoria di grandi dimensioni
- Riscrivere le query.
- Aggiornare le statistiche e mantenerle aggiornate regolarmente.
- Creare indici appropriati per la query o le query identificate. Gli indici possono ridurre il numero elevato di righe elaborate, modificando così gli
JOINalgoritmi e riducendo le dimensioni delle concessioni o eliminandole completamente. - Usare l'hint
OPTION(min_grant_percent = XX, max_grant_percent = XX). - Usare Resource Governor.
- SQL Server 2017 e 2019 usano l'elaborazione di query adattive, consentendo al meccanismo di feedback delle concessioni di memoria di modificare dinamicamente le dimensioni delle concessioni di memoria in fase di esecuzione. Questa funzionalità può impedire in primo luogo problemi di concessione della memoria.
- Aumentare SQL Server o memoria del sistema operativo.
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per