SQL Server/インデックス

• SQL Server のインデックス設計

インデックスの種類 †

• SQLServerのインデックスについてざっくりとまとめてみた
• インデックスの主なオプション
• 【SQLServer】パフォーマンスを意識したSQL

クラスタ化／非クラスタ化インデックス †

• クラスタ化インデックスは主キーにつけないほうがいいかも

複合キーのインデックス †

• カーディナリティを意識する
• カーディナリティが高いカラムから順に指定していくのが良い
• カーディナリティが低いカラムが先頭にあると、インデックスのB-Treeが適切な形にならず、同時アクセス時に更新待ち（LCK_M_U）が発生する
  • 実際、同時アクセス時に更新待ち（LCK_M_U）が多発していたテーブルの復号キーのカラム順を、カーディナリティが高い順に入れ替えたら発生しなくなった

フィルタ選択されたインデックス †

• SQL Server のチューニングについてまとめてみる - その2 - ( フィルタ選択されたインデックス )

• 例えば、テーブルのあるフィールドの内容が大部分 NULL の場合、そのフィールドの値が NULL ではないレコードだけを含むインデックスを作成することが可能
• フィルター条件は Where 句に指定するような感じ
• フィルタ選択されたインデックスを使用することで、ある条件に合致した効率的な検索を行うことができる
• インデックスに格納される物理的な行数も減少するため、I / O 負荷が減り、高速化につながる

断片化 †

• インデックスの再構成と再構築
• SQL Serverにおけるインデックスの再構成と再構築の性能比較
• 断片化の状態によってインデックス (index) を再構成 (Reorganize) または再構築 (Rebuild) する方法
• インデックスの断片化率を調べる方法
• SQL Server INDEXの断片化の解消
• SQL Server の断片化発生の 2 種類のパターン
• SQLServerは、断片化率が高くなると、SQLが実行される際、パラレル処理されなくなり、極端に処理性能が低下します

• どうする？ SQL Server のクエリ パフォーマンスが低下した！
• [SQL Troubleshooting] SQL Server トラブルシューティング 6 回シリーズのご案内

断片化調査 †

--インデックスと断片化率の一覧を取得する
SELECT 'ALTER INDEX ' + '[' + C.name + ']' + ' ON [' + D.name + '].[' + B.name + '] REBUILD' AS 'rebuild command'
      ,D.name AS schemaname
      ,B.name AS table_name
      ,C.name AS index_name
      ,A.avg_fragmentation_in_percent
      ,A.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(),null,null,null,null) AS A 

LEFT OUTER JOIN  sys.objects AS B 
ON  A.object_id = B.object_id 

LEFT OUTER JOIN  sys.indexes AS C 
ON  A.object_id = C.object_id  AND A.index_id = C.index_id 

LEFT OUTER JOIN  sys.schemas AS D 
ON  B.schema_id = D.schema_id 

WHERE B.type = 'U'
AND   C.index_id > 0 

--断片化率が30%以上のものを抽出する
AND   A.avg_fragmentation_in_percent > 30 

ORDER BY A.avg_fragmentation_in_percent DESC; 

断片化への対処 †

• 断片化の比率が30%以下なら再構成、30%超なら再構築がセオリー
• 5%に満たない場合は対処不要

再構築と再構成 †

• インデックス再構築と再構成の違い

• 再構築と再構成では、実行中のインデックスの使用可否や実行後のインデックスの状態が異なる
• 処理に割り当てられる時間が限られていて、処理を途中でキャンセルしなければならない可能性があるのであれば、再構築よりも再編成の方が、キャンセルした場合にもすべてが無駄にならず、毎日時間の許される範囲で実行するという方法を取ることができる。反対に、再構築に必要となる時間は確保できるので、実行時間にばらつきが出るよりもある程度一定の時間で完了することが優先されるのであれば、再構成よりも再構築の方が有利。

再構築 †

alter index [インデックス名] on [テーブル名] rebuild

• 1つのトランザクションでインデックスを完全に再作成
• 再構築は1つのトランザクションなので、キャンセルすれば当然そのトランザクションはロールバックされ、トランザクション開始前の状態、つまり、断片化した状態に戻される
• 再構築はインデックスを作り直すので、インデックス行数 (テーブルの行数) が増えない限り、大きく処理時間が変わることはない
• 再構築の場合、再作成中も元のインデックスを削除することはできませんので、一時的には、断片化した古いインデックスと再作成した新しいインデックスがふたつ存在する状況となり、それらを格納するための容量がデータファイルに必要になる。さらに既定ではソート用の領域も必要になる。

再構成 †

alter index [インデックス名] on [テーブル名] reorganize

• 処理単位ごと (処理単位はページ) にトランザクションが分割されていて、リーフページ間でインデックス行を移動させることで行を前に詰めて断片化を解消する
• 再構成はトランザクションが分割されているため、キャンセルしても、既に完了しているトランザクションの結果は維持される
• 再構成は、前のページに空きがある場合に後ろのページから前のページへ行を移動するという処理であるため、移動する行が多い、つまり、断片化の度合が大きいほど処理時間が長くなる
• 再構成の場合は、ページ間で行を移動するだけなので、そのような追加の領域は基本的には必要としない

• インデックスの再構成は、行の移動を行っているページに対して排他ロックを獲得する。これは非常に短い期間のみ保持されるロックであるため、その他の処理が長期間ブロックされることはない。ただし、その他の処理が獲得しようとするロックによっては、デッドロックとなる可能性がある。