sys.dm_exec_query_profiles (Transact-SQL)

Artículo
07/05/2024

Se aplica a: SQL Server Azure SQL Database Azure SQL Instancia administrada

Supervisa el progreso de la consulta en tiempo real mientras la consulta está en ejecución. Por ejemplo, use esta DMV para determinar qué parte de la consulta se está ejecutando con lentitud. Combine esta DMV con otras DMV del sistema mediante las columnas identificadas en el campo de descripción. O bien, combine esta DMV con otros contadores de rendimiento (como el Monitor de rendimiento, xperf) mediante las columnas de marca de tiempo.

Tabla devuelta

Los contadores devueltos son por operador y por subproceso. Los resultados son dinámicos y no coinciden con los resultados de las opciones existentes, como SET STATISTICS XML ON las que solo crean la salida cuando finaliza la consulta.

Nombre de la columna	Tipo de datos	Descripción
session_id	smallint	Identifica la sesión en la que se ejecuta esta consulta. Hace referencia a dm_exec_sessions.session_id.
request_id	int	Identifica la solicitud de destino. Hace referencia a dm_exec_sessions.request_id.
sql_handle	varbinary(64)	Es un token que identifica de forma única el lote o el procedimiento almacenado del que forma parte la consulta. Hace referencia a dm_exec_query_stats.sql_handle.
plan_handle	varbinary(64)	Es un token que identifica de forma exclusiva un plan de ejecución de consultas de un proceso por lotes que se ha ejecutado y cuyo plan reside en la caché del plan, o se está ejecutando actualmente. Hace referencia a dm_exec_query_stats.plan_handle.
physical_operator_name	nvarchar(256)	Nombre del operador físico.
node_id	int	Identifica un nodo de operador en el árbol de consulta.
thread_id	int	Distingue los subprocesos (para una consulta en paralelo) que pertenecen al mismo nodo de operador de consulta.
task_address	varbinary(8)	Identifica la tarea de SQLOS que está utilizando este subproceso. Hace referencia a dm_os_tasks.task_address.
row_count	bigint	Número de filas que ha devuelto hasta ahora el operador.
rewind_count	bigint	Número de rebobinados hasta ahora.
rebind_count	bigint	Número de reenlaces hasta ahora.
end_of_scan_count	bigint	Número de finales de examen hasta ahora.
estimate_row_count	bigint	Número de filas estimado. Puede ser útil comparar estimated_row_count con el row_count real.
first_active_time	bigint	Hora, en milisegundos, a la que se llamó por primera vez al operador.
last_active_time	bigint	Hora, en milisegundos, a la que se llamó por última vez al operador.
open_time	bigint	Marca de tiempo al abrir (en milisegundos).
first_row_time	bigint	Marca de tiempo en la que se abrió la primera fila (en milisegundos).
last_row_time	bigint	Marca de tiempo en la que se abrió la última fila (en milisegundos).
close_time	bigint	Marca de tiempo al cerrar (en milisegundos).
elapsed_time_ms	bigint	Tiempo transcurrido total (en milisegundos) usado por las operaciones del nodo de destino hasta ahora.
cpu_time_ms	bigint	Tiempo total de CPU (en milisegundos) que usan las operaciones del nodo de destino hasta ahora.
database_id	smallint	Identificador de la base de datos que contiene el objeto en el que se efectúan las lecturas y escrituras.
object_id	int	El identificador para el objeto en el que se efectúan las lecturas y escrituras. Hace referencia a sys.objects.object_id.
id_de_índice	int	El índice (si existe) en el que se abre el conjunto de filas.
scan_count	bigint	Número de exámenes de índice o tabla hasta ahora.
logical_read_count	bigint	Número de lecturas lógicas hasta ahora.
physical_read_count	bigint	Número de lecturas físicas hasta ahora.
read_ahead_count	bigint	Número de lecturas anticipadas hasta ahora.
write_page_count	bigint	Número de escrituras en páginas hasta ahora debido al rebosamiento.
lob_logical_read_count	bigint	Número de lecturas lógicas LOB hasta ahora.
lob_physical_read_count	bigint	Número de lecturas físicas LOB hasta ahora.
lob_read_ahead_count	bigint	Número de lecturas anticipadas LOB hasta ahora.
segment_read_count	int	Número de lecturas anticipadas de segmento hasta ahora.
segment_skip_count	int	Número de segmentos omitidos hasta ahora.
actual_read_row_count	bigint	Número de filas leídas por un operador antes de aplicar el predicado residual.
estimated_read_row_count	bigint	Se aplica a: a partir de SQL Server 2016 (13.x) SP1. Número de filas estimadas que un operador debe leer antes de aplicar el predicado residual.

