ネイティブ コンパイル ストアド プロシージャのパフォーマンスの監視

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

この記事では、ネイティブ コンパイル ストアド プロシージャとその他のネイティブ コンパイル T-SQL モジュールのパフォーマンスを監視する方法を説明します。

拡張イベントの使用

クエリの実行をトレースするには、 sp_statement_completed 拡張イベントを使用します。 このイベントを使用して拡張イベント セッションを作成し、オプションで、特定のネイティブ コンパイル ストアド プロシージャに対応する object_id でフィルター処理を実行します。 各クエリの実行後に、拡張イベントが生成されます。 拡張イベントによって報告された CPU 時間と期間は、クエリが使用した CPU 時間と実行時間の長さを示します。 多くの CPU 時間を使用しているネイティブ コンパイル ストアド プロシージャには、パフォーマンスの問題が存在している可能性があります。

拡張イベント内の line_number と object_id を組み合わせて使用し、クエリを調査することができます。 次のクエリを使用して、プロシージャの定義を取得することができます。 行番号を使用して、定義内のクエリを特定できます:

SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;

データ管理ビューとクエリ ストアの使用

SQL Server と Azure SQL データベース は、プロシージャ レベルとクエリ レベルの両方で、ネイティブ コンパイル ストアド プロシージャに関する実行の統計の収集をサポートしています。 パフォーマンスに与える影響が原因で、実行の統計の収集は既定では有効になっていません。

実行統計は、sys.dm_exec_procedure_stats および sys.dm_exec_query_stats のシステム ビューと、クエリ ストアにも反映されます。

プロシージャ レベルの実行統計

SQL Server: sys.sp_xtp_control_proc_exec_stats (Transact-SQL) を使用して、プロシージャ レベルでネイティブ コンパイル ストアド プロシージャの統計コレクションを有効または無効にします。 次のステートメントは、現在のインスタンス上のすべてのネイティブ コンパイル T-SQL モジュールに対し、プロシージャ レベルの実行統計のコレクションを有効にします。

EXEC sys.sp_xtp_control_proc_exec_stats 1

Azure SQL データベース および SQL Server: データベース スコープの構成オプション XTP_PROCEDURE_EXECUTION_STATISTICSを使用して、プロシージャ レベルでネイティブ コンパイル ストアド プロシージャの統計収集を有効または無効にします。 次のステートメントは、現在のデータベース内のすべてのネイティブ コンパイル T-SQL モジュールに対し、プロシージャ レベルの実行統計のコレクションを有効にします。

ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;

クエリレベルの実行の統計

SQL Server: sys.sp_xtp_control_query_exec_stats (Transact-SQL) を使用して、クエリ レベルでネイティブ コンパイル ストアド プロシージャの統計コレクションを有効または無効にします。 次のステートメントは、現在のインスタンス上のすべてのネイティブ コンパイル T-SQL モジュールに対し、クエリ レベルの実行統計のコレクションを有効にします。

EXEC sys.sp_xtp_control_query_exec_stats 1

Azure SQL データベース および SQL Server: データベース スコープの構成オプション XTP_QUERY_EXECUTION_STATISTICS を使用して、ネイティブ コンパイルされたストアド プロシージャの統計収集をステートメント レベルで有効または無効にします。 次のステートメントは、現在のデータベース内のすべてのネイティブ コンパイル T-SQL モジュールに対し、クエリ レベルの実行統計のコレクションを有効にします。

ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;

サンプル クエリ

統計を収集した後、ネイティブ コンパイル ストアド プロシージャに関する実行の統計を要求するには、プロシージャに対して sys.dm_exec_procedure_stats (Transact-SQL) を使用し、クエリに対して sys.dm_exec_query_stats (Transact-SQL) を使用することが実行できます。

次のクエリは、統計コレクションを有効にした後、現在のデータベース内で実行されたネイティブ コンパイル ストアド プロシージャに関するプロシージャ名と実行の統計を返します。

SELECT object_id, object_name(object_id) AS 'object name',
       cached_time, last_execution_time, execution_count,
       total_worker_time, last_worker_time,
       min_worker_time, max_worker_time,
       total_elapsed_time, last_elapsed_time,
       min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

次のクエリは、統計コレクションが有効になっている現在のデータベースに対して実行されたネイティブ コンパイル ストアド プロシージャ内のすべてのクエリに対応するクエリ テキストと実行の統計を返し、合計ワーカー時間の降順に並べ替えます。

SELECT st.objectid,
        OBJECT_NAME(st.objectid) AS 'object name',
        SUBSTRING(
            st.text,
            (qs.statement_start_offset/2) + 1,
            ((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
            ) AS 'query text',
        qs.creation_time, qs.last_execution_time, qs.execution_count,
        qs.total_worker_time, qs.last_worker_time, qs.min_worker_time, 
        qs.max_worker_time, qs.total_elapsed_time, qs.last_elapsed_time,
        qs.min_elapsed_time, qs.max_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE database_id = DB_ID()
      AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;

クエリ実行プラン

ネイティブ コンパイル ストアド プロシージャでは、SHOWPLAN_XML (推定実行プラン) がサポートされています。 推定実行プランを使用してクエリ プランを調査し、不適切なプランの問題を見つけることができます。 不適切なプランの一般的な理由は次のとおりです。

  • プロシージャを作成する前に統計を更新していません。

  • 欠落したインデックス

Showplan XML を取得するには、次の Transact-SQL を実行します。

SET SHOWPLAN_XML ON  
GO  
EXEC my_proc   
GO  
SET SHOWPLAN_XML OFF  
GO

代わりに、SQL Server Management Studio でプロシージャ名を選択し、[推定実行プランの表示] をクリックすることもできます。

ネイティブ コンパイル ストアド プロシージャに対応する推定実行プランでは、プロシージャ内に存在するクエリに関するクエリ演算子と式が表示されます。 SQL Server 2014 (12.x) では、ネイティブ コンパイル ストアド プロシージャに対して、すべての SHOWPLAN_XML をサポートしているわけではありません。 たとえば、クエリ オプティマイザー コストに関連する属性は、プロシージャに対応する SHOWPLAN_XML の一部ではありません。

参照

ネイティブ コンパイル ストアド プロシージャ