クエリプロファイリングインフラストラクチャ

[アーティクル]
03/18/2023

SQL Server データベースエンジンでは、クエリ実行プランのランタイム情報にアクセスする機能を提供しています。パフォーマンスの問題が発生したときに最も重要なアクションの 1 つは、実行中のワークロードとリソース使用量が促進される仕組みを正確に把握することです。そのためには、実際の実行プランにアクセスすることが重要です。

クエリの完了は、実際のクエリプランの利用における前提条件ですが、データが 1 つのクエリプラン演算子から別のクエリプラン演算子に移動するので、ライブクエリ統計からリアルタイムの分析情報をクエリ実行プロセスに提供できます。ライブクエリプランには、全体的なクエリ進捗状況と演算子レベルのランタイム実行統計が表示されます。生成された行の数、経過時間、演算子の進捗状況などです。このデータはクエリの完了を待つことなくリアルタイムで利用できるため、これらの実行統計はクエリパフォーマンス問題のデバッグで非常に役立ち、例えば、長期のクエリの実行や、不確定な実行で終わらないクエリなどです。

標準クエリ実行統計プロファイリングインフラストラクチャ

実行プラン、つまり行数、CPU、および I/O の使用状況に関する情報を収集するには、クエリ実行統計プロファイルインフラストラクチャ、すなわち標準プロファイリングを有効にする必要があります。次のターゲットセッションの実行プラン情報収集メソッドでは、標準プロファイリングインフラストラクチャが活用されます。

注意

SQL Server Management Studio で [ライブクエリ統計を含む] ボタンをクリックすると、標準プロファイリングインフラストラクチャを活用できます。
SQL Server の上位バージョンで、軽量プロファイリングインフラストラクチャが有効になっていると、利用状況モニターを通じて表示したとき、または sys.dm_exec_query_profiles DMV を直接クエリしたときに、標準プロファイリングではなく、ライブクエリ統計によって利用されます。

次のすべてのセッションのグローバルな実行プラン情報収集メソッドでは、標準プロファイリングインフラストラクチャが活用されます。

query_post_execution_showplan 拡張イベント。拡張イベントを有効にする方法については、「 Monitor System Activity Using Extended Events」を参照してください。
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 データベース

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_plan_profile 拡張イベントでは、標準プロファイリングを使用する query_post_execution_showplan とは異なり、軽量プロファイリングに基づいて、実際の実行プランと同等のものが収集されます。また、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);

クエリプロファイリングインフラストラクチャの使用に関するガイダンス

以下の表は、標準プロファイリングと軽量プロファイリングをグローバル (サーバーレベル) または単一セッションで有効にするためのアクションをまとめたものです。そのアクションを使用できる最も古いバージョンも記載されています。

Scope	標準プロファイリング	軽量プロファイリング
グローバル	`query_post_execution_showplan` XEとの xEvent セッション。SQL Server 2012 (11.x) 以降	トレースフラグ 7412では、SQL Server 2016 (13.x) SP1 以降
グローバル	`Showplan XML` トレースイベントを使用する SQL トレースおよび SQL Server Profiler。SQL Server 2000 以降	`query_thread_profile` XEとの xEvent セッション。SQL Server 2014 (12.x) SP2 以降
グローバル	-	`query_post_execution_plan_profile` XEとの xEvent セッション。SQL Server 2017 (14.x) CU14 と SQL Server 2019 (15.x)
Session	`SET STATISTICS XML ON` を使用。SQL Server 2000 以降	`QUERY_PLAN_PROFILE` クエリヒントを `query_plan_profile` XE との xEvent セッションと共に使用します。SQL Server 2016 (13.x) SP2 CU3 および SQL Server 2017 (14.x) CU11 以降
Session	`SET STATISTICS PROFILE ON` を使用。SQL Server 2000 以降	-
Session	SSMS 内で [ライブクエリ統計] ボタンをクリック。SQL Server 2014 (12.x) SP2 以降	-

解説

重要

