Compartir vía

sys.dm_exec_query_memory_grants (Transact-SQL)

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Instancia administrada Azure Synapse Analytics Analytics 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 mostrar información que afecte a la contención de la base de datos o que exponga datos acerca de otras bases de datos a las que el usuario tenga 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.

Nota:

Para llamar a esto desde Azure Synapse Analytics o Analytics Platform System (PDW), use el nombre sys.dm_pdw_nodes_exec_query_memory_grants. El grupo de SQL sin servidor no admite esta sintaxis en Azure Synapse Analytics.

Nombre de la columna Tipo de datos Descripción
session_id smallint Id. (SPID) de la sesión en la que se está ejecutando esta consulta.
id_de_solicitud int Id. de la solicitud. Es único en el contexto de la sesión.
scheduler_id int Id. del programador que programa esta consulta.
Dop smallint Grado de paralelismo de esta consulta.
request_time datetime Fecha y hora a la que esta consulta solicitó la concesión de memoria.
grant_time datetime Fecha y hora a la que se concedió la memoria para esta consulta. Es NULL si aún no se ha concedido la memoria.
requested_memory_kb bigint Memoria solicitada total en kilobytes.
granted_memory_kb bigint Memoria total realmente otorgada en kilobytes. Puede ser NULL si aún no se ha concedido la memoria. Para una situación típica, este valor debe ser el mismo que requested_memory_kb. En la creación de índices, el servidor puede permitir memoria adicional a petición además de la memoria concedida inicialmente.
required_memory_kb bigint Memoria mínima necesaria para ejecutar esta consulta en kilobytes. requested_memory_kb es igual o mayor que esta cantidad.
used_memory_kb bigint Memoria física usada en este momento en kilobytes.
max_used_memory_kb bigint Memoria física máxima usada hasta este momento en kilobytes.
query_cost float Costo estimado de la consulta.
timeout_sec int Tiempo de espera en segundos antes de que esta consulta abandone la solicitud de concesión de memoria.
resource_semaphore_id smallint Identificador no único del semáforo de recursos al que está esperando esta consulta.

Nota: Este identificador es único en versiones de SQL Server anteriores a SQL Server 2008 (10.0.x). Este cambio puede afectar a la solución de problemas de ejecución de consultas. Para obtener más información, vea la sección "Comentarios" más adelante en este artículo.
queue_id smallint Id. de la cola de espera en la que esta consulta espera las concesiones de memoria. NULL si ya se ha concedido la memoria.
wait_order int Orden secuencial de consultas en espera dentro del especificado queue_id. Este valor puede cambiar para una consulta determinada si otras consultas obtienen concesiones de memoria o tiempo de espera. NULL si ya se ha concedido memoria.
is_next_candidate bit Candidata para la siguiente concesión de memoria.

1 = Sí

0 = No

NULL = Ya se ha concedido la memoria.
wait_time_ms bigint Tiempo de espera en milisegundos. NULL si ya se ha concedido la memoria.
plan_handle varbinary(64) Identificador de este plan de consulta. Use sys.dm_exec_query_plan para extraer el plan XML real.
sql_handle varbinary(64) Identificador del texto de Transact-SQL para esta consulta. Use sys.dm_exec_sql_text para obtener el texto real de Transact-SQL.
group_id int Id. para el grupo de cargas de trabajo donde se está ejecutando la consulta.
pool_id int Id. del grupo de recursos de servidor al que pertenece este grupo de cargas de trabajo.
is_small tinyint Cuando se establece en 1, indica que esta concesión utiliza el semáforo de recursos pequeño. Cuando se establece en 0, indica que se utiliza un semáforo normal.
ideal_memory_kb bigint Tamaño, en kilobytes (KB), de la concesión de memoria para ajustar todo en la memoria física. Está basado en la estimación de la cardinalidad.
pdw_node_id int Identificador del nodo en el que se encuentra esta distribución.

Se aplica a: Azure Synapse Analytics, Sistema de la plataforma de análisis (PDW)
reserved_worker_count bigint Número de subprocesos de trabajo reservados.

Se aplica a: SQL Server (a partir de SQL Server 2016 (13.x)) y Azure SQL Database
used_worker_count bigint Número de subprocesos de trabajo usados en este momento.

Se aplica a: SQL Server (a partir de SQL Server 2016 (13.x)) y Azure SQL Database
max_used_worker_count bigint Número máximo de subprocesos de trabajo usados hasta este momento.

Se aplica a: SQL Server (a partir de SQL Server 2016 (13.x)) y Azure SQL Database
reserved_node_bitmap bigint Mapa de bits de nodos NUMA donde se reservan los subprocesos de trabajo.

Se aplica a: SQL Server (a partir de SQL Server 2016 (13.x)) y Azure SQL Database

Permisos

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

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VER ESTADO DE RENDIMIENTO DEL SERVIDOR en el servidor.

Comentarios

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 general de la memoria del sistema mediante sys.dm_os_memory_clerks, sys.dm_os_sys_info y varios contadores de rendimiento.

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

  • Compruebe si hay consultas en esperade 1 concesión 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;

    1 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 están ejecutando con 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 también