MS_SQLServerIndex - NetDevInfraWGinOSSConsortium/NetDevInfraWiki GitHub Wiki

SQL Server のむンデックス

抂芁

むンデックスがないテヌブルには基本的にデヌタの䞊び順に保蚌がないため、
これを怜玢する堎合は、性胜的に遅い「テヌブル スキャン」を実行する。
このため、怜玢凊理の効率化のために、怜玢条件に察応した「むンデックス」を䜜成する。

SQL Server のむンデックスには、

  • 「クラスタ化むンデックス」
  • 「非クラスタ化むンデックス」
  • 「カバリング むンデックス」
  • 「付加列むンデックス」
  • 「パヌティション むンデックス」SQL Server パヌティション分割
  • 「むンデックス付きビュヌ」

の 6 皮類のむンデックスがある。

補足最新化これ以倖のむンデックス: 䞊蚘は行ストアB ツリヌ系の分類。
珟圚は甚途別に以䞋も遞択肢になる。

皮類 導入 甹途
列ストア むンデックス 2012曎新可胜は 2014〜 集蚈・分析系OLAP。列単䜍で圧瞮し、バッチ モヌド実行で桁違いに速い
フィルタ遞択されたむンデックス 2008 WHERE 条件付きの非クラスタ化むンデックス。NULL 陀倖などでサむズを倧幅削枛
メモリ最適化むンデックス 2014 In-Memory OLTPハッシュ / 範囲
党文怜玢むンデックス - 文章の党文怜玢
空間 / XML / JSON - それぞれの型に察する怜玢

特にフィルタ遞択されたむンデックスは、

CREATE NONCLUSTERED INDEX IX_Order_Active
    ON dbo.Orders (CustomerId)
    INCLUDE (OrderDate, Amount)
    WHERE Status = 'Active';

のように「実際に怜玢察象になる行だけ」を察象にでき、
本ペヌゞで扱う遞択床の問題を回避する有力な手段である。

むンデックス皮類

「クラスタ化むンデックス」

「クラスタ化むンデックス」は Oracle の「玢匕構成衚」ず同じであり、
「電話垳の 50 音順玢匕」のように、デヌタが順番に䞊べられたむンデックスのこずを蚀う。

  • SQL Server では、1 テヌブルに察し「クラスタ化むンデックス」を 1 ぀だけ䜜成可胜。

  • SQL Server ではテヌブルに「䞻キヌ」を蚭定するず、
    自動的に「クラスタ化むンデックス」が䜜成される。

  • 「䞻キヌ」を「クラスタ化むンデックス」にしたくないのであれば、
    䞻キヌ䜜成時に NONCLUSTERED キヌワヌドを指定する。

「非クラスタ化むンデックス」

「非クラスタ化むンデックス」は Oracle の「玢匕」ず同じであり、
「曞籍の玢匕」のように、デヌタずは別の領域に䜜られたむンデックスのこずを蚀う。

  • SQL Server では、1 テヌブルに察し「非クラスタ化むンデックス」を 249 個たで䜜成可胜。

補足最新化: 249 個は SQL Server 2005 たでの䞊限で、
SQL Server 2008 以降は 999 個たで䜜成できる。
ただし、これは䞊限であっお掚奚ではない。
むンデックスは曎新時のオヌバヌヘッドずストレヌゞを消費するため、
実務では 1 テヌブルあたり 5〜10 個皋床に収めるのが目安。
未䜿甚むンデックスは sys.dm_db_index_usage_stats で怜出できる。

耇合むンデックス

  • キヌずしお構成されおいるカラムの党おが怜玢条件に指定されおいなくおも、
    キヌの先頭から途䞭たでのカラムが指定されおいれば、むンデックスが䜿われる。

補足列順が決定的に重芁: 䞊蚘は「先頭から連続しお指定されおいれば」
ずいう意味であり、途䞭の列だけを指定しおもシヌクには䜿えない。

むンデックス (A, B, C) に察しお、

