❄️

Snowflake「クエリが遅い」を撲滅！パフォーマンスチューニングの体系的アプローチ

に公開
Database
performance
DWH
Snowflake
dataengineering
tech

🚀 はじめに

Snowflakeを運用していて、「特定のクエリが遅い」「BIダッシュボードの表示が詰まる」「バッチ処理が終わらない」といったパフォーマンスの問題に直面したことはありませんか？

Snowflakeは非常に強力なDWHですが、その性能を最大限に引き出すには、発生している問題に応じた適切な「処方箋」が必要です。

この記事は、Snowflakeのパフォーマンス課題を体系的に整理し、「何が原因で」「どの機能を使って」「どう解決すべきか」を、具体的な設計方針とステップバイステップの思考プロセスで徹底的に解説します。

※この記事は、私が調査したりして得た知識を言語化しているので、認識齟齬や内容間違いがあればコメントいただけると幸いです。

🎯 課題の切り分け：あなたの問題は「遅延」か「待機」か？

パフォーマンス・チューニングの第一歩は、問題を正しく特定することです。
Snowflakeの課題は、大きく以下の2つに分類できます。

  1. 単一クエリが遅い（遅延）

    • 症状: 1つのクエリの実行時間が非常に長く、なかなか結果が返ってこない。
    • 原因: クエリ自体が複雑（大規模なJOIN、巨大なテーブルスキャン）である、またはウェアハウスのメモリが不足して「スピル」が発生している。

  2. 多くのクエリが詰まる（待機）

    • 症状: BIツールからのアクセス集中時などに、クエリが実行されず「待機 (Queued)」状態になり、全体の応答性が悪化する。
    • 原因: ウェアハウスの処理能力（キャパシティ）に対して、同時に実行されるクエリやユーザー数が多すぎる。

最も重要なのは、これら2つの問題を混同しないことです。
「待機」が発生しているのにウェアハウスをスケールアップ（サイズ拡大）しても、コストが上がるだけで解決しない可能性があります。逆に、「スピル」で遅いのにスケールアウト（クラスター数増加）しても、クエリは速くなりません。

1. 基礎戦略：ウェアハウス(WH)のサイジング方針

具体的なチューニングの前に、大前提となるウェアハウスの設計思想を定めます。
パフォーマンスとコストのバランスは、この基本方針によって決まります。

方針1：ワークロード分離の原則

「何でもかんでも1つのWHで実行する」のはアンチパターンです。

データベースやスキーマ単位ではなく、実行される処理の特性（ワークロード）に応じてWHを分離してください。

  • ETL/バッチ用WH: 大量データを処理する。実行時間は長いが、同時実行数は少ない。
  • BIツール用WH: 対話的で短時間。実行時間は短いが、同時実行数が非常に多い。
  • アドホック分析用WH: データサイエンティストなどが使う。いつ、どれだけ重いクエリが飛んでくるか予測不能。

これらを分離することで、重いETLバッチ がBIダッシュボードの表示 を邪魔する、といった「うるさい隣人問題」を防ぐことができます。

方針2：適正規模の原則（スモールスタート）

最初から大きなサイズ（Largeなど）でWHを作るのはやめましょう。

全てのWHは最小サイズ（X-Small）から開始し、後述する「監視・分析」のサイクルに基づき、データ（証拠）を持ってサイズアップ（スケールアップ）またはクラスター数の追加（スケールアウト）を行います。

方針3：コスト管理の徹底

Snowflakeは従量課金です。クレジット消費を抑える設定を徹底します。

  • 自動停止 (Auto-Suspend): 全てのWHで有効にします。アイドル時間による不要なクレジット消費を防ぎます。（例: BI用は5分、バッチ用は1分などワークロードに応じ調整）
  • リソースモニター: 想定外のクエリ暴走やコスト発生を検知・制御するために必ず設定します。

2. 実践編①：「詰まる(待機)」を解決するスケールアウト

「多くのクエリが詰まる（待機）」問題の解決策は、スケールアウト（マルチクラスター） です。

これは、ウェアハウスのサイズ（馬力）は変えずに、クラスター数（サーバー台数）を増やすアプローチです。

適用を見極める「症状」

  • QUERY_HISTORY ビューで QUEUED_OVERLOAD_TIME （待機時間）が継続的に発生している。
  • Snowsightのウェアハウス負荷モニターで、「待機中」のクエリが常時発生している。
  • BIダッシュボードやアプリの利用者が増える時間帯に、全体のレスポンスが明らかに悪化する。

実行する「アクション」

  • ウェアハウスの設定で MAX_CLUSTER_COUNT （最大クラスター数）を 1 から 2, 3... と段階的に増やします。

