クエリの性能を改善していくための指標を明確にする

改善の指標と目標を明確にしましょう。
いろいろな指標がありますが私が気にしたことがある指標は以下だけです。

経過時間　応答を返すまでの時間。ロック待ちなどで経過時間とCPU時間は乖離する。
CPU時間　CPUを使った時間。並列処理されている場合などは経過時間より長くなる。
論理読み取り　バッファキャッシュから読み取られたページ数。
物理読み取り　ディスクから読み取られたページ数。
ログIO　トランザクションログのIO。
TempDB DB内の一時領域

実行時間や消費しているリソースの確認方法

クエリの実行時間や消費しているリソースを確認する方法は複数ありますが、通常はset statistics time, io onとクエリストアのsys.query_store_runtime_statsをメインで使います。クエリストアが利用できないリードレプリカではsys.dm_exec_query_statsを使う必要があります。

set statistics time, io on

こんな感じで表示されます。読み取り数とかはページ数です。テーブル名が worktable、workfile として出力されます。これが出力される場合には、tempdb を利用していることを示します。

SET STATISTICS IO, TIME ON;

SELECT
*
FROM test t1, test2 t2

SET STATISTICS IO, TIME OFF;

SET STATISTICS IO, TIME OFF;を忘れないようにしましょう。結果が変になります。

クエリストア

バージョンにもよりますが、AzureのSQL Databaseだとデフォルトではすべてのクエリがクエリストアに記録されるわけではありません。すべてのクエリを記録する場合は以下のように設定を変更します。

ALTER DATABASE [データベース名]
SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);

クエリストアの場合はsys.query_store_runtime_statsの以下の項目を使います。avg以外に、min、max、lastがあります。

経過時間（マイクロ秒） avg_duration
CPU時間（マイクロ秒） avg_cpu_time
論理読み取り（ページ数） avg_logical_io_reads
物理読み取り（ページ数） avg_physical_io_reads
LogIO（バイト数） avg_log_bytes_used
TempDB（ページ数） avg_tempdb_space_used

SELECT
q.query_id AS [クエリID], 
p.plan_id AS [プランID], 
s.first_execution_time AS [集計期間内の最初の実行日時], 
s.last_execution_time AS [集計期間内の最後の実行日時], 
t.query_sql_text AS [クエリのテキスト], 
CAST( p.query_plan AS XML) AS [実行計画], 
s.avg_duration AS [経過時間], 
s.avg_cpu_time AS [CPU時間], 
s.avg_logical_io_reads AS [論理IO], 
s.avg_physical_io_reads AS [物理IO],
s.avg_log_bytes_used AS [LogIO], 
s.avg_tempdb_space_used AS [TempDB]
FROM 
sys.query_store_query_text t
INNER JOIN sys.query_store_query q ON t.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats s ON p.plan_id = s.plan_id
WHERE t.query_sql_text LIKE '%SQL文%'

XMLにキャストすると実行計画がリンクになってスムーズに中身を確認できるようになりますが、サイズが大きいとキャストが失敗してしまうので、その時はCASTを外してください。

クエリIDが特定できたら、クエリがどのように変化したかをSSMSで簡単に調べることができます。

クエリストアに保存できる容量は有限で、デフォルトは100MBです。実行されるクエリの量に応じて 1 GB から 2 GB 程度まで増やす必要があります。
現在の使用量と上限の確認方法は以下です。

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

クエリストアの主なオプションは以下で、いずれもALTER DATABASE ... SET ～で変更できます。

構成	説明	既定値
QUERY_CAPTURE_MODE	デフォルトはAUTOなので、すべてのクエリを追跡するならALLに変更する	AUTO
MAX_STORAGE_SIZE_MB	データ領域の制限。単位はMB	100
INTERVAL_LENGTH_MINUTES	ランタイム統計の集計のWindowサイズ。単位は分	60分
STALE_QUERY_THRESHOLD_DAYS	保存されたランタイム統計と非アクティブなクエリの保持日数	30日
SIZE_BASED_CLEANUP_MODE	データサイズが制限値に近づいたときに、データの自動クリーンアップが発生するかどうか。90%になると、80%まで減らす動きをする	AUTO

リードレプリカの場合 sys.dm_exec_query_stats

リードレプリカではクエリストアを使うことができません。そのためsys.dm_exec_query_statsを使います。

SELECT
s.creation_time AS [集計期間内の最初の実行日時], 
s.last_execution_time AS [集計期間内の最後の実行日時], 
t.text AS [クエリのテキスト], 
p.query_plan AS [実行計画], 
s.total_elapsed_time / s.execution_count / 1000.0 AS [経過時間], 
s.total_worker_time / s.execution_count / 1000.0 AS [CPU時間], 
(s.total_logical_reads) / s.execution_count AS [論理IO], 
(s.total_physical_reads) / s.execution_count AS [物理IO]
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p

この他にもリードレプリカの監視には以下を使う必要があります。これらについては別の機会に取り上げます。

動的管理View	用途
sys.dm_db_resource_stats	過去 1 時間のリソース使用率のメトリック
sys.dm_os_wait_stats	データベースエンジンインスタンスの待機統計
sys.dm_database_replica_states	レプリカの正常性状態と同期の情報
sys.dm_exec_query_stats	実行回数、CPU 使用時間など、クエリごとの実行統計
sys.dm_exec_query_plan()	キャッシュされたクエリプラン
sys.dm_exec_sql_text()	キャッシュされたクエリプランのクエリテキスト
sys.dm_exec_query_profiles	クエリの実行中のリアルタイムでのクエリの進行状況
sys.dm_exec_query_plan_stats()	クエリのランタイム統計を含む最後の既知の実際の実行プラン

参考資料

シリーズ SQL Server/SQL Database再入門

なんとなくならSQL Serverを使えている人のためのSQL Server/SQL DatabaseのTips集です。