f:id:vasilyjp:20190819164200j:plain

こんにちは。開発部基幹SREチームの廣瀬です。弊社では、システムの一部にSQL Serverを使用しています。

本記事では、SQL Serverにおけるインデックスのメンテナンス方法である再構成と再構築について、それぞれを実行した場合のクエリ性能の比較結果をご紹介したいと思います。

比較を実施するに至った背景の前に、まずはインデックスの再構成と再構築について説明したいと思います。

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

インデックス

SQL Serverのインデックスについて簡単にご紹介します。下図は、SQL Serverのデータ構造の概略図です。 f:id:vasilyjp:20190819163901j:plain

テーブルは、1つ以上のインデックスから構成されます。なお、ヒープという別のデータ構造だけからテーブルを構成することもできますが、今回は省略します。

インデックスは複数のページから構成されます。各ページは論理的につながりを持ちB-Treeを形成します。ページとは、8KBの物理的に連続した領域のことで、各ページに実際のレコードが格納されています。

なお、インデックスの詳細なアーキテクチャについてはSQL Server のインデックスのアーキテクチャとデザインガイドにまとめられています。

インデックスの断片化

SQL Serverのインデックスは、データの更新パターンによっては断片化が発生します。断片化が発生すると、本来であれば1000レコードを10ページに格納できるはずが15ページにまばらに格納される、といった状態になります。

断片化により、データ読み取り時に読み取るべきページ数が増えるため、結果的にCPU負荷増やクエリ実行時間の増加などの性能低下を引き起こす傾向にあります。

「どの程度断片化が進んでいるか」を示す値として、「断片化率」という値があります。こちらはsys.dm_db_index_physical_statsというDMVを使って以下のように取得できます。

declare @DB_ID int
  ,@OBJECT_ID int

set @DB_ID = DB_ID('DB名')
set @OBJECT_ID = OBJECT_ID('テーブル名')

select *
from sys.dm_db_index_physical_stats(@DB_ID, @Object_ID, null, null, 'DETAILED') as A
join sys.objects as B on A.object_id = B.object_id

例えば、約200万レコードのテーブルに対して上記クエリを実行すると、以下のような結果が得られました。 f:id:vasilyjp:20190819170350j:plain

index_level

この例だと0がリーフレベル、1が中間レベル、2がルートです。

avg_fragmentation_in_percent

各レベルにおける断片化率です。高いほど断片化が激しいことを示します。

page_count

各レベルにおけるページ数です。断片化が激しいと、本来必要なページ数よりも多くなります。

avg_page_space_used_in_percent

各レベルにおいて、各ページにどれだけレコードが詰まっているかを示します。100％に近いほど読み取り性能がよくなります。

断片化を解消する手段として、インデックスの再構成と再構築という2つの方法があります。

断片化解消のための再構成と再構築

インデックスの再構成と再構築の違いは、以下の表に分かりやすくまとまっています。 f:id:vasilyjp:20190819163951j:plain 出典：インデックス再構築と再構成の違い

プロダクション環境で気にすべきポイントとして、「同時実行性」に特に注意が必要です。再構成は、実行中に取得するロックの範囲、ロックをかけている時間ともに限定的であるためオンライン操作とみなすことができます。一方、再構築でもEnterprise Editionではオンライン操作が可能ですが、それ以外のエディションの場合オフライン操作となってしまいます。

ここで、オフライン操作とは「その操作を実行中に、他プロセスが同一テーブルに読み書きできなくなる」操作のことを言います。したがってオフライン操作のインデックス再構築に5分間かかる場合、同一テーブルへのSELECTが5分間ブロックされ続けることになります。オンライン操作はその逆で、並行して他プロセスが読み書きできます。

先ほどのテーブルをインデックス再構成してみると、以下のような結果になりました。 f:id:vasilyjp:20190819170411j:plain