得られる「効果」と「コスト」

  • 効果: スループット（単位時間あたりに捌けるクエリ数）が向上します。
    • 注意点: 単一クエリの実行速度は一切変わりません。
  • コスト: ピーク時に稼働したクラスターの分だけ追加コストが発生します。常に2台分のコストがかかるわけではないため、非常にコスト効率が良い解決策です。

3. 実践編②：「遅い(遅延)」を解決するスケールアップ

「単一クエリが遅い（遅延）」問題の基本的な解決策は、スケールアップ（サイズ拡大） です。

これは、クラスター数（台数）は変えずに、ウェアハウスのサイズ（XS \rightarrow S \rightarrow M \rightarrow L...）を大きくして、1台あたりの馬力（CPU, メモリ, キャッシュ）を増強するアプローチです。

適用を見極める「症状」

  • 最重要指標: 「スピル (Spilling)」の発生。
    • クエリプロファイルを確認し、「ローカル/リモートストレージへのスピル」が多発している場合、それはWHのメモリが足りず、処理途中のデータをディスクに書き出している証拠です。これが遅延の最大の原因です。
  • 複雑なJOINや大規模な集計処理に時間がかかっている。
  • クエリの実行時間（EXECUTION_TIME）が、定義したSLO（サービスレベル目標）を恒常的に超えている。

実行する「アクション」

  • ウェアハウスのサイズを XS から S、M... と段階的に上げます。

得られる「効果」と「コスト」

  • 効果: 単一クエリの実行速度が向上します（特にスピルが解消された場合、劇的に改善します）。
  • コスト: 時間あたりの単価が倍増します（XS \rightarrow S で2倍、S \rightarrow M で2倍...）。

💡 Tips: 「速いほど安い」ケースを知る

「コストが倍増する」と聞くと躊躇しがちですが、「実行時間が半分以下になれば、トータルの消費クレジットはむしろ安くなる」 という逆転現象がSnowflakeでは頻繁に起こります。

以下の事例を見てください。

ウェアハウスサイズ (消費クレジット/h) 処理時間 (分) 1クエリ消費クレジット
XS (1) 90 1.50
S (2) 40 1.33
M (4) 19 1.27
L (8) 9.5 1.27
XL (16) 5 1.33
2XL (32) 4 2.13

表を視覚的にグラフにすると以下のようなイメージ。

このケースでは、XSで90分かかっていた処理（1.50クレジット）が、Lサイズ（8倍の単価）にすると9.5分で完了し、消費クレジットは1.27と、XSより安くなっています。

スピルが発生しているクエリは、WHサイズを上げることで、時間もコストも最適化できる可能性が高いです。

また、以下のSnowflakeのドキュメントに記載されている内容をまとめると、1時間あたりのクレジット消費目安 は以下のようなイメージになると思います。そのためこの表を基にして、ウェアハウスのサイジングのコストを評価するのが良いと考えます。

4. 実践編③：ストレージ設計（クラスタリングキー）

ウェアハウスをスケールアップしてもI/O（スキャン）がボトルネックで遅い場合、次の手はストレージ層のチューニング、すなわちクラスタリングキーの設計です。

これは、テーブルの物理的な配置を特定の列（キー）で並べ替えておくことで、クエリ時のスキャン範囲（マイクロパーティション）を劇的に減らす（プルーニングする）技術です。

方針1：戦略的適用の原則

クラスタリングキーは万能薬ではなく、コストもかかります。

  • 対象: テラバイト級の大規模テーブルに限定します。小規模なテーブルでは効果が薄く、維持コストに見合いません。
  • 要件: 明確な性能改善要件（SLO）が存在するクエリが対象です。

方針2：費用対効果の原則

  • クラスタリングキーは、データのDML（挿入/更新/削除）のたびに自動で並べ替え（自動クラスタリング）を行います。これには維持コスト（クレジット）が発生します。
  • この維持コストと、クエリ実行時間の短縮によるコスト削減（WH稼働時間の短縮）を比較衡量する必要があります。

方針3：キー選定の原則（最重要）

キーの選び方を間違えると、コストだけがかかり、性能は一切改善しません。

  1. クエリパターンに基づく

    • 推測で設計してはいけません。QUERY_HISTORY を分析し、WHERE句でのフィルタリングやJOIN句で最も頻繁に使用される列をキーに選びます。

  2. カーディナリティ（値の種類の数）を考慮する

    • 高すぎる（非推奨）: UUID やナノ秒単位のタイムスタンプなど、値がユニークすぎる列。絞り込みが効きません。
    • 低すぎる（非推奨）: BOOLEAN（真偽値）や性別など、値の種類が少なすぎる列。これも絞り込み効果が薄いです。
    • 適切: DATE 列、CUSTOMER_TYPE 列など、適度な数の種類を持つ列。

  3. キーの順序（複合キーの場合）

    • カーディナリティが最も低い（値の種類が少ない）カラムを先に指定します。
    • 例: (ORDER_DATE, CUSTOMER_TYPE, CUSTOMER_ID)。
    • これにより、まず日付で大まかに絞り込み、次（のマイクロパーティション内）に顧客種別で絞り込む、という効率的なプルーニングが期待できます。

