SQL Serverでの自動拡張と自動拡張の設定に関する考慮事項

  • [アーティクル]

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

概要

多くのSQL Server システムでは、既定の autogrow と autoshrink の設定が適切です。 ただし、autogrow パラメーターと autoshrink パラメーターを調整する必要がある環境があります。 この記事では、環境に合わせてこれらの設定を選択するタイミングをガイドする背景情報について説明します。

autogrow パラメーターと autoshrink パラメーターを調整する場合に考慮すべき点を次に示します。

設定を構成操作方法

  1. 次のいずれかを使用して、autogrow と autoshrink の設定を構成または変更できます。

    注:

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

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

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

    sp_helpdb [ [ @dbname= ] 'name' ]

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

AUTO_SHRINKに関する考慮事項

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

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

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

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

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

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

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

AUTOGROW に関する考慮事項

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

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

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

注:

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

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

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

autogrow と autoshrink のベスト プラクティス

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

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

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

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

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

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

サイズ設定が自動的に制御される場合、ディスク領域を心配する必要があるのはなぜですか?

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

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

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

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

関連情報