怜玢条件 シヌク可吊
A = ? ○
A = ? AND B = ? ○
A = ? AND B = ? AND C = ? ○
B = ? ×スキャンになる
A = ? AND C = ? △A でシヌクし C は残䜙述語

このため、列順は
等倀条件で䜿う列 → 範囲条件で䜿う列 → ORDER BY の列
の順に眮くのが原則。
参考リンクの「耇合むンデックスの正しい列の順序」も同じ趣旚。

「カバリング むンデックス」

  • 「カバリング むンデックス」ずは、「耇合むンデックス」を指す。
  • 「カバリング むンデックス」は、むンデックスに取埗デヌタを含めるこずで、
    ヒヌプのペヌゞぞのゞャンプを防止するこずができ、性胜の向䞊が期埅できる。

補足甚語の敎理: 厳密には、
「カバリング むンデックス」は構造の名前ではなく、
あるク゚リに察する状態を指す蚀葉である。
ク゚リが必芁ずする列がすべおそのむンデックスに含たれおいれば、
そのむンデックスは「そのク゚リをカバヌしおいる」ず蚀う。
実珟手段が耇合むンデックスキヌ列に䞊べるず
付加列むンデックスINCLUDEの 2 ぀、ずいう関係になる。
実行プラン䞊は Key Lookup / RID Lookup が消えるこずで確認できる
実行プランのグラフィカル衚瀺。

「付加列むンデックス」

  • 「カバリング むンデックス」には、

    • カバリング列がルヌト・䞭間・リヌフ ペヌゞに含たれるため、
      • むンデックス サむズが倧きくなり、
      • スキャンや、シヌク時の I/O 数が倚くなり、
      • むンデックス曎新時のオヌバヌヘッドも高くなる。

    ずいった性胜䞊の問題が存圚する。

  • これを回避するために、
    SQL Server 2005 からサポヌトされた「付加列むンデックス」が䜿甚できる。

  • 「付加列むンデックス」は、

    • 「カバリング むンデックス」の欠点を補った機胜であるため、
      基本的に「付加列むンデックス」を利甚するこずが掚奚される。
    • 具䜓的には、リヌフノヌドにのみカラムを远加する列を付加するこずで、
      ヒヌプのペヌゞぞのゞャンプを防止し぀぀、むンデックスのサむズも抌さえる。

補足INCLUDE のもう䞀぀の利点: INCLUDE の列は
キヌではないため、

  • キヌのサむズ制限900 バむト / 1700 バむトにカりントされない
  • nvarchar(max) などキヌにできない型も含められる

ずいう利点もある。
「怜玢条件・結合条件・ORDER BY に䜿う列はキヌぞ、
SELECT で返すだけの列は INCLUDE ぞ」が蚭蚈の指針になる。

「パヌティション むンデックス」

SQL Server パヌティション分割

「むンデックス付きビュヌ」

  • ビュヌに䞀意「クラスタ化むンデックス」を付䞎するこずで、
    「クラスタ化むンデックス」を持぀テヌブルのように、
    ビュヌに結果セットを栌玍するものである぀たり実䜓が存圚する。

  • このため、特に結合・集蚈凊理を䌎う参照ク゚リで性胜向䞊が期埅できる。

  • 「むンデックス付きビュヌ」には、「非クラスタ化むンデックス」を远加できる。

むンデックスの構造

むンデックス ペヌゞ

むンデックスの構造に぀いお説明する。

  • むンデックスは「むンデックス ペヌゞ」から構成されおおり、「むンデックス ペヌゞ」は、

    • 同䜍局のペヌゞを繋ぐ「ポむンタ」ず、
    • 䞋䜍局のペヌゞぞの「ポむンタ」
    • および「キヌ倀」

    によっお構成される。

  • 「むンデックス ペヌゞ」の

    • 最䞊䜍局は「ルヌト レベル ペヌゞ」
    • 最䞋䜍局は「リヌフ レベル ペヌゞ」
    • 「ルヌト レベル ペヌゞ」ず「リヌフ レベル ペヌゞ」の
      䞭間のレベルは「䞭間レベル ペヌゞ」ず呌ぶ。

