SE の雑記

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

SQL Azure のインデックス断片化と再構築について調べてみる

leave a comment

SQL Azure のテーブルはクラスター化インデックスが必須となっています。
そのためヒープ構造のテーブルは作成ができず、B-Tree 構造のテーブルとなります。

image

インデックスが必須ということはデータの挿入状況によってはインデックスの断片化が発生するはずです。
今回の投稿ではそのあたりを見ていきたいと思います。

■テスト用テーブルの準備


まずは、断片化が発生して、断片化の状況がわかりやすいテーブルを作ってみます。

準備したテーブルがこちらになります。

CREATE TABLE [dbo].[Table_8](
    [Col1] [uniqueidentifier] NOT NULL,
    [Col2] [char](3500) NULL,
CONSTRAINT [PK_Table_8] PRIMARY KEY CLUSTERED
([Col1] ASC)
)

GUID 列と固定長の文字列のみの単純なテーブルとなっています。

Col1 uniqueidentifier 16 バイト
Col2 char 3,500 バイト
合計 3,516 バイト

 

1 レコードのサイズが 3,516 バイトですので 1 ページには 2 レコード格納することが可能です。
# 1 ページには 8,060 バイトのデータを格納できるので、RCSI の行バージョン情報 (14 バイト) を考慮しても 2 レコード格納できる計算になります。今回は BLOB のことはちょっと置いておきます。
image

このテーブルはクラスター化インデックスが GUID になっているので、頻繁にページ分割が発生する構造になっています。

このテーブルに以下のクエリでレコードを挿入すればテスト用テーブルの準備は完了です。

SET NOCOUNT ON
DECLARE @i int = 0
WHILE(@i < 20000)
BEGIN
    INSERT INTO Table_8 VALUES(NEWID(), @i)
    SET @i += 1
END

 

■断片化の状態を確認してみる


オンプレミスの SQL Server の場合は[sys.dm_db_index_physical_stats] や [DBCC SHOWCONTIG] を使用することでインデックスの断片化情報を取得することができます。

SQL Azure ではこれらの DMV / DBCC コマンドは使用することができません。
そのため、断片化の状態を判断するためには、[sys.dm_db_partition_stats] を使用することになりそうです。

先ほどのテーブルにはレコードを 20,000 件 INSERT しています。
1 ページには 2 レコード格納できますので、断片化していなければレコードの格納領域として使用しているデータページ数は 10,000 ページになるはずです。

以下のクエリで現在のページ数を確認してみます。

SELECT
    in_row_data_page_count,
    row_count
FROM
    sys.dm_db_partition_stats
WHERE
    object_id = OBJECT_ID(N’Table_8′)

結果がこちらになります。
image

20,000 レコードを格納するためのデータページを 13,317 ページ使用していることが確認できます。

データを挿入しようとしたときにデータを格納すべきページ内に空きが存在していない場合ページはページ分割をして、空きを確保します。
データの並びとして、レコード 1 と 2 の間に入るレコード 3 を INSERT するとします。
このページには空きページとして 1,028 バイトしかないため、レコード 3 を INSERT するための領域が足りません。
image

この場合、ページを 50/50 分割させ空きページを作ってから
image
レコード 3 を INSERT します。
image

次に レコード 1 と 3 の間に入るレコード 4 が INSERT された場合は、以下のようになり 4 レコードを格納するために 3 ページを使用していることになります。
image

この状態が断片化が発生している状態となります。

20,000 レコードを格納するためのデータページは最小で 10,000 ページ (2 レコード / ページ 格納) の状態となりますので、13,3
17 ページ使用している状態は断片化が発生している状態となります。
# FILLFACTOR を設定していて、インデックスの再構築をしたのであれば、最小ページ数より多いページ数が割り当てられていて問題はないですが今回は FILLFACTOR は設定していない状態なので。

今回は固定長レコードでテーブルを構成しているので計算が楽ですが、可変長レコードの場合はこんなに単純に計算できないですが…。

 

■断片化の解消


FILLFACTOR を設定していない状態で、最小ページ数になっていないことから断片化が発生していそうということがわかりました。

それでは、この断片化を解消してみたいと思います。

オンプレミスの SQL Server の場合は断片化の解消として、再構成 (REORGANIZE) と再構築 (REBUILD) を使用することが可能です。

SQL Azure の場合は、再構築 (REBUILD) のみが可能となっています。
ALTER INDEX (SQL Azure Database)

再構築のみということは必ず、対象のインデックスに排他ロックをかけてしまうかというとそういうこともなく、オンプレミスの SQL Server の Enterprise Edition 以上で使用可能なオンラインのインデックス再構築は SQL Azure で標準で使用することが可能です。

SQL Azure ではインデックスの再構築としては以下の 2 種類の方法があるようですね。

[オフライン インデックス 再構築 (デフォルト)]
ALTER INDEX [PK_Table_8] ON [dbo].[Table_8] REBUILD

[オンラインインデックス 再構築]
ALTER INDEX [PK_Table_8] ON [dbo].[Table_8] REBUILD WITH (ONLINE=ON)

 

インデックス再構築時の注意点としては、DB を容量の上限まで使用している場合、インデックスの再構築は失敗するようです。

メッセージ 40544、レベル 20、状態 5、行 1
The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. Code: 524289
メッセージ 0、レベル 20、状態 0、行 0
現在のコマンドで重大なエラーが発生しました。結果は破棄しなければなりません。

データサイズが大きい場合に、ONLINE=ON で実行した場合に tempdb の利用状況でエラーとならないかも少し気にはなるのですが、今回は Web Edition の 1GB の制限があるのでそこまでは試せていません…。

[Table_8] の [PK_Table_8] にインデックスの再構築を実行して、[sys.dm_db_partition_stats] の値を確認してみたいと思います。
image

ページ数が減っていることが確認できます。
断片化が解消し、ページ密度が上がっていますね。
ページの連続性に関しては、この DMV では判断できないのですが、通常のインデックス再構築と同じ動作だと思いますので、密度と連続性に関しては解消されているかと。

ALTER INDEX REBUILD が用意されているということは SQL Azure でもインデックスの断片化解消の必要性は考える必要がありそうですね。

■おまけ


インデックスの再構築ですが、SQL Azure 特有のロックがかかっているようです。

SELECT
    resource_type,
    resource_subtype,
    resource_description,
    request_mode,
    request_type,
    request_status,
    request_session_id,
    count(*)
FROM
    sys.dm_tran_locks
GROUP BY
    resource_type,
    resource_subtype,
    resource_description,
    request_mode,
    request_type,
    request_status,
    request_session_id
ORDER BY
    request_session_id

[SQL Server 2008 R2]
image

[SQL Azure]
image

CLOUD_PARTITION_DML_STABILITY
CLOUD_PARTITION_EXTERNAL_STABILITY
REPLICA_COPY_OPERATION
の 3 種類の resource_subtype は SQL Azure 特有のようです。

Share

Written by Masayuki.Ozawa

6月 7th, 2011 at 8:43 pm

Posted in SQL Server,Windows Azure

Tagged with

Leave a Reply