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

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric の SQL データベース

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

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

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

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

• SET STATISTICS XML
• 統計プロファイルの設定
• ライブ クエリ統計

Note

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

Note

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

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

適用対象: 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 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 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_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	標準プロファイル	軽量プロファイリング
Global	query_post_execution_showplan XE を使用した拡張イベント セッション。SQL Server 2012 (11.x) 以降	トレース フラグ 7412;SQL Server 2016 (13.x) SP1 以降
Global	Showplan XML トレース イベントを含む SQL トレースと SQL Server Profiler	query_thread_profile XE を使用した拡張イベント セッション。SQL Server 2014 (12.x) SP2 以降
Global	N/A	query_post_execution_plan_profile XE を使用した拡張イベント セッション。SQL Server 2017 (14.x) CU14 および SQL Server 2019 (15.x) 以降
Session	SET STATISTICS XML ON を使用する	QUERY_PLAN_PROFILE クエリ ヒントを、query_plan_profile XE との拡張イベント セッションと共に使用します。SQL Server 2016 (13.x) SP2 CU3 および SQL Server 2017 (14.x) CU11 以降
Session	SET STATISTICS PROFILE ON を使用する	N/A
Session	SSMS で [ライブ クエリ統計 ] ボタンを選択します。SQL Server 2014 (12.x) SP2 以降	N/A

Remarks

Important

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 番目のセッションでは、引き続き標準プロファイルからの情報が使用されます。

Note

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

関連コンテンツ

• パフォーマンスの監視とチューニング
• パフォーマンスの監視とチューニング ツール
• SQL Server Management Studio (SSMS) でアクティビティ モニターを開く
• 利用状況モニター
• クエリ ストアを使用したパフォーマンスの監視
• 拡張イベントを使用したシステムの使用状況の監視
• sys.dm_exec_query_statistics_xml
• sys.dm_exec_query_profiles
• DBCC TRACEON (Transact-SQL) を使用してトレース フラグを設定する
• 論理操作と物理操作のリファレンス
• 実際の実行プラン
• ライブ クエリ統計