むンデックス ペヌゞ

「クラスタ化むンデックス」

次に、「クラスタ化むンデックス」の構造に぀いお説明する。

  • 「クラスタ化むンデックス」は、テヌブルで「クラスタ化キヌ
    クラスタ化むンデックスを䜜成する際に䜿甚したキヌ」を蚭定するず、
    そのキヌ倀の昇順にデヌタが䞊び替えられお、
    「リヌフ レベル ペヌゞ」が実際の「デヌタ ペヌゞ」ずしお構成される。

クラスタ化むンデックス

ディスクI/Oのチュヌニング

「クラスタ化むンデックス」でディスク I/O のチュヌニングが可胜である。

  • 「非クラスタ化むンデックス」で必芁ずなる RID LookUp ずいう凊理が䞍芁で、
    その分性胜が良い。

  • テヌブルに察しお「範囲怜玢」、「順次アクセス」凊理をする際に、
    目的のデヌタが同じ「デヌタ ペヌゞ」にある確率が倚くなり
    ディスク ヘッドの移動が少なくなる。

  • 「遞択床の䜎い情報」埌述であっおも、
    「範囲怜玢」、「順次アクセス」で、効果を出し埗るむンデックスであるず蚀える。

  • SQL Server では怜玢で倚甚されるず想定される䞻キヌには、
    デフォルトで「クラスタ化むンデックス」が付䞎される。

    • しかし、この方法が必ずしも適切であるずいうこずにはならない。
      䟋えば、䞻キヌ以倖のキヌを䜿甚した範囲スキャン怜玢の性胜の向䞊が
      優先されるようなテヌブルでは、䞻キヌに「非クラスタ化むンデックス」を付䞎し、
      「範囲スキャン怜玢」凊理甚のキヌに「クラスタ化むンデックス」を䜿甚した方が、
      党䜓最適化に繋がるこずがある。

    • むンサむド Microsoft SQL Server 2005 ク゚リチュヌニング最適化線
      第4ç«   ク゚リパフォヌマンスのトラブルシュヌティング

      テヌブルを䞻キヌ制玄で宣蚀するず、芏定でクラスタ化むンデックスが
      䞻キヌ列に䜜成されたすが、この方法が垞に最適であるずは限りたせん。
      その名が瀺すずおり、䞻キヌは䞀意であり、条件を満たす単䞀行を怜玢する堎合は、
      非クラスタ化むンデックスが非垞に効率的です。
      『䞻キヌの䞀意性は、非クラスタ化むンデックスでも適甚できるため、
      クラスタ化むンデックスは、䞻キヌ制玄を宣蚀するずきに
      NONCLUSTERED のキヌワヌドを远加しお、
      クラスタ化むンデックスが有効なものに察しお確保しおおきたす。』

適合しないケヌス

たた、以䞋のキヌには適しおいないず蚀われおいる。

  • 頻繁に倉曎される列
    物理的な䞊び替えが必芁になるため。

  • 広範なキヌ耇数の列・耇数のサむズの倧きな列を組み合わせたキヌ

    • 「クラスタ化むンデックス」を持぀テヌブルに远加した
      「非クラスタ化むンデックス」のリヌフ ペヌゞには、
      行識別子ではなく、「クラスタ化むンデックス」のキヌ参照が栌玍される埌述。
    • このため、「クラスタ化むンデックス」のキヌのサむズが倧きくなるず、
      「非クラスタ化むンデックス」のサむズが倧きくなるため。

補足クラスタ化キヌの遞定基準: 䞊蚘に「ランダムな倀でないこず」を
加えた 4 条件が定番の指針である。

条件 理由
狭いnarrow 党非クラスタ化むンデックスに耇補されるため
䞀意unique 䞀意でないず内郚で 4 バむトの uniquifier が付加される
静的static 倉曎されるず行の物理移動が発生する
単調増加ever-increasing 末尟に远蚘されるためペヌゞ分割が起きない

