クエリの性能を改善していくための指標を明確にする
改善の指標と目標を明確にしましょう。
いろいろな指標がありますが私が気にしたことがある指標は以下だけです。
- 経過時間 応答を返すまでの時間。ロック待ちなどで経過時間と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集です。