次の方法で共有

SQL Server での autogrow と autoshrink の設定に関する考慮事項

  • [アーティクル]

元の製品バージョン: SQL Server
元の KB 番号: 315512

まとめ

既定の自動拡張と自動圧縮の設定は、多くの SQL Server システムで適切です。 ただし、環境によっては、自動拡張と自動圧縮のパラメーターの調整が必要です。 この記事では、環境に合わせてこれらの設定を選ぶときの目安となる背景情報について説明します。

自動拡張と自動縮小のパラメーターを調整する場合は、次の点を考慮する必要があります。

設定を構成操作方法

  1. 自動拡張と自動圧縮の設定は、次のいずれかを使用して構成または変更できます。

    Note

    これらの設定をデータベース ファイル レベルで設定する方法の詳細については、「 データ ファイルまたはログ ファイルをデータベースに追加するを参照してください。

    データベースの作成時に自動拡張オプションを構成することもできます。

    現在の設定を表示するには、次の Transact-SQL コマンドを実行します。

    sp_helpdb [ [ @dbname= ] 'name' ]

  2. 自動拡張の設定はファイルごとに行われます。 そのため、データベースごとに少なくとも 2 つの場所 (プライマリ データ ファイル用とプライマリ ログ ファイル用に 1 つ) に設定する必要があります。 複数のデータ ファイルやログ ファイルがある場合は、各ファイルにオプションを設定する必要があります。 環境によっては、データベース ファイルごとに異なる設定で終わる場合があります。

AUTO_SHRINKに関する考慮事項

AUTO_SHRINK は SQL Server のデータベース オプションです。 データベースに対してこのオプションを有効にすると、このデータベースはバックグラウンド タスクによる圧縮の対象になります。 このバックグラウンド タスクは、データ ファイルまたはログ ファイルの圧縮と圧縮の条件を満たすすべてのデータベースを評価します。

SQL Server インスタンス内のデータベースに対してこのオプションの設定を慎重に評価する必要があります。 頻繁に拡大および縮小操作を行うと、さまざまなパフォーマンスの問題が発生する可能性があります。

  • 複数のデータベースが頻繁に圧縮および拡張操作を行う場合、ファイル システム レベルの断片化が簡単になります。 これはパフォーマンスに重大な影響を与える可能性があります。 これは、自動設定を使用するか、手動でファイルを頻繁に拡張および縮小するかに関係なく当てはまります。

  • データまたはログ ファイル AUTO_SHRINK 正常に圧縮された後、領域が必要であり、ファイルを拡張する必要がある場合、後続の DML または DDL 操作が大幅に遅くなる可能性があります。

  • AUTO_SHRINKバックグラウンド タスクは、圧縮が必要なデータベースが多数ある場合にリソースを占有する可能性があります。

  • AUTO_SHRINKバックグラウンド タスクでは、他の通常のアプリケーション アクティビティと競合する可能性があるロックやその他の同期を取得する必要があります。

データベースを必要なサイズに設定し、事前に拡張することを検討してください。 アプリケーションの使用パターンで再び必要になると思われる場合は、データベース ファイルに未使用の領域を残しておきます。 これにより、データベース ファイルの圧縮と拡張が頻繁に行われなくなる可能性があります。

AUTOGROW に関する考慮事項

  • 使用可能な量より多くのログ領域を必要とするトランザクションを実行し、そのデータベースのトランザクション ログの自動拡張オプションをオンにした場合、トランザクションの完了にかかる時間には、トランザクション ログが構成された量分増加するまでの時間が含まれます。 増加の増分が大きい場合、または時間がかかる原因となる他の要因がある場合、タイムアウト エラーが原因でトランザクションを開くクエリが失敗する可能性があります。 同じ種類の問題は、データベースのデータ部分の自動拡張によって発生する可能性があります。

  • ログを拡大する必要がある大きなトランザクションを実行する場合、トランザクション ログへの書き込みを必要とする他のトランザクションも、拡張操作が完了するまで待機する必要があります。

  • ログ ファイルのファイルの増加が多い場合は、仮想ログ ファイル (VLF) の数が多すぎる可能性があります。 これにより、データベースの起動/オンライン操作、レプリケーション、ミラーリング、変更データ キャプチャ (CDC) のパフォーマンスの問題が発生する可能性があります。 また、これにより、データの変更に関するパフォーマンスの問題が発生する場合があります。

Note

