Infraestructura de generación de perfiles de consultas

  • Artículo

Se aplica a:SQL ServerAzure SQL Database

El motor de base de datos SQL Server ofrece la posibilidad de acceder a información en tiempo de ejecución sobre planes de ejecución de consultas. Una de las acciones más importantes cuando se produce un problema de rendimiento es obtener una descripción precisa de la carga de trabajo que se está ejecutando y de cómo se controla el uso de recursos. Por eso es importante el acceso al plan de ejecución real.

Aunque la finalización de una consulta es un requisito previo para la disponibilidad de un plan de consulta real, las estadísticas de consultas dinámicas pueden proporcionar información en tiempo real sobre el proceso de ejecución de las consultas a medida que los datos fluyen de un operador de plan de consulta a otro. El plan de consulta en tiempo real muestra el progreso general de la consulta y las estadísticas de ejecución a nivel de operador, como el número de filas producidas, el tiempo transcurrido, el progreso del operador, etc. Como estos datos están disponibles en tiempo real sin necesidad de esperar a que se complete la consulta, estas estadísticas de ejecución son extremadamente útiles para depurar problemas de rendimiento de la consulta, como consultas de larga duración y consultas que se ejecutan indefinidamente y nunca terminan.

Infraestructura de generación de perfiles de estadísticas de ejecución de consultas estándar

La infraestructura de generación de perfiles de estadísticas de ejecución de consultas, o generación de perfiles estándar, debe habilitarse para recopilar información sobre los planes de ejecución, concretamente el recuento de filas y el uso de CPU y E/S. Los siguientes métodos de recopilación de información de planes de ejecución para una sesión de destino aprovechan la infraestructura de generación de perfiles estándar:

Nota:

Al hacer clic en el botón Incluir estadísticas de consulta en tiempo real en SQL Server Management Studio, se aprovecha la infraestructura de generación de perfiles estándar.
En versiones superiores de SQL Server, si la infraestructura de perfilado ligero está habilitada, entonces se aprovecha de las estadísticas de consulta en vivo en lugar de la generación de perfiles estándar cuando se ve a través de la supervisión de actividad o consultando directamente el DMV sys.dm_exec_query_profiles.

Los siguientes métodos de recopilación de información de planes de ejecución para todas las sesiones aprovechan la infraestructura de generación de perfiles estándar:

Cuando se ejecuta una sesión de eventos extendidos que usa el evento query_post_execution_showplan, la DMV sys.dm_exec_query_profiles también se rellena, lo que habilita las estadísticas de consulta dinámicas para todas las sesiones mediante Monitor de actividad o una consulta directa a la DMV. Para obtener más información, consulte Live Query Statistics.

Infraestructura ligera de generación de perfiles estadísticos de ejecución de consultas

A partir de SQL Server 2014 (12.x) SP2 y SQL Server 2016 (13.x), se presentó una nueva infraestructura de generación de perfiles de estadísticas de ejecución de consultas ligeras, o perfilado ligero.

Nota:

Concretamente, los procedimientos almacenados compilados de forma nativa no se admiten con la generación de perfiles ligera.

Infraestructura de generación de perfiles de estadísticas de ejecución de consultas ligera v1

Se aplica a: SQL Server (SQL Server 2014 (12.x) SP2 hasta SQL Server 2016 (13.x)).

A partir de SQL Server 2014 (12.x) SP2 y SQL Server 2016 (13.x), la sobrecarga de rendimiento para recopilar información sobre los planes de ejecución se redujo con la introducción de la generación de perfiles ligera. A diferencia de la generación de perfiles estándar, la ligera no recopila información en tiempo de ejecución de CPU, aunque sigue recopilando la información de uso de E/S y de recuento de filas.

Además se ha incorporado un nuevo evento extendidoquery_thread_profile que aprovecha la generación de perfiles ligera. Este evento extendido expone estadísticas de ejecución por operador, lo que ofrece más información sobre el rendimiento de cada nodo y subproceso. Se puede configurar una sesión de ejemplo con este evento extendido como en el ejemplo siguiente:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Nota:

