SQL サーバーの「自動拡張」および「自動圧縮」の設定に関する考慮事項

適用対象: Microsoft SQL Server 2005 Standard EditionMicrosoft SQL Server 2005 Express EditionMicrosoft SQL Server 2005 Developer Edition

概要


既定の 自動拡張 および 自動圧縮 の設定は、多くのSQL サーバー システムで調整を行いません。 ただし、設定をオンにする必要がなかったり、あるいは 自動拡張 および 自動圧縮 パラメーターを調整する必要がある場合があります。 この記事では、環境の設定を選択するときに参考になるいくつかの背景情報を示します。

詳細情報


自動拡張 および 自動圧縮 パラメーターをチューニングする場合、いくつかの点について考慮する必要があります。

設定の構成方法

  1. 自動拡張 および 自動圧縮の設定は、次のいずれかを使用して構成できます。
    • ALTER DATABASE ステートメント
    • SQL Server Management Studio
    • sp_dboption ストアド プロシージャ (SQL Server 2005 では非推奨)
    SQL Server 2005 でこれらの設定を構成する方法の詳細については、以下の MSDN (Microsoft Developer Network) Web サイトを参照してください。
    方法: データ ファイルまたはログ ファイルをデータベースに追加する (SQL Server Management Studio)
    http://msdn2.microsoft.com/ja-jp/library/ms189253.aspx
    [データベースのプロパティ] ([ファイル] ページ)
    http://msdn2.microsoft.com/ja-jp/library/ms180254.aspx
    また、データベースの作成時に自動拡張オプションを構成することもできます。

    現在の設定を表示するには、次の Transact-SQL コマンドを実行します。
    sp_helpdb [ [ @dbname= ] 'name' ]
  2. 自動拡張設定はファイル単位であることに注意してください。 したがって、データベースごとに 2 つ以上の場所に設定する必要があります (1 つはプライマリ データ ファイル用、もう 1 つはプライマリ ログ ファイル用)。 データ ファイルやログ ファイルが複数ある場合は、各ファイルにオプションを設定する必要があります。 環境によっては、データベース ファイルごとに異なる設定で終了する場合があります。

パフォーマンスへの影響

  • 使用可能なログ領域よりも多くのログ領域を必要とするトランザクションを実行し、そのデータベースのトランザクション ログの自動拡張オプションをオンにした場合、トランザクションが完了するまでの時間には、トランザクション ログが構成された量で拡張するまでの時間が含まれます。 拡張率が大きい場合や、時間がかかるその他の要因がある場合、タイムアウト エラーのためにトランザクションを開くクエリが失敗することがあります。 同じ種類の問題は、データベースのデータ部分の自動拡張によって発生する可能性があります。 自動拡張構成を変更するには、SQL Server Books Online の「ALTER DATABASE」のトピックを参照してください。
  • ログを拡張する必要がある大規模なトランザクションを実行する場合、そのトランザクション ログに書き込む必要がある他のトランザクションも、拡張操作が完了するまで待機する必要があります。
  • 自動拡張オプションと自動圧縮オプションを組み合わせると、不要なオーバーヘッドが発生する可能性があります。 拡張操作および圧縮操作をトリガーするしきい値が、頻繁な上下サイズ変更を引き起こさないことを確認します。 たとえば、トランザクション ログをコミットするまでに 100 MB 拡張させるトランザクションを実行することができます。 その後、自動圧縮は、トランザクション ログを 100 MB 単位で開始および圧縮します。 次に、同じトランザクションを実行すると、トランザクション ログが 100 MB 拡張します。 この例では、不要なオーバーヘッドを作成し、ログ ファイルの断片化を作成する可能性があります。いずれかがパフォーマンスに悪影響を及ぼす可能性があります。
  • データ ファイルまたはログ ファイルのサイズを変更することによる物理的な断片化は、パフォーマンスに大きな影響を及ぼす可能性があります。 これは、自動設定を使用する場合でも、手動でファイルを頻繁に拡張および圧縮する場合でも当てはまります。
  • 小さな増分でデータベースを拡張したり、拡張して圧縮した場合は、ディスクの断片化を招く可能性があります。 ディスクの断片化は、状況によってはパフォーマンスの問題を引き起こす可能性があります。 小さな拡張増分のシナリオでは、システムのパフォーマンスが低下する可能性もあります。
  • SQL Server 2005 以降のバージョンでは、インスタント ファイルの初期化を有効にすることができます。 インスタント ファイルの初期化は、データ ファイルのファイル割り当てだけを高速化します。 インスタント ファイルの初期化は、ログ ファイルには適用されません。
  • ログ ファイルでファイル拡張の数が多い場合は、仮想ログ ファイル (VLF) の数が多すぎる可能性があります。 これにより、データベースの起動/オンライン操作、レプリケーション、ミラーリング、および変更データ取得 (CDC) に関するパフォーマンスの問題が発生する可能性があります。 さらに、これにより、データの変更でパフォーマンスの問題が発生する可能性があります。

