SQL Server でインデックスの断片化を検出および測定するにはどうすればよいですか?

公開: 2023-06-15

今日は、データベースの管理に使用されるシステムである SQL Server ( wiki ) の興味深い側面を探っていきます。 この日のトピックは「SQL Server におけるインデックスの断片化」です。 それを検出して測定する方法を学びます。 心配しないでください。思ったほど複雑ではありません。

お気に入りの曲のプレイリストについて考えてみましょう。 曲順に並べてありますので、お好みに合わせてお楽しみください。 しかし、時間の経過とともに、一部の曲が削除され、新しい曲が追加され、他の曲が移動された場合はどうなるでしょうか? プレイリストの順番が崩れてしまいますよね？ これは、インデックスの断片化について話したときにデータベースで起こることと似ています。

データベースでは、データは特定の方法で編成され、迅速かつ簡単にアクセスできるようになります。 しかし、データが追加、更新、または削除されると、この順序が崩れ、いわゆる「インデックスの断片化」が発生する可能性があります。 これは、シャッフルされたプレイリストがリスニング体験を妨げるのと同じように、データベースの速度を低下させる可能性があります。

この記事では、この「シャッフル」がいつ発生するかを特定する方法と、データがどの程度「シャッフル」されているかを測定する方法を学びます。 DJ のようなものですが、データベースを対象としています。 それでは、デッキを回す準備をして、始めましょう!

目次の表示
  • インデックスの断片化について
  • インデックスの断片化の検出
  • インデックスの断片化の測定
  • 結果の解釈
  • 結論

インデックスの断片化について

さて、インデックスの断片化とは実際には何なのかをもう少し詳しく見てみましょう。 プレイリストの例を覚えていますか? プレイリスト内の曲と同様に、データベース内のデータは特定の順序で保存されます。 この順序は、「インデックス」と呼ばれるものを使用して維持されます。インデックスは、すべてが保存されている場所への地図またはガイドのようなものです。

ここで、新しい曲 (またはデータ) を追加したり、一部を削除したり、移動したりすると、プレイリスト (またはインデックス) がシャッフルされたり断片化されたりすることがあります。 データベース用語では、これを「インデックスの断片化」と呼びます。

断片化には、内部断片化と外部断片化の 2 つのタイプがあります。

  • 内部断片化は、プレイリストに空のトラックがある場合など、データのページ内に空のスペースがある場合に発生します。
  • 一方、外部断片化とは、曲が意図した順序になっていない場合など、ページの論理的な順序が物理的な順序と一致しない場合です。

では、なぜインデックスの断片化を気にする必要があるのでしょうか? インデックスが断片化すると、SQL Server は必要なデータを見つけるためにさらに努力する必要があります。 それは、シャッフルされたプレイリストを特定の順序で聴こうとするようなものです。より多くの労力がかかりますよね。 同様に、インデックスが断片化するとデータベースのパフォーマンスが低下し、データの取得が遅くなり、効率が低下する可能性があります。

次のセクションでは、この断片化を検出する方法と、それを修正するために何ができるかを学びます。 それは、好きなように音楽を楽しめるようにプレイリストを整理する方法を学ぶようなものです。 それでは、旅の次の部分に進みましょう。

あなたにおすすめ: SQL インジェクション: それでも脅威ですか? どうすればそれを回避できるでしょうか？

インデックスの断片化の検出

インデックスの断片化とは何かを理解したところで、それを検出する方法について説明します。 SQL Server は、これを行うための便利なツールとコマンドをいくつか提供します。 これは、プレイリストがシャッフルされ、再編成が必要になったときに通知してくれる特別なアプリを持っているようなものです。

SQL Server で使用する主なツールは、 sys.dm_db_index_physical_statsと呼ばれるシステム関数です。 かなりの一口ですね。 しかし、心配しないでください。思っているほど複雑ではありません。 この関数は、データベースを調べてインデックスがどの程度断片化されているかを教えてくれる探偵のようなものです。 使用方法は次のとおりです。

1. データベースとテーブルの選択:

まず、どのデータベースとテーブルを調べたいかを関数に伝えます。 チェックしたいプレイリストを選択するようなものです。

2. 関数の実行:

次に、関数を実行します。 これは、次のような SQL コマンドを実行することで行われます。

 SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabaseName'), OBJECT_ID(N'YourTableName'), NULL, NULL, 'DETAILED');

このコマンドでは、「YourDatabaseName」と「YourTableName」をデータベースとテーブルの名前に置き換えます。

3. 結果を読む:

この関数は多くの情報を返しますが、私たちが注目する重要なことはavg_fragmentation_in_percentと呼ばれる値です。 これにより、インデックスがどの程度断片化されているかがパーセンテージでわかります。 これは、プレイリストがどの程度シャッフルされているかを伝えるようなものです。

SQLデータベース言語

インデックスの断片化の測定

身長や体重を測定するのと同じように、インデックスがどの程度断片化されているかを測定することもできます。 SQL Server では、これを行うためにいくつかの主要なメトリックを使用します。 これは、プレイリストのどれだけが順番に狂っているかを測定するようなものだと考えてください。 その方法は次のとおりです。

メトリクスを理解する:

私たちが使用する主なメトリクスはavg_fragmentation_in_percentと呼ばれます。 これにより、インデックス内の論理断片化 (順序が乱れたページ) の割合がわかります。 これは、プレイリストの何パーセントがシャッフルされているかを知らせるようなものです。

もう 1 つの重要なメトリクスは、 page_countです。 これにより、インデックス内のインデックスまたはデータ ページの合計数がわかります。 これは、プレイリスト内の曲の総数と考えてください。

