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

適用対象: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL Server データベース エンジンSQL Server Database Engine では、クエリ実行プランのランタイム情報にアクセスする機能を提供しています。The SQL Server データベース エンジンSQL Server Database Engine provides the ability to access runtime information on query execution plans. パフォーマンスの問題が発生したときに最も重要なアクションの 1 つは、実行中のワークロードとリソース使用量が促進される仕組みを正確に把握することです。One of the most important actions when a performance issue occurs, is to get precise understanding on the workload that is executing and how resource usage is being driven. そのためには、実際の実行プランにアクセスすることが重要です。For this, access to the actual execution plan is important.

クエリの完了は、実際のクエリ プランの利用における前提条件ですが、データが 1 つのクエリ プラン演算子から別のクエリ プラン演算子に移動するので、ライブ クエリ統計からリアルタイムの分析情報をクエリ実行プロセスに提供できます。While query completion is a prerequisite for the availability of an actual query plan, live query statistics can provide real-time insights into the query execution process as the data flows from one query plan operator to another. ライブ クエリ プランには、全体的なクエリ進捗状況と演算子レベルのランタイム実行統計が表示されます。生成された行の数、経過時間、演算子の進捗状況などです。このデータはクエリの完了を待つことなくリアルタイムで利用できるため、これらの実行統計は、長時間実行されるクエリや、不明確で決して終わることのないクエリなど、クエリ パフォーマンスの問題のデバッグで非常に役立ちます。The live query plan displays the overall query progress and operator-level run-time execution statistics such as the number of rows produced, elapsed time, operator progress, etc. Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues, such as long running queries, and queries that run indefinitely and never finish.

標準クエリ実行統計プロファイリング インフラストラクチャThe standard query execution statistics profiling infrastructure

実行プラン、つまり行数、CPU、および I/O の使用状況に関する情報を収集するには、クエリ実行統計プロファイル インフラストラクチャ、すなわち標準プロファイリングを有効にする必要があります。The query execution statistics profile infrastructure, or standard profiling, must be enabled to collect information about execution plans, namely row count, CPU and I/O usage. 次のターゲット セッションの実行プラン情報収集メソッドでは、標準プロファイリング インフラストラクチャが活用されます。The following methods of collecting execution plan information for a target session leverage the standard profiling infrastructure:

注意

SQL Server Management StudioSQL Server Management Studio[ライブ クエリ統計を含む] ボタンをクリックすると、標準プロファイリング インフラストラクチャを活用できます。Clicking the button Include Live Query Statistics in SQL Server Management StudioSQL Server Management Studio leverages the standard profiling infrastructure.
SQL ServerSQL Server の上位バージョンで、軽量プロファイリング インフラストラクチャが有効になっていると、利用状況モニターを通じて表示したとき、または sys.dm_exec_query_profiles DMV を直接クエリしたときに、標準プロファイリングではなく、ライブ クエリ統計によって利用されます。In higher versions of SQL ServerSQL Server, if the lightweight profiling infrastructure is enabled, then it is leveraged by live query statistics instead of standard profiling when viewed through Activity Monitor or directly querying the sys.dm_exec_query_profiles DMV.

次のすべてのセッションのグローバルな実行プラン情報収集メソッドでは、標準プロファイリング インフラストラクチャが活用されます。The following methods of collecting execution plan information globally for all sessions leverage the standard profiling infrastructure:

query_post_execution_showplan イベントを使用する拡張イベント セッションの実行時に、sys.dm_exec_query_profiles DMV も入力されます。これによって、すべてのセッションのライブ クエリ統計が有効になり、利用状況モニターを使用することや、DMV に直接クエリを実行することができます。When running an extended event session that uses the query_post_execution_showplan event, then the sys.dm_exec_query_profiles DMV is also populated, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV. 詳細については、「 Live Query Statistics」を参照してください。For more information, see Live Query Statistics.

軽量クエリ実行統計プロファイリング インフラストラクチャThe lightweight query execution statistics profiling infrastructure

SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 および SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降で、新しい軽量クエリ実行統計プロファイリング インフラストラクチャ、すなわち軽量プロファイリングが導入されました。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), a new lightweight query execution statistics profiling infrastructure, or lightweight profiling was introduced.

注意

軽量プロファイリングでは、ネイティブ コンパイル ストアド プロシージャはサポートされていません。Natively compiled stored procedures are not supported with lightweight profiling.

軽量クエリ実行統計プロファイリング インフラストラクチャ v1Lightweight query execution statistics profiling infrastructure v1

適用対象:SQL ServerSQL Server (SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 から SQL Server 2016 (13.x)SQL Server 2016 (13.x))。Applies to: SQL ServerSQL Server ( SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 through SQL Server 2016 (13.x)SQL Server 2016 (13.x)).

SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 および SQL Server 2016 (13.x)SQL Server 2016 (13.x) 以降で、軽量プロファイリングの導入により、実行プランに関する情報を収集するパフォーマンスのオーバーヘッドが軽減されました。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 and SQL Server 2016 (13.x)SQL Server 2016 (13.x), the performance overhead to collect information about execution plans was reduced with the introduction of lightweight profiling. 標準プロファイリングと異なり、軽量プロファイリングでは CPU のランタイム情報が収集されません。Unlike standard profiling, lightweight profiling does not collect CPU runtime information. ただし、軽量プロファイリングでも行数と I/O の使用状況の情報は収集されます。However, lightweight profiling still collects row count and I/O usage information.

軽量プロファイリングを活用する新しいquery_thread_profile 拡張イベントも導入されました。A new query_thread_profile extended event was also introduced that leverages lightweight profiling. この拡張イベントでは、演算子ごとの実行統計が示されるため、各ノードおよびスレッドのパフォーマンスについて、より多くの分析情報を提供できます。This extended event exposes per-operator execution statistics allowing more insight on the performance of each node and thread. この拡張イベントを使用するサンプル セッションは、次の例のように構成できます。A sample session using this extended event can be configured as in the below example:

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 (開発者の選択: クエリの進行状況 - いつでも、どこでも) 」をご覧ください。For more information on the performance overhead of query profiling, see the blog post Developers Choice: Query progress - anytime, anywhere.

query_thread_profile イベントを使用する拡張イベント セッションの実行時に、sys.dm_exec_query_profiles DMV も軽量プロファイリングを使用して入力されます。これによって、利用状況モニターを使用することや、DMV に直接クエリを実行することができます。When running an extended event session that uses the query_thread_profile event, then the sys.dm_exec_query_profiles DMV is also populated using lightweight profiling, which enables live query statistics for all sessions, using Activity Monitor or directly querying the DMV.

軽量クエリ実行統計プロファイリング インフラストラクチャ v2Lightweight query execution statistics profiling infrastructure v2

適用対象:SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 から SQL Server 2017 (14.x)SQL Server 2017 (14.x))。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 through SQL Server 2017 (14.x)SQL Server 2017 (14.x)).

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 には、オーバーヘッドが最小限の軽量プロファイリングの改訂版が含まれます。SP1 includes a revised version of lightweight profiling with minimal overhead. 軽量プロファイリングも、適用対象の前述のバージョンで、トレース フラグ 7412 を使用してグローバルに有効にできます。Lightweight profiling can also be enabled globally using trace flag 7412 for the versions stated above in Applies to. 送信中の要求にクエリ実行プランを返すために、新しい DMF sys.dm_exec_query_statistics_xml が導入されました。A new DMF sys.dm_exec_query_statistics_xml is introduced to return the query execution plan for in-flight requests.

SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 と SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11 以降で、軽量プロファイリングがグローバルで有効でない場合、新しい USE HINT クエリ ヒント引数 QUERY_PLAN_PROFILE を使用して、任意のセッションで、クエリ レベルで軽量プロファイリングを有効にできます。Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11, if lightweight profiling is not enabled globally then the new USE HINT query hint argument QUERY_PLAN_PROFILE can be used to enable lightweight profiling at the query level, for any session. この新しいヒントを含むクエリが終了すると、新しい query_plan_profile 拡張イベントも出力され、query_post_execution_showplan 拡張イベントに類似した実際の実行プラン XML が提供されます。When a query that contains this new hint finishes, a new query_plan_profile extended event is also output that provides an actual execution plan XML similar to the query_post_execution_showplan extended event.