適切な運用方法

  • 管理された運用システムでは、自動拡張が予期しない拡張のための不測の事態であると考える必要があります。 自動拡張を使用して、日々のデータとログの拡張を管理しないでください。
  • アラートやモニタリング プログラムを使用してファイル サイズを監視し、ファイルを積極的に拡張することができます。 これにより、断片化を回避し、これらのメンテナンス作業をピーク時以外に転換できるようになります。
  • 自動圧縮自動拡張は、訓練されたデータベース管理者 (DBA) が慎重に評価する必要があります。これらは管理されないままにしてはいけません。
  • 自動拡張の増分は、前のセクションで示したパフォーマンスの低下を避けるために十分な大きさでなければなりません。 構成設定で使用する正確な値と、パーセンテージの拡張と特定の MB サイズの拡張の選択は、環境内のさまざまな要因によって異なります。 テストに使用できる一般的な経験則は、ファイルのサイズの約 1/8 に自動拡張設定を設定することです。
  • ファイルごとに <MAXSIZE> 設定を有効にして、使用可能なすべてのディスク領域を使い切るまで 1 つのファイルが拡張しないようにします。
  • トランザクションのサイズができる限り小さいまま保持されるようにして、予定外のファイル拡張が行われないようにします。

サイズの設定を自動制御する場合にディスク領域を考慮する必要がある理由

  • 自動拡張設定では、ファイルが定義されているドライブ上の使用可能なディスク領域の制限を超えてデータベース サイズを拡張することはできません。 したがって、データベースのサイズを変更するために自動拡張機能を使用する場合は、使用可能なハードディスク領域を独自に確認する必要があります。 自動拡張設定は、ファイルごとに選択する MAXSIZE パラメーターによっても制限されます。 領域を使い果たす可能性を減らすために、パフォーマンス モニタ カウンターをモニターできます SQL Server: データベース オブジェクト: データ ファイル サイズ (KB) を監視し、データベースが特定のサイズに達したときのアラートを設定できます。
  • データ ファイルまたはログ ファイルの予定外の拡張により、他のアプリケーションで使用可能と想定されていた領域が使用され、それらのアプリケーションで問題が発生することがあります。
  • トランザクション ログの拡張増分は、トランザクション ユニットのニーズを先取りするのに十分な大きさである必要があります。 自動拡張をオンにしても、クエリのニーズを満たすのに十分に速く拡張できない場合、トランザクション ログがいっぱいであるというメッセージを受信できます。
  • SQL Server は、自動圧縮に対して構成されたしきい値を超えたデータベースを常にテストしません。 代わりに、使用可能なデータベースを参照し、自動圧縮するように構成されている最初のデータベースを検索します。 そのデータベースをチェックし、必要に応じてそのデータベースを圧縮します。 次に、自動圧縮用に構成されている次のデータベースをチェックするまで、数分間待機します。 つまり、SQL Server はすべてのデータベースを一度にチェックしたり、すべてを一度に圧縮したりすることはありません。 これは、ラウンド ロビン方式でデータベースを介して一定期間負荷をずらす作業を行います。 したがって、自動圧縮に構成した特定の SQL Server インスタンスのデータベースの数に応じて、データベースが実際に圧縮するまでにしきい値に達してから数時間かかることがあります。

次の表に、SQL Server のインスタンスとルールが評価される SQL Server 製品のバージョンでこの条件を自動的にチェックする製品またはツールの詳細を示します。

ルール ソフトウェアルール タイトルルールの説明ルールが評価される対象である製品バージョン
System Center AdvisorSQL Server データベース ファイルの拡張設定が現在使用可能なディスク領域よりも大きいSystem Center Advisor によって、インスタンス SQL Server 内のデータベースがデータベース ファイル用に自動拡張構成されているかどうかが確認されます。 結果として生じた自動拡張増分がボリューム上の使用可能な領域より大きい場合、System Center Advisor はこの SQL Server データベースに対して警告を生成します。 System Center Advisor からこの警告が表示された場合は、影響を受けるファイルの自動拡張設定を確認して修正を行います。 次のいずれかの方法を使用して、この状況を解決することができます。
  • このボリュームで次の拡張が起こるように拡張の増分を減らします。
  • このボリュームにさらに領域を追加します。
  • このファイルを、拡張に使用可能な領域のある別のボリュームに移動します。
SQL Server 2008
SQL Server 2008 R2



関連情報


データベースおよびログ ファイルの拡張方法と圧縮方法の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。

256650 SQL Server 7.0 のトランザクション ログを圧縮する方法

272318 DBCC SHRINKFILE による SQL Server 2000 のトランザクション ログの圧縮

317375 SQL Server を実行しているコンピューターでトランザクション ログのサイズが予期せず拡張する、またはログがいっぱいになる

247751 バグ: データベース メンテナンス プランでデータベースが圧縮されない

305635 データベースの自動拡張時にタイムアウトが発生する

949523 「初期サイズ」プロパティの値と自動拡張プロパティの値が小さい場合、トランザクション レプリケーションの待機時間は SQL Server 2005 で高くなります

データベース ファイルの初期化の詳細については、次の MSDN (Microsoft Developer Network) Web サイトを参照してください。

インスタント ファイルの初期化を有効にする方法の詳細については、次のマイクロソフト Web サイトを参照してください。 トランザクション ログの物理アーキテクチャの詳細については、以下の MSDN Web サイトを参照してください。

SQL Server Books Online;トピック: 「トランザクション ログの物理アーキテクチャ」; 「トランザクション ログを圧縮する」