SQL Server の自動拡張および自動圧縮の設定に関する考慮事項

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

要約

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

詳細情報

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

設定を構成する方法

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

    注意

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

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

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

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

AUTO_SHRINK に関する考慮事項

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

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

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

  2. AUTO_SHRINK によってデータファイルまたはログファイルが正常に縮小されると、その後の DML または DDL 操作は、スペースが必要でファイルを拡張する必要がある場合に、大幅に遅くなる可能性があります。

  3. 圧縮が必要なデータベースが多数存在する場合、AUTO_SHRINK のバックグラウンドタスクでリソースが消費されることがあります。

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

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

自動拡張の考慮事項

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

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

  • ログファイルに多数のファイルが含まれている場合は、非常に多くの仮想ログファイル (' 中 ') が存在する可能性があります。 これにより、データベースの起動/オンライン操作、レプリケーション、ミラーリング、および change data capture (CDC) のパフォーマンスの問題が発生する可能性があります。 また、これにより、データの変更によってパフォーマンス上の問題が発生することがあります。

注意

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

データベースを少量ずつ拡張した場合、またはデータベースを拡張してから縮小した場合は、ディスクの断片化が生じることがあります。 ディスクの断片化は、状況によってはパフォーマンス上の問題が発生することがあります。 小さな成長増分のシナリオでは、システムのパフォーマンスが低下することもあります。

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

自動拡張および自動圧縮のベストプラクティス

  • 管理された運用システムの場合は、自動拡張が予期しない成長のための緊急対応であることを考慮する必要があります。 自動拡張によって、データを管理し、毎日のログ拡張を管理しないでください。

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

  • 自動圧縮と自動拡張は、認定されたデータベース管理者 (DBA) によって慎重に評価する必要があります。非管理対象のままにしないでください。

  • 自動拡張の増分は、前のセクションで一覧表示されているパフォーマンス低下を回避するために十分な大きさでなければなりません。 構成設定で使用する正確な値と、割合の増加と特定の MB のサイズの増加との違いは、環境の多くの要因によって異なります。 テストに使用できる一般的な経験則は、ファイルのサイズを約8分の自動拡張設定に設定することです。

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

  • トランザクションのサイズをできるだけ小さくして、予期しないファイル拡張が発生しないようにします。

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

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

  • 予定外のデータまたはログファイルは、他のアプリケーションで利用できると予想される領域を必要とし、他のアプリケーションで問題が発生する可能性があります。

  • トランザクションログの拡張増分は、トランザクションの単位の必要性を維持するのに十分な大きさでなければなりません。 自動拡張が有効になっている場合でも、トランザクションログがいっぱいになったときに、クエリの必要性を十分に満たすことができないというメッセージが表示されます。

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

関連情報