追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消その5

こんにちは、nagino です。

思いつきで書いている本シリーズですが、意外とまだネタがあるようです。

実際、ほとんどの処理がタイムアウトでお呼ばれ ⇒ インデックスの再構築 ⇒ すっかり解決・・・実は運用開始してから 1 年以上たつのに 1 度もインデックスのメンテナンスをしてなかった、なんていう笑い話のようなこともあったりするので、現実は小説より奇なり、です。

さて、今回はファイルの自動拡張のお話です。

データベースには、日々データが登録・修正・削除されます。

可変長の型もありますので、データ量は日々増減しているわけです。

そのため、データが格納されている mdf ファイルや ndf ファイルといったデータファイルのデータ量は日々変化しています。

ここで、HDD 上の記憶領域について OS と SQL Server のレベルでは扱いが異なることを理解している必要があります。

具体的には、以下のような 4 パターンがありえます。

-1. OS から見て空き領域

-2. OS から見てデータファイルとして使用中、SQL Server から見て空き領域

-3. OS から見てデータファイルとして使用中、SQL Server から見て使用領域

つまり、データファイルの中身は全てデータが記録されているとは限らず、データが書き込まれていないけれどもとりあえずファイルの一部となっている領域があります。

これは、ファイルサイズの変更にはファイルシステムの管理上のオーバーヘッドがかかり、それを避けるためにあらかじめデータファイルのサイズを大きめに確保しています。

さて、データベース毎に自動拡張と自動圧縮というパラメータがあります。

自動拡張というのは、データファイルの空き容量が不足した際にあらかじめ決められたサイズにファイルサイズを拡張します。

自動圧縮というのは、一定時間間隔でデータファイルの空き容量を開放します。

（SQL Server 2000 は 30 分間隔のようです。2005 以降はちょっと記載が見当たりません。）

つまり、これらは OS から見たときにデータファイルを拡張する、縮小するという処理になります。

通常のデータベースであれば、データは概ね増えていきますので、デフォルトでは自動拡張は有効、自動圧縮は無効となっています。

DELETE や UPDATE によってデータが減っても、いずれは他の増えたデータが記録されるので、ファイルとして領域を確保したままにしているということです。

このデフォルト設定は一番無難な設定となっていますが、自動拡張のオーバーヘッドは存在します。

そこで、パフォーマンスをあげるためにあらかじめファイルサイズを大きく確保しておき、自動拡張は有効にしておくものの万が一のための安全策として位置づける、という考え方があります。

ではどのぐらいのサイズにするか、というのは、データの増加傾向をみて判断するしかありません。

また、メンテナンス時や負荷が低いときに、必要に応じてファイルサイズを手動で拡張しておく必要があります。

一方自動圧縮は、パフォーマンスを考えると基本的には無効のままが一番良いです。

ディスクが不足していていかんともしがたいときの、様々な弊害覚悟での奥の手、という位置づけが妥当かと思います。

というのも、圧縮処理でパフォーマンスは悪化する、開放してもいずれは再拡張が行われる際にやはりパフォーマンスが悪化する、再拡張されるので一時的なディスク確保にしかならない、繰り返せばデータファイルが断片化していく、といった具合の弊害があります。

ちなみに自動拡張と自動圧縮は以下の KB がありますが、パフォーマンスを最速に、ということを考えると自動拡張やデータファイルのサイズの初期値は調整したほうが最善になると思いますのでご注意を。

なお、あんまりにもデータファイルのサイズを大きくしすぎると、デタッチしたファイルがその分大きくなりますので、デタッチ、アタッチを利用されている場合はご注意ください。

バックアップでは未使用領域は省かれるので、その場合は大丈夫です。

野良エンジニアの足跡