query-profiling-infrastructure.md

title	Query Profiling Infrastructure
description	Learn how the SQL Server Database Engine accesses runtime information on query execution plans to understand the workload and how resource usage is driven.
author	rwestMSFT
ms.author	randolphwest
ms.reviewer	wiassaf
ms.date	09/07/2025
ms.service	sql
ms.subservice	performance
ms.topic	concept-article
ms.custom	ignite-2025
helpviewer_keywords	query plans [SQL Server] execution plans [SQL Server] query profiling lightweight query profiling lightweight profiling lwp
monikerRange	=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

Query Profiling Infrastructure

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

The [!INCLUDE ssDEnoversion] provides access to runtime information on query execution plans. One of the most important actions when a performance issue occurs, is to get precise understanding on the workload that is executing and how resource usage is being driven. Thus, access to the actual execution plan is important.

While query completion is a prerequisite for the availability of an actual query plan, live query statistics can provide real-time insights into the query execution process as the data flows from one query plan operator to another. The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc. Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues, such as long running queries, and queries that run indefinitely and never finish.

The standard query execution statistics profiling infrastructure

The query execution statistics profile infrastructure, or standard profiling, must be enabled to collect information about execution plans, namely row count, CPU, and I/O usage. The following methods of collecting execution plan information for a target session use the standard profiling infrastructure:

• SET STATISTICS XML
• SET STATISTICS PROFILE
• Live Query Statistics

Note

Selecting the button Include Live Query Statistics in [!INCLUDE ssManStudioFull] uses the standard profiling infrastructure. In later versions of [!INCLUDE ssNoVersion], if the lightweight profiling infrastructure is enabled, then it's used by live query statistics instead of standard profiling when viewed through Activity Monitor or directly querying the sys.dm_exec_query_profiles DMV.

The following methods of collecting execution plan information globally for all sessions use the standard profiling infrastructure:

• The query_post_execution_showplan extended event. To enable Extended Events, see Monitor System Activity Using Extended Events.
• The Showplan XML trace event in SQL Trace and SQL Server Profiler. For more information on this trace event, see Showplan XML Event Class.

When running an extended event session that uses the query_post_execution_showplan event, then the sys.dm_exec_query_profiles DMV is also populated, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV. For more information, see Live Query Statistics.

The lightweight query execution statistics profiling infrastructure

Starting with [!INCLUDE ssSQL14] SP2 and [!INCLUDE sssql16-md], a new lightweight query execution statistics profiling infrastructure, or lightweight profiling was introduced.

Note

Natively compiled stored procedures aren't supported with lightweight profiling.

Lightweight query execution statistics profiling infrastructure v1

Applies to: [!INCLUDE ssSQL14] SP2 through [!INCLUDE sssql16-md].

Starting with [!INCLUDE ssSQL14] SP2 and [!INCLUDE sssql16-md], the performance overhead to collect information about execution plans was reduced with the introduction of lightweight profiling. Unlike standard profiling, lightweight profiling doesn't collect CPU runtime information. However, lightweight profiling still collects row count and I/O usage information.

A new query_thread_profile extended event was also introduced that uses lightweight profiling. This extended event exposes per-operator execution statistics allowing more insight on the performance of each node and thread. A sample session using this extended event can be configured as in the following example:

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

For more information on the performance overhead of query profiling, see the blog post Developers Choice: Query progress - anytime, anywhere.

When running an extended event session that uses the query_thread_profile event, then the sys.dm_exec_query_profiles DMV is also populated using lightweight profiling, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV.

Lightweight query execution statistics profiling infrastructure v2

Applies to: [!INCLUDE sssql16-md] SP1 through [!INCLUDE ssSQL17].

[!INCLUDE sssql16-md] SP1 includes a revised version of lightweight profiling with minimal overhead. Lightweight profiling can also be enabled globally using trace flag 7412 for the versions stated previously in Applies to. A new DMF sys.dm_exec_query_statistics_xml is introduced to return the query execution plan for in-flight requests.

Starting with [!INCLUDE sssql16-md] SP2 CU3 and [!INCLUDE ssSQL17] CU11, if lightweight profiling isn't enabled globally then the new USE HINT query hint argument QUERY_PLAN_PROFILE can be used to enable lightweight profiling at the query level, for any session. When a query that contains this new hint finishes, a new query_plan_profile extended event is also output that provides an actual execution plan XML similar to the query_post_execution_showplan extended event.

Note