コマンドの実行:

断片化の検出と同様に、 sys.dm_db_index_physical_stats関数を実行してインデックスの断片化を測定します。 ただし、今回はavg_fragmentation_in_percentとpage_count の値に注目します。

もう一度コマンドを示します。

 SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabaseName'), OBJECT_ID(N'YourTableName'), NULL, NULL, 'DETAILED');

「YourDatabaseName」と「YourTableName」をデータベースとテーブルの名前に忘れずに置き換えてください。 以下に表示される内容の例を示します。わかりやすくするためにいくつかの列のみを示します。

インデックス断片化 SQL サーバー データベース テーブル

この簡略化された表では次のようになります。

  • object_id はテーブルの ID です。
  • Index_id はインデックスの ID です。
  • Index_type_desc はインデックスのタイプを記述します (例: 「CLUSTERED INDEX」)。
  • avg_fragmentation_in_percent は、インデックスの平均断片化率 (パーセント単位) です。
  • Fragment_count は、インデックス内のフラグメント (連続したページのグループ) の数です。
  • page_count は、インデックス内の総ページ数です。

この表は、インデックスの断片化ステータスを明確に示します。

結果の解釈:

avg_fragmentation_in_percentが 5% 未満の場合、インデックスはかなり良好な状態にあります。プレイリストが少しだけシャッフルされているようなものです。 5% から 30% の間であれば、インデックスの再編成が必要になる可能性があります。 また、それが 30% を超えている場合は、プレイリストを最初から並べ替えるなど、インデックスを完全に再構築する必要がある可能性があります。

page_count値は、インデックス (またはプレイリスト) がどのくらいの大きさであるかを示します。 数が少ない場合は、断片化についてあまり心配する必要はないかもしれません。 しかし、その数が大きい場合、断片化により処理が大幅に遅くなる可能性があるため、それを修正するための措置を必ず講じる必要があります。

結果の解釈

思い出してください。ここでは、データベースの健康診断レポートのような、インデックスの状態を示すテーブルを見ていることに注意してください。

1. 断片化レベルを理解する

avg_fragmentation_in_percent列はインデックスのハートビートのようなものです。 これは、インデックスがどの程度断片化されているか、または整理されていないかを示します。 0 パーセントや 1 パーセントなどの低い数値は、インデックスが良好な状態にあること、つまり、よく保管された図書館と同じように整理されていることを意味します。 しかし、60 パーセントや 70 パーセントなどの高い数値は、インデックスがかなり断片化されていることを意味します。整然とした図書館というよりは、散らかった部屋に似ています。

2. フラグメント数とページ数

Fragment_count 列とpage_count列により、インデックスに関する詳細が得られます。 「断片」は本の一部のようなもの、「ページ」はその本のページのようなものと考えることができます。 断片が多数ある場合は、本が多くのセクションに分割されていることを意味し、速く読むのが難しくなる可能性があります。 ページ数が多いということは、本 (この場合はインデックス) がかなり大きいことを意味します。

3. いつ行動を起こすべきか

では、断片化についていつから心配し始めるべきでしょうか? 一般的なルールとして、 avg_fragmentation_in_percentが 5% 未満の場合、インデックスは正常であるため、何もする必要はありません。 それが 5 ～ 30% の間であれば、少し散らかった部屋を掃除するような、ちょっとした整理整頓をインデックスに適用できます。 また、30% を超えている場合は、インデックスが大幅に断片化されているため、部屋が非常に散らかった場合に大規模な掃除が必要になるのと同じように、インデックスを再編成するための措置を講じる必要があります。

これらは単なるガイドラインであることに注意してください。 正確な数値は、データベースの特定のニーズとパフォーマンスによって異なる場合があります。 しかし、これらの結果を理解することで、インデックスとデータベースをスムーズに実行し続けることができます。

こちらもおすすめです: Asp.Net MVC 開発で SQL の GeoGraphy DataType を使用する方法。

結論

結論

プレイリストがよく整理されていると、お気に入りの曲を簡単に見つけて再生できるのと同じように、データベースがよく整理されていると、SQL Server が必要なデータを簡単に見つけて取得できます。 これが、インデックスの断片化の検出と測定が非常に重要である理由です。これは、データベースをスムーズかつ効率的に実行し続けるのに役立ちます。

この記事を通して、インデックスの断片化はシャッフルされたプレイリストに少し似ていることがわかりました。 インデックスが断片化またはシャッフルされると、SQL Server は必要なデータを見つけるためにさらに努力する必要があります。 これにより、クエリが遅くなり、データベースの効率が低下する可能性があります。

ただし、これまで説明してきたツールとコマンドを使用すると、インデックスの断片化を検出して測定できます。 これにより、問題を特定し、インデックスを再編成するか完全に再構築するなど、問題を修正するための措置を講じることができます。 これは、シャッフルされたプレイリストの順序を変更するのと少し似ています。すべてを元の位置に戻すことで、探しているものを見つけやすくなります。

結局のところ、インデックスの維持はデータベースを維持する上で重要な部分です。 インデックスの断片化を定期的にチェックして対処することで、データベースが常に最高のパフォーマンスを維持できるようになります。

SQL Server のインデックスの断片化について詳しく知りたい場合は、この詳細な記事を参照することをお勧めします。 これは、このトピックをさらに深く掘り下げたい人にとって素晴らしいリソースです。

プレイリストを整然とした状態に保つのと同じように、インデックスの維持は継続的なタスクであることを覚えておいてください。 しかし、適切な知識とツールがあれば、データベースのパフォーマンスの点で大きな利益を得ることができるタスクです。 インデックス作成を楽しんでください。