SQL Serverでテーブルパーティション化して実行計画の前後比較

はじめに

数億レコードが登録されたテーブルをパーティショニング化し、

性能の改善が見込めたのでパーティショニング前後での比較結果をまとめました。

僕が開発リーダーをしているプロダクトでは、

大きいテーブルで4億レコードほど登録した状態でのオンライン処理(検索処理)や、

300万レコードを3~4時間以内に集計するバッチ処理を行います。

それにも関わらず、、、

データファイルの設計など行わずに

現在primary１ファイルに全部データが入っていたり、

大きいテーブルのパーティショニング化の検討等もできないまま半年間運用してきました。

現状、インデックスの貼り方を工夫したり、

オンラインの検索仕様をシンプルにすることででなんとか性能担保していますが、

今後取り扱うデータ量はまだまだ増えそうなのと、どれくらい増加するのか予測が難しいです。

そこで、データ量が増加した際の対策として実行計画を分析をしながらいくつか検証してみました。

その中の１つとして、

テーブルパーティショニング化の検証を行ったので結果をまとめました。

SQL Serverでテーブルをパーティション化して前後比較結果

この記事ではパーティショニングテーブルとは？？メリットは？？などについての説明は省略しますが、

この辺りが参考になるので、興味のある方は参照ください。

＞＞SQL Server のデータパーティションについて

＞＞パーティション テーブルとパーティション インデックス

ここから本題ですが、

まず、

今回はこんなイメージで数億規模のトランザクションテーブルをパーティション化しました。

上のようにパーティション化し、

このパーティションテーブルに、

パーティション化する前と同じクエリを実行した際の比較結果はこのようになりました。

パーティション化する前からクエリのチューニングはほぼ完璧に行っていたので、

クエリタイムだけ見るとそんなに改善されていないようにみえますが、、、

そこは気にしないでください。

ちなみにチューニングして、

SQLの実行計画を比較する際は上のようにSSMSの比較機能を使うのがオススメです。

クエリプランを開いて右クリック＞プラン表示の比較

SQL Serverでテーブルのパーティション化前後の比較結果からわかること

Index SeekオペレーションでのCPUの推定コスト、I/O推定コストが半分になったことから、

同じクエリを実行して同じIndexを利用して、

同じデータを特定するまでのデータアクセス経路が最適化(IO回数削減)されたことが分かります。

こんなイメージです。

元々１つの大きなB-Treeインデックスを辿ってデータを特定していたのを・・・

パーティション化されたB-Treeインデックスの該当パーティションを辿るだけになりました。

そのため、

探索するツリーが分割されて深さが浅くなっているため探索のコストが減りました。

また、

最後のSELECTオペレーションでサブツリーの推定コストが半分になったことから、

Index Seekオペレーションだけでなく

クエリ全体で見てもクエリ実行にかかるコストが半分になったことが分かります。

SQL Serverでテーブルのパーティション化前後の実行計画からわかること

実行計画のコストを見ると、

Index Seekのコストはクエリ全体で見たときに3%です。

本来ならここをいくら最適化したところで性能改善にはならないです。。。。

(今回はパーティション化の検証が目的ですが、

本来はこの実行計画を見たらコストの高いsortオペレーションを最適化すべきです。)

また、

Index Seekオペレーションの「述語」があります。

これは、Index Seekした後にIndex Scanしているということです。

こんなイメージ。

もしこれがIndex Seekだけで辿れるようにできるようになれば

さらに性能がさらに改善されるかもしれません。

ちょっと反省

ちなみに、、

これはプロダクトの画面仕様において性能観点での工夫が足りていなかったところですが、

このIndex Scanのkeyになっている項目は画面で検索対象の金額のレンジを設定するものです。

「0円から9999999円の取引データを検索対象に絞り込む。」

みたいなイメージ

しかも、画面仕様として初期値で金額スパン0円から9999999円に設定されます

(where句に金額スパンを条件指定する必要がないのに毎回指定している)。

それによって、

ユーザは実際にスパンを絞り込む必要がなくても

無意識にこの設定で検索していることがほとんど。。。

初期値は金額スパン設定なしで、絞り込みたい時だけ入力させるような画面仕様にしておけば、

インデックスを付与するカラムからこの項目はホントなら外せて、

Index Seekの後にIndex Scanするようなこともなかったと反省しています。

前提：よく使われる検索条件だけクエリの性能を最適化させ、

オンラインアプリに必要なインデックス作成を最低限にすることで、

夜間大量データバッチ処理での登録更新処理性能を優先させる方針

まとめ

SQL Serverでテーブルをパーティション化して、

前後比較してみての結果をまとめました。

今回はクエリを爆速にすることはできませんでしたが、

今後システムを運用していってデータが溜まってきて

今まで通りIndex Seekしても遅くなった場合でも以下対応で改善の余地があることが分かりました。

１点だけ注意点としては、

パーティション化することでパーティションを跨ぐような検索にはオーバーヘッドかかってくるので、

分割列の選定は

該当テーブル検索時、

良く条件指定に利用され、なおかつパーティションを跨がないで済むような列

を選定することに注意してください。

またパーティション化のメリットに軽く触れておくと、

テーブルロックやインデックスのリビルドのメンテナンスを

パーティション単位でかけれたりなどなど。

(逆にデメリットは定期的にデータファイル作成して、

パーティション用のfanctionの定義を作ることとかかな)

大量データを保持するテーブルを運用する際に、この記事が参考になれば幸いです。

おまけ

9年間のPG・SE生活の中で、

現場で必須なデータベース周りのまとめ記事を作ったので

是非そちらもご覧ください。

現場で必要なデータベースの基礎知識まとめ｜正規化、ACID特性、SQLチューニングなど