はじめに
サービス運用の中で商用環境のデータベースへ手動でクエリ実行することがあります。
多くの場合は問題なく完了しますが、時として以下のような障害につながることがあります。
- 重いクエリ: FULL SCANや大きなテーブルの多重JOINによるCPUメモリの高騰
- ロック問題: 広範囲ロックや長時間ロックを取り続けることでアプリケーションのクエリをブロック
- 意図しないバグ: 複雑なスクリプトなどのバグやCOMMIT忘れによるサービス影響
この記事では、このような障害への対策を様々な観点で整理してみました。
また、SQL Serverにおける実例をいくつか紹介します。
SRE(DBRE)として「誰が手動実行しても安全な環境を作る」ための仕組みを作る参考になれば幸いです。
1. SREとしての基本原則
1.1 人を責めず仕組みで防ぐ
SRE本などにもあるように、原因となった人を非難することは萎縮を招き、長期的に組織の心理的安全性を損ないます。
また、ダブルチェックなど人的な対応策は根本解決にならないため、仕組みで解決したいところです。
1.2 多層防御
セキュリティでは重要な考え方ですが、障害対応でも同様です。
複数箇所で対策を入れることで様々な事象に対応できるようにすることが大切ですが、実際に障害が発生した場合にも自動復旧により影響を最小化できる仕組みを考える必要があります。
1.3 フェイルセーフ
問題を検知したら安全側に倒す設計思想です。手動実行のクエリに問題があれば、アプリケーションのセッションを優先して手動クエリは自動でKillされる仕組みが望ましいです。
2 対策の全体像
2.1 防ぎたい障害の種類
手動作業によって起こり得る問題は以下の通りです。
| 障害パターン | 影響 |
|---|---|
| ロック競合・デッドロック | アプリケーションのタイムアウト |
| リソース枯渇（CPU,メモリ,I/O） | データベース全体の性能劣化 |
| 誤ったデータ変更・削除 | データ消失、データ不整合、サービス影響 |
| 長時間のトランザクション | ブロッキングの連鎖 |
これらに対して、どのようなフェーズでどのような対策ができるでしょうか。
2.2 フェーズ別の対策
手動クエリ実行による障害を防ぐため、多段防御の考えに則り、以下の4フェーズで対策を洗い出しました。
【平常時からの対策】
アクセス制御・標準化・ツール整備
↓
【作業前の対策】
レビュー・承認・周知
↓
【実行時の対策】
ツールや設定での自動適用、自動制限
↓
【異常発生時】
検知・自動復旧
2.3 フェーズ別の対策一覧
2.3.1 平常時からの対策
「いつでも安全に作業できる環境を作る」
アクセス制御
| 対策 | 詳細 |
|---|---|
| 権限制御（最小権限） | 不要な操作をさせない |
| ロール分離（DML, DDL） | 権限の粒度を細かく |
| アクセス元の制限 | 直接接続を排除。踏み台サーバーの用意。手動実行のアクセス元を一元化することで様々な対策(後述)を入れやすくなる |
標準化
| 対策 | 詳細 |
|---|---|
| クエリテンプレート | 安全なパターンの再利用。自然と実績のある作業として作業品質が上がる。最終的には自動化を検討。 |
| 作業手順書の整備 | 標準的な作業フローの定義。レビューやステージングでの手順確認。切り戻し方法の確立など |
| 禁止パターン定義 | やってはいけないことの明文化。(TRUNCATE文は使わないなど) |
ツール整備
| 対策 | 詳細 |
|---|---|
| ツールや接続環境の統一化 | 実行時の安全策を適用しやすくする。ツールの機能や設定で統制がとれる |
| 監視基盤の構築 | 異常検知の仕組み |
| アラート設定 | 通知の仕組み |
2.3.2 作業前の対策
「本当に実行して大丈夫かを確認する」
レビュー・承認
人的な対応策ですがこれによって防げる事故は多いです。
しかし、実際には手動実行のクエリに対してレビューやフローを通すのは運用上現実的でないことが多いと思われます。
| 対策 | 詳細 |
|---|---|
| クエリレビュー（人/AI/静的チェック） | リスクがあるクエリかどうかをチェック |
| 承認フロー | 事前のテストなどが行われているかのチェック。実行可否の判断 |
| 作業予定の周知 | 関係者への事前共有・周知。作業バッティングの防止。 |
影響の把握
今後作業レビューをAIで行う際も、この辺りの情報を自動で取得できるようになると精度の高いレビューができそうです。
| 対策 | 詳細 |
|---|---|
| 対象行数の事前確認 | 対象テーブルや操作対象の行数を確認し、想定外の大量処理を防止する |
| 実行計画の確認 | リソース消費量の見積もりやロック範囲を事前に推測してリスクを測る |
| ロック対象の確認 | 作業時間帯のアクセス頻度。ブロッキングの発生リスク |
事前検証
アプリケーションの開発であれば開発環境でのテストは必ず行われますが、DBへの作業は自動化されていないことから、その工程がスキップされたり、データ量やアクセス負荷の関係で本番環境でしか発生し得ない問題が発生します。
完璧な対策は不可能ですが、できる限りの検証や準備はするべきです。
| 対策 | 詳細 |
|---|---|
| 非本番環境での事前検証 | 手順に問題はないか、切り戻しが可能かの確認 |
実行前に BEGIN TRAN + ROLLBACK での確認 |
本番環境でのデータ更新時の常套手段。意図通りに変更されるかの確認 |
| 作業前のバックアップ | ロールバック可能にするために、データをとっておくなどの準備 |
2.3.3 実行時の対策
「問題が起きても被害を最小化する」
サービス影響が起きていないかを確認しながら作業をすること以外にも、クエリ実行時のオプション設定でより安全に作業を行うことができます。
ツールによる対策
ツール整備の項目にも書きましたが、ツールや接続元を統一化することで、その環境やツールに一律の設定を入れることで防げる障害も多そうです。
各個人のツール設定でタイムアウト値などは設定可能なことは多いですが、設定を各利用者に委ねるのではなく、管理者権限で強制的に適用できると望ましいです。
SQL Serverでは、「SQL Server Management Studio(SSMS)」 というMS純正のクライアントツールが主流ですが、接続を踏み台サーバー上のSSMSに限定し、拡張機能を作成・導入することで様々な対策を一元的に施せそうです。以下はその一例です。
| 対策 | 詳細 |
|---|---|
| タイムアウト値の自動挿入 | ロック待ち上限により長時間のブロック発生を強制的に防止する |
| 本番環境の視覚警告（背景色等） | 誤接続の気づき ※可能であれば環境ごとに接続環境を分けるべき |
| DROP / TRUNCATEなどの警告 | 危険な操作の注意喚起 |
DB設定による対策
DBMSの機能によって、手動実行のクエリを判別して制御をかけることもできるでしょう。
SQL Serverでは 「リソースガバナー」 という機能があり、条件に合致したSQLに対してCPUやメモリの割当量の上限を設定できます。
手動クエリの実行元を特定ツールに限定していれば、接続時のApplication Nameが固定化でき、それらに対してのみ制限をかけることができます。
| 対策 | 詳細 |
|---|---|
| リソース制御 | 特定ユーザーや特定の接続元のCPUやメモリの割り当て上限を設ける |
クエリ実行時のオプション設定による対策
これは実行者に委ねるしかない部分です。
レビューが漏れることもあり完全には防げませんが、実行時のオプション指定だけでもかなりの障害を防ぐことができます。
| 対策 | 詳細 |
|---|---|
| トランザクション分離レベル変更やスナップショット分離での実行 | 手動のSELECTでロックを取らないようにする |
| CPU並列数の制限 | 並列度の制限をかける。SQL Serverの場合はMAXDOPの制限がかけられる |
| クエリのメモリ割当量制限 | メモリ割当量に制限をかける。SQL Serverの場合はOPTION(MAX_GRANT_PERCENT = n)によりメモリの使いすぎを防ぐ |
2.3.4 異常発生時の対策
「素早く検知して自動で復旧する」
最終防衛策として、障害発生を検知してその原因が手動実行クエリであればそのクエリを強制的にkillする仕組みも重要です。
この場合も、実行元で手動クエリかどうかが判別がつくと安全に導入できそうです。
また、仕組みを導入する前に作業者への周知と事前の承諾を得る必要があるでしょう。
検知と自動復旧
| 対策 | 詳細 |
|---|---|
| ロックの検知と自動kill | ロックによるアプリケーションの長時間ブロックを防ぐ |
| リソース監視（CPU / メモリ / IO）による自動kill | DB全体のパフォーマンス低下を防ぐ |
| ロングトランザクション監視 | 長時間未コミットのクエリによりブロッキングの発生を防ぐ |
3. 手動クエリに対する対策の課題
網羅的に対策を挙げてみましたが、 手動で実行されるクエリはレビュープロセスや事前検証を経由しない ことが多く、アプリケーションから実行されるクエリと比べて事前防止が困難です。
どのようなクエリが実行されても問題ないように 実行時の緩和策や異常検知と自動復旧の仕組み の導入が重要です。
4. 対策事例
実行時の対策事例をいくつか紹介します。
4.1 リソースガバナーによるリソース制限
SSMSなどのクライアントツールからの接続にのみCPUやメモリの制限をかける設定です。
-- 1. リソースプール作成（SSMSユーザー用に制限）
CREATE RESOURCE POOL SSMSPool
WITH (
MIN_CPU_PERCENT = 0,
MAX_CPU_PERCENT = 20, -- CPU最大20%に制限
MIN_MEMORY_PERCENT = 0,
MAX_MEMORY_PERCENT = 10 -- メモリ最大10%に制限
);
GO
-- 2. ワークロードグループ作成
CREATE WORKLOAD GROUP SSMSWorkload
WITH (
IMPORTANCE = LOW, -- 優先度: 低
REQUEST_MAX_MEMORY_GRANT_PERCENT = 5, -- 1クエリあたりメモリ5%まで
REQUEST_MAX_CPU_TIME_SEC = 120, -- 1クエリ120秒でタイムアウト
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 30, -- メモリ割当待ち30秒でタイムアウト
MAX_DOP = 2, -- 並列度最大2
GROUP_MAX_REQUESTS = 5 -- 同時実行5クエリまで
)
USING SSMSPool;
GO
-- 3. 分類関数作成（SSMSを識別）
CREATE FUNCTION dbo.ResourceGovernorClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup SYSNAME = 'default';
-- SSMSのアプリケーション名を検出
IF APP_NAME() LIKE 'Microsoft SQL Server Management Studio%'
BEGIN
SET @WorkloadGroup = 'SSMSWorkload';
END
RETURN @WorkloadGroup;
END;
GO
-- 4. 有効化
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
-- 5. 確認
SELECT
wg.name AS WorkloadGroup,
rp.name AS ResourcePool,
wg.importance,
wg.request_max_cpu_time_sec,
wg.request_max_memory_grant_percent,
wg.max_dop,
wg.group_max_requests
FROM sys.resource_governor_workload_groups wg
JOIN sys.resource_governor_resource_pools rp ON wg.pool_id = rp.pool_id
WHERE wg.name != 'internal';
効果：
- SSMSからの重いクエリがアプリケーションに影響を与えない
- 120秒でタイムアウト → 長時間クエリを自動停止
- 同時実行数制限 → 大量クエリ発行も防止
デメリットとしては、内部的には全てのSQL実行に対して分類関数のチェックが走るため、DBの負荷懸念があります。なるべく分類関数はシンプルに収めるのがおすすめです。
4.2 クエリ実行時のオプション設定による対策
手動実行のクエリのSELECTがアプリケーションのトランザクションをブロックしなくなります。
特に以下のようなオプションをつけることでSELECT文でのロックの発生は大幅に防げます。
| SNAPSHOT ISOLATION | NOLOCK (READ UNCOMMITTED) | |
|---|---|---|
| 仕組み | tempdbにバージョンを保持 | ロックを無視して直接読む |
| ダーティリード | 起きない | 起きる |
| 一貫性 | トランザクション開始時点で一貫 | 保証なし |
| ファントムリード | 起きない | 起きる |
| tempdb負荷 | あり | なし |
このあたりを実行時にチェックして強制できる仕組みができると良さそうです。
4.3 監視対象と検知条件
リソースガバナーやオプション設定による制御をすり抜けた問題のあるクエリは、最終的に実行中に検知して自動Killすることで対処します。
SSMSなどからの手動実行のセッションを対象に、以下の条件で検知・Killします。
| 問題パターン | 検知条件 | 自動Kill条件例 |
|---|---|---|
| 長時間クエリ | 実行時間 > 閾値 | 300秒以上 |
| ブロッキング | 他セッションをブロック | 30秒以上ブロック継続 |
| 高CPU消費 | CPU時間 > 閾値 | CPU 5分以上消費 |
| 大量I/O | logical_reads > 閾値 | 1000万reads以上 |
| オープンTX | トランザクション開始から経過時間 | 300秒以上（アイドル状態） |
この仕組みは今後検討したいと考えています。
実際に導入を考えると、検知の閾値調整が難しそうです。
あまり厳しすぎると運用作業に支障が出るため、本当に異常と判断できる場合のみkillする必要があります。
以下サンプル例です
-- SSMSからの問題セッションを自動Kill
DECLARE @session_id INT;
DECLARE @kill_cmd NVARCHAR(100);
DECLARE @program_name NVARCHAR(128);
DECLARE @elapsed_sec INT;
DECLARE kill_cursor CURSOR FOR
SELECT
r.session_id,
s.program_name,
r.total_elapsed_time / 1000
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE s.is_user_process = 1
AND r.session_id != @@SPID
-- SSMSからの接続に絞る
AND (
s.program_name LIKE 'Microsoft SQL Server Management Studio%'
OR s.program_name LIKE 'SQLQuery%'
OR s.program_name LIKE 'Azure Data Studio%'
OR s.program_name LIKE 'SQLCMD%'
)
-- 問題のある条件
AND (
r.total_elapsed_time > 30000 OR -- 30秒以上
r.blocking_session_id > 0 OR
r.cpu_time > 60000
);
OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @session_id, @program_name, @elapsed_sec;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @kill_cmd = 'KILL ' + CAST(@session_id AS NVARCHAR(10));
PRINT 'Killing session ' + CAST(@session_id AS VARCHAR) +
' (' + @program_name + ', ' + CAST(@elapsed_sec AS VARCHAR) + ' sec)';
BEGIN TRY
EXEC sp_executesql @kill_cmd;
END TRY
BEGIN CATCH
PRINT 'Failed: ' + ERROR_MESSAGE();
END CATCH
FETCH NEXT FROM kill_cursor INTO @session_id, @program_name, @elapsed_sec;
END
CLOSE kill_cursor;
DEALLOCATE kill_cursor;
実際にはkill前にクエリ文を取得し、アラート通知文に含めるか、ログとして出力する機能は実装したいです。
また、作業によっては、どうしても実行時間やリソース使用量が多く、killされたくないという場合もあるかもしれません。その場合は周知の上このkill対象から除外できるような仕組みも必要になるかもしれません。
例えば、SSMSではログイン時にApplication Nameを任意の文字列にすることでkill対象から回避することができます。
5. まとめ
改めて網羅的に洗い出してみることで今までにない視点で多くの気づきがありました！
- 開発者が安全にDBを触る仕組みを作るためには、実行前の人的なプロセスによる防止よりも、実行時の防止・実行後の自動復旧の仕組みのほうが確実かつ重要度も高い
- 手動実行のクエリにのみ対策を入れるためには、手動実行クエリであることを明確に判別できる必要がある。そのためにもアクセス元の一元化やツールの共通化が重要。それ以外の方法でのアクセスを遮断することができれば抜け漏れなく手動クエリへ対策が施せる
- 異常検知と自動killの仕組みは実現可能だが、周囲の理解と閾値の細かいチューニングが必要