注意

query_plan_profile 拡張イベントではまた、クエリ ヒントが使用されない場合でも、軽量プロファイリングが活用されます。The query_plan_profile extended event also leverages lightweight profiling even if the query hint is not used.

query_plan_profile 拡張イベントを使用したサンプル セッションは下の例のように構成できます。A sample session using the query_plan_profile extended event can be configured like the example below:

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

軽量クエリ実行統計プロファイリング インフラストラクチャ v3Lightweight query execution statistics profiling infrastructure v3

適用対象:SQL ServerSQL Server (SQL Server 2019 (15.x)SQL Server 2019 (15.x) 以降)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x))

SQL Server 2019 (15.x)SQL Server 2019 (15.x) には、すべての実行の行数情報を収集する軽量プロファイリングの新しい改訂版が含まれます。includes a newly revised version of lightweight profiling collecting row count information for all executions. 軽量プロファイリングは、SQL Server 2019 (15.x)SQL Server 2019 (15.x) で既定で有効であり、トレース フラグ 7412 は機能しません。Lightweight profiling is enabled by default on SQL Server 2019 (15.x)SQL Server 2019 (15.x) and trace flag 7412 has no effect. 軽量プロファイリングは、LIGHTWEIGHT_QUERY_PROFILING データベース スコープ構成: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF; を使用して、データベース レベルで無効にできます。Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

ほとんどのクエリで最後の既知の実際の実行プランと同等のものが返されるように、最後のクエリ プランの統計と呼ばれる新しい DMF sys.dm_exec_query_plan_stats が導入されました。A new DMF sys.dm_exec_query_plan_stats is introduced to return the equivalent of the last known actual execution plan for most queries, and is called last query plan statistics. 最後のクエリ プランの統計は、LAST_QUERY_PLAN_STATS データベース スコープ構成: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON; を使用して、データベース レベルで有効にすることができます。The last query plan statistics can be enabled at the database level using the LAST_QUERY_PLAN_STATS database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;.

新しい query_post_execution_plan_profile 拡張イベントでは、標準プロファイリングを使用する query_post_execution_showplan とは異なり、軽量プロファイリングに基づいて、実際の実行プランと同等のものが収集されます。A new query_post_execution_plan_profile extended event collects the equivalent of an actual execution plan based on lightweight profiling, unlike query_post_execution_showplan which uses standard profiling. query_post_execution_plan_profile 拡張イベントを使用したサンプル セッションは下の例のように構成できます。A sample session using the query_post_execution_plan_profile extended event can be configured like the example below:

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: 標準プロファイリングを使用した拡張イベント セッションExample 1 - Extended Event session using standard profiling

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: 軽量プロファイリングを使用した拡張イベント セッションExample 2 - Extended Event session using lightweight profiling

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 Profiling Infrastruture usage guidance

以下の表は、標準プロファイリングと軽量プロファイリングをグローバル (サーバー レベル) または単一セッションで有効にするためのアクションをまとめたものです。The following table summarizes the actions to enable either standard profiling or lightweight profiling, both globally (at the server level) or in a single session. そのアクションを使用できる最も古いバージョンも記載されています。Also includes the earliest version for which the action is available.

