SQLShack

データベース縮小のクエリーパフォーマンスへの影響

データベース自動縮小オプションと自動成長設定を一緒にオンにすると、クエリーのパフォーマンス側に悪い影響を上げることが可能です。 データベース サイズを最適な値に設定することで、データ ファイルが成長するためのスペースを確保し、また、自動成長イベントの頻発を避けることができます。または、ほとんどすべてのデータベースは、自動成長機能が有効ないくつかのパラメーターを持っています。 複数のデータベースがこの機能をオンにしている場合、空き領域は、自動縮小機能によってラウンドロビン順次優先で定期的にデータファイルとログファイルに自動的に解放されます。

大きなサイズのデータベースについては、自動縮小は、システムレベルの断片化によってパフォーマンスの問題をもたらすことが実行されます。 以上のことから、どのようなデータベースであっても、以下の理由で自動縮小を有効にすべきではないと考えられます。

• SQL Server の自動縮小アルゴリズムを目的もなく実行すると、間違いなく大量のリソースを浪費することになる
• SQL Server の自動縮小または手動縮小を実行しても、明らかにインデックスの断片化を引き起こし、最終的にデータファイルも縮小される
• もしサーバーも IO サブシステムの境界を圧迫しているなら、縮小実行によってそれが押し広げられるかもしれない。 その結果、ディスク キューの長さが長くなり、場合によっては IO タイムアウトが発生し、サーバー IO および CPU リソースを大量に消費することになります。

  重要なデータベースでは、エキスパートがデータベース ファイル レベルで手動収縮操作を実行することができます。 手動縮小ファイルの活動は、削除操作が実行され、その後、スペースが再生されたときに実行することができます。 縮小作業によりインデックスの断片化が発生する可能性があるため、縮小作業の実行時に断片化したインデックスを再構築しなければなりません。 インデックスの断片化の割合は、DMVのT-SQLステートメントを使用して、ユーザーが確認することができます。

  ディスク使用量を定期的に監視するために、SSMS を使用して手動で縮小操作を行う前にディスク使用量レポートを分析すると、表示中のデータベースのデータとログスペース情報を確認することができます。 もし、ユーザーがデータベースファイルの予約容量と空き容量を計算するためのダッシュボードを取得したい場合、ディスクレポートは非常に有用になります。 ただし、ディスクレポートは、SQL Server DMVを使用して情報を生息させます。 ディスクレポートは、SSMSを使用して以下のディレクトリで利用できます。

  Database >> Reports >> Standard Reports >> Disk Usage

  

  ここに、データベース用のディスクレポートが用意されています。 データファイルとログファイルのリアルタイムの統計情報を見ることができます。 このレポートには、予約された総スペース、データ ファイルのスペース、トランザクション ログ スペース、およびインメモリ OLTP スペースの主要な情報が組み込まれています。

  

  Shrink database automatically uses SQL Server Jobs

  こうしたスクリプトは、スケジューラーの活動でデータベース縮小操作を実行するジョブをユーザーによってスケジュールすることで実行されることがあります。 スクリプトの最初のステップとして、データベースファイルの空き領域を見つけ、空き領域に対する定義された基準が一致する場合、そのファイルを縮小します。

  基本的に、データベースファイルの監視は、データベース管理者によって実行され、データベース管理者はデータベースファイルのサイズを監視するためにデータベースをステップ実行します。 事前に定義された境界を任意のファイルによって交差された場合、彼らはそのような手順を実行する必要があります。 そのため、そのアクティビティを SQL Server ジョブを使用して自動化し、毎日、毎週、または毎月、事前に定義された時刻に実行することができます。

  ベスト プラクティスは、データベース ログ ファイルのみにこのジョブをスケジュールし、データ ファイルの空き領域を手動で監視することです。 なぜなら、それはクエリのパフォーマンスにも影響を与える可能性があるからです。 SQL Server DMVは、データファイルとログファイルを同じ結果セットで返すので、SQL Server DMVのT-SQLステートメントでファイルタイプを分岐させる必要があります。 ファイルを縮小する前に、ログファイルの総容量と空き容量にクエリロジックを適用することができます。

  Check free space for the database files:

  If criteria matched with the database files, if free space is greater than (n) MB/GB, if n(%) free space compare to total space, and more many more. ログファイルに空き領域がなく、ファイルサイズが最大ファイルサイズパラメータ値の近くにある場合、データベース管理者はトランザクションログを検索する必要があります。

  Shrink database file:

  1	DBCC SHRINKFILE(file_name, 5120);

  ここで、5120 MBでターゲットファイルのサイズである。 つまり、ファイルは5120MBのサイズになります。 SQL Serverジョブステップで必要な条件を含むT-SQLクエリを設定し、オンオフの時間帯にスケジュールします。

  SQL Server自動縮小オプションは、すべてのデータベースで有効にすることはできません。 特に、CRUD 操作が比較的少ない小規模なデータベースで役立ちます。

  まとめ

  この記事では、データおよびログ ファイルを縮小して未使用領域を削除する、SQL Server の自動縮小データベース プロパティについて説明しました。 データベースの縮小は高価な操作であり、慎重に使用する必要があります。

  • Author
  • Recent Posts

  

  Jignesh はデータベースソリューションとアーキテクチャにおいて良い経験を持っています。 データベース設計&アーキテクチャ、SQL開発、管理、クエリ最適化、パフォーマンスチューニング、HAおよびディザスターリカバリーにおいて複数の顧客と協働している。
  Jignesh Raiyani さんの投稿をすべて表示

  
  Jignesh Raiyani の最新の投稿 (すべて見る)
  • Page Life Expectancy (PLE) in SQL Server – July 17, 2020年
  • SQL Serverでテーブルパーティショニングを自動化する方法 – 2020年7月7日
  • AWS EC2上でSQL Server Always On Availability Groupを構成する – 2020年7月6日