twitter で [クラスター化 / 非クラスター化 双方のインデックスを持つテーブルで、非クラスター化インデックスの再構築をする場合、そのテーブル自体へのアクセスってブロックされますか?] というナイスな質問をパスして頂いたのですが、最初きちんと理解していなかっため、うまく答えることができませんでした…。
# というか最初、間違ったこと伝えていました…。
ということでいちから調べてみたいと思います。
■テーブルの準備
今回用意したのがこちらのテーブルです。
CREATE TABLE [dbo].[Table_1]( CREATE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1] |
クラスター化インデックスと非クラスター化インデックスを持つ単純な構成のテーブルです。
このテーブルにデータを INSERT して非クラスター化インデックスの再構築を行い、ロックの状況を確認してみます。
■ロックの確認
ロックの確認には以下のクエリを使用しています。
# request_session_id には、非クラスター化インデックスの再構築を実行するセッションのSPID を指定します。
SELECT |
非クラスター化インデックスの再構築に関しては以下のクエリを使用します。
# 短時間で
ALTER INDEX [IX_Table_1] ON [dbo].[Table_1] REBUILD |
それでは、非クラスター化インデックスの再構築中のロックの情報を取得してみます。
ロックは
- ALLOCATION_UNIT
- DATABASE
- HOBT
- KEY
- METADATA
- OBJECT
- PAGE
に対して取得されています。
CASE 文で、resource_associated_entity_id の解決をしていますので、実際のオブジェクト名も表示されています。
今回は、[IX_Table_1] という非クラスター化インデックスを再構築していますので、[Table_1.IX_Table_1] に対してのロックは問題にはならないと思います。
システムテーブル系に関しても気にしないでよいとは思いますが、インデックス操作のために非クラスター化インデックを設定しているベーステーブル (Table_1) に対して、[Sch-M] のロックがかかっていることが確認できます。
以下の表は Books Online に書かれている [ロックの互換性] についてのマトリックスになります。
[Sch-M] のロックですが、かなり広範囲のロックと競合します。
単純な SELECT を実行した場合、以下のようなロックが取得されます。
SELECT を実行しているテーブルに対して、[Sch-S] のロックを取得していますね。
[Sch-M] と [Sch-S] のロックは競合しますので、この状態ではブロッキング (ロックの競合) が発生していることになります。
実際にブロッキングを確認してみたいと思います。
ブロッキングの確認方法に関しては技術情報が出ていますのでこちらが参考になります。
SQL Server 2005 および SQL Server 2000 のブロッキングを監視する方法
今回は以下のような単純なクエリでブロッキングが発生しているかを確認してみます。
SELECT |
[LCK_M_SCH_S] (Sch-S) のロックを取得しようとした際にブロッキングが発生していることが確認できますね。
■ブロッキングを発生させないためには
インデックスの再構築中にブロッキングを発生させないようにするためには、Enterprise Edition 以上で使用できるインデックスのオンライン再構築を使用すると回避することができます。
Standard Edition を使用している場合はオンライン再構築は使えませんので、ブロッキングを発生させたくないということであれば、インデックスを削除して再作成する必要がありそうです。
DROP INDEX [IX_Table_1] ON [dbo].[Table_1] CREATE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1] |
DROP_EXISTING も削除して作成という動きになるはずですが、ロックに関しては再構築と変わらず、[Sch-M] が発生していましたので、DROP → CREATE としています。
DROP 時も テーブルに対して、[Sch-M] が発生するのですが、作成時のロックは以下のようになります。
再構築時と似たようなロックの傾向なのですが、テーブルに対してのロックは、[S] と [Sch-S] となっています。
これであれば先ほどの検索のクエリとは競合がしないためインデックスの作成中でも検索が可能となります。
他の操作 (UPDATE / INSERT / DELETE) はどうでしょう。
一つずつ確認をしてみます。
データを INSERT するときは以下のようなロックとなります。
インデックスの作成時には、[Table_1] に対して [S] のロックがかかっています。
データの INSERT 時には [Table_1] に対して、[IX] のロックをかけるためロックが競合してブロッキングが発生します。
データを UPDATE するときは以下のようなロックとなります。
先ほどと同じく [Table_1] に対して、[IX] のロックをかけるためここでもブロッキングが発生します。
これは UPDATE と同じですね。
ここでも [Table_1] に対して、[IX] のロックをかけるためここでもブロッキングが発生します。
DROP → CREATE をすることで、検索に関してはブロッキングを回避できそうですが他の操作は難しそうですね。
検索もどこまでできるかは微妙な気がしますが。
ただし、インデックスの作成時にはインデックスの列を [ソートする] というコストがかかりますので、再構築と比較して負荷が高くなるので注意が必要です。
# 再構築はソート済みのインデックスデータに対して実施しますが、作成はソートされていないデータを対象としますので。
インデックスの作成時にも [ONLINE = ON] は指定ができますので、オンラインでインデックスの作成をすることでこれらのブロッキングを緩和させることができそうです。
インデックスのメンテナンス (再構築 / 作成) を業務時間内に他のトランザクションの影響を抑えて実行するためにはオンラインインデックスの操作が必須となってきそうですね。
SQL Azure だと、インデックスのオンライン操作はどのエディションでも使用できるので、この辺の柔軟性は高そうですね。
久しぶりにロックについて確認してみたのですが、単純な動作でも奥が深いです…。