Risolvere i problemi di prestazioni lente o memoria insufficiente causati dalle concessioni di memoria in SQL Server
Che cosa sono le concessioni di memoria?
Le concessioni di memoria, dette anche prenotazioni QE (Query Execution), Memoria 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 negli indici COLUMNSTORE perché i dizionari hash o le tabelle vengono usati in fase di esecuzione per la compilazione di indici (non un problema comune)
Per fornire un contesto, durante la durata, una query può richiedere memoria da allocatori di memoria o impiegati diversi a seconda delle operazioni da eseguire. Ad esempio, quando una query viene analizzata e compilata inizialmente, usa la memoria di compilazione. Dopo la compilazione della query, la memoria viene rilasciata e il piano di query risultante viene archiviato nella memoria della cache del piano. 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 dall'allocatore di esecuzione delle query. Inizialmente, la query richiede la memoria di esecuzione e successivamente, se viene concessa questa memoria, la query usa tutta o parte della memoria per ordinare i risultati o i bucket hash. Questa memoria allocata durante l'esecuzione della query è detta concessione di memoria. Come si può immaginare, al termine dell'operazione di esecuzione della query, la concessione di memoria viene rilasciata a SQL Server da usare per altre operazioni. Pertanto, le allocazioni di concessioni di memoria sono di natura temporanea, ma possono comunque durare molto tempo. Ad esempio, se un'esecuzione di query esegue un'operazione di ordinamento su un set di righe molto grande in memoria, l'ordinamento può richiedere molti secondi o minuti e la memoria concessa viene usata per la durata della query.
Esempio di query con una concessione di memoria
Di seguito è riportato 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 di 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 nello 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" />
Qui sono necessari diversi termini. Una query può richiedere una determinata quantità di memoria di esecuzione (DesiredMemory) e richiederebbe in genere 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 inizialmente richiesta (MaxUsedMemory). Se Query Optimizer ha sovrastimato la quantità di memoria necessaria, usa meno delle dimensioni richieste. Ma tale memoria viene sprecato perché potrebbe essere stata usata da un'altra richiesta. D'altra parte, se l'ottimizzatore ha sottovalutato le dimensioni della memoria necessarie, le righe in eccesso possono essere distribuite su disco per eseguire il lavoro in fase di esecuzione. Anziché allocare più memoria rispetto alle dimensioni inizialmente richieste, SQL Server esegue il push delle righe aggiuntive su disco e lo 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
Esaminiamo i diversi termini che potresti 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 l'ordinamento o la memoria hash viene usata durante l'esecuzione di una query. La memoria QE è in genere il consumer di memoria più grande durante la durata di una query.
Prenotazioni di esecuzione di query (QE) o prenotazioni di memoria: quando una query richiede memoria per operazioni di ordinamento o hash, effettua una richiesta di prenotazione per la memoria. La richiesta di prenotazione viene calcolata in fase di compilazione in base alla cardinalità stimata. Successivamente, quando la query viene eseguita, 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. C'è un impiegato di memoria (contabile della memoria) denominato "MEMORYCLERK_SQLQERESERVATIONS" che tiene traccia di queste allocazioni di memoria (controllare 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 è verificata una concessione di memoria. Esistono alcuni contatori delle prestazioni che usano il termine "grant". Questi contatori,
Memory Grants OutstandingeMemory Grants Pending, visualizzano il conteggio delle concessioni di memoria soddisfatte o in attesa. Non mettono in considerazione le dimensioni della concessione di memoria. Una sola query potrebbe aver utilizzato, ad esempio, 4 GB di memoria per eseguire un ordinamento, ma non si riflette in uno 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)contatore , un altro contatore, rappresenta la quantità massima di memoria dell'area di lavoro disponibile per tutte le richieste che potrebbero dover eseguire tali 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 elevato della memoria QE
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 un paio di scenari di eccezione in cui il thread non ha esito negativo, ma attende fino a quando la memoria non diventa disponibile. Uno di questi scenari è le concessioni di memoria e l'altro è la memoria di compilazione delle query. SQL Server usa un oggetto di sincronizzazione thread denominato semaforo per tenere traccia della quantità di memoria concessa per l'esecuzione di query. Se SQL Server si esaurisce dall'area di lavoro QE predefinita, invece di non riuscire la query con un errore di memoria insufficiente, fa sì che la query attenda. Dato che la memoria dell'area di lavoro può richiedere una percentuale significativa di memoria complessiva di SQL Server, l'attesa della memoria in questo spazio ha gravi implicazioni in termini di prestazioni. Un numero elevato di query simultanee ha richiesto memoria di esecuzione e, insieme, ha esaurito il pool di memoria QE o alcune query simultanee hanno richiesto concessioni molto grandi. In entrambi i casi, i problemi di prestazioni risultanti possono avere i sintomi seguenti:
- È probabile che i dati e le pagine di indice di una cache del buffer siano stati scaricati per creare spazio per le richieste di concessione di memoria di grandi dimensioni. Ciò significa che le letture di pagina provenienti dalle richieste di query devono essere soddisfatte dal disco (operazione significativamente più lenta).
- Le richieste di altre allocazioni di memoria potrebbero non riuscire 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 del completamento della risorsa e richiedono molto tempo. 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 frequentemente. Per altre informazioni, vedere Che cosa può fare uno sviluppatore sulle 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. Selezionare quelli che servono meglio per visualizzare l'immagine più grande a livello di server. Alcuni di questi strumenti potrebbero non essere disponibili per l'utente(ad esempio, Perfmon non è disponibile in database SQL di Azure). Dopo aver identificato il problema, è necessario eseguire il drill-down a livello di singola query per verificare quali query richiedono l'ottimizzazione o la riscrittura.
A livello di server, usare i metodi seguenti:
- Dmv semaforo di risorsa sys.dm_exec_query_resource_semaphores Per altre informazioni, vedere sys.dm_exec_query_resource_semaphores.
- contatori Monitor prestazioni Per altre informazioni, vedere Oggetto Gestione memoria di SQL Server.
- DBCC MEMORYSTATUS Per altre informazioni, vedere DBCC MEMORYSTATUS.
- Dmv di memoria sys.dm_os_memory_clerks per altre informazioni, vedere sys.dm_os_memory_clerks.
- Identificare le concessioni di memoria usando gli eventi estesi (XEvents) Per altre informazioni, vedere Eventi estesi (XEvents).
A livello di query individuale, usare i metodi seguenti:
- Identificare query specifiche con sys.dm_exec_query_memory_grants: attualmente in esecuzione query. Per altre informazioni, vedere sys.dm_exec_query_memory_grants.
- Identificare query specifiche con sys.dm_exec_requests: attualmente in esecuzione di query. 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.
Aggregare le statistiche sull'utilizzo della memoria
Sys.dm_exec_query_resource_semaphores DMV del semaforo di risorse
Questa DMV suddivide la memoria della prenotazione di 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 della 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 di Performance Monitor
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 è 828.288 KB. Il Memory Grants Pending contatore Perfmon con un valore diverso da zero indica che la memoria è stata esaurita.
Per altre informazioni, vedere Oggetto Gestione memoria di SQL Server.
- SQLServer, Memory Manager: memoria massima dell'area di lavoro (KB)
- SQLServer, Gestione memoria: concessioni di memoria in sospeso
- SQLServer, Gestione memoria: concessioni di memoria in sospeso
- SQLServer, Memory Manager: Memoria dell'area di lavoro concessa (KB)
DBCC MEMORYSTATUS
Un'altra posizione in cui è possibile visualizzare i dettagli sulla memoria della prenotazione di query è DBCC MEMORYSTATUS (sezione Query Memory Objects). È possibile esaminare l'output Query Memory Objects (default) per le query utente. Se Resource Governor è stato abilitato con un pool di risorse denominato PoolAdmin, ad esempio, è possibile esaminare sia Query Memory Objects (default) che Query Memory Objects (PoolAdmin).
Ecco un output di esempio da un sistema in cui sono state concesse 18 richieste di memoria di esecuzione delle query e 2 richieste sono in attesa di memoria. Il contatore disponibile è zero, che indica che non è disponibile più memoria dell'area di lavoro. Questo fatto spiega le due richieste in attesa. Wait Time Mostra il tempo trascorso in millisecondi perché 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 della query. L'output seguente mostra che le pagine allocate per le prenotazioni QE (Query Execution) superano 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
Dmv di memoria sys.dm_os_memory_clerks
Se è necessario più di un set di risultati tabulare, diverso da quello basato su DBCC MEMORYSTATUSsezione, è possibile usare sys.dm_os_memory_clerks per informazioni simili. Cercare il clerk 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'
Di seguito è riportato un output di esempio:
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identificare le concessioni di memoria usando gli eventi estesi (XEvents)
Esistono 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 una concessione di memoria maggiore durante l'esecuzione. Il mancato recupero di questa concessione di memoria aggiuntiva può causare il rallentamento della query.
- sqlserver.query_memory_grant_blocking: si verifica quando una query blocca altre query durante l'attesa di una concessione di memoria.
- sqlserver.query_memory_grant_info_sampling: si verifica alla fine delle query campionate casualmente che forniscono informazioni sulla concessione di memoria ( ad esempio, per la 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 alla fine dell'elaborazione delle query per le query con concessioni di memoria superiori a 5 MB per informare gli utenti sulle imprecisioni 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 relativi ai commenti e suggerimenti 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 dal feedback.
Avvisi di esecuzione delle query correlati alle 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 ottenere memoria non riesce. Usare questo evento in combinazione con gli 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 è insufficiente per elaborare l'input di compilazione di un hash join. Utilizzare questo evento insieme a uno qualsiasi degli
query_pre_execution_showplaneventi oquery_post_execution_showplanper determinare quale operazione nel piano generato causa lo spill hash. - sqlserver.hash_warning: si verifica quando la memoria è insufficiente per elaborare l'input di compilazione di un hash join. Ciò comporta una ricorsione hash quando l'input di compilazione viene partizionato o un salvataggio hash quando il partizionamento dell'input di compilazione supera il livello di ricorsione massimo. Utilizzare 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, solo per le operazioni di ordinamento in una query. Ad esempio, un oggetto in un'istruzione
Order BySelect. Utilizzare questo evento per identificare le query che eseguono lentamente a causa dell'operazione di ordinamento, in particolare quando =warning_type2, a indicare 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 granted_memory_kb e ideal_memory_kb campi 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. Qui sono presenti diverse colonne di interesse.
- sqlserver.column_store_index_build_memory_trace: 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
Esistono due tipi di query che è possibile trovare quando si esamina il livello di richiesta individuale. Le 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 responsabili se richiedono grandi dimensioni di memoria. Concentrarsi su di loro se si scopre che per essere il caso. Può essere comune trovare che una particolare query è l'autore del reato, ma molte istanze di esso vengono generati. Le istanze che ottengono le concessioni di memoria causano l'attesa di altre istanze della stessa query per la concessione. Indipendentemente dalle 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 della memoria richieste e concesse, è possibile eseguire una query sulla vista a sys.dm_exec_query_memory_grants gestione dinamica. Questa DMV mostra informazioni sull'esecuzione di query, non sulle informazioni cronologiche.
L'istruzione seguente ottiene i dati dalla DMV e recupera anche il testo della query e il piano di query come risultato:
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 di memoria QE attivo. La maggior parte delle query ha concesso la memoria, come illustrato da granted_memory_kb e used_memory_kb come valori numerici non NULL. Le query che non hanno ottenuto la richiesta concessa sono in attesa della memoria di esecuzione e .granted_memory_kb = NULL Inoltre, vengono inseriti in una coda di attesa con = queue_id 6. Indica wait_time_ms circa 37 secondi di attesa. La sessione 72 è successiva nella riga per ottenere una concessione come indicato da wait_order = 1, mentre la sessione 74 viene dopo di essa 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
In SQL Server è presente un tipo di attesa che indica che una query è in attesa di concessione RESOURCE_SEMAPHOREdi memoria. È possibile osservare questo tipo di attesa per sys.dm_exec_requests 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 usato la memoria. Un numero elevato di richieste in attesa e tempi di attesa lunghi indica un numero eccessivo di query simultanee che usano la memoria di esecuzione o le dimensioni di richieste di memoria di grandi dimensioni.
Nota
Il tempo di attesa per le concessioni di memoria è finito. Dopo un'eccessiva attesa (ad esempio, più di 20 minuti), SQL Server esegue il timeout della query e genera l'errore 8645, "Si è verificato un timeout durante l'attesa dell'esecuzione della query da parte delle risorse di memoria. Rieseguire la query." È possibile che venga visualizzato il valore di timeout 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 di SQL Server.
Con l'uso di sys.dm_exec_requestsè possibile visualizzare le query a cui sono state concesse la 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 sono state concesse due richieste di memoria e due dozzine di altre sono in attesa di concessioni. La granted_query_memory colonna indica 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 di memoria non si verifica in questo momento, ma si vuole identificare le query che causano l'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 di SQL Server o dopo una pressione di memoria causa una versione della cache del piano. Detto questo, è possibile trovare le informazioni qui preziose, in particolare per le statistiche di query aggregate. Un utente potrebbe aver recentemente segnalato la visualizzazione di concessioni di memoria di grandi dimensioni dalle query, ma quando si esamina il carico di lavoro del server, è possibile che il problema sia andato. In questa situazione, sys.dm_exec_query_stats può fornire informazioni dettagliate che non possono essere fornite da altri DVM. Ecco una query di esempio che consente di trovare le prime 20 istruzioni che hanno utilizzato la quantità maggiore 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 dimensioni massime di concessione superiori a 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 dettagliate ancora più potenti 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 poiché il piano di query è stato memorizzato nella cache per la prima volta.
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 Query Store è abilitato, è possibile sfruttare le statistiche cronologiche persistenti. Contrariamente ai dati di sys.dm_exec_query_stats, queste statistiche sopravvivono a un riavvio di SQL Server o a un utilizzo elevato della memoria perché sono 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 ottimale di Query Store e Mantenere i dati più rilevanti in Query Store nelle procedure consigliate per la gestione di Query Store.
Identificare se per i database è abilitato Query Store 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
sys.dm_exec_query_statsdi . Vengono visualizzate statistiche di aggregazione per le istruzioni. 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 determinata richiesta di concessione di memoria. In caso contrario, eseguire questa query di diagnostica in più database fino a trovare le concessioni di memoria sizable.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
Query di diagnostica personalizzata
Ecco una query che combina i dati di più visualizzazioni, inclusi i tre elencati in precedenza. Offre 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 restituisce 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 della risorsa di 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 dalla query stessa e non viene usata alcuna ORDER BY clausola. Se la query di diagnostica è in attesa di una concessione stessa, lo scopo della diagnosi delle concessioni di memoria verrebbe sconfitto. L'hint LOOP JOIN potrebbe potenzialmente rallentare la query di diagnostica, ma in questo caso è più importante ottenere i risultati di diagnostica.
Ecco un output di esempio abbreviato di questa query di diagnostica con solo 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 ha 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 usava solo 1 MB di 9 MB ricevuti dal server. L'output mostra anche che le sessioni 75 e 86 sono in attesa di concessioni di memoria, pertanto .RESOURCE_SEMAPHORE wait_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 adatto alle proprie esigenze. Una versione di questa query viene usata anche negli strumenti di diagnostica usati da Microsoft SQL Server.
Strumenti di diagnostica
Sono disponibili strumenti di diagnostica usati dal supporto tecnico di Microsoft SQL Server per raccogliere i log e risolvere in modo più efficiente i problemi. Sql LogScout e Pssdiag Configuration Manager (insieme a SQLDiag) raccolgono gli output delle DMV descritte in precedenza e dei contatori Monitor prestazioni 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 -- e -- dm_exec_query_memory_grants -- output. In alternativa, anziché esaminare manualmente, è possibile usare SQL Nexus per importare l'output proveniente da SQL LogScout o PSSDIAG in un database di SQL Server. SQL Nexus crea due tabelle e tbl_dm_exec_query_resource_semaphores 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 del database
In base all'esperienza di supporto Microsoft, i problemi di concessione di 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 in SQL Server a causa di operazioni di ordinamento o hash enormi. Tali query non solo utilizzano una grande quantità di memoria di SQL Server, ma causano anche l'attesa che la memoria diventi disponibile, 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 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 sulle operazioni di ordinamento e hash
Dopo aver identificato query specifiche che utilizzano una grande quantità di memoria della prenotazione di query, è possibile eseguire le operazioni necessarie per ridurre le concessioni di memoria riprogettando queste query.
Quali sono le cause delle operazioni di ordinamento e hash nelle query
Il primo passaggio consiste nel conoscere 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) comporta l'ordinamento delle righe prima di essere trasmesso 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 un'analisi di indice ordinata.L'operatore Merge Join , se selezionato da Query Optimizer, richiede l'ordinamento di entrambi gli input uniti. 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 offre una scelta tra tre operatori fisici,
Nested Loop,Merge JoineHash Join. Se SQL Server sceglie un hash join, è necessaria la memoria QE per archiviare ed elaborare i risultati intermedi. In genere, una mancanza di indici validi può portare a questo operatoreHash Joinjoin più costoso, . Per esaminare il piano di query per identificareHash Match, vedere Informazioni di riferimento sugli 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 Informazioni di riferimento sugli operatori logici e fisici.UNION (T-SQL): è simile a
DISTINCT. È possibile utilizzare 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 Informazioni di riferimento sugli operatori logici e fisici.
Conoscere questi motivi comuni consente di eliminare, il più possibile, le richieste di concessione di memoria di grandi dimensioni provenienti da SQL Server.
Modi per ridurre le operazioni di ordinamento e hash o le dimensioni delle concessioni
- 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 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 sovrastima o sottostima della richiesta di concessione e quindi portare a una richiesta di concessione inutilmente elevata o alla distribuzione di risultati sul disco, rispettivamente. Assicurarsi che le statistiche di aggiornamento automatico siano abilitate nei database e/o mantenere aggiornate le statistiche 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 per ordinare o aggregare un set di risultati più piccolo. Un set di risultati intermedio più piccolo richiede meno memoria del set di lavoro. 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 la CPU e l'I/O (questo passaggio non è sempre possibile). Se sono già presenti query ben scritte ed efficienti per le risorse, queste linee guida sono state soddisfatte.
- Creare indici nelle colonne join per facilitare l'unione di join. Le operazioni intermedie in un piano di query sono interessate dagli indici nella tabella sottostante. Ad esempio, se una tabella non dispone di un indice in una colonna join e un merge join viene trovato come l'operatore 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 operazioni hash o ordinamenti di grandi dimensioni, se possibile. I join hash vengono comunemente selezionati per elaborare input di grandi dimensioni, non ordinamento 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 per gli indici mancanti.
- Usare gli indici COLUMNSTORE, se appropriati per le query di aggregazione che usano
GROUP BY. Le query di analisi che gestiscono set di righe molto grandi e in genere eseguono aggregazioni "group by" possono richiedere blocchi di memoria di grandi dimensioni per svolgere il lavoro. Se un indice non è disponibile che fornisce risultati ordinati, viene introdotto automaticamente un ordinamento nel piano di query. Un tipo 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 ordinarlo autonomamente. Le applicazioni di creazione di report come Power BI o Reporting Services sono esempi di applicazioni di questo tipo che consentono agli utenti finali di ordinare i dati. - Prendere in considerazione, sebbene con cautela, l'uso di un hint LOOP JOIN quando i join esistono in una query T-SQL. Questa tecnica può evitare join hash o merge che usano concessioni di memoria. Tuttavia, questa opzione è consigliata solo come ultima risorsa perché l'uso forzato di 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 loop join annidato potrebbe non essere nemmeno un'opzione. In questo caso, SQL Server potrebbe non riuscire con errore MSSQLSERVER_8622, "Query Processor non è riuscito a produrre un piano di query a causa degli hint definiti in questa query".
Suggerimento per la query di concessione di memoria
A partire da SQL Server 2012 SP3, è presente un hint per la query che consente di controllare le dimensioni della concessione di memoria per ogni query. Ecco un esempio di come usare questo hint:
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
In questo caso è consigliabile usare valori conservativi, in particolare nei casi in cui si prevede l'esecuzione simultanea di molte istanze della query. Assicurarsi di testare lo stress del carico di lavoro in modo che corrisponda all'ambiente di produzione e determinare i valori da usare.
Per altre informazioni, vedere MAX_GRANT_PERCENT e MIN_GRANT_PERCENT.
Resource Governor
Memoria QE è la memoria che Resource Governor limita effettivamente quando vengono usate le impostazioni di 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 occupare fino al 25% della memoria che può essere concessa in un'istanza di SQL Server. Per altre informazioni, vedere Pool di risorse di Resource Governor e CREATE WORKLOAD GROUP.
Feedback per l'elaborazione di query adattive e la concessione 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 assegnata alla query in base alla cronologia precedente. L'obiettivo è ridurre le dimensioni della concessione quando possibile o aumentarlo quando è necessaria una quantità maggiore di memoria. Questa funzionalità è stata rilasciata in tre onde:
- Feedback delle concessioni di memoria in modalità batch in SQL Server 2017
- Feedback delle concessioni di memoria in modalità riga in SQL Server 2019
- Commenti e suggerimenti sulle concessioni di memoria sul disco usando l'archivio query e la concessione percentile in SQL Server 2022
Per altre informazioni, vedere Feedback delle concessioni di memoria . La funzionalità di concessione di 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 notino problemi di memoria provenienti dall'esecuzione di query. Tuttavia, se questa funzionalità è attiva (attivata per impostazione predefinita) e viene comunque visualizzato un utilizzo elevato della memoria QE, applicare i passaggi descritti in precedenza per riscrivere le query.
Aumentare la memoria di SQL Server o del sistema operativo
Dopo aver eseguito i passaggi per ridurre le concessioni di memoria non necessarie per le query, se si verificano ancora problemi di memoria insufficiente, il carico di lavoro richiede probabilmente più memoria. È pertanto consigliabile aumentare la memoria per SQL Server usando l'impostazione max server memory se è disponibile memoria fisica sufficiente nel sistema per farlo. Seguire le raccomandazioni per 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.
Interno delle concessioni di memoria
Per altre informazioni su alcuni elementi interni sulla memoria di esecuzione delle query, vedere il post di blog Informazioni sulle concessioni di memoria di SQL Server.
Come creare uno scenario di prestazioni con un utilizzo elevato della concessione di memoria
Infine, nell'esempio seguente viene illustrato come simulare un utilizzo elevato della memoria di esecuzione delle query e come introdurre query in attesa di 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 viene fornita per facilitare la comprensione del concetto e per facilitare l'apprendimento.
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 di SQL Server a 1.500 MB:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREAprire un prompt dei comandi e passare alla cartella delle utilità RML:
cd C:\Program Files\Microsoft Corporation\RMLUtilsUsare ostress.exe per generare più richieste simultanee rispetto a 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 di memoria.
Riepilogo dei modi per gestire 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 regolare le dimensioni delle concessioni di memoria in modo dinamico in fase di esecuzione. Questa funzionalità può impedire problemi di concessione di memoria al primo posto.
- Aumentare la memoria di SQL Server o del sistema operativo.