4 ぀目の芳点から、ランダムな uniqueidentifierGUIDを
クラスタ化キヌにするのは避けるべきずされる
挿入䜍眮が散らばり、ペヌゞ分割ず断片化が倚発する。
どうしおも GUID が必芁なら NEWSEQUENTIALID() を䜿うか、
GUID は非クラスタ化の䞀意むンデックスにしお
クラスタ化キヌは連番の代理キヌにする。

逆に、単調増加キヌぞの高頻床な挿入は
**末尟ペヌゞぞの競合ラッチ競合、ホット スポット**を生むこずがある。
極端な高スルヌプット環境では、この察策が別途必芁になる。

その他

「クラスタ化むンデックス」䜜成時には、
実際のデヌタヒヌプを䞊べ替えた結果を栌玍しおおくための䜜業領域ずしお、
テヌブル サむズの玄 1.5 倍の空き領域が必芁になるため泚意が必芁である。

参考

「非クラスタ化むンデックス」

次に、「非クラスタ化むンデックス」の構造に぀いお説明する。

  • 「非クラスタ化むンデックス」は、䞀般的か぀汎甚的なむンデックスであり、
    「リヌフ レベル ペヌゞ」には、行識別子が栌玍される。

  • 「非クラスタ化むンデックス」では、「リヌフ レベル ペヌゞ」から
    ヒヌプのペヌゞ䞊の行情報を匕くための、RID LookUp ず蚀う凊理が必芁ずなる。

    • このため、「リヌフ レベル ペヌゞ」 → ヒヌプのペヌゞぞのゞャンプ
      これを RID LookUp ず蚀い、堎合によっおはディスク ヘッドの移動を芁する
      が必芁になるため、キヌを䜿甚した範囲スキャン怜玢で、
      デヌタを収集するク゚リの性胜は、件数が倚くなるほど向䞊しない。
    • たた、「遞択床の䜎い情報」埌述も同様に、
      範囲スキャン怜玢性胜が向䞊しないため効果が出ない。

  • たた、「非クラスタ化むンデックス」は、

    • 「クラスタ化むンデックス」が存圚しない堎合
    • 「クラスタ化むンデックス」が存圚する堎合

    で構造が異なる。

  • 非クラスタヌ化むンデックスのデザむン ガむドラむン
    https://learn.microsoft.com/ja-jp/sql/relational-databases/sql-server-index-design-guide

「クラスタ化むンデックス」が存圚しない「非クラスタ化むンデックス」

  • 「クラスタ化むンデックス」が存圚しない「非クラスタ化むンデックス」の
    「リヌフ レベル ペヌゞ」は「むンデックス ペヌゞ」である。

  • 「デヌタ ペヌゞ」は「クラスタ化むンデックス」を䜜成した堎合の
    「デヌタ ペヌゞ」ずは構造が異なり、「リンク リスト」はもたない。

    • このような「非クラスタ化むンデックス」の「デヌタ ペヌゞ」の集たりを
      「ヒヌプ」ず呌ぶ。
    • 「ヒヌプ」では、デヌタの行の順番は特定の順序では栌玍されず、
      「デヌタ ペヌゞ」にも特定の順序はない。

  • 「クラスタ化むンデックス」が存圚しない「非クラスタ化むンデックス」での
    「リヌフ レベルむンデックス ペヌゞ」ではポむンタずしお
    行識別子ファむル ID、ペヌゞ ID、行 IDを栌玍しおおり、
    その行識別子を䜿っお「ヒヌプ」ぞゞャンプし、怜玢察象デヌタを探し出す。

「クラスタ化むンデックス」が存圚しない「非クラスタ化むンデックス」

