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

適用対象: SQL Server Azure SQL データベース

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

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

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

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

• SET STATISTICS XML
• SET STATISTICS PROFILE
• ライブ クエリ統計

Note

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) 以降、新しい 軽量クエリ実行統計プロファイル インフラストラクチャ、または 軽量プロファイリング が導入されました。

Note

軽量プロファイリングでは、ネイティブ コンパイル ストアド プロシージャはサポートされていません。

軽量クエリ実行統計プロファイリング インフラストラクチャ 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);

Note

クエリ プロファイリングのパフォーマンス オーバーヘッドの詳細については、ブログの投稿「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 が提供されます。

Note

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);

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

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

範囲	標準プロファイリング	軽量プロファイリング
グローバル	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) 以降
セッション	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 以降
セッション	SET STATISTICS PROFILE ON を使用。SQL Server 2000 以降	-
セッション	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 に直接クエリを実行するなどして、いつでも処理中の実行から、ランタイム統計を含むクエリ プランを取得できます。

クエリ プロファイリングのパフォーマンス オーバーヘッドの詳細については、ブログの投稿「Developers Choice: Query progress - anytime, anywhere 」(開発者の選択: クエリの進行状況 - いつでも、どこでも) を参照してください。

Note

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

Note

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
トレース フラグ
プラン表示の論理操作と物理操作のリファレンス
実際の実行プラン
ライブ クエリ統計