查詢分析基礎結構

文章
11/02/2024

SQL Server 資料庫引擎讓您能夠存取有關查詢執行計畫的執行階段資訊。發生效能問題時最重要的動作之一，是準確地了解正在執行的工作負載以及衍生資源使用量的方式。基於此因素，存取實際執行計畫就很重要。

儘管完成查詢是取得實際查詢計畫可用性的必要條件，當資料從查詢計畫運算子流到另一個運算子時，即時查詢統計資料可以提供查詢執行程序的即時深入解析。即時查詢計畫會顯示整體的查詢進度，以及運算子層級的執行階段執行統計資料，如產生的資料列數目、耗用時間、運算子進度等等。因為此資料會即時提供，不需等待查詢完成，所以，這些執行統計資料在偵錯查詢效能問題方面非常有用，例如，長時間執行查詢，以及無限期執行且永遠不會完成的查詢。

標準查詢執行統計資料分析基礎結構

「查詢執行統計資料分析基礎結構」 (或標準分析) 必須啟用，才能收集執行計畫的相關資訊，也就是資料列計數、CPU 和 I/O 使用量。下列針對目標工作階段收集執行計畫資訊的方法會利用標準分析基礎結構：

注意

按一下 SQL Server Management Studio 的 [包含即時查詢統計資料] 按鈕，利用標準分析基礎結構。
在更高版本的 SQL Server，如果已啟用輕量型分析基礎結構，則在透過活動監視器檢視或直接查詢 sys.dm_exec_query_profiles DMV 時，就會由即時查詢統計資料而不是標準分析加以利用。

下列針對所有工作階段全域收集執行計畫資訊的方法，會利用標準分析基礎結構：

query_post_execution_showplan 擴充事件。若要啟用擴充事件，請參閱使用擴充事件監視系統活動。
SQL 追蹤和 SQL Server Profiler 中的 Showplan XML 追蹤事件。如需此追蹤事件的詳細資訊，請參閱 Showplan XML 事件類別。

執行擴充事件工作階段以使用 query_post_execution_showplan 事件時，接著也會填入 sys.dm_exec_query_profiles DMV，其會使用活動監視器或直接查詢 DMV，針對所有工作階段啟用即時查詢統計資料。如需相關資訊，請參閱 Live Query Statistics。

輕量型查詢執行統計資料分析基礎結構

從 SQL Server 2014 (12.x) SP2 與 SQL Server 2016 (13.x) 開始，引進了新的輕量型查詢執行統計資料分析基礎結構 (或輕量型分析)。

注意

輕量型分析不支援原生編譯的預存程序。

輕量型查詢執行統計資料分析基礎結構 v1

適用於：SQL Server (SQL Server 2014 (12.x) SP2 到 SQL Server 2016 (13.x))。

從 SQL Server 2014 (12.x) SP2 與 SQL Server 2016 (13.x) 開始，已藉由引進輕量型分析來降低收集執行計畫相關資訊的效能額外負荷。不同於標準分析，輕量型分析不會收集 CPU 執行階段資訊。不過，輕量型分析仍會收集資料列計數和 I/O 使用量資訊。

同時，也引進了新的 query_thread_profile 擴充事件來利用輕量型分析。此擴充事件會公開每個運算子執行統計資料，以便更深入了解每個節點和執行緒的效能。您可以設定使用此擴充事件的範例工作階段，如下列範例所示：

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

注意

如需查詢分析的效能額外負荷詳細資訊，請參閱部落格文章 Developers Choice:Query progress - anytime, anywhere (開發人員選擇：查詢進度 - 隨時隨地)。

執行擴充事件工作階段以使用 query_thread_profile 事件時，接著也會使用輕量型分析填入 sys.dm_exec_query_profiles DMV，其會使用活動監視器或直接查詢 DMV，針對所有工作階段啟用即時查詢統計資料。

輕量型查詢執行統計資料分析基礎結構 v2

適用於：SQL Server (SQL Server 2016 (13.x) SP1 到 SQL Server 2017 (14.x))。

SQL Server 2016 (13.x) SP1 包含額外負荷最低的輕量型分析修訂版。針對上方「適用於」中所述的版本，也可以使用追蹤旗標 7412 全域啟用輕量型分析。已引進新的 DMF sys.dm_exec_query_statistics_xml，針對進行中的要求傳回查詢執行計畫。

從 SQL Server 2016 (13.x) SP2 CU3 與 SQL Server 2017 (14.x) CU11 開始，如未全域啟用輕量型分析，則可使用新的 USE HINT 查詢提示引數 QUERY_PLAN_PROFILE，針對任何工作階段啟用查詢層級的輕量型分析。當包含這個新提示的查詢完成時，也會輸出新的 query_plan_profile 擴充事件，以提供類似 query_post_execution_showplan 擴充事件的實際執行計畫 XML。

注意

即使未使用查詢提示，query_plan_profile 擴充事件也會利用輕量型分析。

使用 query_plan_profile 擴充事件的範例工作階段可以如以下範例所示設定：

CREATE EVENT SESSION [PerfStats_LWP_Plan] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

輕量型查詢執行統計資料分析基礎結構 v3

適用於：SQL Server (從 SQL Server 2019 (15.x) 開始) 與 Azure SQL Database

