Query Profiling Infrastructure

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

The SQL Server Database Engine provides the ability to access 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. For this, 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 leverage the standard profiling infrastructure:

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

The following methods of collecting execution plan information globally for all sessions leverage 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 SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x), a new lightweight query execution statistics profiling infrastructure, or lightweight profiling was introduced.

Lightweight query execution statistics profiling infrastructure v1

Applies to: SQL Server (SQL Server 2014 (12.x) SP2 through SQL Server 2016 (13.x)).

Starting with SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x), the performance overhead to collect information about execution plans was reduced with the introduction of lightweight profiling. Unlike standard profiling, lightweight profiling does not 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 leverages 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 below 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);

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: SQL Server (SQL Server 2016 (13.x) SP1 through SQL Server 2017 (14.x)).

SQL Server 2016 (13.x) 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 above 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 SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x) CU11, if lightweight profiling is not 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.

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

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: SQL Server (starting with SQL Server 2019 (15.x)) and Azure SQL Database

SQL Server 2019 (15.x) and Azure SQL Database include a newly revised version of lightweight profiling collecting row count information for all executions. Lightweight profiling is enabled by default on SQL Server 2019 (15.x) and Azure SQL Database. Starting with SQL Server 2019 (15.x), 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. SQL Server 2017 (14.x) also offers this event starting with CU14. A sample session using the query_post_execution_plan_profile extended event can be configured like the example below:

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.

Remarks

Starting with lightweight profiling v2 and its low overhead, any server that is not 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.

See Also

Monitor and Tune for Performance
Performance Monitoring and Tuning Tools
Open Activity Monitor (SQL Server Management Studio)
Activity Monitor
Monitoring Performance By Using the Query Store
Monitor System Activity Using Extended Events
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Trace flags
Showplan Logical and Physical Operators Reference
actual execution plan
Live Query Statistics