Rediger

Del via

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, and more.

Because you can access this data in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance problems.

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

Warning

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.
You can use this feature with the Transact-SQL debugger.

View live query statistics for one query

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

    Screenshot from SQL Server Management Studio, showing the Live Query Stats button on toolbar.

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

    Screenshot from SQL Server Management Studio, showing the Live Query Stats button on popup menu.

  2. Execute the query. The live query plan displays the overall query progress and the run-time execution statistics (for example, elapsed time or progress) 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 timeouts.

    Screenshot from SQL Server Management Studio, showing the Live Query Stats button in showplan.

View live query statistics for any query

You can also access the live execution plan from Activity Monitor by right-clicking any query in the Processes or Active Expensive Queries table.

Screenshot of Live Query Stats button in Activity Monitor.

Remarks

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

Permissions

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

Note

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

Related content

  • Last updated on