[[Open棟梁Project>http://opentouryo.osscons.jp/]] - [[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]
「[[マイクロソフト系技術情報 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 のインデックス]]の断片化を解消し、ディスクI/Oを減らす。
[[インデックス>SQL Server のインデックス]]の[[断片化>SQL Server のインデックス#z9ed663c]]を解消し、ディスクI/Oを減らす。

*「インデックスの断片化」レベルの監視 [#r4ac0f88]
*監視 [#r4ac0f88]
「DBCC SHOWCONTIG」ステートメントで、「インデックスの断片化」を特定できる。~
（DBCC SHOWCONTIGは削除予定なので、sys.dm_db_index_physical_stats を使用する）

-「DBCC SHOWCONTIG」ステートメントを使用した、「インデックスの断片化」レベルの監視
**断片化の監視 [#qe662148]

--「DBCC SHOWCONTIG」ステートメントを使用することで、~
-「DBCC SHOWCONTIG」ステートメントを使用することで、~
「インデックスの断片化」レベルを監視し、断片化の進んだインデックスを特定できる。

--「DBCC SHOWCONTIG」ステートメントの「TABLERESULTS」オプションを使用すると、情報を行セットとして返すので、~
-「DBCC SHOWCONTIG」ステートメントの「TABLERESULTS」オプションを使用すると、情報を行セットとして返すので、~
これをテーブルに定期的に書き込めば、時系列に「インデックスの断片化」レベルを記録、監視できる。

--負荷の高いサーバで「DBCC SHOWCONTIG」ステートメントを実行するときは、「WITH FAST」オプションを使用し、~
-負荷の高いサーバで「DBCC SHOWCONTIG」ステートメントを実行するときは、「WITH FAST」オプションを使用し、~
インデックスの「リーフ ページ」がスキャンされないようにして、性能を向上できる。ただし、「ページ密度」の測定はできない。

--「DBCC SHOWCONTIG」ステートメントの実行結果を次に示す。
---再構築したばかりの「クラスタ化インデックス」に対して、~
**実行結果 [#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%

-「DBCC SHOWCONTIG」ステートメントの出力結果の分析~
**出力結果 [#d08c7ded]
ここでは、「DBCC SHOWCONTIG」ステートメントで出力した情報から、~
「インデックスの断片化」レベルを確認する２つの方法について説明する。

--「スキャン密度（%）」で確認
---「ページ分割」が発生すると、~
***「スキャン密度（%）」で確認 [#j27804cb]
-「ページ分割」が発生すると、~
分割された一部の「リーフ レベル ページ」が、別の「エクステント」に格納されることがあるので、~
「インデックスの断片化」が進むと、正味のデータ量の割に「エクステント」の数が大きくなる。

---この問題は、「スキャン密度（％）」で判断することができる。~
-この問題は、「スキャン密度（％）」で判断することができる。~
「スキャン密度（％）」が小さくなった場合、「インデックスの断片化」が進んでいることを示す。

---「スキャン密度（％）」は、次の式で算出される。
-「スキャン密度（％）」は、次の式で算出される。
|値|説明（式）|h
|スキャン密度（％）|最善のエクステント変更回数 / 現在のエクステント変更回数 * 100|
|最善のエクステント変更回数|すべての「ページ」が連続的にリンクされる場合、スキャン処理によってエクステントが変更される回数|
|現在のエクステント変更回数|実際のスキャン処理を実行してエクステントが変更された回数|

--「論理スキャン フラグメンテーション」で確認
---「クラスタ化インデックス」の場合、~
***「論理スキャン フラグメンテーション」で確認 [#p8a27259]
-「クラスタ化インデックス」の場合、~
「リーフ レベル ページ」の「データ ページ」中のデータが物理的に順序正しく並べられる。~
しかし、「ページ分割」が発生すると、分割された一部の「データ ページ」が、~
物理的に離れた位置に格納されることがあるので、順序が不正な「データ ページ」の割合が増える。

---この問題は、「論理スキャン フラグメンテーション」で判断することができる。~
-この問題は、「論理スキャン フラグメンテーション」で判断することができる。~
この値が大きくなった場合、「インデックスの断片化」が進んでいることを示す。~
この値はできるだけ0%に近い値にする。0 ~ 10%が許容範囲であり、~
これを超えると、「インデックス スキャン」の性能が低下する可能性がある。
|値|説明（式）|h
|論理スキャン フラグメンテーション（％）|物理的に順序正しく並べられていない「リーフ レベル ページ」の割合|
|エクステント スキャン フラグメンテーション（％）|物理的に離れた位置にある「エクステント」の割合|

*「インデックスの断片化」の修正 [#e0c20d82]
-インデックスの再構築 (alter index rebuild)
--テーブル、インデックスの構造や制約がわからない場合でも、~
*修正 [#e0c20d82]

**インデックスの再構築 (alter index rebuild) [#td4b0cd0]
再構築なので断片化の度合に響されず、All or Nothing。

-テーブル、インデックスの構造や制約がわからない場合でも、~
１つのステートメントでテーブルの全ての「再構築」ができるため、~
複数の「DROP INDEX」と「CREATE INDEX」を使用して「再構築」するより簡単。
--同時に、オプティマイザの使用する統計が更新される。
--オプションで「ページ密度」を変更することができる。

-再構成 (alter index reorganize)
--「リーフ レベル ページ」を物理的に順序正しく並べ替え、「最適化」する。
--これにより、「インデックス スキャン」の性能が向上する。
--ただし、この方法は「再構築」の操作よりも劣る。
--期待した効果が得られない場合は、「再構築」が必要になる。
-同時に、オプティマイザの使用する統計が更新される。
-オプションで「ページ密度」を変更することができる。

-比較
--インデックス再構築と再構成の違い | Microsoft SQL Server Japan Support Team Blog~
https://blogs.msdn.microsoft.com/jpsql/2013/02/28/977/
**再構成 (alter index reorganize) [#v09ecaad]
再構成なので断片化の度合に響され、キャンセル時点までの再構成は有効。

--比較の詳細
---必要なデータ領域の空き容量
「再構築」には、「データ ファイル」に十分な空き容量が必要になる。必要な空き領域は、再構築するインデックス量に応じて変化する。「クラスタ化インデックス」の場合、「必要な空き領域 =  1.2 × （平均行サイズ） × （行数）」が目安となる。
-「リーフ レベル ページ」を物理的に順序正しく並べ替え、「最適化」する。
-これにより、「インデックス スキャン」の性能が向上する。
-ただし、この方法は「再構築」の操作よりも劣る。
-期待した効果が得られない場合は、「再構築」が必要になる。

---トランザクション ログ量の比較
「再構築」
インデックスの「ページ」のイメージをトランザクション ログに記録する。このため、トランザクション ログ領域を大量に使用することがある。必要なトランザクション ログ領域は、大まかに見積もって、「ページ」数×8 KBとなる。「ページ」数は、「DBCC SHOWCONTIG」ステートメントで確認できる。
**比較 [#c19901b1]

ただし、「再構築」のトランザクション ログは、「一括ログ復旧モデル」では記録されないので、必要に応じて「復旧モデル」を変更する。
***同時実行性 [#da3adf94]
-「再構築」
--処理中インデックス全体がロックされインデックスは使用不可
--ONLINEオプションを付与した場合は使用可だが、~
再構築を完了するためにかなり多くのリソースを使用する。

	「最適化」
一般的に、「最適化」のトランザクション ログ領域の使用量は、「再構築」のトランザクション ログ領域の使用量より少量になる。ただし、「最適化」処理の作業量による。
-「最適化」~
処理中ページのみロックされインデックスは使用可

「最適化」のトランザクション ログは、「一括ログ復旧モデル」でも記録される。
***必要なデータ領域の空き容量 [#p9d58da8]
-「再構築」~
再構成よりも多い。
--「再構築」には、「データ ファイル」に十分な空き容量が必要になる。
--必要な空き領域は、再構築するインデックス量に応じて変化する。
--「クラスタ化インデックス」の場合、~
「必要な空き領域 = 1.2 * (平均行サイズ) * (行数)」が目安となる。

*その他、注意事項 [#pc2c2765]
-「最適化」~
再構築よりも少ない。

***トランザクション ログ量の比較 [#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]]
トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS