SE の雑記

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

データ圧縮とトランザクションログの関係について

leave a comment

SQL Server 2008 以降の Enterprise Editionではデータ圧縮 (行圧縮 / ページ圧縮) の機能が追加されました。
この機能を使用すると CPU 使用率とのトレードオフでデータを圧縮することが可能となり、ディスク / バッファキャッシュの使用率を抑えることができるようになります。

データ圧縮は 「データ」 にかかわる個所にしか効果はないのでしょうか?

今回はこの点に関してまとめてみたいと思います。

■データ圧縮とトランザクションログの関係


結論を先に書いてしまいますとデータ圧縮をすることでトランザクションログの使用量を抑えることができる可能性があります。

前回使用した以下のテーブルがあります。
image

このテーブルに対して 20 万件のデータを挿入してデータ圧縮の設定有無でトランザクションログの使用状況を比較してみます。

[非圧縮]
image

[ページ圧縮設定済み]
image

今回は TEST というデータベースを使用しているのですが、ページ圧縮の設定有無でログの使用状況が変わっている (設定なし 16.6% : 設定済み 5.8%) のが確認できます。

圧縮をすることでトランザクションログの書き込みが減っていますね。

それでは、この理由を考えてみます。

ページ圧縮をする前の行挿入のログを見てみます。
image

こちらがページ圧縮時の行挿入のログとなります。image

[Log Record Length] の数値が異なっていることが確認できます。

今回使用しているテーブルには char 型のデータが含まれています。固定長データですので列に格納する文字の長さにかかわらず一定のデータが格納されます。

ページ圧縮をすることで (この動作であれば行圧縮も同様ですが) 固定長データの空白は圧縮されますので列のデータサイズは減ります。
この動作がログのレコードサイズにも影響を与え、ログに書き込むデータを少なくすることができているようです。

ログのサイズの差でも見てみたいと思います。
image

image

圧縮をする前は [312,795,774 = 312 MB] ですが、圧縮後は [117,255,032 = 117MB] になっています。
最初に見た全体のサイズに対してのログの使用率で算出できる値と近似値が取得できてます。

実際のログレコードをベースで確認をしても書き込まれているレコードのサイズが減っていることが確認できますね。

それでは、なぜこのような差が出てくるかを少し考えてみたいと思います。

差が出てくる理由としては 2 つの事象があると思っています。

  1. 圧縮により 1 レコードで使用される実バイト数が減ったため
  2. レコードの圧縮により使用されるページ数が減ったことにより管理ページへの書き込みが減ったため

このような理由が考えられるのではないでしょうか。

先ほどは行を挿入した際のログレコードのサイズの差を確認してみました。
圧縮前後ではログレコードのサイズが変化していますので圧縮によりデータの 1 レコードで使用されるデータサイズが減ればログに書き込まれるレコードのサイズも減ります。

ページ内に新規のレコードが挿入できなくなった場合には新規ページの割り当てが発生しますがこの際には GAM (SGAM) / IAM / PFS というような管理ページの変更が必要なり、この操作もログに書き込みがされます。(各操作は 100 バイト程度のログになると思いますが。)
圧縮によりデータサイズが減ることでページ内の密度が上がり、新規のページ割り当てを減らすことでこの管理ページに対して操作を抑え、ログレコードの減少につなげることができます。

トランザクションログはシーケンシャルアクセスですので複数のファイルで構成しても負荷分散にならず、負荷を減らすためにはディスクのスピンドルを増やしていく必要があるかと思いますが (Fusion-io のような PCI-e 直結の高速なストレージを使用する選択肢もありますが)、書き込むデータを少なくすることでログへの負荷も減らすことができそうですね。

Written by masayuki.ozawa

3月 16th, 2012 at 7:51 am

Posted in SQL Server

Tagged with

Leave a Reply

*