Compartir a través de

Infraestructura de generación de perfiles de consultas

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLBase de datos SQL en Microsoft Fabric

El motor de base de datos de SQL Server proporciona acceso a la información en tiempo de ejecución sobre los 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 lo tanto, el acceso al plan de ejecución real es importante.

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 del perfil de estadísticas de ejecución de consultas o la generación de perfiles estándar debe estar habilitada para recopilar información sobre los planes de ejecución, es decir, el recuento de filas, la CPU y el uso de E/S. Los métodos siguientes para recopilar información del plan de ejecución para una sesión de destino usan la infraestructura de generación de perfiles estándar:

Note

Al seleccionar el botón Incluir estadísticas de consultas dinámicas en SQL Server Management Studio se usa la infraestructura de generación de perfiles estándar. En versiones posteriores de SQL Server, si la infraestructura de generación de perfiles ligera está habilitada, las estadísticas de consulta activa se usan en lugar de la generación de perfiles estándar cuando se ven a través del Monitor de actividad o consultan directamente la DMV de sys.dm_exec_query_profiles .

Los siguientes métodos para recopilar información del plan de ejecución globalmente para todas las sesiones usan la infraestructura de generación de perfiles estándar:

Al ejecutar una sesión de eventos extendidos que usa el query_post_execution_showplan evento, el sys.dm_exec_query_profiles DMV también se rellena, lo que permite las estadísticas de consulta activa para todas las sesiones, mediante el Monitor de actividad o consultando directamente 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.

Note

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 2014 (12.x) SP2 a 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 generación de perfiles ligera no recopila información de tiempo de ejecución de CPU. aunque sigue recopilando la información de uso de E/S y de recuento de filas.

También se introdujo un nuevo query_thread_profile evento extendido que usa 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. Una sesión de ejemplo con este evento extendido se puede configurar 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
);

Note

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).

Al ejecutar una sesión de eventos extendidos que usa el query_thread_profile evento, el sys.dm_exec_query_profiles DMV también se rellena mediante la generación de perfiles ligera, lo que permite estadísticas de consulta dinámicas para todas las sesiones, mediante el Monitor de actividad o consultando directamente la DMV.

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

Se aplica a: SQL Server 2016 (13.x) SP1 a 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 se puede habilitar globalmente mediante la marca de seguimiento 7412 para las versiones previamente indicadas 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 ligera no está habilitada globalmente, se puede usar el nuevo argumento de QUERY_PLAN_PROFILE para habilitar la generación de perfiles ligera en el nivel de consulta para cualquier sesión. Cuando finaliza una consulta que contiene esta nueva sugerencia, también se genera un nuevo query_plan_profile evento extendido que proporciona un XML de plan de ejecución real similar al query_post_execution_showplan evento extendido.

Note

El query_plan_profile evento extendido 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 mediante el query_plan_profile evento extendido, 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 2019 (15.x) y versiones posteriores, 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. En SQL Server 2019 (15.x) y versiones posteriores, la marca de seguimiento 7412 no tiene ningún efecto. La generación de perfiles ligera se puede deshabilitar en el nivel de base de datos mediante la LIGHTWEIGHT_QUERY_PROFILINGconfiguración con ámbito de base de datos: 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 del plan de consulta se pueden habilitar en el nivel de base de datos mediante la LAST_QUERY_PLAN_STATSconfiguración con ámbito de base de datos: . ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

Un nuevo query_post_execution_plan_profile evento extendido recopila el equivalente de un plan de ejecución real basado en la generación de perfiles ligera, a diferencia query_post_execution_showplande , que usa 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 mediante el query_post_execution_plan_profile evento extendido, 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.

Scope Generación de perfiles estándar Generación de perfiles ligera
Global Sesión de eventos extendidos 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
Global Seguimiento de SQL y SQL Server Profiler con el evento de Showplan XML seguimiento Sesión de eventos extendidos con el query_thread_profile XE; A partir de SQL Server 2014 (12.x) SP2
Global N/A Sesión de eventos extendidos con el query_post_execution_plan_profile XE; A partir de SQL Server 2017 (14.x) CU14 y SQL Server 2019 (15.x)
Session Utilice SET STATISTICS XML ON Use la sugerencia de QUERY_PLAN_PROFILE consulta junto con una sesión de eventos extendidos con el query_plan_profile XE; A partir de SQL Server 2016 (13.x) SP2 CU3 y SQL Server 2017 (14.x) CU11
Session Utilice SET STATISTICS PROFILE ON N/A
Session Seleccione el botón Estadísticas de consulta activa en SSMS; A partir de SQL Server 2014 (12.x) SP2 N/A

Remarks

Important

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 la CPU puede ejecutar la generación de perfiles ligera continuamente y permitir que los profesionales de bases de datos accedan a cualquier ejecución en cualquier momento, por ejemplo, mediante el Monitor de actividad o consulta directa, y obtengan el plan de consulta sys.dm_exec_query_profilescon 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).

Eventos extendidos que usan perfiles ligeros usan información de generación de perfiles estándar, en caso de que la infraestructura de generación de perfiles estándar 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 sigue usando información de la generación de perfiles estándar.

Note

En SQL Server 2017 (14.x), la generación de perfiles ligera está desactivada de forma predeterminada, pero se activa cuando se inicia un seguimiento de eventos extendidos en query_post_execution_plan_profile el que se basa y, a continuación, se desactiva de nuevo cuando se detiene el seguimiento. Como consecuencia, si los seguimientos de eventos extendidos basados en query_post_execution_plan_profile se inician y detienen con frecuencia en una instancia de SQL Server 2017 (14.x), se debe activar la generación de perfiles liviana a nivel global con la marca de seguimiento 7412 para evitar la sobrecarga de repetidas activaciones y desactivaciones.

Contenido relacionado

  • Last updated on