導入と運用のプロセス

  1. 事前評価: SYSTEM$CLUSTERING_INFORMATION や SYSTEM$CLUSTERING_DEPTH で現状を把握し、SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS でコストを見積もります。
  2. 実装: ALTER TABLE ... CLUSTER BY (...) でキーを適用します。
    • 注意: 初回適用時には大量の並べ替え処理（リクラスタリング）が発生し、多くのクレジットを消費する可能性があります。
  3. 監視: AUTOMATIC_CLUSTERING_HISTORY ビューで維持コストを監視します。適用前後でクエリ性能（特にスキャンしたパーティション数）を比較し、効果を定量的に評価します。
  4. 見直し: 費用対効果が薄いと判断された場合は、ALTER TABLE ... DROP CLUSTERING KEY でキーを削除する勇気も必要です。

5. 実践編④：高度な最適化機能（SOS, QAS, MV）

ウェアハウスのサイジングやクラスタリングでも解決が難しい、特定のボトルネックを解消する3つの強力な機能です。これらは追加コストが発生するため、費用対効果を慎重に見極める必要があります。

1. 検索最適化サービス (SOS: Search Optimization)

  • 機能: 大規模テーブルに対し、特定の列の値からデータ所在地（マイクロパーティション）を高速に引くための「検索アクセスパス（索引）」を作成・維持します。
  • 効果: クラスタリングキーが不得意な「点」の検索、すなわち等価検索 (=) や IN句 での検索を劇的に高速化します。
  • 適用基準:
    • テーブルサイズが数百GB以上と大きい。
    • 特定のダッシュボードやアプリで、IDやコードなど、クラスタリングキーに設定されていない列での等価検索が頻発し、性能問題となっている場合。
  • コスト: 検索アクセスパスのストレージコストと、その維持（DML追随）のためのコンピューティングコスト が別途発生します。

2. クエリ高速化サービス (QAS: Query Acceleration)

  • 機能: ウェアハウスの処理能力を超える一部の巨大な処理（主にスキャンやソート）を、Snowflakeが管理するサーバーレスなコンピュートリソースにオフロード（委譲） します。
  • 効果: ウェアハウス全体を圧迫する一部の「外れ値」的な巨大クエリの実行時間を短縮し、他の通常クエリへの影響を低減します。
  • 適用基準:
    • 多数の通常クエリの中に、時折「外れ値」的な巨大スキャンを伴うクエリが混在し、全体のパフォーマンスに影響を与えている場合。
    • その「外れ値」クエリのためにウェアハウス全体のサイズを恒久的にスケールアップするのは費用対効果が悪い場合に、最適な選択肢となります。
  • コスト: ウェアハウスのクレジットとは別に、オフロードで利用された分だけ従量課金が発生します。

3. マテリアライズドビュー (MV: Materialized View)

  • 機能: 複雑な集計や結合を伴うクエリの結果を、実体を持つテーブルとして事前に計算・保持しておく機能です。
  • 効果: BIダッシュボードなどで利用される定型的な集計クエリのレスポンスが、ほぼゼロになるほど高速化します。
  • 適用基準:
    • 特定（かつ高コスト）の集計クエリが繰り返し実行されており、それが性能上のボトルネックになっている場合。
    • 元となるベーステーブルの更新頻度が、クエリの実行頻度よりも低いこと（更新が多いと、MVの維持コストが高騰するため）。
  • コスト: ビューのストレージコストと、データの鮮度を保つための維持（リフレッシュ）のためのコンピューティングコストが別途発生します。

😌 おわりに

Snowflakeのパフォーマンスチューニングは、「魔法のボタン」を押して完了するものではありません。
本記事で解説したように、まずは問題を正しく診断することから始まります。

  1. 課題の切り分け: 「遅延（スピル）」か「待機（キュー）」かを見極める。
  2. WHチューニング: スピルならスケールアップ、キューならスケールアウト を検討する。
  3. ストレージチューニング: TB級テーブルのスキャンが遅ければクラスタリングキーを設計する。
  4. 高度な機能: 特定の「点検索」ならSOS、「外れ値クエリ」ならQAS、「繰り返し集計」ならMV を検討する。

推測でサイジングするのをやめ、QUERY_HISTORY やクエリプロファイルを監視し、データに基づいて最適なパフォーマンス改善サイクルを実践してみましょう。

※改めてになりますが、この記事は私が調査したりして得た知識を言語化しているので、認識齟齬や内容間違いがあればコメントいただけると幸いです。

Discussion