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

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース

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

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

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

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

注意

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

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

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 データベース には、すべての実行の行数情報を収集する軽量プロファイリングの新しい改訂版が含まれます。 SQL Server 2019 (15.x) および Azure SQL データベース では、軽量プロファイリングが既定で有効にされています。 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 とは異なり、軽量プロファイリングに基づいて、実際の実行プランと同等のものが収集されます。 このイベントは、CU14 以降の SQL Server 2017 (14.x) でも提供されています。 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 XE を使用する xEvent イベント セッションと共に QUERY_PLAN_PROFILE クエリ ヒントを使用。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 に直接クエリを実行するなどして、いつでも処理中の実行から、ランタイム統計を含むクエリ プランを取得できます。

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

注意

軽量プロファイリングを利用する拡張イベントでは、標準プロファイリング インフラストラクチャが既に有効になっている場合は、標準プロファイルの情報を使用します。 たとえば、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
トレース フラグ
プラン表示の論理操作と物理操作のリファレンス
実際の実行プラン
ライブ クエリ統計