SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2014 の SSMS を使用した SQL Database v12 のクエリ ストアの利用

leave a comment

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 で複数のクエリが情報として格納されていることが確認できます。

image

プランを強制したい場合は、以下のようなクエリで取得できます。

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 ではありますが、クエリベースでも使用することができますので、クエリの実行情報を確認したい場合には、有効にして情報を取得してみてもいいかもしれないですね。

Share

Written by Masayuki.Ozawa

6月 6th, 2015 at 12:52 pm

Leave a Reply