SQLServer2014 データベースの初期サイズとデータベースの圧縮について

Question

お世話になっております。

質問させていただきます。

SQLServer2014のデータベースAの初期サイズを102,400MB（100GB)にして

自動拡張は100MB単位で無制限に設定しました。

データベースAは週1回圧縮をかけており昨日も圧縮が実施されました。

本日データベースAの初期サイズを見ると29,984MBに縮小されていました。

（データベースAのサイズは30,945MBです。）

自動拡張がなるべく発生しないように初期サイズを100GBに設定したのに縮小されてしまったのですが
データベースの圧縮を行うと初期サイズも縮小されるということでしょうか？
仮にそうだとすると、自動拡張を防ぐにはデーターベースの圧縮はしない方がいいとうことでしょうか？

よろしくお願いいたします。

（OSはWindowsServer2012です。）

Answer 1

INFO20171128さん

圧縮のオプション次第な気がします。

以下のコマンドだと、最小でも100GBまでしか圧縮しなくなると思いますのでお試しください。

DBCC SHRINKFILE (N'データファイル名' , 102400)

ちなみに、「ボリュームの保守タスクを実行」というポリシーを設定していると、データファイルの自動拡張時に拡張が一瞬で終わるので、拡張時間を気にする必要がなくなります。（「ボリュームの保守タスクを実行」でググってみてください）

-------------------------------

※参考になった場合は投票と回答としてマークをお願いします。

Answer 2

maaaaaaaa8株式会社ゾゾテクノロジーズ様

ご返信ありがとうございます。

DBCC SHRINKFILE (N'データファイル名' , 102400)が
最小でも100GBまでしか圧縮しなくなるということは
逆にいうと100GBまではDBの容量は膨らみ続けるということでしょうか？

Answer 3

https://docs.microsoft.com/ja-jp/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

dbcc shrinkfileのドキュメントです。ご参考になると思います。

>逆にいうと100GBまではDBの容量は膨らみ続けるということでしょうか？

データの容量が増え続ければ、その通りです。

圧縮はあくまで未使用領域の開放ですので、例えば100GB全部領域を使っていれば、圧縮しても100GBより小さくなることはありません。そのため、テキストファイルの圧縮とは違うイメージでとらえられたほうがよさそうな気がします。

そのため、毎週圧縮といった作業は不要かと思います。

Answer 4

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

テーブルをフルスキャンする際、
先頭ブロックからHighWaterMark（HWM）まで順番にスキャンすると思いますが
HWMはこれまでにデータが格納されたことのあるページの中で先頭からもっとも遠い場所にあるページで
データが削除されてもリセットされないと別のところで教わりました。
HWMをリセットするためにも週1回は圧縮をするようにしておりました。

仮に100GBに達するまで圧縮をかけないとすると
未使用領域も100GBまでどんどん大きくなるというイメージなのですが
HWMもリセットされないまま遠ざかるということでしょうか？

Answer 5

HWMについて知らなかったので調べてみましたが、Oracleの概念でしょうか？

SQL Serverですと、テーブルまたはヒープの最終ページまでスキャン、という話かと思います。

この場合、インデックスおよびテーブルの断片化の話になるのではないでしょうか。

1テーブルのスキャン量を減らすという目的ですと、インデックスの断片化解消は有効ですが、圧縮は逆に断片化率を増大させる可能性があると、前回紹介したドキュメントに書いてあります。

そのためHWMについての質問には回答できかねますが、目的が各テーブルのスキャン速度の悪化を避けたいという目的であれば、週1回の圧縮ではなく、定期的なインデックスの再構成、再構築をすべきかと思います。

-------------------------------

※参考になった場合は投票と回答としてマークをお願いします。

Answer 6

ご丁寧な回答ありがとうございます。
理解できました。
(HWMはOracleの概念のようでした。)
運用を見直したいと思います。

ありがとうございました。