sys.dm_exec_query_memory_grants (Transact-SQL)

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure azure Synapse Analytics Analytics Platform System (PDW)

Restituisce informazioni su tutte le query richieste e in attesa di una concessione di memoria o che hanno ricevuto una concessione di memoria. Le query che non richiedono una concessione di memoria non verranno visualizzate in questa visualizzazione. Ad esempio, le operazioni di ordinamento e hash join hanno concessioni di memoria per l'esecuzione di query, mentre le query senza una ORDER BY clausola non avranno una concessione di memoria.

Nel database SQL di Azure, le viste a gestione dinamica non possono esporre le informazioni che influenzerebbero l'indipendenza del database o le informazioni sugli altri database a cui l'utente dispone di accesso. Per evitare di esporre queste informazioni, ogni riga contenente dati che non appartengono al tenant connesso viene filtrata. Inoltre, i valori nelle colonne scheduler_id, wait_orderpool_id, vengono group_id filtrati. Il valore della colonna è impostato su NULL.

Autorizzazioni

In SQL Server è richiesta VIEW SERVER STATE l'autorizzazione.
Nel database SQL di Azure è richiesta l'autorizzazione VIEW DATABASE STATE.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il server.

Osservazioni:

Le query che usano viste a gestione dinamica che includono ORDER BY o aggregazioni possono aumentare il consumo di memoria e quindi contribuire al problema che stanno risolvendo.

La funzionalità Resource Governor consente a un amministratore di database di distribuire risorse del server fra un massimo di 64 pool di risorse. A partire da SQL Server 2008 (10.0.x), ogni pool si comporta come una piccola istanza del server indipendente e richiede due semafori. Il numero di righe restituite da sys.dm_exec_query_resource_semaphores può essere fino a 20 volte superiore alle righe restituite in SQL Server 2005 (9,x).

Esempi

Uno scenario di debug tipico per il timeout delle query può analizzare quanto segue:

• Controllare lo stato complessivo della memoria del sistema usando sys.dm_os_memory_clerks, sys.dm_os_sys_info e vari contatori delle prestazioni.

• Verificare la presenza di prenotazioni di memoria di esecuzione query in sys.dm_os_memory_clerks dove type = 'MEMORYCLERK_SQLQERESERVATIONS'.

• Verificare la presenza di query in attesa^{di 1} per le concessioni usando sys.dm_exec_query_memory_grants:

  --Find all queries waiting in the memory queue  
  SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;
  

  ¹ In questo scenario il tipo di attesa è in genere RESOURCE_SEMAPHORE. Per altre informazioni, vedere sys.dm_os_wait_stats (Transact-SQL).

• Cercare nella cache le query con concessioni di memoria usando sys.dm_exec_cached_plans (Transact-SQL) e sys.dm_exec_query_plan (Transact-SQL)

  -- retrieve every query plan from the plan cache  
  USE master;  
  GO  
  SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
  GO  
  

• Se si sospetta una query in esecuzione, esaminare showplan nella query_plan colonna da sys.dm_exec_query_plan e eseguire query batch text da sys.dm_exec_sql_text. Esaminare ulteriormente le query a elevato utilizzo di memoria attualmente in esecuzione, usando sys.dm_exec_requests.

  --Active requests with memory grants
  SELECT
  --Session data 
    s.[session_id], s.open_transaction_count
  --Memory usage
  , r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
  --Query 
  , query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan, request_row_count = r.row_count, session_row_count = s.row_count
  --Session history and status
  , s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, session_status = s.[status], request_status = r.status
  --Session connection information
  , s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
  FROM sys.dm_exec_sessions s 
  LEFT OUTER JOIN sys.dm_exec_requests AS r 
      ON r.[session_id] = s.[session_id]
  LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
      ON mg.[session_id] = s.[session_id]
  OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
  OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
  OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
  WHERE mg.granted_memory_kb > 0
  ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;
  GO
  

Vedi anche

• sys.dm_exec_query_resource_semaphores (Transact-SQL)
• sys.dm_os_wait_stats (Transact- SQL)
• Funzioni e viste a gestione dinamica relative all'esecuzione (Transact-SQL)
• Guida sull'architettura dei thread e delle attività
• MSSQLSERVER_701
• Risolvere i problemi relativi a errori di memoria insufficiente con il database SQL di Azure