元の製品バージョン: SQL Server
元の KB 番号: 315512
まとめ
自動拡張と自動圧縮の設定では、SQL Server がデータベース データ ファイルとログ ファイルのサイズを自動的に拡大 (自動拡張) または縮小 (自動圧縮) する方法を制御します。 既定の設定は多くのSQL Server システムで適切ですが、一部の環境ではそれらを調整する必要があります。 この記事では、これらの設定がパフォーマンスに与える影響について説明し、トランザクションの低速化、ファイルの断片化、および計画外のディスク領域の使用を回避するためのベスト プラクティスを提供します。
この記事は、SQL Serverのデータベースのファイルの拡張と圧縮の動作を構成するデータベース管理者 (DBA) を対象にしています。
自動拡張と自動圧縮の設定を構成するにはどうすればよいですか?
次のいずれかの方法を使用して、自動拡張と自動圧縮の設定を構成または変更します。
ALTER DATABASEステートメント:-
[ファイル] オプションと [ファイル グループ] オプションを使用して、自動拡張の設定 (
FILEGROWTH) を変更します。 -
SET オプションを使用して、
AUTO_SHRINK設定を構成します。
-
[ファイル] オプションと [ファイル グループ] オプションを使用して、自動拡張の設定 (
注
データベース ファイル レベルでこれらの設定を設定する方法の詳細については、「データベースに データ ファイルまたはログ ファイルを追加する」を参照してください。
データベースの作成時に自動拡張オプションを構成することもできます。
現在の設定を表示するには、次の Transact-SQL コマンドを実行します。
sp_helpdb [ [ @dbname= ] 'name' ]現在のデータベースの sys.database_files カタログ ビュー、またはインスタンス上のすべてのデータベースの sys.master_files カタログ ビューに対してクエリを実行することもできます。 これらのビューには、増加の増分、増加率がパーセントかどうか、および最大ファイル サイズが含まれます。
AUTO_SHRINKが有効になっているかどうかを確認するには、is_auto_shrink_onの 列に対してクエリを実行します。自動拡張の設定はファイルごとに行われます。 そのため、データベースごとに少なくとも 2 つの場所 (プライマリ データ ファイル用とプライマリ ログ ファイル用に 1 つ) に設定する必要があります。 複数のデータ ファイルとログ ファイルがある場合は、各ファイルに対してオプションを設定する必要があります。 環境によっては、各データベース ファイルの設定が異なる場合があります。
既定の自動拡張設定は何ですか?
SQL Server 2016 (13.x) 以降のバージョンでは、データ ファイルとログ ファイルの両方の既定のFILEGROWTH値は 64 MB です。 以前のバージョンでは、既定値としてより小さい既定値、または割合ベースの既定値が使われます。 各バージョンの既定については、「ALTER DATABASE (Transact-SQL) ファイルとファイル グループのオプション」を参照してください。
AUTO_SHRINKを有効にする前に考慮すべき点
AUTO_SHRINK は SQL Server のデータベース オプションです。 データベースに対してこのオプションを有効にすると、バックグラウンド タスクでデータベースを縮小できます。 このバックグラウンド タスクは、縮小の条件を満たすすべてのデータベースを評価し、データまたはログ ファイルを縮小します。
SQL Server インスタンス内のデータベースに対してこのオプションを設定するかどうかを慎重に検討してください。 頻繁に拡大および縮小操作を行うと、さまざまなパフォーマンスの問題が発生する可能性があります。
複数のデータベース ファイルで頻繁に縮小および拡張操作が行われると、ファイル システムの断片化が発生する可能性があります。 断片化はパフォーマンスに重大な影響を与える可能性があります。 この影響は、自動設定を使用するか、手動でファイルを頻繁に拡大および縮小するかに関係なく発生します。
AUTO_SHRINKデータまたはログ ファイルが正常に圧縮された後、領域が必要であり、ファイルを拡張する必要がある場合、後続の DML または DDL 操作が大幅に遅くなる可能性があります。AUTO_SHRINKバックグラウンド タスクでは、多くのデータベースで縮小が必要な場合に、大量のリソースを使用できます。AUTO_SHRINKバックグラウンド タスクでは、通常のアプリケーション アクティビティと競合する可能性があるロックやその他の同期オブジェクトを取得する必要があります。
代わりに、データベースを必要なサイズに設定し、事前に拡張します。 アプリケーションの使用パターン上、再び必要になると思われる場合は、データベース ファイルに未使用の領域を残しておきます。 ファイルの圧縮の詳細については、「データベースの圧縮」および「DBCC SHRINKFILE」を参照してください。
自動拡張に依存する前に、何を考慮する必要がありますか?
自動拡張は、トランザクションの継続時間、その他のセッション、およびトランザクション ログ内の仮想ログ ファイル (VLF) の数に影響を与える可能性があります。
利用可能なログ領域を超えるログ領域を必要とするトランザクションを実行し、そのデータベースのトランザクション ログに対して自動拡張が有効になっている場合、トランザクションの完了にかかる時間には、トランザクション ログが構成済みの量だけ拡張されるまでの時間が含まれます。 増加の増分が大きいか、別の要因によって増加に時間がかかる場合、タイムアウト エラーが原因でトランザクションを開始するクエリが失敗する可能性があります。 同じ問題は、データベースのデータ部分の自動拡張によって発生する可能性があります。
ログを拡張する必要がある大規模なトランザクションを実行する場合、トランザクション ログへの書き込みを必要とする他のトランザクションも、拡張操作が完了するまで待機する必要があります。
ログ ファイルの拡張が何度も発生している場合は、仮想ログ ファイル (VLF) の数が多すぎる可能性があります。 多くの VLF では、データベースの起動、オンライン操作、およびその他のログに依存する機能が低下する可能性があります。 VLF の数を確認するには、動的管理関数 sys.dm_db_log_info に対してクエリを実行します。
データベースを少しずつ増やすか、データベースを拡張して縮小すると、最終的にディスクの断片化が発生する可能性があります。 ディスクの断片化は、状況によってはパフォーマンスの問題を引き起こす可能性があります。 増加量が小さいと、システムのパフォーマンスが低下する可能性もあります。
注
自動拡張オプションと自動圧縮オプションを組み合わせると、不要なオーバーヘッドが発生する可能性があります。 拡大および縮小操作をトリガーするしきい値によって、サイズの変更が頻繁に発生しないようにします。 たとえば、コミットされるまでにトランザクション ログが 100 MB 増加するようなトランザクションを実行できます。 その後しばらくすると、自動圧縮が開始され、トランザクション ログが 100 MB 縮小されます。 次に、同じトランザクションを実行すると、トランザクション ログが再び 100 MB 増加します。 この例では、不要なオーバーヘッドを作成し、ログ ファイルの断片化が発生する可能性があり、どちらかがパフォーマンスに悪影響を与える可能性があります。
ファイルの瞬時初期化は自動拡張にどのように影響しますか?
ファイルの瞬時初期化 (IFI) により、データ ファイルの自動拡張操作が高速化されます。これは、SQL Server が新しい領域をゼロ クリアしないためです。 SQL Server 2022 (16.x) 以降のバージョンでは、最大 64 MB のトランザクション ログ自動拡張イベントも IFI の恩恵を受けることができます。 IFI がないと、増加の増分が大きくなると、自動拡大に時間がかかります。 要件と制限事項については、「データベースのファイルの瞬時初期化」を参照してください。
自動拡張と自動縮小のベスト プラクティスは何ですか?
マネージド運用システムの場合、autogrow は単なる想定外の増加に備えるための予備策と考えてください。 自動拡張を使用して、データやログの増加を日常的に管理しないでください。
アラートまたは監視プログラムを使用して、ファイル サイズを監視し、ファイル サイズを事前に拡張します。 このアプローチは、断片化を回避するのに役立ち、これらのメンテナンス アクティビティを非ピーク時間に移行できます。
訓練を受けたデータベース管理者 (DBA) に自動圧縮と自動拡張を慎重に評価してもらう。 アンマネージドのままにしないでください。
この記事で前述したパフォーマンスの低下を避けるために、自動拡張インクリメントを十分な大きさにします。 構成設定で使用する正確な値と、増加率と特定の MB サイズの増加の選択は、環境内の多くの要因によって異なります。 テストに使用できる一般的な経験則は、自動拡張設定をファイルのサイズの約 8 分の 1 に設定することです。 トランザクション ログの増加に関する推奨事項については、 トランザクション ログ ファイルのサイズの管理に関するページを参照してください。
各ファイルの
MAXSIZE設定をオンにして、1 つのファイルが使用可能なすべてのディスク領域を使い切る時点まで拡大しないようにします。予定外のファイルの増加を防ぐために、トランザクションのサイズをできるだけ小さくしてください。
自動拡張が有効になっているときにディスク領域を監視する必要がある理由
自動拡張設定では、ファイルが定義されているドライブで使用可能なディスク領域の制限を超えてデータベース サイズを拡大することはできません。 そのため、自動拡張機能を使用してデータベースのサイズを拡張する場合は、使用可能なハード ディスク領域を個別に確認する必要があります。 自動拡張設定は、各ファイルに対して選択した
MAXSIZEパラメーターによっても制限されます。 空き容量が不足する可能性を減らすために、パフォーマンス モニター カウンター SQL Server: Databases Object: Data File(s) Size (KB) を監視し、データベースが特定のサイズに達したときにアラートを設定できます。データ ファイルまたはログ ファイルが計画外に増加すると、他のアプリケーションで使用可能であると想定されている領域を占有し、他のアプリケーションで問題が発生する可能性があります。
トランザクション ログの拡張増分は、トランザクション ユニットの必要量を上回るのに十分な大きさである必要があります。 自動拡張が有効になっている場合でも、クエリのニーズを満たすのに十分な速度で拡張できない場合は、トランザクション ログがいっぱいであるというメッセージを受け取ることができます。 詳細については、完全なトランザクション ログのトラブルシューティング (SQL Server エラー 9002) を参照してください。
SQL Serverはどのくらいの頻度でデータベースの自動圧縮をチェックしますか?
SQL Server では、データベースが自動圧縮用に構成されたしきい値に達しているかどうかを常に確認するわけではありません。 代わりに、使用可能なデータベースを調べ、自動圧縮するように構成されている最初のデータベースを見つけます。 データベースがチェックされ、必要に応じて縮小されます。 その後、自動圧縮用に構成されている次のデータベースを確認するまで数分待機します。
言い換えると、SQL Server はすべてのデータベースを一度にチェックして、一度にすべてのデータベースを圧縮するわけではありません。 ラウンドロビン方式でデータベースを順番に処理し、一定期間にわたって負荷をずらします。 そのため、特定の SQL Server インスタンスで自動圧縮するように構成したデータベースの数によっては、データベースがしきい値に達してから実際に縮小されるまでに数時間かかる場合があります。