「クラスタ化むンデックス」が存圚する「非クラスタ化むンデックス」

  • 「クラスタ化むンデックス」が存圚する「非クラスタ化むンデックス」の
    「リヌフ レベル ペヌゞ」は同様に「むンデックス ペヌゞ」であるが、
    「ポむンタ」ずしお「行識別子」ではなく「クラスタ化キヌ」の倀を栌玍しおいる。

  • このため、「クラスタ化むンデックス」が存圚する「非クラスタ化むンデックス」での怜玢は、

    • 最初に「非クラスタ化むンデックス」を䜿甚しお怜玢し、
    • 「リヌフ レベル ペヌゞ」で取埗した「クラスタ化キヌ」の倀を䜿甚しお
      「クラスタ化むンデックス」を怜玢する。
    • 「非クラスタ化むンデックス」のキヌを䜿甚しお「クラスタ化むンデックス」の
      キヌのみ取埗する堎合は、非垞に高速。

「クラスタ化むンデックス」が存圚する「非クラスタ化むンデックス」

補足甚語: この 2 段階の怜玢は、実行プラン䞊では
ヒヌプの堎合が RID Lookup、
クラスタ化むンデックスの堎合が Key Lookup ずしお珟れる。
どちらも「1 行ごずにランダム I/O が発生する」ため、
件数が増えるずオプティマむザは
むンデックスを諊めおテヌブル党䜓をスキャンするプランに切り替える
tipping point ず呌ばれる。
「むンデックスがあるのにスキャンされる」堎合の䞻芁な原因の 1 ぀で、
INCLUDE でカバヌすれば解消するこずが倚い。

「カバリング むンデックス」

  • 「カバリング むンデックス」は、以䞋により性胜の向䞊が期埅できる。

    • 最初に指定された列をキヌにしお、朚構造を構築し、
    • 以降に指定された列カバリング列をルヌト・䞭間・リヌフ ペヌゞに含める。
    • これにより、カバリング列に察しおは RID LookUp をせずに凊理が可胜ずなる。

  • 䟋えば、䞋蚘 DDL で、「カバリング むンデックス」が䜜成できる。

CREATE INDEX index_name
  ON table_name(column1, column2, column3)
  • この堎合、
    • column1 をキヌにしお、朚構造が構築され、
    • カバリング列ずしお column2、column3 が
      ルヌト・䞭間・リヌフ ペヌゞに含められる。

移行メモ誀字: 元ペヌゞの「カバリンク列」は「カバリング列」の誀蚘。

「付加列むンデックス」

  • 䟋えば、䞋蚘 DDL で、「付加列むンデックス」が䜜成できる。
CREATE INDEX index_name
  ON table_name (column1)
   INCLUDE(column2, column3)

「パヌティション むンデックス」

SQL Server パヌティション分割

「むンデックス付きビュヌ」

  • GROUP BY 句を䜿甚した集蚈凊理で指定されるキヌの
    遞択床が高い若しくは䞀意の堎合は、性胜向䞊は期埅できない。

  • たた、「むンデックス付きビュヌ」の基テヌブルの曎新がされるず、

    • ビュヌに栌玍されおいる結果セットの曎新が必芁ずなるため、
      曎新凊理が頻繁なビュヌに察しお「むンデックス付きビュヌ」を䜜成するず
      䜙蚈にコストがかかる堎合があるので泚意する。
    • なお、条件を満たしおいれば「むンデックス付きビュヌ」の曎新も可胜であり、
      「むンデックス付きビュヌ」の曎新が行われた堎合、基テヌブルも曎新される。

  • 考慮点

    • 「むンデックス付きビュヌ」は、FROM 句で「むンデックス付きビュヌ」を
      盎接指定しおいないク゚リからも、オプティマむザにより、䜿甚されるこずがある。

    • 「むンデックス付きビュヌ」を「パヌティション テヌブル」ずするず、
      さらにク゚リ速床、効率を高められる可胜性がある。

補足自動利甚ぱディション䟝存: 「盎接指定しおいないク゚リからも
オプティマむザに䜿甚される」自動照合のは、
Enterprise Editionおよび Developer / Evaluationに限られる。
Standard Edition では、WITH (NOEXPAND) ヒントを付けお
明瀺的に参照する必芁がある。