リーフレベルの断片化率（avg_fragmentation_in_percent）が約70％から約0％へと変化しています。それに伴ってリーフレベルを構成するページ数（page_count）が40％ほど少なくなっています。この結果は、そのまま「テーブルスキャン時のリーフレベル読み取りページ数が約40％少なくなる」と言い換えることができ、読み取り性能の向上に貢献しそうです。

ここまで、SQL Serverにおけるインデックスの再構成と再構築について紹介しました。次に、本記事を書くモチベーションとなった背景についてご説明します。

背景

再構成と再構築のどちらを定期的に実施すべきか、という疑問がありますが、Microsoftのドキュメントでは以下のガイドラインが紹介されています。 f:id:vasilyjp:20190819164020j:plain

出典：インデックスの再構成と再構築

つまり、断片化率がある一定の閾値を超えた場合は再構築（REBUILD）、そうでない場合は再構成（REORGANIZE）を推奨する、というものです。

ただし、再構築をオンラインで実行するためにはSQL ServerのエディションがEnterpriseでなければいけません。

そのため、Standard Editionを使っている場合などは積極的に再構築を実施することはできません。

インデックス再構築と再構成の違いという記事の中で、「実際の運用を考慮した場合、再構築や再構成前の断片化の状態よりも、再構築や再構成の実行中の状況や実行後のインデックスの状態の方が重要ではないでしょうか？」という問いかけがなされています。

さらに踏み込むと、個人的には「再構築や再構成を実施した結果、性能がどの程度向上するかが重要ではないか」と考えます。

弊社では、リードレプリカDBのインデックスメンテナンスとして、毎日インデックスの「再構成」を実施しています。また、セールなどの高トラフィックなイベント前にはインデックスの「再構築」を実施しています。

リードレプリカDBはStandard Editionであるため、インデックスの再構築はオフライン操作でしか実行できません。そのため、以下の手順でインデックス再構築を実施していました。

WEBサーバーを数グループに分割
1グループをLoadBalancerから外し、アクセスが無い状況にする
アクセスが無い状態でインデックスのオフライン再構築
完了後、グループをLoadBalancerに戻す
2-4を各グループで実施

以下のGIFアニメのイメージです。 f:id:vasilyjp:20190819163831g:plain

この作業は数カ月に一度の頻度で発生する運用で頻度は高くありませんが、作業者の負担が大きい作業でした。

それでも、再構成より再構築のほうが、性能面で優れた結果をもたらすのであれば、継続して実施すべき作業です。

ですが、もし再構成と再構築とでクエリ性能およびサーバー負荷に差が無いことを確認できれば、今後はこの運用を無くすことができます。

以上の背景を踏まえて、インデックスの再構成と再構築の性能比較を実施することになりました。

比較方法

SQL Server 2012 Standard Editionが入った、同一スペックのサーバー2台で比較を実施します。尚、ディスクは「SSD」です。サーバーAでは全テーブルのインデックス「再構成」、サーバーBでは全テーブルのインデックス「再構築」を行い、クエリ性能およびサーバー負荷の観点で比較を実施しました。

比較用のメトリクスにはパフォーマンスカウンタの値を使用しました。

留意点1

冒頭でお話したように、インデックスの再構成と再構築には様々な違いがあります。説明した点以外でも、例えばインデックス再構築の方がテーブル容量を圧縮できるといったメリット等も考えられます。

ただし、今回興味があるのは「どれだけクエリ性能に差が生じるか」という点だけであり、その点に絞って優劣をつけることとします。

留意点2

本当は「インデックスメンテ無し」「インデックス再構成」「インデックス再構築」という3パターンの比較を考えていました。

しかし、プロダクション環境において性能劣化の懸念がある「インデックスメンテ無し」のサーバーを用意することが難しいため、再構成と再構築でのみ比較しました。

比較結果：クエリ実行時間

「SQLServer:Batch Resp Statistics」というメトリクスを使うことで、「実行に1ms-2msかかったクエリが〇個あった」という風に、実行時間ごとのクエリ実行回数の分布情報が取得できます。

これはサーバーが起動してからの累積値のため、2点間の差分を取得することで、例えば特定の1時間におけるクエリ性能を確認することができます。