Para obtener más información sobre la sobrecarga de rendimiento de generación de perfiles de consulta, vea la entrada de blog Developers Choice: Query progress - anytime, anywhere (Elección de los desarrolladores: progreso de la consulta, en cualquier momento y en cualquier lugar).

Cuando se ejecuta una sesión de eventos extendidos que usa el evento query_thread_profile, la DMV sys.dm_exec_query_profiles también se rellena con la generación de perfiles ligera, lo que habilita las estadísticas de consulta dinámicas para todas las sesiones mediante Monitor de actividad o la consulta directa a la DMV.

Infraestructura de generación de perfiles de estadísticas de ejecución de consultas ligera v2

Se aplica a: SQL Server (SQL Server 2016 (13.x) SP1 hasta SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) SP1 incluye una versión revisada de generación de perfiles ligera con una sobrecarga mínima. La generación de perfiles ligera también puede habilitarse de forma global mediante la marca de seguimiento 7412 para las versiones indicadas anteriormente en Se aplica a. Se ha incorporado una nueva DMF sys.dm_exec_query_statistics_xml para devolver el plan de ejecución de consultas de las solicitudes en curso.

A partir de SQL Server 2016 (13.x) SP2 CU3 y SQL Server 2017 (14.x) CU11, si la generación de perfiles ligeros no está habilitada globalmente, el nuevo argumento de sugerencia de consulta USE HINTQUERY_PLAN_PROFILE se puede usar para habilitar la generación de perfiles ligeros a nivel de consulta, para cualquier sesión. Cuando una consulta que contiene esta nueva sugerencia finaliza, también se devuelve un nuevo evento extendido query_plan_profile que proporciona un archivo XML de plan de ejecución real similar al evento extendido query_post_execution_showplan.

Nota:

El evento ampliado query_plan_profile también usa la generación de perfiles ligera, incluso si no se usa la sugerencia de consulta.

Se puede configurar una sesión de ejemplo que use el evento ampliado query_plan_profile, como en el ejemplo siguiente:

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Infraestructura de generación de perfiles de estadísticas de ejecución de consultas ligera v3

Se aplica a: SQL Server (a partir de SQL Server 2019 (15.x)) y Azure SQL Database.

SQL Server 2019 (15.x) y Azure SQL Database incluyen una nueva versión revisada de generación de perfiles ligeros que recopila información de recuento de filas para todas las ejecuciones. La generación de perfiles ligeros está habilitada de manera predeterminada en SQL Server 2019 (15.x) y Azure SQL Database. A partir de SQL Server 2019 (15.x), la marca de seguimiento 7412 no tiene ningún efecto. Se puede deshabilitar la generación de perfiles ligera en el nivel de base de datos mediante la configuración de ámbito de base de datos LIGHTWEIGHT_QUERY_PROFILING: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Se ha introducido una nueva DMF sys.dm_exec_query_plan_stats para devolver el equivalente del último plan de ejecución real conocido para la mayoría de las consultas, y se llama últimas estadísticas de plan de consulta. Las últimas estadísticas de plan de consulta se pueden habilitar en el nivel de base de datos mediante la configuración de ámbito de base de datos LAST_QUERY_PLAN_STATS: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Un nuevo evento extendido query_post_execution_plan_profile recopila el equivalente de un plan de ejecución real en función de la generación de perfiles ligera, a diferencia de query_post_execution_showplan que utiliza la generación de perfiles estándar. SQL Server 2017 (14.x) también ofrece este evento a partir de CU14. Se puede configurar una sesión de ejemplo que use el evento ampliado query_post_execution_plan_profile, como en el ejemplo siguiente:

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

Ejemplo 1: Sesión de eventos extendidos mediante la generación de perfiles estándar

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Ejemplo 2: Sesión de eventos extendidos mediante la generación de perfiles ligera

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

Guía de uso de la infraestructura de generación de perfiles de consulta

