背景
DBのスキーマを変更する際や、データを大量に更新する際はブロッキングによるタイムアウトエラー多発などの障害が発生しやすいです。
組織によっては専門のチームがスキーマの変更をすべて請け負う体制になっている場合もあると思いますが、各開発者やSREが本番環境にリリースする場合もあるかと思います。
そのようなケースで事故なく本番環境へスキーマ変更等をリリースできるように、ガイドラインを整備したいと思い考えてみました。専門知識を持った人がリリースを請け負うのがDBA的な志向だとすると、誰でも安全にリリースできるような仕組みを整えるのはDBRE的な志向だといえます。
今回はインデックス削除についてまとめました。
必須のknowledge
リスクと問題発生時のサービス影響
| リスク | 問題発生時のサービス影響 |
|---|---|
| ヒント句でインデックスを直接指定したクエリが実行されている場合、インデックスが無くなるため該当クエリがエラーになる | 運用中サービスの一部ページで必ずエラーが発生する |
| インデックスが効いていたクエリのパフォーマンスが劣化してクエリタイムアウトにつながることがある | 運用中サービス内の一部ページの読み込みがいつまでも終わらず、ユーザーにエラーが返る |
| 削除対象のテーブルに長時間ロックをかけることでブロッキングを起こしクエリタイムアウト多発につながることがある | 運用中サービス内の一部ページの読み込みがいつまでも終わらず、ユーザーにエラーが返る |
実施手順
１．インデックスを明示的に指定したクエリが存在しないか調査
以下のクエリを実行することで、特定の文言を含むストアドプロシージャを検索できます。このクエリを使って、削除対象のインデックスがヒント句として指定されていないか確認します。
合わせて、アプリケーションコード内のSQLにもヒント句として指定されていないか、インデックス名でgrepします。
1件でもヒットした場合は削除を断念するか、ヒント句を削除してからインデックスを削除します。
select
routine_name,
routine_type,
object_definition(object_id(routine_name)) as fulldefinition
from
information_schema.routines
where
object_definition(object_id(routine_name)) like '%index_name%' --ここに引っかけたい文言を指定
order by
routine_name
２．該当のインデックスが使用されていないか調査
以下のクエリを用いてインデックスの利用状況を確認します。直近で該当インデックスへのアクセスが無いことが確認できればOKです。
アクセスがある場合でも、DROPした後もクエリパフォーマンスの劣化が起きないと判断できる場合は削除を実行して問題ありません。（例：他のインデックスでカバーできる場合）
declare @tablename varchar(100) = 'table_name'
select top 100 object_name(sys.indexes.object_id) as table_name
,sys.indexes.name as index_name
,sys.dm_db_partition_stats.row_count as row_count
,cast(sys.dm_db_partition_stats.reserved_page_count * 8.0 / 1024 as numeric(10,1)) as size_mb
,type_desc
,sys.dm_db_index_usage_stats.*
from sys.dm_db_partition_stats
left join sys.indexes on sys.dm_db_partition_stats.object_id = sys.indexes.object_id
and sys.dm_db_partition_stats.index_id = sys.indexes.index_id
left join sys.dm_db_index_usage_stats on sys.dm_db_partition_stats.object_id = sys.dm_db_index_usage_stats.object_id
and sys.dm_db_partition_stats.index_id = sys.dm_db_index_usage_stats.index_id
where object_name(sys.indexes.object_id) = @tablename
order by sys.dm_db_partition_stats.reserved_page_count desc
３．インデックス削除クエリの実行
インデックスの削除は、該当のテーブルにSch-Mロックを要求します。
ロックが獲得できれば瞬時に削除クエリは完了しますが、他のクエリにブロックされると処理が長引くことがあります。
他のクエリにブロックされている間は、該当のテーブルへのあらゆるクエリがブロックされるため、非常に危険です。
この「基本的には瞬時に完了するが、長引く場合は他のクエリをブロックする危険性がある」という性質を考慮して、事前にタイムアウトを設定して削除クエリを実行します。
set lock_timeout 1000
drop index ix_index_name on table_name
タイムアウトした場合は何度実行しても問題ありません。削除が成功するまで繰り返します。
所要時間
該当テーブルのレコード数に依存せず、基本的には瞬時に完了します。
他のクエリにブロックされた場合は、ブロッカーとなっているクエリの実行が完了するまで待たされることになります。
問題が発生したとき
クエリ実行を取り消しましょう。
明示的にトランザクションを開いている場合は必ずrollbackしてトランザクションを終了させます。
実施後の確認項目
- アプリケーションのエラーログでエラーが急に多発するようになっていないか確認
- サーバーで現在実行中のクエリリストを確認し、スロークエリが多発していないかを確認
注意事項
- トランザクションを明示的に開始（begin tran）した場合は、必ずcommitまたはrollbackさせます。ロックを獲得する時間を最小限に抑えましょう。
- 基本的に、インデックス作成時は明示的なトランザクション開始は不要です。
- どうしても削除が成功しない場合はこちらの手順を参考に、成功するまでリトライするクエリを実行します。