实时查询统计信息Live Query Statistics

适用对象:yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server Management StudioSQL 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. SQL Server 2016 (13.x)SQL Server 2016 (13.x) Management StudioManagement Studio开始支持此功能,但它可以与 SQL Server 2014 (12.x)SQL Server 2014 (12.x)配合使用。This feature is available beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) Management StudioManagement Studio, however it can work with SQL Server 2014 (12.x)SQL Server 2014 (12.x).

备注

在内部,实时查询统计信息利用 sys.dm_exec_query_profiles DMV。Internally, live query statistics leverages the sys.dm_exec_query_profiles DMV.

适用范围SQL ServerSQL ServerSQL Server 2014 (12.x)SQL Server 2014 (12.x)SQL Server 2017SQL Server 2017)。Applies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) through SQL Server 2017SQL Server 2017).

警告

此功能主要用于故障排除。This feature is primarily intended for troubleshooting purposes. 使用此功能会明显降低整体查询性能,尤其是在 SQL Server 2014 (12.x)SQL Server 2014 (12.x) 中。Using this feature can moderately slow the overall query performance, especially in SQL Server 2014 (12.x)SQL Server 2014 (12.x). 有关详细信息,请参阅查询分析基础结构For more information, see Query Profiling Infrastructure.
此功能可与 Transact-SQL 调试器配合使用。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.

    工具栏上的“实时查询统计信息”按钮Live Query Stats button on toolbar

    还可以查看实时查询执行计划,方法是在 Management StudioManagement Studio 中右键单击所选查询,然后单击“包含实时查询统计信息”。You can also view access the live query execution plan by right-clicking on a selected query in Management StudioManagement Studio and then click Include Live Query Statistics.

    弹出菜单上的“实时查询统计信息”按钮Live Query Stats button on popup menu

  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. 使用此信息可了解整个查询执行过程,以及调试长时间运行的查询、无限期运行的查询、导致 tempdb 溢出和超时问题的查询。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.

    显示计划中的“实时查询统计信息”按钮Live Query Stats button in showplan

查看任何查询的实时查询统计信息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.

活动监视器中的“实时查询统计信息”按钮Live Query Stats button in Activity Monitor

RemarksRemarks

必须启用统计信息配置文件基础结构,实时查询统计信息才能捕获查询进度的相关信息。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.

PermissionsPermissions

需要数据库级别 SHOWPLAN 权限来填充“实时查询统计信息”结果页,需要服务器级别 VIEW SERVER STATE 权限来查看实时统计信息,还需要执行查询所需的所有权限。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.

另请参阅See Also

监视和优化性能 Monitor and Tune for Performance
性能监视和优化工具 Performance Monitoring and Tuning Tools
打开活动监视器 (SQL Server Management Studio) 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_statistics_xml
sys.dm_exec_query_profiles sys.dm_exec_query_profiles
跟踪标志 Trace flags
Showplan 逻辑运算符和物理运算符参考 Showplan Logical and Physical Operators Reference
查询分析基础结构Query Profiling Infrastructure