sys.dm_exec_query_memory_grants (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Devuelve información sobre todas las consultas que han solicitado y están esperando una concesión de memoria o se les ha concedido una concesión de memoria. Las consultas que no requieren una concesión de memoria no aparecerán en esta vista. Por ejemplo, las operaciones de ordenación y combinación hash tienen concesiones de memoria para la ejecución de consultas, mientras que las consultas sin una ORDER BY cláusula no tendrán una concesión de memoria.

En Azure SQL Database, las vistas de administración dinámica no pueden exponer información que afectaría a la contención de la base de datos ni exponería información sobre otras bases de datos a las que el usuario tiene acceso. Para evitar exponer esta información, se filtran todas las filas que contienen datos que no pertenecen al inquilino conectado. Además, los valores de las columnas scheduler_id, wait_order, pool_id, group_id se filtran; el valor de columna se establece en NULL.

Permisos

En SQL Server, se requiere el permiso VIEW SERVER STATE.
En Azure SQL Database, requiere el permiso VIEW DATABASE STATE en la base de datos.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VIEW SERVER PERFORMANCE STATE en el servidor.

Observaciones

Las consultas que usan vistas de administración dinámica que incluyen ORDER BY o agregados pueden aumentar el consumo de memoria y, por tanto, contribuir al problema que están solucionando.

La característica del regulador de recursos permite que un administrador de bases de datos distribuya los recursos del servidor entre los grupos de recursos de servidor, hasta un máximo de 64 fondos. A partir de SQL Server 2008 (10.0.x), cada grupo se comporta como una pequeña instancia de servidor independiente y requiere dos semáforos. El número de filas devueltas desde sys.dm_exec_query_resource_semaphores puede ser de hasta 20 veces más que las filas que se devuelven en SQL Server 2005 (9.x).

Ejemplos

Un escenario de depuración típico para el tiempo de espera de consulta puede investigar lo siguiente:

• Compruebe el estado de la memoria del sistema global con sys.dm_os_memory_clerks, sys.dm_os_sys_info y diversos contadores de rendimiento.

• Compruebe si hay reservas de memoria de ejecución de consultas en donde sys.dm_os_memory_clerkstype = 'MEMORYCLERK_SQLQERESERVATIONS'.

• Compruebe si hay consultas que esperan¹ para obtener concesiones mediante 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;
  

  ¹ En este caso, el tipo de espera normalmente es RESOURCE_SEMAPHORE. Para obtener más información, consulte sys.dm_os_wait_stats (Transact-SQL).

• Buscar caché de consultas con concesiones de memoria mediante sys.dm_exec_cached_plans (Transact-SQL) y 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  
  

• Si se sospecha que hay una consulta descontrolada, examine el plan de presentación de la query_plan columna desde sys.dm_exec_query_plan y consulte el lote text de sys.dm_exec_sql_text. Examine aún más las consultas que consumen mucha memoria que se ejecutan actualmente mediante 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
  

Vea también

• sys.dm_exec_query_resource_semaphores (Transact-SQL)
• sys.dm_os_wait_stats (Transact-SQL)
• Funciones y vistas de administración dinámica relacionadas con ejecuciones (Transact-SQL)
• Guía de arquitectura de subprocesos y tareas
• MSSQLSERVER_701
• Solución de problemas de errores de memoria con Azure SQL Database