The query_plan_profile extended event also uses lightweight profiling even if the query hint isn't used.

A sample session using the query_plan_profile extended event can be configured like the following example:

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

Lightweight query execution statistics profiling infrastructure v3

Applies to: [!INCLUDE sql-server-2019] and later versions, and [!INCLUDE ssazure-sqldb]

[!INCLUDE sql-server-2019] and [!INCLUDE ssazure-sqldb] include a newly revised version of lightweight profiling collecting row count information for all executions. Lightweight profiling is enabled by default on [!INCLUDE sql-server-2019] and [!INCLUDE ssazure-sqldb]. In [!INCLUDE sql-server-2019] and later versions, trace flag 7412 has no effect. Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

A new DMF sys.dm_exec_query_plan_stats is introduced to return the equivalent of the last known actual execution plan for most queries, and is called last query plan statistics. The last query plan statistics can be enabled at the database level using the LAST_QUERY_PLAN_STATS database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

A new query_post_execution_plan_profile extended event collects the equivalent of an actual execution plan based on lightweight profiling, unlike query_post_execution_showplan, which uses standard profiling. [!INCLUDE ssSQL17] also offers this event starting with CU14. A sample session using the query_post_execution_plan_profile extended event can be configured like the following example:

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

Example 1 - Extended Event session using standard profiling

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

Example 2 - Extended Event session using lightweight profiling

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

Query Profiling Infrastructure usage guidance

The following table summarizes the actions to enable either standard profiling or lightweight profiling, both globally (at the server level) or in a single session. Also includes the earliest version for which the action is available.

Scope	Standard Profiling	Lightweight Profiling
Global	Extended Event session with the query_post_execution_showplan XE; Starting with [!INCLUDE ssSQL11]	Trace flag 7412; Starting with [!INCLUDE sssql16-md] SP1
Global	SQL Trace and SQL Server Profiler with the Showplan XML trace event	Extended Event session with the query_thread_profile XE; Starting with [!INCLUDE ssSQL14] SP2
Global	N/A	Extended Event session with the query_post_execution_plan_profile XE; Starting with [!INCLUDE ssSQL17] CU14 and [!INCLUDE sql-server-2019]
Session	Use SET STATISTICS XML ON	Use the QUERY_PLAN_PROFILE query hint together with an Extended Event session with the query_plan_profile XE; Starting with [!INCLUDE sssql16-md] SP2 CU3 and [!INCLUDE ssSQL17] CU11
Session	Use SET STATISTICS PROFILE ON	N/A
Session	Select the Live Query Statistics button in SSMS; Starting with [!INCLUDE ssSQL14] SP2	N/A

Remarks

Important

Due to a possible random access violation while executing a monitoring stored procedure that references sys.dm_exec_query_statistics_xml, ensure KB 4078596 is installed in [!INCLUDE sssql16-md] and [!INCLUDE ssSQL17].

Starting with lightweight profiling v2 and its low overhead, any server that isn't already CPU bound can run lightweight profiling continuously, and allow database professionals to tap into any running execution at any time, for example using Activity Monitor or directly querying sys.dm_exec_query_profiles, and get the query plan with runtime statistics.

For more information on the performance overhead of query profiling, see the blog post Developers Choice: Query progress - anytime, anywhere.

Extended Events that use lightweight profiling use information from standard profiling, in case the standard profiling infrastructure is already enabled. For example, an extended event session using query_post_execution_showplan is running, and another session using query_post_execution_plan_profile is started. The second session still uses information from standard profiling.

Note

On [!INCLUDE ssSQL17], Lightweight Profiling is off by default but is activated when an Extended Event trace relying on query_post_execution_plan_profile is started, and is then deactivated again when the trace is stopped. As a consequence, if Extended Event traces based on query_post_execution_plan_profile are frequently started and stopped on a [!INCLUDE ssSQL17] instance, you should activate Lightweight Profiling at global level with trace flag 7412 to avoid the repeated activation/deactivation overhead.

Related content

• Monitor and Tune for Performance
• Performance monitoring and tuning tools
• Open Activity Monitor in SQL Server Management Studio (SSMS)
• Activity Monitor
• Monitor performance by using the Query Store
• Monitor System Activity Using Extended Events
• sys.dm_exec_query_statistics_xml
• sys.dm_exec_query_profiles
• Set trace flags with DBCC TRACEON (Transact-SQL)
• Logical and physical showplan operator reference
• actual execution plan
• Live Query Statistics