SQL Server 2016 CTP 2.0 の SSMS であれば、GUI ベースで、Preview: Azure SQL Database Query Store を使用することができますが、CTP 2.0 の SSMS を使っている方は、それほど多くないかと思います。
クエリ ストアですが、クエリベースでも有効化 / データの取得をすることができます。
今回の投稿では、SQL Server 2014 の SSMS を使用して、クエリベースで使用する方法をまとめてみたいと思います。
SQL Server 2016 と情報は共通ですので、 Monitoring Performance By Using the Query Store から詳細を確認することができます。
有効化ですが以下のクエリで実行することができます。
CREATE DATABASE [QueryStoreTEST] (EDITION = 'Basic', SERVICE_OBJECTIVE='Basic') ALTER DATABASE [QueryStoreTEST] SET QUERY_STORE = ON ALTER DATABASE [QueryStoreTEST] SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 300, INTERVAL_LENGTH_MINUTES = 60)
プションについては、ALTER DATABASE SET Options (Transact-SQL) から確認することができます。
データの取得については以下のようなクエリで行うことができます。
最初にテスト用のクエリを実行しておきます。
CREATE TABLE Test (Col1 uniqueidentifier) INSERT INTO Test VALUES(NEWID()), (NEWID()), (NEWID()) GO SELECT * FROM Test WHERE Col1 = NEWID() GO CREATE INDEX CIX_Test_Col1 ON Test (Col1) GO SELECT * FROM Test WHERE Col1 = NEWID() GO
次に以下のクエリを実行して QUery Store から情報を取得します。
# 直近 1 時間の集計情報を取得しています。
SELECT qsp.query_id, qsrs.plan_id, qsp.engine_version, qsp.compatibility_level, qsrs.execution_type_desc, CONVERT(datetime2(0),DATEADD(hh,9,qsrsi.start_time)) AS start_time, CONVERT(datetime2(0),DATEADD(hh,9,qsrsi.end_time)) AS end_time, CONVERT(datetime2(0),DATEADD(hh,9,qsrs.first_execution_time)) AS first_execution_time, CONVERT(datetime2(0),DATEADD(hh,9,qsrs.last_execution_time)) AS last_execution_time, CONVERT(datetime2(0),DATEADD(hh,9,qsp.initial_compile_start_time)) AS initial_compile_start_time, CONVERT(datetime2(0),DATEADD(hh,9,qsp.last_compile_start_time)) AS last_compile_start_time, CONVERT(datetime2(0),DATEADD(hh,9,qsp.last_execution_time)) AS last_execution_time, qsqt.query_sql_text, qsp.query_plan, qsrs.count_executions, qsrs.avg_duration, qsrs.last_duration, qsrs.min_duration, qsrs.max_duration, qsrs.stdev_duration, qsrs.avg_cpu_time, qsrs.last_cpu_time, qsrs.min_cpu_time, qsrs.max_cpu_time, qsrs.stdev_cpu_time, qsrs.avg_logical_io_reads, qsrs.last_logical_io_reads, qsrs.min_logical_io_reads, qsrs.max_logical_io_reads, qsrs.stdev_logical_io_reads, qsrs.avg_logical_io_writes, qsrs.last_logical_io_writes, qsrs.min_logical_io_writes, qsrs.max_logical_io_writes, qsrs.stdev_logical_io_writes, qsrs.avg_physical_io_reads, qsrs.last_physical_io_reads, qsrs.min_physical_io_reads, qsrs.max_physical_io_reads, qsrs.stdev_physical_io_reads, qsrs.avg_clr_time, qsrs.last_clr_time, qsrs.min_clr_time, qsrs.max_clr_time, qsrs.stdev_clr_time, qsrs.avg_dop, qsrs.last_dop, qsrs.min_dop, qsrs.max_dop, qsrs.stdev_dop, qsrs.avg_query_max_used_memory, qsrs.last_query_max_used_memory, qsrs.min_query_max_used_memory, qsrs.max_query_max_used_memory, qsrs.stdev_query_max_used_memory, qsrs.avg_rowcount, qsrs.last_rowcount, qsrs.min_rowcount, qsrs.max_rowcount, qsrs.stdev_rowcount, qsp.plan_group_id, qsp.is_online_index_plan, qsp.is_trivial_plan, qsp.is_parallel_plan, qsp.is_forced_plan, qsp.force_failure_count, qsp.last_force_failure_reason, qsp.last_force_failure_reason_desc, qsp.count_compiles, qsp.avg_compile_duration, qsp.last_compile_duration, qsqt.is_part_of_encrypted_module, qsqt.has_restricted_text FROM sys.query_store_runtime_stats AS qsrs LEFT JOIN sys.query_store_plan AS qsp ON qsrs.plan_id = qsp.plan_id LEFT JOIN sys.query_store_query_text AS qsqt ON qsp.query_id = qsqt.query_text_id LEFT JOIN sys.query_store_runtime_stats_interval AS qsrsi ON qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id WHERE qsrsi.start_time >= DATEADD(hh,-1, GETUTCDATE())
テスト用のクエリで、インデックス有無のクエリを実行しているため、同一の Query ID で複数のクエリが情報として格納されていることが確認できます。
プランを強制したい場合は、以下のようなクエリで取得できます。
sp_query_store_force_plan @query_id = 5, @plan_id = 7 sp_query_store_unforce_plan @query_id = 5, @plan_id = 7
SQL Database での実装は、現状 Preview ではありますが、クエリベースでも使用することができますので、クエリの実行情報を確認したい場合には、有効にして情報を取得してみてもいいかもしれないですね。