En la tabla siguiente se resumen las acciones para habilitar la generación de perfiles estándar o la generación de perfiles ligera, tanto globalmente (en el nivel de servidor) como en una sola sesión. También incluye la versión más antigua para la que está disponible la acción.

Ámbito Generación de perfiles estándar Generación de perfiles ligera
Organización Sesión xEvent con el query_post_execution_showplan XE; A partir de SQL Server 2012 (11.x) Marca de seguimiento 7412; a partir de SQL Server 2016 (13.x) SP1
Organización Seguimiento de SQL y SQL Server Profiler con el evento de seguimiento Showplan XML, a partir de SQL Server 2000 Sesión xEvent con el query_thread_profile XE; A partir de SQL Server 2014 (12.x) SP2
Organización - Sesión xEvent con el query_post_execution_plan_profile XE; A partir de SQL Server 2017 (14.x) CU14 y SQL Server 2019 (15.x)
Sesión Use SET STATISTICS XML ON, a partir de SQL Server 2000. Use la sugerencia de consulta QUERY_PLAN_PROFILE junto con una sesión xEvent con el query_plan_profile XE; A partir de SQL Server 2016 (13.x) SP2 CU3 y SQL Server 2017 (14.x) CU11.
Sesión Use SET STATISTICS PROFILE ON, a partir de SQL Server 2000. -
Sesión Haga clic en el botón Estadísticas de consulta en vivo en SSMS; A partir de SQL Server 2014 (12.x) SP2 -

Comentarios

Importante

Debido a una posible infracción de acceso aleatorio al ejecutar un procedimiento almacenado de supervisión que hace referencia a sys.dm_exec_query_statistics_xml, asegúrese de que KB 4078596 esté instalado en SQL Server 2016 (13.x) y SQL Server 2017 (14.x).

A partir de la generación de perfiles ligera v2 y su baja sobrecarga, cualquier servidor que aún no esté enlazado a CPU puede ejecutar la generación de perfiles ligera continuamente y permitir que los profesionales de bases de datos pulsen en cualquier ejecución activa en cualquier momento, por ejemplo mediante Monitor de actividad o directamente al consultar a sys.dm_exec_query_profiles, y obtengan el plan de consulta con estadísticas en tiempo de ejecución.

Para obtener más información sobre la sobrecarga de rendimiento de generación de perfiles de consulta, vea la entrada de blog Developers Choice: Query progress - anytime, anywhere (Elección de los desarrolladores: progreso de la consulta, en cualquier momento y en cualquier lugar).

Nota:

Los eventos extendidos que aprovechan la generación de perfiles ligera usarán información de la generación de perfiles estándar en el caso de que la infraestructura correspondiente ya esté habilitada. Por ejemplo, se está ejecutando una sesión de evento extendido mediante query_post_execution_showplan y se inicia otra mediante query_post_execution_plan_profile. La segunda sesión seguirá utilizando la información de la generación de perfiles estándar.

Nota:

En SQL Server 2017 (14.x), la generación de perfiles ligeros está desactivada de forma predeterminada, pero se activa cuando se inicia un seguimiento XEvent que depende de query_post_execution_plan_profile y se desactiva de nuevo cuando se detiene el seguimiento. En consecuencia, si los seguimientos de Xevent basados en query_post_execution_plan_profile se inician y detienen con frecuencia en una instancia de SQL Server 2017 (14.x), se recomienda encarecidamente activar la generación de perfiles ligeros a nivel global con el marcador de seguimiento 7412 para evitar la sobrecarga de activación/desactivación repetida.

Consulte también

Supervisión y optimización del rendimiento
Herramientas de optimización y supervisión del rendimiento
Abrir el Monitor de actividad (SQL Server Management Studio)
Monitor de actividad
Supervisar el rendimiento mediante el Almacén de consultas
Supervisar la actividad del sistema mediante eventos extendidos
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Marcas de seguimiento
Referencia de operadores lógicos y físicos del plan de presentación
Mostrar un plan de ejecución real
Estadísticas de consultas activas