SE の雑記

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

SQL Server の断片化発生の 2 種類のパターン

leave a comment

SQL Server のデータストレージはデータの追加 / 更新のトレンドによっては断片化が発生し、読み取りの効率が低下していきます。

今回の投稿では断片化が発生する 2 種類のパターンを見ていきたいと思います。

■2 種類のパターン


断片化が発生するパターンですが以下の 2 種類が基本パターンになると思います。

  1. ページの空きがない状態でページ内にデータが追加される
  2. ページの空きがない状態でページ内のデータの列サイズが変更される

断片化はページの空きがない状態でデータの追加 / 更新が行われた際に発生するのが一般的です。
# 50/50分割と言われる処理になります。

断片化は

  1. ページ密度の低下
  2. ページ連続性の低下

が発生している状態です。
50/50 分割が発生するとページ密度が低下して、読み取るページ数が多くなりデータ読み取りの効率が悪くなります。
中間ページが多くなると、目的のデータにたどり着くまでのページ数も多くなりますので Index Seek のプランでも読み取り効率が低下しています。

それでは、ページの空きがない状態での断片化について見ていきたいと思います。

 

■ページ内にデータを追加することによる断片化


データ追加時の断片化ですが、こちらも 2 パターンあるかと思います。

  1. INSERT によるページ内のデータ追加
  2. UPDATE によるインデックスキーの変更によるデータの移動

通常の運用で断片化の原因となるのは前者の方かと思います。
# 後者はインデックスキーの変更によるデータ移動になります

新規にデータを追加しようとした場合、対象のデータを格納するページ内に空きがない場合、
image

ページ内のデータを半分に分割して空き領域を作成してからデータを追加します。
image

このようなページ分割が発生しないようにするためにはデータを追加される位置を考えてインデックスキーを設定する必要があります。

追加されるデータがページの最後に格納されるようにキーを選定することでページの途中にデータが追加されることはないため、データ追加時に50/50分割による断片化を防ぐことができます。

それでは、データ追加による 50/50分割を見ていきたいと思います。
現在、[254031] には 54 件のレコードが格納されています。
image

FILLFACTOR は設定しないでインデックスを再構築した直後の状態ですので、各ページには可能な限りデータが格納された状態です。

この状態でページ内 (254031 ページ) に新規のデータを追加すると以下のようなページ配置となります。
image

データを追加する前はスロットは 51 まで使用されていました。
# スロットが 51 ということはページ内に 0 ~ 51 までの 52 行が格納されていたことになります。
データを追加した後はスロットが 26 になっています。
データの追加前は [254031] ページの後は [256079] ページとなっていたのですが、データを追加した後は [217097] となっています。
これが 50/50 分割が発生した状態です。
今回 INSERT をしたデータは [254031] ページに格納されるデータだったのですが、ページ内には新規にレコードを追加する空きがなかったため、ページ内のデータを [254031] と [217097] ページに分割し、空きを作りデータを格納しています。

これがデータ追加に伴うページ分割の発生の基本動作になります。

 

■ページ内の列サイズを変更することによる断片化


こちらの断片化は UPDATE による断片化になります。

以下のようにデータが格納されていたとします。
image

このページは空きがない状態です。
可変長 (varchar / nvarchar) の列を使用している場合は、格納されているデータの長さが変わる可能性があります。
そのため、以下のようにデータの長さが変わる更新が行われた場合には 50/50 分割で空きを作ってからデータの格納が行われます。
image

[217104] ページのスロット [53] のレコードの Col2 のレコード長を変更してみます。
image

変更されたレコード長が空きページにマッチしない場合は 50/50 分割が発生します。
レコード長が変更されることにより、先ほどは [21704] ページに存在していたレコードが [217145] ページに移動されていることが確認できます。

image

これが更新による断片化の発生になります。

 

これらの断片化の回避をするためには FILLFACTOR を設定する必要があります。
FILLFACTOR の設定による空き領域はデータの追加 / データの更新による影響を考える必要が出てきます。

ページの最後にデータを追加していて断片化の発生が頻繁に起きている場合はレコードの更新による列長変化を考慮したほうが良いかと。
SQL Server のパフォーマンスモニタでは、[AccessMethods] [PageSplits/sec] でページ分割の発生状況が確認できますので、データの更新のトレンドと合わせて状況を確認すると原因が把握できそうです。

Written by masayuki.ozawa

4月 16th, 2012 at 10:42 pm

Posted in SQL Server

Tagged with

Leave a Reply

*