Partilhar via

sys.dm_exec_query_memory_grants (Transact-SQL)

Aplica-se a:Banco de Dados SQL dodo AzureInstância Gerenciada SQL do Azuredo Azure Synapse Analyticsdo Analytics Platform System (PDW)Banco de Dados SQL no Microsoft Fabric

Devolve informações sobre todas as consultas que solicitaram e estão à espera de uma concessão de memória ou que receberam uma concessão de memória. Consultas que não requerem uma concessão de memória não aparecerão nesta visão. Por exemplo, as operações de ordenação e de junção por hash têm concessões de memória para execução de consultas, enquanto consultas sem cláusula ORDER BY não terão 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 possam afetar 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 esta informação, todas as linhas que contenham dados que não pertencem ao inquilino ligado 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.

Observação

Para chamar isso do Azure Synapse Analytics ou do Analytics Platform System (PDW), use o nome sys.dm_pdw_nodes_exec_query_memory_grants. Essa sintaxe não é suportada pelo pool SQL sem servidor no Azure Synapse Analytics.

Nome da coluna Tipo de dados Descrição
session_id smallint ID (SPID) da sessão onde esta consulta está a ser executada.
request_id int ID do pedido. Único no contexto da sessão.
scheduler_id int ID do agendador que está a agendar esta consulta.
dop smallint Grau de paralelismo desta consulta.
request_time datetime Data e hora em que esta consulta solicitou a concessão de memória.
grant_time datetime Data e hora em que a memória foi concedida para esta consulta. NULL se a memória ainda não for concedida.
requested_memory_kb bigint Quantidade total de memória solicitada em kilobytes.
granted_memory_kb bigint Quantidade total de memória realmente concedida em kilobytes. Pode ser NULL se a memória ainda não for concedida. Para uma situação típica, este valor deve ser o mesmo que requested_memory_kb. Para a criação de índices, o servidor pode permitir memória adicional on-demand para além da memória inicialmente concedida.
required_memory_kb bigint Memória mínima necessária para executar esta consulta em kilobytes. requested_memory_kb é igual ou maior do que este valor.
used_memory_kb bigint Memória física usada neste momento em kilobytes.
max_used_memory_kb bigint A memória física máxima usada até este momento em kilobytes.
query_cost float Custo estimado da consulta.
timeout_sec int Tempo de espera em segundos antes desta consulta entregar o pedido de concessão de memória.
resource_semaphore_id smallint ID não único do semáforo de recurso em que esta consulta está à espera.

Nota: Este ID é único em versões do SQL Server anteriores ao SQL Server 2008 (10.0.x). Esta alteração pode afetar a resolução de problemas na execução da consulta. Para mais informações, consulte a secção "Observações" mais adiante neste artigo.
queue_id smallint ID da fila de espera onde esta consulta espera por concessões de memória. NULL se a memória já for concedida.
wait_order int Ordem sequencial das consultas em espera dentro do especificado queue_id. Este valor pode mudar para uma dada consulta se outras consultas receberem concessões de memória ou timeout. NULL se a memória já for concedida.
is_next_candidate bit Candidato à próxima bolsa de memória.

1 = Sim

0 = Não

NULL = A memória já é concedida.
wait_time_ms bigint Tempo de espera em milissegundos. NULL se a memória já for concedida.
plan_handle varbinary(64) Identificador para este plano de consulta. Use sys.dm_exec_query_plan para extrair o plano XML real.
sql_handle varbinary(64) Identificador para Transact-SQL texto desta consulta. Usa sys.dm_exec_sql_text para receber a mensagem Transact-SQL real.
group_id int ID para o grupo de carga de trabalho onde esta consulta está a correr.
pool_id int ID do pool de recursos a que este grupo de carga de trabalho pertence.
is_small tinyint Quando definido para 1, indica que esta concessão utiliza o pequeno recurso semáforo. Quando definido para 0, indica que é usado um semáforo regular.
ideal_memory_kb bigint O tamanho, em kilobytes (KB), da memória permite caber tudo na memória física. Isto baseia-se na estimativa de cardinalidade.
pdw_node_id int O identificador do nó em que esta distribuição se encontra.

Aplica-se a: Azure Synapse Analytics, Analytics Platform System (PDW)
reserved_worker_count bigint Número de threads de trabalhadores reservados.

Aplica-se a: SQL Server (a partir do SQL Server 2016 (13.x)) e Azure SQL Database
used_worker_count bigint Número de fios de trabalho usados neste momento.

Aplica-se a: SQL Server (a partir do SQL Server 2016 (13.x)) e Azure SQL Database
max_used_worker_count bigint Número máximo de fios de trabalhadores usados até este momento.

Aplica-se a: SQL Server (a partir do SQL Server 2016 (13.x)) e Azure SQL Database
reserved_node_bitmap bigint Bitmap dos nós NUMA onde os worker threads são reservados.

Aplica-se a: SQL Server (a partir do SQL Server 2016 (13.x)) e Azure SQL Database

Permissions

No SQL Server, requer VIEW SERVER STATE permissão.
No Azure SQL Database, requer a VIEW DATABASE STATE permissão na base de dados.

Permissões para SQL Server 2022 e posterior

Requer a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Observações

Consultas que utilizam vistas de gestão dinâmica que incluem ORDER BY ou agregam podem aumentar o consumo de memória e, assim, contribuir para o problema que estão a resolver.

A funcionalidade Resource Governor permite a um administrador de base de dados distribuir recursos do servidor entre pools de recursos, até um máximo de 64 pools. A partir do SQL Server 2008 (10.0.x), cada pool comporta-se como uma pequena instância independente de servidor e requer dois semáforos. O número de linhas que sys.dm_exec_query_resource_semaphores são devolvidas pode ser até 20 vezes superior às linhas devolvidas no SQL Server 2005 (9.x).

Examples

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

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

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

  • Verifique se há consultas à esperade 1 para bolsas 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;

    1 Neste cenário, o tipo de espera é tipicamente RESOURCE_SEMAPHORE. Para mais informações, veja sys.dm_os_wait_stats (Transact-SQL).

  • Pesquisa na cache de 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 Apresentação na query_plan coluna de sys.dm_exec_query_plan e o lote text de consultas de sys.dm_exec_sql_text. Analise ainda as consultas intensivas em memória atualmente em execução, utilizando 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

Consulte também

  • Last updated on