Live Query Statistics

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

SQL Server Management Studio provides the ability to view the live execution plan of an active query. This live query plan provides real-time insights into the query execution process as the controls flow 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. This feature is available starting with SQL Server 2016 (13.x) Management Studio, however it can work with SQL Server 2014 (12.x).

Bilješka

Internally, live query statistics leverages the sys.dm_exec_query_profiles DMV.

Applies to: SQL Server (starting with SQL Server 2014 (12.x)) and Azure SQL Database.

Upozorenje

This feature is primarily intended for troubleshooting purposes. Using this feature can moderately slow the overall query performance, especially in SQL Server 2014 (12.x). For more information, see Query Profiling Infrastructure.
This feature can be used with the Transact-SQL Debugger.

To view live query statistics for one query

1. To view the live query execution plan, on the tools menu click the Include Live Query Statistics icon.

   

   You can also view access the live query execution plan by right-clicking on a selected query in Management Studio and then click Include Live Query Statistics.

   

2. Now execute the query. The live query plan displays the overall query progress and the run-time execution statistics (e.g. elapsed time, progress, etc.) for the query plan operators. The query progress information and execution statistics are periodically updated while query execution is in progress. Use this information to understand the overall query execution process and to debug long running queries, queries that run indefinitely, queries that cause tempdb overflow, and timeout issues.

   

To view live query statistics for any query

The live execution plan can also be accessed from the Activity Monitor by right-clicking on any query in the Processes or Active Expensive Queries table.

Remarks

The statistics profile infrastructure must be enabled before live query statistics can capture information about the progress of queries. Depending on the version, the overhead may be significant. For more information on this overhead, see Query Profiling Infrastructure.

Permissions

Requires the database level SHOWPLAN permission to populate the Live Query Statistics results page, and requires any permissions necessary to execute the query. On SQL Server, requires the server level VIEW SERVER STATE permission to see the live statistics.
On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database to see the live statistics. On SQL Database Standard and Basic Tiers, requires the Server admin or Microsoft Entra admin account to see the live statistics.

Bilješka

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

See Also

Execution Plans
Query Processing Architecture Guide
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
sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
Trace flags
Showplan Logical and Physical Operators Reference
Query Profiling Infrastructure