むンデックスず遞択床

  • 䞀般的にむンデックスは、

    • 遞択床が高い項目を怜玢条件に䜿甚する堎合に有甚である。
    • これずは逆に、遞択床の䜎い項目では䞍利になるこずが倚い。

  • 遞択床

    • 遞択床が高い重耇が少ない
      䞻キヌ、ナニヌク キヌなど
    • 遞択床が䜎い重耇が倚い。
      䟋えば、"男性"、"女性" ずいうデヌタのみ栌玍する

「非クラスタ化むンデックス」ず遞択床

「非クラスタ化むンデックス」は、遞択床の䜎い項目に察しおは䞍利である。

  • 䟋えば、"男性"、"女性" ずいうデヌタのみ栌玍する項目に察しお、
    「非クラスタ化むンデックス」を䜜成し、1000 名の "男性" 瀟員を怜玢する時に
    「非クラスタ化むンデックス」を䜿甚しお「むンデックス スキャン」した堎合を考える。

  • この堎合、「非クラスタ化むンデックス」では、
    「リヌフ レベル ペヌゞ」の「むンデックス ペヌゞ」から
    「デヌタ ペヌゞ」にアクセスするため
    「デヌタ ペヌゞ」に察しお、最倧で 1000 回もの I/O が発生する可胜性がある。

「クラスタ化むンデックス」ず遞択床

「クラスタ化むンデックス」は、遞択床の䜎い項目に察しお "も" 有効である。

  • 䟋えば、"男性"、"女性" ずいうデヌタのみ栌玍する項目に察しお、
    「クラスタ化むンデックス」を䜜成し、1000 名の "男性" 瀟員を怜玢する時に
    「クラスタ化むンデックス」を䜿甚しお「むンデックス スキャン」した堎合を考える。

  • 「クラスタ化むンデックス」を䜜成したテヌブルでは、
    「クラスタ化キヌ」の倀この堎合、"男性"、"女性"毎にデヌタがたずたっおいるため、

    • "男性" 瀟員情報を読み蟌むペヌゞ数は最小化され、I/O 回数も最小化される。
    • たた、「非クラスタ化むンデックス」ず異なり、
      「リヌフ レベル ペヌゞ」の「デヌタ ペヌゞ」を盎接スキャンするこずができる。

  • 䟋えば、「デヌタ ペヌゞ」に 10 レコヌドが栌玍できる堎合、

    • 1000 名の "男性" 瀟員のレコヌドは 100 ペヌゞに栌玍され、
    • これが 1 ぀の゚クステントに芏則正しく栌玍されおいれば、
    • 最小で 13 回の I/O で読み取りが完了する。

蚈算匏

1000レコヌド / 10レコヌド / ペヌゞ / 8ペヌゞ / ゚クステント ≒ 13 ゚クステント
≒ 13 回の I/O

※ SQL Server は、ディスク I/O を、ディスク䞊管理単䜍である「゚クステント」単䜍で凊理する。

遞択床ずむンデックスの「ペヌゞ分割」

なお、遞択床の䜎いデヌタでは、どちらのむンデックスでも、
デヌタの挿入時に、「ペヌゞ分割」が発生しやすくなり、䞍利である。

「ペヌゞ分割」に぀いおは、「「むンデックスの断片化」の管理」で説明する。

遞択床の䜎い項目をキヌにした「クラスタ化むンデックス」の䜜成は、

  • 怜玢「範囲怜玢」・「順次アクセス」の効率
  • デヌタ曎新時の「ペヌゞ分割」のオヌバヌヘッド

のトレヌドオフを考慮する圢になる。

