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

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

• 経過時間　応答を返すまでの時間。ロック待ちなどで経過時間と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 ～で変更できます。

リードレプリカの場合 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

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

参考資料

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

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

1. Decimal型とMoney型の違い
2. Index SeekとIndex Scan
3. クエリ毎の性能指標の確認方法を知る
4. SQL Databaseでパーティション テーブルとパーティション インデックス
5. 統計とクエリの関係
6. 結合方法：NESTED LOOP、MERGE、HASH