SQL Server 2019 (15.x) 與 Azure SQL Database 包含最新修訂的輕量型分析版本，可收集所有執行的資料列計數資訊。輕量型分析預設會在 SQL Server 2019 (15.x) 與 Azure SQL Database 啟用。從 SQL Server 2019 (15.x) 開始，追蹤旗標 7412 沒有任何作用。可以在資料庫層級使用 LIGHTWEIGHT_QUERY_PROFILING 資料庫範圍設定來停用輕量級分析：ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;。

現已引進新的 DMF sys.dm_exec_query_plan_stats，它在大多數查詢中會傳回最後一個已知實際執行計畫的對等項目，稱為「最後一個執行計畫統計資料」。可以在資料庫層級使用 LAST_QUERY_PLAN_STATS 資料庫範圍設定來啟用最後一個查詢計畫統計資料：ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;。

不同於使用標準分析的 query_post_execution_showplan，新的 query_post_execution_plan_profile 擴充事件會根據輕量型分析收集實際執行計畫的對等項目。 SQL Server 2017 (14.x) 也會從 CU14 開始提供此事件。使用 query_post_execution_plan_profile 擴充事件的範例工作階段可依照下列範例進行設定：

CREATE EVENT SESSION [PerfStats_LWP_All_Plans] ON SERVER
ADD EVENT sqlserver.query_post_execution_plan_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

範例 1 - 使用標準分析的擴充事件工作階段

CREATE EVENT SESSION [QueryPlanOld] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename = N'C:\Temp\QueryPlanStd.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

範例 2 - 使用輕量型分析的擴充事件工作階段

CREATE EVENT SESSION [QueryPlanLWP] ON SERVER 
ADD EVENT sqlserver.query_post_execution_plan_profile(
    ACTION(sqlos.task_time, sqlserver.database_id, 
    sqlserver.database_name, sqlserver.query_hash_signed, 
    sqlserver.query_plan_hash_signed, sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\QueryPlanLWP.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, 
    MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF);

查詢分析基礎結構使用方式指導

下表摘要說明啟用標準分析或輕量型分析 (全域 (在伺服器層級) 或在單一工作階段中) 的動作。也包括支援動作的最新版本。

影響範圍	標準分析	輕量型分析
全域	XEvent 會話與 `query_post_execution_showplan` XE;從 SQL Server 2012 開始（11.x）	追蹤旗標 7412；從 SQL Server 2016 (13.x) SP1 開始
全域	具有 `Showplan XML` 追蹤事件的 SQL 追蹤與 SQL Server Profiler；從 SQL Server 2000 開始	XEvent 會話與 `query_thread_profile` XE;從 SQL Server 2014 （12.x） SP2 開始
全球	-	XEvent 會話與 `query_post_execution_plan_profile` XE;從 SQL Server 2017 （14.x） CU14 和 SQL Server 2019 （15.x）開始
工作階段	使用 `SET STATISTICS XML ON`；從 SQL Server 2000 開始	搭配 `QUERY_PLAN_PROFILE` 使用查詢提示與 XE 的 XEvent 會話 `query_plan_profile` ;從 SQL Server 2016 （13.x） SP2 CU3 和 SQL Server 2017 （14.x） CU11 開始
工作階段	使用 `SET STATISTICS PROFILE ON`；從 SQL Server 2000 開始	-
工作階段	按一下 SSMS 的 [即時查詢統計資料]按鈕；從 SQL Server 2014 (12.x) SP2 開始	-

備註

重要

由於執行參考 sys.dm_exec_query_statistics_xml 的監視預存程序時可能會產生隨機 AV，因此請確保會在 SQL Server 2016 (13.x) 與 SQL Server 2017 (14.x) 安裝 KB 4078596。

從輕量型分析 v2 及其低額外負荷開始，尚未受限於 CPU 的任何伺服器都可持續執行輕量型分析，並讓資料庫專業人員可隨時點選任何執行中的執行 (例如，使用活動監視器，或直接查詢 sys.dm_exec_query_profiles)，並取得含執行階段統計資料的查詢計畫。

如需查詢分析的效能額外負荷詳細資訊，請參閱部落格文章 Developers Choice:Query progress - anytime, anywhere (開發人員選擇：查詢進度 - 隨時隨地)。

注意

如果已啟用標準分析基礎結構，採用輕量型分析的擴充事件將會使用標準分析中的資訊。例如，假設在使用 query_post_execution_showplan 的擴充事件工作階段執行時，啟動了另一個使用 query_post_execution_plan_profile 的工作階段。第二個工作階段仍會使用標準分析中的資訊。

注意

在 SQL Server 2017 (14.x) 上，輕量型分析預設為關閉，但會在 XEvent 追蹤依賴 query_post_execution_plan_profile 啟動時啟用，然後在追蹤停止時再次停用。因此，如果 SQL Server 2017 (14.x) 執行個體經常啟動和停止以 query_post_execution_plan_profile 為基礎的 Xevent 追蹤，則強烈建議在具有追蹤旗標 7412 的全域層級啟用輕量型分析，以避免重複啟用/停用負擔。

另請參閱

效能的監視與微調
 效能監視及微調工具
 開啟活動監視器 (SQL Server Management Studio)
活動監視器
 使用查詢存放區監視效能
 使用擴充事件監視系統活動
 sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
追蹤旗標 \(機器翻譯\)
執行程序邏輯和實體運算子參考
 實際執行計畫
 即時查詢統計資料

分享方式：