自動拡張オプションと自動圧縮オプションを組み合わせると、不要なオーバーヘッドが発生する可能性があります。 拡大/縮小操作をトリガーするしきい値によって、サイズの変更が頻繁に発生しないようにしてください。 たとえば、トランザクション ログがコミット時までに 100 MB 増加するトランザクションを実行できます。 その後しばらくすると、自動圧縮が開始され、トランザクション ログが 100 MB 縮小されます。 次に、同じトランザクションを実行すると、トランザクション ログが再び 100 MB 増加します。 この例では、不要なオーバーヘッドを作成し、ログ ファイルの断片化が発生する可能性があり、どちらかがパフォーマンスに悪影響を与える可能性があります。

データベースを少しずつ増やすか、データベースを拡張して圧縮すると、最終的にディスクの断片化が発生する可能性があります。 ディスクの断片化は、状況によってはパフォーマンスの問題を引き起こす可能性があります。 増加が少ないシナリオでは、システムのパフォーマンスを低下させることもできます。

SQL Server では、 必要なファイルの初期化を有効にすることができます。 ファイルの瞬時初期化により、データ ファイルに対してのみファイルの割り当てが高速化されます。 ファイルの瞬時初期化は、ログ ファイルには適用されません。 詳細については、データベース インスタント ファイルの初期化 を参照してください。

自動拡張と自動圧縮のベスト プラクティス

  • マネージド運用システムの場合、自動拡張は単なる予期しない成長のコンティンジェンシーであると考える必要があります。 自動拡張を使用して、データやログの増加に日常的に対処しないでください。

  • アラートまたは監視プログラムを使用して、ファイル サイズを監視し、ファイルを事前に拡張できます。 これにより、断片化を回避し、これらのメンテナンス アクティビティをピーク時以外の時間に移行できます。

  • 自動圧縮と自動拡張は、トレーニング済みのデータベース管理者 (DBA) によって慎重に評価する必要があります。これらはアンマネージドのままにしないでください。

  • 前のセクションで示したパフォーマンスの低下を回避するには、自動拡張の増分が十分な大きさである必要があります。 構成設定で使用する正確な値と、増加率と特定の MB サイズの増加の選択は、環境内の多くの要因によって異なります。 テストに使用できる一般的な経験則は、自動拡張設定をファイルのサイズの約 8 から 8 に設定することです。

  • 各ファイルの \<MAXSIZE> 設定をオンにして、1 つのファイルが使用可能なすべてのディスク領域を使い切る時点まで拡大しないようにします。

  • 予定外のファイルの増加を防ぐために、トランザクションのサイズをできるだけ小さくしてください。

サイズ設定が自動的に制御される場合、ディスク領域について心配する必要がある理由

  • 自動拡張設定では、ファイルが定義されているドライブで使用可能なディスク領域の制限を超えてデータベース サイズを拡大することはできません。 そのため、自動拡張機能を使用してデータベースのサイズを変更する場合は、使用可能なハード ディスク領域を個別に確認する必要があります。 自動拡張設定は、各ファイルに対して選択した MAXSIZE パラメーターによっても制限されます。 領域が不足する可能性を減らすために、パフォーマンス モニター カウンター SQL Server: Databases Object: Data File(s) Size (KB) を監視し、データベースが特定のサイズに達したときにアラートを設定できます。

  • データ ファイルまたはログ ファイルが計画外に増加すると、他のアプリケーションが使用できるようになると予想される領域が発生し、他のアプリケーションで問題が発生する可能性があります。

  • トランザクション ログの増加増分は、トランザクション ユニットのニーズを先取りするのに十分な大きさである必要があります。 自動拡張が有効になっている場合でも、クエリのニーズを満たすのに十分な速度で増加できない場合は、トランザクション ログがいっぱいであることを示すメッセージを受信できます。

  • SQL Server では、自動圧縮用に構成されたしきい値に達したデータベースが常にテストされることはありません。 代わりに、使用可能なデータベースを調べ、自動圧縮するように構成されている最初のデータベースを見つけます。 そのデータベースがチェックされ、必要に応じてそのデータベースが圧縮されます。 その後、自動圧縮用に構成されている次のデータベースを確認するまで数分待機します。 言い換えると、SQL Server はすべてのデータベースを一度にチェックして、一度にすべてのデータベースを圧縮するわけではありません。 ラウンド ロビン方式でデータベースを介して動作し、一定期間にわたって負荷をずらす。 そのため、特定の SQL Server インスタンスで自動圧縮するように構成したデータベースの数によっては、データベースがしきい値に達してから実際に縮小されるまでに数時間かかる場合があります。

関連情報