表にまとめると、以下の結果となりました。

（サーバーA:インデックス再構成 / サーバーB:インデックス再構築）

f:id:vasilyjp:20190819171458j:plain

サーバーAとサーバーBでは、クエリ実行時間の比率の増減は1ポイント未満であり、クエリ実行時間の傾向に大きな変化は発生していませんでした。

そのため、インデックスの再構築によって、今までは10msだったクエリが1msになり大幅に処理時間が変化したというような傾向は発生していないと考えられます。

比較結果：サーバー負荷

インデックスの再構成および再構築によって、テーブルを構成するページ数に差がでてきます。

そのためデータ読み取り時のディスク負荷や、読み取りページ数増加に伴うCPU負荷等に変化がみられる可能性を考慮し、以下のメトリクスを比較しました。オレンジ色がサーバーA（インデックス再構成）のデータを示し、青色がサーバーB（インデックス再構築）のデータを示します。

LogicalDisk\Avg. Disk Queue Length

f:id:vasilyjp:20190819164052j:plain ディスク負荷が高い場合に高い値を示すメトリクスです。横軸は時間、縦軸はディスクキューの数を示します。サーバーAとBで顕著な変化はみられませんでした。

LogicalDisk\Disk Read Bytes/sec

f:id:vasilyjp:20190819164108j:plain データの読み取り量を示すメトリクスです。横軸は時間、縦軸は秒間のディスク読み取り数（単位：Byte）を示します。サーバーB（青色/インデックス再構築）の方が各テーブルを構成するページ数は少ないはずですが、それに伴って読み取りデータ量も減少している、というような傾向はみられませんでした。

Process(sqlserver)\% Processor Time

f:id:vasilyjp:20190819164127j:plain CPU負荷を示すメトリクスです。横軸は時間、縦軸はサーバーのCPU使用率（％）を示します。データの読み取り量が増えるとCPU負荷も増加する傾向にありますが、読み取りデータ量が変わらなかったことから、CPU負荷にも差はみられませんでした。

考察

今回検証した環境については、インデックス再構成と再構築で、性能観点/サーバー負荷の観点での差はほぼ無いという結果になりました。これは以下の点を踏まえると納得できます。

再構成による「リーフページの断片化のみ解消」でも、大きく断片化が解消できているという点
インデックス階層においてページ数が多いのは圧倒的にリーフページが存在する階層であった点

今回の検証では性能面での差が無いという結論となりましたが、ワークロードの性質や、ディスク性能次第では顕著に差がでる可能性もあります。例えば、ディスク性能が低いHDDですと、インデックス再構築の方が性能面で優れるという結果になったかもしれません。関連するメトリクスを採取して比較し、効果を検証することが大事だと思いました。

まとめ

本記事では、インデックスの再構成と再構築の性能差を比較するための評価手法と比較結果をご紹介しました。比較対象のメトリクスは、インデックスの再構成と再構築の挙動の違いを踏まえて、影響を受けそうなメトリクスを選定しました。本記事で紹介した比較方法を使って、他の環境でも性能の優劣を比較していただけると思います。

今回の検証結果では、インデックスの再構成と再構築とで性能およびサーバー負荷の差異はみられませんでした。そのため、今後は該当サーバーにおけるインデックスのメンテナンスは再構成のみとしました。Enterprise Editionの場合は迷わずオンライン操作の再構築でいいかもしれません。しかし該当サーバーはStandard Editionのため、再構築が不要と判断できたことで運用作業を1つ削減できました。

今回は性能面を重視して確認を行いましたが、冒頭で紹介したインデックスの再構築と再構成の処理の違いの表にも記載があるように、性能面以外の違いを気にする場面もあると思います。インデックスのメンテナンス方法（再構成/再構築/オンライン再構築）によって、インデックスメンテナンスの処理時間や使用するトランザクションログのサイズが変わってきます。短時間でインデックスのメンテナンスを完了させるという観点では、メンテナンス方法を使い分ける必要もでてくると思います。