Notas generales

Si el nodo del plan de consulta no tiene ninguna E/S, todos los contadores relacionados con E/S se establecen en NULL.

Los contadores relacionados con E/S notificados por esta DMV son más granulares que los notificados por SET STATISTICS IO las dos maneras siguientes:

SET STATISTICS IO agrupa los contadores de todas las E/S en una tabla determinada. Con esta DMV obtendrá contadores independientes para cada nodo del plan de consulta que realiza E/S en la tabla.
Si se realizaran búsquedas en paralelo, esta DMV informa sobre los contadores para cada uno de los subprocesos paralelos que se ejecutan en la búsqueda.

A partir de SQL Server 2016 (13.x) SP1, la infraestructura de generación de perfiles de estadísticas de ejecución de consultas estándar existe en paralelo con una infraestructura ligera de generación de perfiles de estadísticas de ejecución de consultas. SET STATISTICS XML ON y SET STATISTICS PROFILE ON siempre usan la infraestructura de generación de perfiles de estadísticas de ejecución de consultas estándar. Para sys.dm_exec_query_profiles que se rellene, se debe habilitar una de las infraestructuras de generación de perfiles de consultas. Para obtener más información, vea Infraestructura de generación de perfiles de consultas.

Nota:

La consulta bajo investigación debe iniciarse después de habilitar la infraestructura de generación de perfiles de consultas, lo que lo habilita después de iniciar la consulta no generará resultados en sys.dm_exec_query_profiles. Para obtener más información sobre cómo habilitar las infraestructuras de generación de perfiles de consultas, consulte Infraestructura de generación de perfiles de consultas.

Permisos

En SQL Server y Azure SQL Instancia administrada, requiere VIEW DATABASE STATE permiso y pertenencia al db_owner rol de base de datos.
En los niveles Premium de Azure SQL Database, requiere el VIEW DATABASE STATE permiso en la base de datos.
En los objetivos de servicio de Azure SQL Database Basic, S0 y S1, y para las bases de datos de grupos elásticos, se requiere la cuenta de administrador del servidor o la cuenta de administrador de Microsoft Entra. En todos los demás objetivos de servicio de SQL Database, el VIEW DATABASE STATE permiso es necesario en la base de datos.

Permisos para SQL Server 2022 y versiones posteriores

Requiere el permiso VIEW DATABASE PERFORMANCE STATE en la base de datos.

Ejemplos

Paso 1: Inicie sesión en una sesión en la que planea ejecutar la consulta que analizará con sys.dm_exec_query_profiles. Para configurar la consulta para generar perfiles, use SET STATISTICS PROFILE ON. Ejecute la consulta en esta misma sesión.

--Configure query for profiling with sys.dm_exec_query_profiles  
SET STATISTICS PROFILE ON;  
GO  

--Or enable query profiling globally under SQL Server 2016 SP1 or above (not needed in SQL Server 2019)  
DBCC TRACEON (7412, -1);  
GO 
  
--Next, run your query in this session, or in any other session if query profiling has been enabled globally

Paso 2: Inicie sesión en una segunda sesión diferente de la sesión en la que se ejecuta la consulta.

La siguiente instrucción resume el progreso que ha realizado la consulta que se ejecutaba de forma simultánea en la sesión 54. Para ello, calcula el número total de filas resultantes de todos los subprocesos para cada nodo y lo compara con el número estimado de filas resultantes para ese nodo.

--Run this in a different session than the session in which your query is running. 
--Note that you may need to change session id 54 below with the session id you want to monitor.
SELECT node_id,physical_operator_name, SUM(row_count) row_count, 
  SUM(estimate_row_count) AS estimate_row_count, 
  CAST(SUM(row_count)*100 AS float)/SUM(estimate_row_count)  
FROM sys.dm_exec_query_profiles   
WHERE session_id=54
GROUP BY node_id,physical_operator_name  
ORDER BY node_id;

Consulte también

Funciones y vistas de administración dinámica (Transact-SQL)
Funciones y vistas de administración dinámica relacionadas con ejecuciones (Transact-SQL)

Compartir a través de