SQL Azure のテーブルはクラスター化インデックスが必須となっています。
そのためヒープ構造のテーブルは作成ができず、B-Tree 構造のテーブルとなります。
インデックスが必須ということはデータの挿入状況によってはインデックスの断片化が発生するはずです。
今回の投稿ではそのあたりを見ていきたいと思います。
■テスト用テーブルの準備
まずは、断片化が発生して、断片化の状況がわかりやすいテーブルを作ってみます。
準備したテーブルがこちらになります。
CREATE TABLE [dbo].[Table_8]( |
GUID 列と固定長の文字列のみの単純なテーブルとなっています。
Col1 | uniqueidentifier | 16 バイト |
Col2 | char | 3,500 バイト |
合計 | 3,516 バイト |
1 レコードのサイズが 3,516 バイトですので 1 ページには 2 レコード格納することが可能です。
# 1 ページには 8,060 バイトのデータを格納できるので、RCSI の行バージョン情報 (14 バイト) を考慮しても 2 レコード格納できる計算になります。今回は BLOB のことはちょっと置いておきます。
このテーブルはクラスター化インデックスが GUID になっているので、頻繁にページ分割が発生する構造になっています。
このテーブルに以下のクエリでレコードを挿入すればテスト用テーブルの準備は完了です。
SET NOCOUNT ON |
■断片化の状態を確認してみる
オンプレミスの 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 |
20,000 レコードを格納するためのデータページを 13,317 ページ使用していることが確認できます。
データを挿入しようとしたときにデータを格納すべきページ内に空きが存在していない場合ページはページ分割をして、空きを確保します。
データの並びとして、レコード 1 と 2 の間に入るレコード 3 を INSERT するとします。
このページには空きページとして 1,028 バイトしかないため、レコード 3 を INSERT するための領域が足りません。
この場合、ページを 50/50 分割させ空きページを作ってから
レコード 3 を INSERT します。
次に レコード 1 と 3 の間に入るレコード 4 が INSERT された場合は、以下のようになり 4 レコードを格納するために 3 ページを使用していることになります。
この状態が断片化が発生している状態となります。
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 [オンラインインデックス 再構築] |
インデックス再構築時の注意点としては、DB を容量の上限まで使用している場合、インデックスの再構築は失敗するようです。
メッセージ 40544、レベル 20、状態 5、行 1 |
データサイズが大きい場合に、ONLINE=ON で実行した場合に tempdb の利用状況でエラーとならないかも少し気にはなるのですが、今回は Web Edition の 1GB の制限があるのでそこまでは試せていません…。
[Table_8] の [PK_Table_8] にインデックスの再構築を実行して、[sys.dm_db_partition_stats] の値を確認してみたいと思います。
ページ数が減っていることが確認できます。
断片化が解消し、ページ密度が上がっていますね。
ページの連続性に関しては、この DMV では判断できないのですが、通常のインデックス再構築と同じ動作だと思いますので、密度と連続性に関しては解消されているかと。
ALTER INDEX REBUILD が用意されているということは SQL Azure でもインデックスの断片化解消の必要性は考える必要がありそうですね。
■おまけ
インデックスの再構築ですが、SQL Azure 特有のロックがかかっているようです。
SELECT |
CLOUD_PARTITION_DML_STABILITY
CLOUD_PARTITION_EXTERNAL_STABILITY
REPLICA_COPY_OPERATION
の 3 種類の resource_subtype は SQL Azure 特有のようです。