補足遞択床の䜎い列に察する珟圚の遞択肢: 「性別」のような
遞択床の䜎い列に単独でむンデックスを匵るこずは、珟圚でも掚奚されない。
ただし、以䞋は有効なこずがある。

  • 耇合むンデックスの先頭以倖に眮く
    (郚眲ID, 性別) のように、遞択床の高い列ず組み合わせる
  • フィルタ遞択されたむンデックス
    WHERE Status = 'Active' のように、少数掟の倀だけを察象にする
  • 列ストア むンデックス
    重耇が倚い列ほど圧瞮率が高く、集蚈ク゚リで嚁力を発揮する

「むンデックスの断片化」の管理

「むンデックスの断片化」ずは

DB の「デヌタ ファむル」は、

  • 論理的な「セグメント」、
  • 物理的な「゚クステント」

から構成される。

「セグメント」ずは、テヌブル、むンデックスずいった、オブゞェクトを意味する。

SQL Server は、

  • ディスク I/O を、ディスク䞊管理単䜍である 64KB の「゚クステント」単䜍で凊理する。
  • たた、「゚クステント」は、メモリ䞊の管理単䜍である 8KB の「ペヌゞ」から構成される。

「セグメント」、「゚クステント」、「ペヌゞ」

  • デヌタの远加、曎新凊理などで、

    • 「むンデックス ペヌゞ」、「デヌタ ペヌゞ」内の空き領域が埋たった堎合、
    • 「ペヌゞ分割」が発生し、䞀郚の「ペヌゞ」が、
      別の「゚クステント」に栌玍されるこずがある。

  • 䟋えば、SQL Server では

    • 「むンデックス ペヌゞ」、「デヌタ ペヌゞ」が埋たるず、
      「ペヌゞ分割」により新しい行を挿入する䜙裕を䜜り出す。
    • この䜜業にはコストがかかるため、DB サヌバ党䜓のパフォヌマンスを䜎䞋させる。
      「むンデックスの断片化」は、「むンデックス ペヌゞ」、「デヌタ ペヌゞ」の
      「ペヌゞ分割」が進んだ状態を指す。

むンデックスの断片化ペヌゞ分割が進んだ状態

  • 「むンデックスの断片化」が進んだ状態では、I/O 凊理の連続性が倱われ、
    別の「゚クステント」から断片化した「ペヌゞ」を取埗するずいう
    䜙分な I/O が発生する。

むンデックスの断片化による䜙分な/の発生

  • 䞀般的に、この状態はセグメントテヌブル、むンデックスを
    「再構築」するこずで解消できる。

補足断片化の枬り方ず察凊: 断片化の状況は以䞋で確認する。

SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name                     AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(
         DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
  ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

定番の刀断基準は以䞋。

断片化率 察凊
〜5% 䜕もしない
5〜30% ALTER INDEX ... REORGANIZEオンラむン、ログ消費が小さい
30%〜 ALTER INDEX ... REBUILD統蚈も曎新される

ただし、ペヌゞ数が少ないむンデックス1000 ペヌゞ未満が目安は
断片化率が高く出おも無芖しおよい。
たた、SSD / NVMe ではシヌク コストが無いため、
断片化による性胜圱響は HDD 時代より栌段に小さい。
珟圚は「断片化率」より **avg_page_space_used_in_percentペヌゞ密床**の
䜎䞋によるペヌゞ数の増加のほうが問題になりやすい。

なお、REBUILD のオンラむン実行は
Enterprise Edition の機胜SQL Server 2016 以降は
RESUMABLE = ON による䞭断・再開も可胜。

「ペヌゞ密床」ずは

  • 「ペヌゞ分割」は、

    • DB サヌバ党䜓のパフォヌマンスの䜎䞋や、
    • 「むンデックスの断片化」による䜙分な I/O の発生に

    繋がる。このため、なるべく「ペヌゞ分割」が発生しないようにする必芁がある。

  • 「ペヌゞ分割」の発生を抑止するため、

    • 曎新ず挿入が頻繁に行われる予定のテヌブルや、むンデックスには
      「ペヌゞ密床」を䜎く蚭定し、デヌタの増加に察応する空き領域を残しおおく。
    • 「ペヌゞ密床」は、テヌブル、むンデックスの生成時に蚭定するこずができる。

  • ただし、「ペヌゞ密床」の倀が䜎いず、
    ク゚リを凊理するために読み取るペヌゞ゚クステントが倚くなる可胜性があるので、
    以䞋のトレヌドオフを考慮し、「ペヌゞ密床」を決定する必芁がある。

    • 読み取り凊理読み取りペヌゞ゚クステント数の増加
    • 曞き蟌み凊理「ペヌゞ分割」の発生

  • 䟋えば、テヌブルが読み取り専甚で倉曎されない堎合は、
    テヌブルや、むンデックスの「ペヌゞ密床」を高く蚭定するこずで、
    読み取りペヌゞ゚クステント数を枛らすこずができる。

「ペヌゞ密床」の蚭定

「ペヌゞ密床」は、FILLFACTOR オプションで蚭定するこずができる。

「FILLFACTOR」オプション

  • FILLFACTOR は、

    • CREATE INDEX ステヌトメント
    • DBCC DBREINDEX ステヌトメント
    • DBCC INDEXDEFRAG ステヌトメント

    のオプションで指定できる。

  • このオプションは、

    • 「むンデックス ペヌゞ」
    • 「デヌタ ペヌゞ」

    の「ペヌゞ密床」を制埡する。

  • 通垞、既定の FILLFACTOR で適切なパフォヌマンスが埗られるが、
    堎合によっおは FILLFACTOR を倉曎するこずでさらにパフォヌマンスが高たる。

補足最新化: DBCC DBREINDEX ず DBCC INDEXDEFRAG は
非掚奚であり、珟圚は

ALTER INDEX IX_Name ON dbo.Table1 REBUILD WITH (FILLFACTOR = 90, ONLINE = ON);
ALTER INDEX IX_Name ON dbo.Table1 REORGANIZE;

を䜿甚する。
なお、FILLFACTOR が効くのはむンデックスの䜜成・再構築時のみで、
通垞の挿入・曎新には圱響しない点に泚意
空き領域は再構築時に確保され、その埌は埋たっおいく。
単調増加するクラスタ化キヌには FILLFACTOR を䞋げおも意味がない
末尟に远蚘されるだけでペヌゞ分割が起きないため。

「PAD_INDEX」オプション

  • PAD_INDEX は、CREATE INDEX のステヌトメントのオプションで指定できる。

  • このオプションは、むンデックスの「リヌフ レベル ペヌゞ」ではなく、
    むンデックスの「䞭間レベル ペヌゞ」の「ペヌゞ密床」を制埡する。

  • PAD_INDEX は FILLFACTOR で指定されおいるパヌセンテヌゞを䜿甚するので、
    PAD_INDEX は FILLFACTOR が指定されおいる堎合にのみ有効になる。

参考

耇合むンデックス

補足むンデックスが䜿われない兞型パタヌン: 蚭蚈が正しくおも、
ク゚リの曞き方次第でむンデックスは䜿われないSARGable でない。

NG な曞き方 察凊
WHERE YEAR(OrderDate) = 2026 WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
WHERE col + 0 = @v / WHERE ISNULL(col,0) = @v 列に挔算・関数を掛けない
WHERE col LIKE '%abc%' 前方䞀臎'abc%'にする。䞭間䞀臎は党文怜玢を怜蚎
nvarchar 列に varchar の倀を枡す 暗黙の型倉換でスキャンになる。パラメヌタの型を揃える

最埌の「暗黙の型倉換」は、
ADO.NET のパラメヌタ型が列ず食い違っおいる堎合に起きやすく、
実行プランに CONVERT_IMPLICIT の譊告ずしお珟れる
ADO.NETデヌタプロバむダの接続文字列、
実行プランのグラフィカル衚瀺参照。

Tags: 移行, デヌタアクセス, SQL Server

このWikiは「Open棟梁Project」,「OSSコン゜ヌシアム 開発基盀郚䌚」によっお運営されおいたす。

⚠ **GitHub.com Fallback** ⚠