Live Query Statistics
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 beginning with SQL Server 2016 Management Studio, however it can work with SQL Server 2014.
Applies to: SQL Server ( SQL Server 2014 through current version).
This feature is primarily intended for troubleshooting purposes. Using this feature can moderately slow the overall query performance. This feature can be used with the Transact-SQL Debugger.
To view live query statistics
To view the live query execution plan, on the tools menu click the 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.
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.
The live execution plan can also be accessed from the Activity Monitor by right-clicking on the queries in the Active Expensive Queries table.
The statistics profile infrastructure must be enabled before live query statistics can capture information about the progress of queries. Specifying Include Live Query Statistics in Management Studio enables the statistics infrastructure for the current query session.
Until SQL Server 2016, there are two other ways to enable the statistics infrastructure which can be used to view the live query statistics from other sessions (such as from Activity Monitor):
SET STATISTICS XML ON;or
SET STATISTICS PROFILE ON;in the target session.
Enable the query_post_execution_showplan extended event. This is a server wide setting that enable live query statistics on all sessions. To enable extended events, see Monitor System Activity Using Extended Events.
Starting with SQL Server 2016 SP1, SQL Server includes a lightweight version of the statistics profile infrastructure. There are two ways to enable the lightweight statistics infrastructure which can be used to view the live query statistics from other sessions (such as from Activity Monitor):
Use global trace flag 7412.
Enable the query_thread_profile extended event. This is a server wide setting that enable live query statistics on all sessions. To enable extended events, see Monitor System Activity Using Extended Events.
Natively compiled stored procedures are not supported.
Requires the database level SHOWPLAN permission to populate the Live Query Statistics results page, the server level VIEW SERVER STATE permission to see the live statistics, and requires any permissions necessary to execute the query.
Monitor and Tune for Performance
Performance Monitoring and Tuning Tools
Open Activity Monitor (SQL Server Management Studio)
Monitoring Performance By Using the Query Store