トランザクションログへの書き込みの負荷を減らすための対応案としてデータの断片化の発生を抑えるという方法が考えられます。
今回はこの関係性についてみていきたいと思います。
■データの断片化とトランザクションログの関係
データの断片化が発生した時の動作を考えてみます。
1 レコードは int (4) + char(800) = 804 バイト になります。
現在、9 レコード格納していますので、7,236 バイト 使用されていることになります。
1 ページのサイズは 8,060 バイトですが列、行区切りのオーバーヘッドが多少ありますので、単純計算ではあと 824 空きはありますが 804 バイトのレコードを入れるには少し不足しており 1 レコード挿入すると断片化が発生します。
この状態で Col1 が 2 のレコードを挿入してみます。
ページの空きとしては 1 レコード格納するには不足しており、Col1 はクラスター化インデックスとなっているため現在格納されているページの途中にデータを挿入する必要があります。
そのためページ分割が発生します。
実際の状態がこちらです。
先ほどは 39425 ページだけが使用されていましたが、ページ内の途中にデータを挿入することになったためページ分割が発生 し、39425 / 40193 の 2 ページが使用されるようになりました。
この時のトランザクションログの状態を見てみます。
ページ分割が発生する前のトランザクションログは以下のようになっています。
データを挿入→ページ分割が発生した時のトランザクションログは以下のようになります。
ページ分割が発生せずに行を挿入した場合のトランザクションログは以下のようになります。
ページ分割が発生する行の挿入では
- 新規の行を挿入
- 50/50 分割をするために新規のページを割り当て
- 新規のページに分割したデータを移動
というような処理が行われ、それぞれの操作がトランザクションログに記録されています。
新規の行を挿入するためのトランザクションログの書き込みはページ分割の発生有無にかかわらず必要になります。
そのほかの操作はページ分割が発生した際の固有の操作となります。
なお、ページにデータが入らなくなったときは、新規のページ割り当ての操作が必要となりますのでその場合は
- 新規のページを割り当て
- 新規の行を挿入
という流れになります。
50/50 分割をするためには分割先のページを確保する必要があります。
そのため、PF / IAM のようなページを管理している情報に対しての変更が必要になります。
データを挿入する場合、トランザクションログには [LOP_INSERT_ROWS] という操作が記録されますが、ページ分割が発生した場合には複数回の操作が記録されています。
25 行目の LOP_INSERT_ROWS は分割先のページに対して行を移動させた操作になり、 [Log Record Length] は[4952] バイト となっているため 5KB 近くのログが書き込まれたことになります。 このログがページ分割時の書き込み操作のレコードとしては大きなものになるかと。
# [Log Record Fixed Length] + [Log Record Length] で計算したほうが良いようですが。
また、ページ分割が発生するとページヘッダ内のページのリンク情報の変更が発生するためにラッチによる待ちが発生することになります。
断片化の発生はページ密度 / ページの連続性に影響を与え、検索の効率に響いてきますがトランザクションログの書き込みにも影響が発生しています。
SQL Server はトランザクションログを先行書き込みする必要がありますので、ログの書き込みはデータの書き込みにも影響を及ぼしてきます。
断片化の発生を 0 にするというのは現実問題としてかなり難しいと思いますが、断片化の発生はデータの書き込みだけでなくトランザクションログの負荷にも影響がありますので、トランザクションログの負荷が高い原因の一つの要因として断片化の発生状況も考慮したほうがよいのかなと。
[…] blog […]
Microsoft SQL Server インデックス再構成/再構築 | IT presenter
20 6月 15 at 13:01