SQLServer2008R2 自動拡張について

質問

SQLServer2008R2で、ＤＢとログに自動拡張の設定をした場合の
パフォーマンス劣化について、質問いたします。

自動拡張すると、元の領域と拡張した領域がディスクの別々の場所に作成され、
ファイルが断片化した状態になると思うのですが、
これはデフラグを行うことで解消する、と考えてよいでしょうか。

また、データの追加・削除を繰り返すと、ＤＢの中に
無駄な領域が増えると思いますが、これはSHRINKFILEを実行することで
解消しますか？

下記のような認識で、合っているでしょうか。

(1)データの追加・削除を繰り返し、ＤＢ領域が不足すると
自動拡張が行われる。

(2)自動拡張されたＤＢは、ファイルが断片化している。
また、ＤＢをしばらく使うと、ＤＢ領域内でデータがまばらに入った状態になり、
パフォーマンスが劣化する。

(3)SHRINKFILEを実行することで、まばらになったデータを
ＤＢ内の１箇所に集めて、無駄な領域を開放する。

(4)さらに、ＤＢの物理ファイルがディスク上で断片化しているのを、
デフラグで解消する。

初心者のため、用語の使い方がおかしいかもしれず、申し訳ありませんが、
よろしくお願いいたします。

回答No.1

nora1962 · ベストアンサー

> (1)データの追加・削除を繰り返し、ＤＢ領域が不足すると自動 > 拡張が行われる。

これはその通りです。

> (2)自動拡張されたＤＢは、ファイルが断片化している。また、
> ＤＢをしばらく使うと、ＤＢ領域内でデータがまばらに入った
> 状態になり、パフォーマンスが劣化する。

一般的に削除や更新が行われるとそうです。

> (3)SHRINKFILEを実行することで、まばらになったデータを
> ＤＢ内の１箇所に集めて、無駄な領域を開放する。

ここは注意が必要です。
http://msdn.microsoft.com/ja-jp/library/ms190488.aspx
によれば、
「圧縮操作では、データベース内のインデックスの断片化状態は
保持されず、一般に、断片化の程度が大きくなります。この理由
からも、データベースを繰り返し圧縮することはお勧めできませ
ん。」
とあります。
インデックスの再作成などを行わないと、パフォーマンスが逆に
劣化する可能性もあります。
また、「DBCC SHRINKFILE」を実行するとテーブル単位にロック
がかかる可能性がありますし、かからないとしてもリソースを消
費するので実行タイミングを検討する必要があります。

> (4)さらに、ＤＢの物理ファイルがディスク上で断片化してい
> るのを、デフラグで解消する。

デフラグするにはSQLSERVERのサービスを止めないといけません。
基本的にはデータベースを作成するタイミング(CREATE DATABASE)
でファイルグループを分けて十分な容量を確保しておき、テーブ
ル、インデックスを作成するタイミングで明示的にファイルグル
ープを指定するほうがいいと思います。

お礼

ご回答ありがとうございます。

「ファイルの断片化」というのが、ファイルの実体のことか、ＤＢ内のデータのことか、いろいろなサイトの説明を見てもはっきりわからなくて質問させていただいたのですが、理解どおりのようでよかったです。

インデックスやデフラグをかけるときの注意事項など、詳細な説明も追加していただき、大変勉強になりました。