適用対象:
SQL ServerAzure SQL データベース
SQL Server クエリ ストアを利用することで、インメモリ OLTP で実行されているワークロードについて、ネイティブ コンパイル コードのパフォーマンスを監視する方法について説明します。
コンパイルと実行時の統計情報が収集され、ディスク ベースのワークロードと同じように公開されます。
インメモリ OLTP に移行しても、SQL Server Management Studio で引き続きクエリ ストア ビューを使用でき、移行前にディスク ベースのワークロード用に開発したカスタム スクリプトを使用できます。 これにより、クエリ ストア テクノロジを学習するための出資を節約し、これをすべてのワークロードのトラブルシューティングで使用できるようになります。
クエリ ストアの使用に関する一般情報については、「 Monitoring Performance By Using the クエリ ストア」を参照してください。
インメモリ OLTP でクエリ ストアを使用する場合、追加の機能構成は必要ありません。 データベースで有効にすれば、すべての種類のワークロードで機能します。
ただし、ユーザーがインメモリ OLTP でクエリ ストアを使用する際に注意する必要がある点がいくつかあります。
クエリ ストアを有効にすると、クエリ、プランおよびコンパイル時の統計が既定で収集されます。 ただし、ランタイム統計の収集は、sys.sp_xtp_control_query_exec_stats (Transact-SQL) で明示的に有効にした場合のみアクティブになります。
@new_collection_value を 0 に設定すると、クエリ ストアは影響を受けるプロシージャまたは SQL Server インスタンス全体のランタイム統計の収集を停止します。
sys.sp_xtp_control_query_exec_stats (Transact-SQL) で構成された値は保持されません。 SQL Server の再起動後に、統計収集を確認し、再構成するようにしてください。
通常のクエリの統計収集の場合と同じように、クエリ ストアを使用してワークロードの実行を追跡する際にパフォーマンスが低下する可能性があります。 ネイティブ コンパイル ストアド プロシージャの重要なサブセットに対してのみ、統計収集を有効にすることを検討してください。
クエリとプランは最初のネイティブ コンパイルでキャプチャおよび格納され、再コンパイルのたびに更新されます。
すべてのネイティブ ストアド プロシージャがコンパイルされてからクエリ ストアを有効にしたか、その内容をクリアした場合は、手動で再コンパイルして、クエリ ストアでキャプチャされるようにする必要があります。 sp_query_store_remove_query (Transact-SQL) または sp_query_store_remove_plan (Transct-SQL) を使用して手動でクエリを削除した場合も同様です。 sp_recompile (Transact-SQL) を使用して、プロシージャを強制的に再コンパイルしてください。
クエリ ストアではインメモリ OLTP のプラン生成メカニズムを活用して、コンパイル時にクエリ実行プランをキャプチャします。 ストアド プランは、
SET SHOWPLAN_XML ONを使用して取得するものと意味的には同じです。ただし、クエリ ストアのプランはステートメントごとに分割され、格納されます。混合ワークロードのデータベースでクエリ ストアを実行する場合は、sys.query_store_plan (Transact-SQL) の is_natively_compiled フィールドを使用して、ネイティブ コード コンパイルで生成されたクエリ プランをすばやく見つけることができます。
クエリ ストアキャプチャ モード ( ステートメントのパラメーター QUERY_CAPTURE_MODE) は、構成された値に関係なく常にキャプチャされるため、ネイティブ コンパイル モジュールからのクエリには影響しません。 これには
QUERY_CAPTURE_MODE = NONEの設定も含まれます。クエリ ストアでキャプチャされるクエリ コンパイルの期間には、ネイティブ コードが生成される前にクエリ最適化に要した時間のみが含まれます。 もっと正確に言うと、C コードのコンパイルおよび C コードの生成に必要な内部構造の生成に要した時間は含まれません。
sys.query_store_runtime_stats (Transact-SQL) 内のメモリ許可メトリックは、ネイティブ コンパイル クエリでは設定されません。この値は常に 0 です。 メモリの許可の列は、avg_query_max_used_memory、last_query_max_used_memory、min_query_max_used_memory、max_query_max_used_memory、および stdev_query_max_used_memory です。
インメモリ OLTP でのクエリ ストアの有効化および使用
エンド ツー エンド ユーザー シナリオでのクエリ ストアとインメモリ OLTP の簡単な使用例を以下に示します。 この例は、インメモリ OLTP に対してデータベース (MemoryOLTP) が有効になっていることを前提としています。
メモリ最適化テーブルの前提条件についての詳細は、「メモリ最適化テーブルおよびネイティブ コンパイル ストアド プロシージャの作成」を参照してください。
Note
Azure SQL DatabaseやMicrosoft FabricのSQLデータベースでは、USE ステートメントは利用できません。 代わりに、意図したデータベースに直接接続し、残りのコードサンプルを実行してください。
USE [MemoryOLTP];
GO
-- Create a simple memory-optimized table
CREATE TABLE dbo.Ord
(OrdNo INTEGER not null PRIMARY KEY NONCLUSTERED,
OrdDate DATETIME not null,
CustCode NVARCHAR(5) not null)
WITH (MEMORY_OPTIMIZED=ON);
GO
-- Enable Query Store before native module compilation
ALTER DATABASE [MemoryOLTP] SET QUERY_STORE = ON;
GO
-- Create natively compiled stored procedure
CREATE PROCEDURE dbo.OrderInsert(@OrdNo integer, @CustCode nvarchar(5))
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English')
DECLARE @OrdDate DATETIME = GETDATE();
INSERT INTO dbo.Ord (OrdNo, CustCode, OrdDate)
VALUES (@OrdNo, @CustCode, @OrdDate);
END;
GO
-- Enable runtime stats collection for queries from dbo.OrderInsert stored procedure
DECLARE @db_id INT = DB_ID()
DECLARE @proc_id INT = OBJECT_ID('dbo.OrderInsert');
DECLARE @collection_enabled BIT;
EXEC [sys].[sp_xtp_control_query_exec_stats] @new_collection_value = 1,
@database_id = @db_id, @xtp_object_id = @proc_id;
-- Check the state of the collection flag
EXEC sp_xtp_control_query_exec_stats @database_id = @db_id,
@xtp_object_id = @proc_id,
@old_collection_value= @collection_enabled output;
SELECT @collection_enabled AS 'collection status';
-- Execute natively compiled workload
EXEC dbo.OrderInsert 1, 'A';
EXEC dbo.OrderInsert 2, 'B';
EXEC dbo.OrderInsert 3, 'C';
EXEC dbo.OrderInsert 4, 'D';
EXEC dbo.OrderInsert 5, 'E';
-- Check Query Store Data
-- Compile time data
SELECT q.query_id, plan_id, object_id, query_hash, p.query_plan,
p.initial_compile_start_time, p.last_compile_start_time,
p.last_execution_time, p.avg_compile_duration,
p.last_force_failure_reason, p.force_failure_count
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
ON q.query_id = p.plan_id
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');
-- Get runtime stats
-- Check count_executions field to verify that runtime statistics
-- have been collected by the Query Store
SELECT q.query_id, p.plan_id, object_id, rsi.start_time, rsi.end_time,
p.last_force_failure_reason, p.force_failure_count, rs.*
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
ON q.query_id = p.plan_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE q.object_id = OBJECT_ID('dbo.OrderInsert');