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:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Estadísticas de consultas activas

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:

• Evento query_post_execution_showplan extendido. Para habilitar eventos extendidos, consulte Supervisión de la actividad del sistema mediante eventos extendidos.
• El evento de seguimiento Showplan XML en SQL Trace y SQL Server Profiler. Para obtener más información sobre este evento de seguimiento, vea Showplan XML [clase de eventos].

Al ejecutar una sesión de eventos extendidos que usa el evento query_post_execution_showplan, la DMV sys.dm_exec_query_profiles también se completa, lo que habilita las estadísticas de consultas en directo para todas las sesiones, mediante 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.

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 de SQL Server 2016 (13.x), se redujo la sobrecarga de rendimiento al recopilar información sobre los planes de ejecución 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. Sin embargo, el perfilado ligero sigue recopilando información sobre el número de filas y el uso de E/S.

También se introdujo un nuevo query_thread_profile evento extendido que usa perfilado ligero. 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
);

Al ejecutar una sesión de eventos extendidos que usa el evento query_thread_profile, la DMV sys.dm_exec_query_profiles también se completa mediante la generación ligera de perfiles, lo que permite estadísticas de consultas en vivo para todas las sesiones, mediante Activity Monitor 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 QUERY_PLAN_PROFILE de la sugerencia de consulta USE HINT query hint para habilitar la generación de perfiles ligera a nivel de consulta en 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.

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. El perfilado ligero se puede deshabilitar a nivel de base de datos mediante la LIGHTWEIGHT_QUERY_PROFILINGconfiguración de á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 la base de datos mediante la LAST_QUERY_PLAN_STATSconfiguración de ámbito de base de datos: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

Un nuevo evento extendido query_post_execution_plan_profile recopila el equivalente a un plan de ejecución real basado en perfiles ligeros, a diferencia de query_post_execution_showplan, que usa 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 con perfilado 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 con perfilado ligero

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.

Remarks

Con el perfilado ligero v2 y su baja sobrecarga, cualquier servidor que no esté ya limitado por la CPU puede ejecutar el perfilado ligero de forma continua y permitir que los profesionales de bases de datos examinen cualquier ejecución en curso en cualquier momento, por ejemplo, mediante el Monitor de actividad o consultando directamente sys.dm_exec_query_profiles, y obtener el plan de consulta con estadísticas de 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).

Los Extended Events que usan perfilado ligero utilizan información del perfilado estándar si la infraestructura de perfilado 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 del perfilado estándar.

Contenido relacionado

• Supervisión y optimización del rendimiento
• Herramientas de supervisión y optimización del rendimiento
• Abrir el Monitor de actividad en SQL Server Management Studio (SSMS)
• Monitor de actividad
• Supervisión del 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
• Establecer marcas de seguimiento con DBCC TRACEON (Transact-SQL)
• Referencia de operador de plan de presentación lógico y físico
• plan de ejecución real
• Estadísticas de consultas activas