sys.dm_exec_query_statistics_xmlを参照する監視ストアドプロシージャの実行中にランダムアクセス違反が発生する可能性があるため、KB 4078596が SQL Server 2016 (13.x) および SQL Server 2017 (14.x) にインストールされていることを確認します。

軽量プロファイリング v2 以降では、低オーバーヘッドでもあることから、CPU バインドされていない任意のサーバーで軽量プロファイリングを継続的に実行できます。データベースの専門家は、利用状況モニターを使用するか、sys.dm_exec_query_profiles に直接クエリを実行するなどして、いつでも処理中の実行から、ランタイム統計を含むクエリプランを取得できます。

注意

軽量プロファイリングを利用する拡張イベントでは、標準プロファイリングインフラストラクチャが既に有効になっている場合は、標準プロファイルの情報を使用します。たとえば、query_post_execution_showplan を使用する拡張イベントセッションが実行されており、query_post_execution_plan_profile を使用する別のセッションが開始されたとします。 2 番目のセッションは、標準プロファイルからの情報を使用し続けます。

注意

SQL Server 2017 (14.x) では、軽量プロファイルが既定でオフになっていますが、 query_post_execution_plan_profile に依存する XEvent トレースが開始されるときにアクティブ化され、トレースが停止されると再び非アクティブになります。その結果、query_post_execution_plan_profile に基づく Xevent トレースが SQL Server 2017 (14.x) インスタンスで頻繁に開始および停止される場合は、アクティブ化/非アクティブ化オーバーヘッドの繰り返しを回避するために、トレースフラグ 7412 を使用して、グローバルレベルで軽量プロファイリングをアクティブ化することを強くおすすめします。

参照

パフォーマンスの監視とチューニング
 パフォーマンス監視およびチューニングツール
 利用状況モニターを開く方法 (SQL Server Management Studio)
利用状況モニター
 クエリのストアを使用した、パフォーマンスの監視
 拡張イベントを使用したシステムの使用状況の監視
 sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles
トレースフラグ
 プラン表示の論理操作と物理操作のリファレンス
 実際の実行プラン
 ライブクエリ統計

クエリプロファイリングインフラストラクチャ

標準クエリ実行統計プロファイリングインフラストラクチャ

軽量クエリ実行統計プロファイリングインフラストラクチャ

軽量クエリ実行統計プロファイリングインフラストラクチャ v1

軽量クエリ実行統計プロファイリングインフラストラクチャ v2

軽量クエリ実行統計プロファイリングインフラストラクチャ v3

例 1: 標準プロファイリングを使用した拡張イベントセッション

例 2: 軽量プロファイリングを使用した拡張イベントセッション

クエリプロファイリングインフラストラクチャの使用に関するガイダンス

解説

参照

フィードバック

フィードバック

その他のリソース

クエリ プロファイリング インフラストラクチャ

標準クエリ実行統計プロファイリング インフラストラクチャ

軽量クエリ実行統計プロファイリング インフラストラクチャ

軽量クエリ実行統計プロファイリング インフラストラクチャ v1

軽量クエリ実行統計プロファイリング インフラストラクチャ v2

軽量クエリ実行統計プロファイリング インフラストラクチャ v3

例 1: 標準プロファイリングを使用した拡張イベント セッション

例 2: 軽量プロファイリングを使用した拡張イベント セッション

クエリ プロファイリング インフラストラクチャの使用に関するガイダンス

解説

参照

フィードバック

フィードバック

その他のリソース

クエリプロファイリングインフラストラクチャ

標準クエリ実行統計プロファイリングインフラストラクチャ

軽量クエリ実行統計プロファイリングインフラストラクチャ

軽量クエリ実行統計プロファイリングインフラストラクチャ v1

軽量クエリ実行統計プロファイリングインフラストラクチャ v2

軽量クエリ実行統計プロファイリングインフラストラクチャ v3

例 1: 標準プロファイリングを使用した拡張イベントセッション

例 2: 軽量プロファイリングを使用した拡張イベントセッション

クエリプロファイリングインフラストラクチャの使用に関するガイダンス