「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。 -[[戻る>SQL Server]] * 目次 [#u956a893] #contents *概要 [#k1b49bf6] [[インデックス>SQL Server のインデックス]]の[[断片化>SQL Server のインデックス#z9ed663c]]を解消し、ディスクI/Oを減らす。 *監視 [#r4ac0f88] 「DBCC SHOWCONTIG」ステートメントで、「インデックスの断片化」を特定できる。~ (DBCC SHOWCONTIGは削除予定なので、sys.dm_db_index_physical_stats を使用する) **断片化の監視 [#qe662148] -「DBCC SHOWCONTIG」ステートメントを使用することで、~ 「インデックスの断片化」レベルを監視し、断片化の進んだインデックスを特定できる。 -「DBCC SHOWCONTIG」ステートメントの「TABLERESULTS」オプションを使用すると、情報を行セットとして返すので、~ これをテーブルに定期的に書き込めば、時系列に「インデックスの断片化」レベルを記録、監視できる。 -負荷の高いサーバで「DBCC SHOWCONTIG」ステートメントを実行するときは、「WITH FAST」オプションを使用し、~ インデックスの「リーフ ページ」がスキャンされないようにして、性能を向上できる。ただし、「ページ密度」の測定はできない。 **実行結果 [#rfe6bb39] -再構築したばかりの「クラスタ化インデックス」に対して、~ 「DBCC SHOWCONTIG」ステートメントを実行した際の出力。 - スキャンされたページ数........................... : 52632 - スキャンされたエクステント数................ : 6604 - 切り替えられたエクステント数................ : 6603 - エクステントごとの平均ページ数............ : 8.0 - スキャン密度 [最善 :実際] ...................... : 99.62% [6579:6604] - 論理スキャン フラグメンテーション...... : 0.01% - エクステント スキャン フラグメンテーション.... : 0.14% - ページごとの平均空きバイト数............... : 10.5 - 平均ページ密度 (全体)............................. : 99.87% -再構築したばかりの「非クラスタ化インデックス」 (「クラスタ化インデックス」が存在しない場合)に対して、~ 「DBCC SHOWCONTIG」ステートメントを実行した際の出力。 - スキャンされたページ数............................ : 55274 - スキャンされたエクステント数.................. : 6913 - 切り替えられたエクステント数................. : 6912 - エクステントごとの平均ページ数............. : 8.0 - スキャン密度 [最善 :実際]........................ : 99.96% [6910:6913] - エクステント スキャン フラグメンテーション .... : 0.03% - ページごとの平均空きバイト数................. : 397.0 - 平均ページ密度 (全体).............................. : 95.10% **出力結果 [#d08c7ded] ここでは、「DBCC SHOWCONTIG」ステートメントで出力した情報から、~ 「インデックスの断片化」レベルを確認する2つの方法について説明する。 ***「スキャン密度(%)」で確認 [#j27804cb] -「ページ分割」が発生すると、~ 分割された一部の「リーフ レベル ページ」が、別の「エクステント」に格納されることがあるので、~ 「インデックスの断片化」が進むと、正味のデータ量の割に「エクステント」の数が大きくなる。 -この問題は、「スキャン密度(%)」で判断することができる。~ 「スキャン密度(%)」が小さくなった場合、「インデックスの断片化」が進んでいることを示す。 -「スキャン密度(%)」は、次の式で算出される。 |値|説明(式)|h |スキャン密度(%)|最善のエクステント変更回数 / 現在のエクステント変更回数 * 100| |最善のエクステント変更回数|すべての「ページ」が連続的にリンクされる場合、スキャン処理によってエクステントが変更される回数| |現在のエクステント変更回数|実際のスキャン処理を実行してエクステントが変更された回数| ***「論理スキャン フラグメンテーション」で確認 [#p8a27259] -「クラスタ化インデックス」の場合、~ 「リーフ レベル ページ」の「データ ページ」中のデータが物理的に順序正しく並べられる。~ しかし、「ページ分割」が発生すると、分割された一部の「データ ページ」が、~ 物理的に離れた位置に格納されることがあるので、順序が不正な「データ ページ」の割合が増える。 -この問題は、「論理スキャン フラグメンテーション」で判断することができる。~ この値が大きくなった場合、「インデックスの断片化」が進んでいることを示す。~ この値はできるだけ0%に近い値にする。0 ~ 10%が許容範囲であり、~ これを超えると、「インデックス スキャン」の性能が低下する可能性がある。 |値|説明(式)|h |論理スキャン フラグメンテーション(%)|物理的に順序正しく並べられていない「リーフ レベル ページ」の割合| |エクステント スキャン フラグメンテーション(%)|物理的に離れた位置にある「エクステント」の割合| *修正 [#e0c20d82] **インデックスの再構築 (alter index rebuild) [#td4b0cd0] 再構築なので断片化の度合に響されず、All or Nothing。 -テーブル、インデックスの構造や制約がわからない場合でも、~ 1つのステートメントでテーブルの全ての「再構築」ができるため、~ 複数の「DROP INDEX」と「CREATE INDEX」を使用して「再構築」するより簡単。 -同時に、オプティマイザの使用する統計が更新される。 -オプションで「ページ密度」を変更することができる。 **再構成 (alter index reorganize) [#v09ecaad] 再構成なので断片化の度合に響され、キャンセル時点までの再構成は有効。 -「リーフ レベル ページ」を物理的に順序正しく並べ替え、「最適化」する。 -これにより、「インデックス スキャン」の性能が向上する。 -ただし、この方法は「再構築」の操作よりも劣る。 -期待した効果が得られない場合は、「再構築」が必要になる。 **比較 [#c19901b1] ***同時実行性 [#da3adf94] -「再構築」 --処理中インデックス全体がロックされインデックスは使用不可 --ONLINEオプションを付与した場合は使用可だが、~ 再構築を完了するためにかなり多くのリソースを使用する。 -「最適化」~ 処理中ページのみロックされインデックスは使用可 ***必要なデータ領域の空き容量 [#p9d58da8] -「再構築」~ 再構成よりも多い。 --「再構築」には、「データ ファイル」に十分な空き容量が必要になる。 --必要な空き領域は、再構築するインデックス量に応じて変化する。 --「クラスタ化インデックス」の場合、~ 「必要な空き領域 = 1.2 * (平均行サイズ) * (行数)」が目安となる。 -「最適化」~ 再構築よりも少ない。 ***トランザクション ログ量の比較 [#q7000327] -「再構築」 --インデックスの「ページ」のイメージをトランザクション ログに記録する。 --このため、トランザクション ログ領域を大量に使用することがある。 --必要なトランザクション ログ領域は、大まかに見積もって、「ページ」数×8 KBとなる。 --「ページ」数は、「DBCC SHOWCONTIG」ステートメントで確認できる。 --ただし、「再構築」のトランザクション ログは、~ 「一括ログ復旧モデル」では記録されないので、必要に応じて「復旧モデル」を変更する。 -「最適化」 --一般的に、「最適化」のトランザクション ログ領域の使用量は、~ 「再構築」のトランザクション ログ領域の使用量より少量になる。~ ただし、「最適化」処理の作業量による(断片化の度合が大きいと多くなる)。 --「最適化」のトランザクション ログは、「一括ログ復旧モデル」でも記録される。 *注意事項 [#pc2c2765] -旧式のI/Oサブシステムを使用している環境では、~ 「インデックスの断片化」を修正する前に「ディスクの断片化」を修正する。 -SANを使用している環境では、「ディスクの断片化」を修正する必要はない。 --「インデックスの断片化」はI/O処理能力に影響するため、~ 「インデックス ページ」、「データ ページ」がデータ キャッシュ内に存在するクエリの性能には影響を与えない。 --SANでは、一般的に大容量のデータ キャッシュが提供されるため、小規模環境に比べると、影響を受け難い。 *参考情報 [#e492c2ff] -Rebuilding SQL Server indexes using the ONLINE option~ https://www.mssqltips.com/sqlservertip/2361/rebuilding-sql-server-indexes-using-the-online-option/ -Microsoft Docs --インデックス再構築と再構成の違い~ https://docs.microsoft.com/ja-jp/archive/blogs/jpsql/977 --オンライン インデックス操作のガイドライン~ --SQL Server ---オンライン インデックス操作のガイドライン~ https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/guidelines-for-online-index-operations ---パフォーマンスを向上させ、リソース使用率を削減するためにインデックスを最適に維持する~ https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/reorganize-and-rebuild-indexes ---- Tags: [[:データアクセス]], [[:SQL Server]]