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

  • 質問

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

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

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

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

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

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

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

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

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

    (OSはWindowsServer2012です。)

    2019年7月1日 9:30

回答

  • INFO20171128さん

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

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

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

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

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

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

    • 回答としてマーク INFO20171128 2019年7月3日 2:24
    2019年7月1日 9:37
  • HWMについて知らなかったので調べてみましたが、Oracleの概念でしょうか?

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

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

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

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


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

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

    2019年7月3日 1:35
  • 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より小さくなることはありません。そのため、テキストファイルの圧縮とは違うイメージでとらえられたほうがよさそうな気がします。

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

    • 回答としてマーク INFO20171128 2019年7月3日 2:23
    2019年7月2日 7:32

すべての返信

  • INFO20171128さん

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

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

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

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

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

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

    • 回答としてマーク INFO20171128 2019年7月3日 2:24
    2019年7月1日 9:37
  • maaaaaaaa8株式会社ゾゾテクノロジーズ様

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

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

    2019年7月2日 0:38
  • 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より小さくなることはありません。そのため、テキストファイルの圧縮とは違うイメージでとらえられたほうがよさそうな気がします。

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

    • 回答としてマーク INFO20171128 2019年7月3日 2:23
    2019年7月2日 7:32
  • ご回答ありがとうございます。

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

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




    • 回答としてマーク INFO20171128 2019年7月3日 2:23
    • 回答としてマークされていない INFO20171128 2019年7月3日 2:23
    2019年7月3日 0:44
  • HWMについて知らなかったので調べてみましたが、Oracleの概念でしょうか?

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

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

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

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


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

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

    2019年7月3日 1:35
  • ご丁寧な回答ありがとうございます。
    理解できました。
    (HWMはOracleの概念のようでした。)
    運用を見直したいと思います。

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

    2019年7月3日 2:28