sys.dm_exec_query_memory_grants (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

Retorna informações sobre todas as consultas que solicitaram e estão aguardando uma concessão de memória ou receberam uma concessão de memória. As consultas que não exigem uma concessão de memória não aparecerão nessa exibição. Por exemplo, as operações de classificação e junção de hash têm concessões de memória para execução de consulta, enquanto consultas sem uma ORDER BY cláusula não terão uma concessão de memória.

No Banco de Dados SQL do Azure, as exibições de gerenciamento dinâmico não podem expor informações que afetariam a contenção do banco de dados ou expor informações sobre outros bancos de dados aos quais o usuário tem acesso. Para evitar expor essas informações, todas as linhas que contêm dados que não pertencem ao locatário conectado são filtradas. Além disso, os valores nas colunas scheduler_id, wait_order, pool_id, group_id são filtrados; o valor da coluna é definido como NULL.

Permissões

No SQL Server, requer a permissão VIEW SERVER STATE.
No Banco de Dados SQL do Azure, requer a permissão VIEW DATABASE STATE no banco de dados.

Permissões do SQL Server 2022 e posteriores

É necessária a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Comentários

As consultas que usam exibições de gerenciamento dinâmico que incluem ORDER BY ou agregam podem aumentar o consumo de memória e, portanto, contribuir para o problema que estão solucionando.

O recurso Administrador de Recursos permite que um administrador de banco de dados distribua recursos de servidor entre pools de recursos, até um máximo de 64 pools. A partir do SQL Server 2008 (10.0.x), cada pool se comporta como uma pequena instância de servidor independente e requer dois semáforos. O número de linhas retornadas pode sys.dm_exec_query_resource_semaphores ser até 20 vezes maior do que as linhas retornadas no SQL Server 2005 (9.x).

Exemplos

Um cenário de depuração típico para o tempo limite da consulta pode investigar o seguinte:

• Verifique o status geral da memória do sistema usando sys.dm_os_memory_clerks, sys.dm_os_sys_info e vários contadores de desempenho.

• Verifique se há reservas de memória de execução de consulta em sys.dm_os_memory_clerks que type = 'MEMORYCLERK_SQLQERESERVATIONS'.

• Verifique se há consultas aguardando¹ para concessões 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;
  

  ¹ Nesse cenário, o tipo de espera costuma ser RESOURCE_SEMAPHORE. Para obter mais informações, confira sys.dm_os_wait_stats (Transact-SQL).

• Pesquisar cache para consultas com concessões de memória 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 houver suspeita de uma consulta descontrolada, examine o Plano de Execução na coluna de sys.dm_exec_query_plan e o query_plan lote text de consultas de sys.dm_exec_sql_text. Examine ainda mais as consultas com uso intensivo de memória em execução no momento, 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
  

Confira também

• sys.dm_exec_query_resource_semaphores (Transact-SQL)
• sys.dm_os_wait_stats (Transact-SQL)
• Funções e exibições de gerenciamento dinâmico relacionadas à execução (Transact-SQL)
• Guia de arquitetura de thread e tarefa
• MSSQLSERVER_701
• Solucionar problemas com erros de falta de memória no Banco de Dados SQL do Azure