SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

非クラスター化インデックス再構築時の同時実行性について

leave a comment

twitter で [クラスター化 / 非クラスター化 双方のインデックスを持つテーブルで、非クラスター化インデックスの再構築をする場合、そのテーブル自体へのアクセスってブロックされますか?] というナイスな質問をパスして頂いたのですが、最初きちんと理解していなかっため、うまく答えることができませんでした…。
# というか最初、間違ったこと伝えていました…。

ということでいちから調べてみたいと思います。

■テーブルの準備


今回用意したのがこちらのテーブルです。

CREATE TABLE [dbo].[Table_1](
    [col1] [uniqueidentifier] NOT NULL,
    [col2] [char](900) NULL,
    [col3] [int] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
([col1] ASC)
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1]
([col2] ASC)
ON [SECONDARY]
GO

 

クラスター化インデックスと非クラスター化インデックスを持つ単純な構成のテーブルです。
このテーブルにデータを INSERT して非クラスター化インデックスの再構築を行い、ロックの状況を確認してみます。

■ロックの確認


ロックの確認には以下のクエリを使用しています。
# request_session_id には、非クラスター化インデックスの再構築を実行するセッションのSPID を指定します。

SELECT
    resource_type,
    resource_subtype,
    resource_description,
    CASE
        WHEN resource_type = N’ALLOCATION_UNIT’ THEN
            (SELECT
                OBJECT_NAME(sp.object_id) + N’:’ +  si.name
             FROM
                sys.allocation_units
                LEFT JOIN
                    sys.partitions sp
                ON
                    container_id = hobt_id
                LEFT JOIN
                    sys.indexes si
                ON
                    sp.object_id = si.object_id
                    AND
                    sp.index_id = si.index_id                   
              WHERE
                allocation_unit_id = resource_associated_entity_id)
               
        WHEN  resource_type = N’OBJECT’ THEN
            OBJECT_NAME(CONVERT(bigint, resource_associated_entity_id))
           
        WHEN resource_type IN(N’HOBT’, N’KEY’, N’PAGE’) THEN
            (SELECT
                OBJECT_NAME(sp.object_id) + N’:’ +  si.name
             FROM
                sys.partitions sp
                LEFT JOIN
                    sys.indexes si
                ON
                    sp.object_id = si.object_id
                    AND
                    sp.index_id = si.index_id
            WHERE
                hobt_id = resource_associated_entity_id)
           
        ELSE   
            CONVERT(nvarchar(40), resource_associated_entity_id)
    END resource_associated_entity_id,
    request_mode,
    request_type
FROM
    sys.dm_tran_locks
WHERE
    request_session_id = 56
GROUP BY
    resource_type,
    resource_subtype,
    resource_description,
    resource_associated_entity_id,
    request_mode,
    request_type
ORDER BY
    resource_type ASC

 

非クラスター化インデックスの再構築に関しては以下のクエリを使用します。
# 短時間で

ALTER INDEX [IX_Table_1] ON [dbo].[Table_1] REBUILD

それでは、非クラスター化インデックスの再構築中のロックの情報を取得してみます。
image

ロックは

  • 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 に書かれている [ロックの互換性] についてのマトリックスになります。
image

[Sch-M] のロックですが、かなり広範囲のロックと競合します。

単純な SELECT を実行した場合、以下のようなロックが取得されます。
image

SELECT を実行しているテーブルに対して、[Sch-S] のロックを取得していますね。
[Sch-M] と [Sch-S] のロックは競合しますので、この状態ではブロッキング (ロックの競合) が発生していることになります。
image

実際にブロッキングを確認してみたいと思います。
ブロッキングの確認方法に関しては技術情報が出ていますのでこちらが参考になります。
SQL Server 2005 および SQL Server 2000 のブロッキングを監視する方法

今回は以下のような単純なクエリでブロッキングが発生しているかを確認してみます。

SELECT
    session_id,
    status,
    command,
    blocking_session_id,
    wait_type,
    wait_resource
FROM
    sys.dm_exec_requests
WHERE
    blocking_session_id > 0

[LCK_M_SCH_S] (Sch-S) のロックを取得しようとした際にブロッキングが発生していることが確認できますね。

image

 

■ブロッキングを発生させないためには


インデックスの再構築中にブロッキングを発生させないようにするためには、Enterprise Edition 以上で使用できるインデックスのオンライン再構築を使用すると回避することができます。

Standard Edition を使用している場合はオンライン再構築は使えませんので、ブロッキングを発生させたくないということであれば、インデックスを削除して再作成する必要がありそうです。

DROP INDEX [IX_Table_1] ON [dbo].[Table_1]

CREATE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1]
([col2] ASC)
ON [SECONDARY]

DROP_EXISTING も削除して作成という動きになるはずですが、ロックに関しては再構築と変わらず、[Sch-M] が発生していましたので、DROP → CREATE としています。

DROP 時も テーブルに対して、[Sch-M] が発生するのですが、作成時のロックは以下のようになります。
image
再構築時と似たようなロックの傾向なのですが、テーブルに対してのロックは、[S] と [Sch-S] となっています。

これであれば先ほどの検索のクエリとは競合がしないためインデックスの作成中でも検索が可能となります。

他の操作 (UPDATE / INSERT / DELETE) はどうでしょう。
一つずつ確認をしてみます。

データを INSERT するときは以下のようなロックとなります。
image

インデックスの作成時には、[Table_1] に対して [S] のロックがかかっています。
データの INSERT 時には [Table_1] に対して、[IX] のロックをかけるためロックが競合してブロッキングが発生します。

データを UPDATE するときは以下のようなロックとなります。
image

先ほどと同じく  [Table_1] に対して、[IX] のロックをかけるためここでもブロッキングが発生します。

それでは最後に DELETE のロックを見てみます。
image

これは UPDATE と同じですね。
ここでも [Table_1] に対して、[IX] のロックをかけるためここでもブロッキングが発生します。

DROP → CREATE をすることで、検索に関してはブロッキングを回避できそうですが他の操作は難しそうですね。
検索もどこまでできるかは微妙な気がしますが。
ただし、インデックスの作成時にはインデックスの列を [ソートする] というコストがかかりますので、再構築と比較して負荷が高くなるので注意が必要です。
# 再構築はソート済みのインデックスデータに対して実施しますが、作成はソートされていないデータを対象としますので。

インデックスの作成時にも [ONLINE = ON] は指定ができますので、オンラインでインデックスの作成をすることでこれらのブロッキングを緩和させることができそうです。

インデックスのメンテナンス (再構築 / 作成) を業務時間内に他のトランザクションの影響を抑えて実行するためにはオンラインインデックスの操作が必須となってきそうですね。

SQL Azure だと、インデックスのオンライン操作はどのエディションでも使用できるので、この辺の柔軟性は高そうですね。

久しぶりにロックについて確認してみたのですが、単純な動作でも奥が深いです…。

Share

Written by Masayuki.Ozawa

6月 18th, 2011 at 6:03 pm

Posted in SQL Server

Leave a Reply