スコープScope 標準プロファイリングStandard Profiling 軽量プロファイリングLightweight Profiling
グローバルGlobal query_post_execution_showplan XE を使用する xEvent セッション。SQL Server 2012 (11.x)SQL Server 2012 (11.x) 以降xEvent session with the query_post_execution_showplan XE; Starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x) トレース フラグ 7412。SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1 以降Trace Flag 7412; Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP1
グローバルGlobal Showplan XML トレース イベントを使用する SQL トレースおよび SQL Server Profiler。SQL Server 2000 以降SQL Trace and SQL Server Profiler with the Showplan XML trace event; Starting with SQL Server 2000 query_thread_profile XE を使用する xEvent セッション。SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 以降xEvent session with the query_thread_profile XE; Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2
グローバルGlobal - query_post_execution_plan_profile XE を使用する xEvent セッション。SQL Server 2019 (15.x)SQL Server 2019 (15.x) 以降xEvent session with the query_post_execution_plan_profile XE; Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)
SessionSession SET STATISTICS XML ON を使用。SQL Server 2000 以降Use SET STATISTICS XML ON; Starting with SQL Server 2000 query_plan_profile XE を使用する xEvent イベント セッションと共に QUERY_PLAN_PROFILE クエリ ヒントを使用。SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 および SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11 以降Use the QUERY_PLAN_PROFILE query hint together with an xEvent session with the query_plan_profile XE; Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) SP2 CU3 and SQL Server 2017 (14.x)SQL Server 2017 (14.x) CU11
SessionSession SET STATISTICS PROFILE ON を使用。SQL Server 2000 以降Use SET STATISTICS PROFILE ON; Starting with SQL Server 2000 -
SessionSession SSMS 内で[ライブ クエリ統計] ボタンをクリック。SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 以降Click the Live Query Statistics button in SSMS; Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x) SP2 -

RemarksRemarks

重要

sys.dm_exec_query_statistics_xml を参照するストアド プロシージャの監視の実行中にランダムにアクセス違反が発生する可能性があるため、KB 4078596SQL Server 2016 (13.x)SQL Server 2016 (13.x)SQL Server 2017 (14.x)SQL Server 2017 (14.x) にインストールされていることを確認してください。Due to a possible random AV while executing a monitoring stored procedure that references sys.dm_exec_query_statistics_xml, ensure KB 4078596 is installed in SQL Server 2016 (13.x)SQL Server 2016 (13.x) and SQL Server 2017 (14.x)SQL Server 2017 (14.x).

軽量プロファイリング v2 以降では、低オーバーヘッドでもあることから、CPU バインドされていない任意のサーバーで軽量プロファイリングを継続的に実行できます。データベースの専門家は、利用状況モニターを使用するか、sys.dm_exec_query_profiles に直接クエリを実行するなどして、いつでも処理中の実行から、ランタイム統計を含むクエリ プランを取得できます。Starting with lightweight profiling v2 and its low overhead, any server that is not already CPU bound can run lightweight profiling continuously, and allow database professionals to tap into any running execution at any time, for example using Activity Monitor or directly querying sys.dm_exec_query_profiles, and get the query plan with runtime statistics.

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

注意

軽量プロファイリングを利用する拡張イベントでは、標準プロファイリング インフラストラクチャが既に有効になっている場合は、標準プロファイルの情報を使用します。Extended Events that leverage lightweight profiling will use information from standard profiling in case the standard profiling infrastructure is already enabled. たとえば、query_post_execution_showplan を使用する拡張イベント セッションが実行されており、query_post_execution_plan_profile を使用する別のセッションが開始されたとします。For example, an extended event session using query_post_execution_showplan is running, and another session using query_post_execution_plan_profile is started. 2 番目のセッションは、標準プロファイルからの情報を使用し続けます。The second session will still use information from standard profiling.

参照See Also

パフォーマンスの監視とチューニング Monitor and Tune for Performance
パフォーマンス監視およびチューニング ツール Performance Monitoring and Tuning Tools
利用状況モニターを開く方法 (SQL Server Management Studio) Open Activity Monitor (SQL Server Management Studio)
利用状況モニター Activity Monitor
クエリのストアを使用した、パフォーマンスの監視 Monitoring Performance By Using the Query Store
拡張イベントを使用したシステムの使用状況の監視 Monitor System Activity Using Extended Events
sys.dm_exec_query_statistics_xml sys.dm_exec_query_statistics_xml
sys.dm_exec_query_profiles sys.dm_exec_query_profiles
トレース フラグ Trace flags
プラン表示の論理操作と物理操作のリファレンス Showplan Logical and Physical Operators Reference
実際の実行プラン actual execution plan
ライブ クエリ統計 Live Query Statistics
開発者の選択:クエリの進行状況 - いつでも、どこでもDevelopers Choice: Query progress - anytime, anywhere