SQL Server:インデックスの変更を安全に行う方法
背景
SQL Serverでは、インデックスにカラム追加をするコマンド用意されていません。
ドキュメントにも、以下の記載があります。
したがって、
create index IX_Table1 on Table1 (
col1,
col2
)
上記のインデックスにcol3を追加したい場合は、以下のようにDROP+CREATEする必要があります。
drop index IX_Table1 on Table1
create index IX_Table1 on Table1 (
col1,
col2,
col3
)
ただし、この方法だと一時的に必要なインデックスが無くなってしまいます。
そのため、プロダクション環境で大量にクエリ実行されている状況だとCPU負荷の高騰につながる危険性があります。
他の方法としては、インデックスを別名で作って、古いインデックスをDROPするという方法もあります。
create index IX_Table1_2 on Table1 (
col1,
col2,
col3
)
drop index IX_Table1 on Table1
この方法だと、必要なインデックスが無くなることは無いため、一時的なパフォーマンス劣化は防ぐことができます。
ただし、インデックス名が変わってしまうため、ヒント句でインデックスを直接指定したクエリが実行されているとエラーになってしまいます。
そのため、影響範囲の事前調査が必要となる場合があり、面倒です。
以下のように、最初の案を明示的なトランザクションで囲ってあげれば、インデックスヒントを使っていたとしてもエラーになることはありませんが、create indexが終わるまでブロックされ続けてしまいます。したがって、テーブルのレコードサイズが大きい場合はブロッキング時間が数分~数十分になってしまうこともありますのであまり良い方法とはいえません。
begin tran
drop index IX_Table1 on Table1
create index IX_Table1 on Table1 (
col1,
col2,
col3
)
commit tran
解決方法
--まず、新しいインデックスを作成する
create index IX_Table1_2 on Table1 (
col1,
col2,
col3
) with(online=on) --enterprise edition または sql database ならオンライン操作可能
--次に、同一トランザクション内で、不要インデックスのDROPと、新しいインデックスを同じ名前にrenameする処理を実施
begin tran
drop index IX_Table1 on Table1
exec sp_rename 'Table1.IX_Table1_2', 'IX_Table1'
commit tran
--まず、新しいインデックスを作成する
create index IX_Table1_2 on Table1 (
col1,
col2,
col3
) with(online=on) --enterprise edition または sql database ならオンライン操作可能
--次に、同一トランザクション内で、不要インデックスのDROPと、新しいインデックスを同じ名前にrenameする処理を実施
begin tran
drop index IX_Table1 on Table1
exec sp_rename 'Table1.IX_Table1_2', 'IX_Table1'
commit tran
上記クエリだと、drop indexもsp_renameも、レコードサイズに関係なく一瞬で完了する処理のため、わずかな時間だけロックを保持するものの、インデックスの名前を同じに保ったまま、インデックスを構成するカラムを変更することができます。
※参考として、秒間300アクセス程度のテーブルに対して今回の方法を実施したところ、うまくいきました。
ただし、数秒以上かかるクエリでアクセスされているとブロッキングが発生する可能性が高いため注意が必要です。
Author And Source
この問題について(SQL Server:インデックスの変更を安全に行う方法), 我々は、より多くの情報をここで見つけました https://qiita.com/maaaaaaaa/items/21b2ee678774a9e14af7著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .