即時查詢統計資料Live Query Statistics

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 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 Server (SQL 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.

權限Permissions

填入 [即時查詢統計資料] 結果頁面需要資料庫等級的 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 Logical and Physical Operators Reference
查詢分析基礎結構Query Profiling Infrastructure