SQL Server で OPTION(RECOMPILE) を脳死で付けて遅くなった話

みなさんこんにちは！ヒロポンです！

SQL Serverで「このクエリ、なんか遅いな〜」って思った時。とりあえずOPTION(RECOMPILE)付けて様子見した経験ないですか？？ありますよね。多分あるはず。

私もやりました。物流系の基幹SELECTに脳死で付けたら、もともと30秒だったSELECTがなんと1分に。

体感だと2倍。でもバッチ全体に効いてくると、数十分の遅延になります。

まじでやらかした

RECOMPILEを「付けときゃ速くなるおまじない」だと思い込んでる人が、一番踏みやすい罠なんですよね。

💡 SQL Serverの実行計画の読み方そのものは別記事 SQL Server実行計画の読み方— Estimated vs Actualで業務SEが最初に見る5箇所でまとめてます。今回の記事はその中でRECOMPILEを「使うべき場面」と「付けたらアカン場面」を切り分ける話に絞ってます。

忙しい人向けに最初にまとめ

• OPTION(RECOMPILE)を全SELECTに付けるのは逆効果。プランキャッシュが死ぬ
• 効くのは「パラメータ依存性高」×「実行頻度低」の組み合わせだけ(詳細は記事中の比較表PNG)
• OLTPの高頻度SELECTに付けるとCPUが上がる。私の現場では30秒→1分に悪化した
• 動作確認はAzure SQL Edge (SQL Server 2019互換)で再現できる。ターミナルPNG 2枚で実行プランも見せる

動作確認メモ:今回のサンプルSQLはAzure SQL Edge (ARM64・SQL Server 2019互換)でクエリ実行とSET SHOWPLAN_TEXT ONの出力まで実機確認しています。業務系本番のSQL Server 2016 / 2019でも文法的には同じ挙動です。ただし「秒間50発の高頻度負荷下でのCPU実測」は今回の検証範囲外なので、そこはSQLQueryStressなどのツールで別途検証をおすすめします。

結論: RECOMPILEは3条件を満たす時だけ付ける

最初に身も蓋もない結論から。

OPTION(RECOMPILE)を付けて意味があるのは、以下3つ全部が当てはまる時だけ！！！

1. パラメータ依存性が高い — @status = '完了'と@status = 'エラー'で、実行計画がガラッと変わる
2. 実行頻度が低い —夜間バッチや日次集計など、1日1回〜数回しか叩かれない
3. 統計情報が乱れる時期にある —大量INSERT / DELETE直後、統計自動更新の閾値手前

ぶっちゃけ、業務系のSQLでこの3条件が全部揃うクエリってそんなにないです。はい。

なので「とりあえず付けとけ」は失敗確率の方が高い。

OLTP (1秒に何十回も叩かれる小さいSELECT)に付けたらほぼ悪化します。

RECOMPILEは毎回コンパイル時間を積み上げるやつなので、本来プランキャッシュで使い回せるはずの実行計画が完全に無効化されるんですよね。

3条件を「バッチ寄り/ OLTP寄り/高頻度OLTP」のシチュエーション軸でまとめるとこんな感じになります。

「本命用途」行を見ると一目瞭然ですね。

RECOMPILEは夜間バッチや日次集計の時だけ味方になります。

アプリ全画面で叩く主キーSeek、ましてや秒間50発の高頻度クエリには、付けたら完全に悪手です。

なぜ「とりあえずRECOMPILE」が逆効果になるのか

ここでSQL Serverの中身を1段だけ覗いてみます。

SQL ServerはSELECTを投げるとまず実行プランを作ります。

「このテーブル先に絞って、次にこのインデックスを使って」という設計図ですね。

これを作る作業が、地味に重い。体感で数ms〜数十ms。

なので一度作ったプランはプランキャッシュに保存して、次回同じクエリが来たら使い回します。

これが効くからこそ、OLTPの小さいSELECTが秒間100発でも回るわけです。

ここにOPTION(RECOMPILE)を入れるとどうなるか。

毎回プランを作り直し。キャッシュは使われません。

これが罠の正体。

-- これは罠
SELECT * FROM Sessions WHERE SessionId = @sid OPTION (RECOMPILE);
-- ↑ 1秒に100回叩かれる SELECT に付けると、コンパイル時間が積み重なって CPU が爆上がり

「いやでもパラメータスニッフィング対策で要るんでしょ？？」って疑問が出てくると思うんですが、そこが条件次第。値によって実行計画が劇的に変わるクエリにだけ意味があります。

SessionId = @sidみたいに主キー一発引きの場合、どの値でも同じIndex Seek。実行計画はキャッシュで使い回せばいいだけです。

ぶっちゃけここにRECOMPILEは要らない。

逆にStatus = @statusで@status = '完了'だと1000万件/ @status = 'エラー'だと10件、みたいなクエリは値依存が強い。

完了用にできたプランをエラー用に使い回すとIndex Scanのまま小件数を舐めて遅くなる。

こういう時こそRECOMPILEの出番です。

3パターンのコード比較(動作確認つき)

実際にAzure SQL Edgeで動かして、ターミナルログを取りました。

パターン1: RECOMPILE不要(主キー一発引き・プランキャッシュで十分)

SELECT OrderId, CustomerId, TotalAmount
  FROM Orders
 WHERE OrderId = @orderId;

実行結果(Azure SQL Edge・SHOWPLAN_TEXT):

主キー検索なのでどの値でも実行プランは同じIndex Seek。

これにRECOMPILE付けてもコンパイル時間が積み増しになるだけで性能は1ミリも上がりません。

むしろ落ちる。これもやらかしの原因。

パターン2: RECOMPILEが効く(パラメータ依存性高)

SELECT OrderId, CustomerId, OrderDate
  FROM Orders
 WHERE Status = @status
 OPTION (RECOMPILE);

実行結果(Status列Indexなし→ Clustered Index Scanで行数が値依存):

@statusの値で実行計画を変えたいケース。完了データ(大量)はClustered Index Scan、エラーデータ(少量)はNonClustered Index Seek、みたいに切り替えてほしい時ですね。

ここで効きます。

ただしこのクエリの実行頻度が日に数回レベルであることが大前提。

OLTPで毎秒叩かれるならRECOMPILEじゃなくて後述のクエリヒント(OPTIMIZE FOR)を検討した方が安全です。

パターン3: RECOMPILE逆効果(高頻度+パラメータ依存なし)

SELECT SessionId, UserId, LastAccess
  FROM Sessions
 WHERE SessionId = @sid
 OPTION (RECOMPILE);

実行結果(RECOMPILE付けてもPattern 1と同じClustered Index Seekプラン):

これはもう最悪パターン。

主キー検索(値依存なし)をアプリの全画面で叩く(高頻度)クエリにRECOMPILE付けてしまった例です。

SHOWPLANを見るとPattern 1と完全に同じプランが出ます。

RECOMPILEしても実行計画は変わらず毎回コンパイル時間だけが純粋に上乗せされる構図。

これやったらマジ最悪。

これ、まさに私が物流系の基幹でやらかしたやつなんですよ。

元のテーブルが2億レコード、SELECTは主キーSeekで本来は数ms。。。。。そこに「念のため」でRECOMPILE付けたら毎回プラン生成のオーバーヘッドが積み上がって、バッチ全体で30秒→1分の悪化。気付くのに半日かかりました。

ハマりポイント:多用するとCPUが天井に張り付く

「3条件揃った時だけ使え」という話はここまでで済んだんですが、あなたのためにもう1個だけ罠を共有しておきます。特別に

RECOMPILEはCPU使うパスです。

SQL Serverのsys.dm_exec_query_statsを見ると、total_worker_time (CPU時間)がコンパイル時間込みで計上されます。

RECOMPILE付けまくると、SQL ServerのプロセスCPUが見た目に上がる構図。

これがマジで厄介で、監視でSQL Server CPUが80%張り付き始めて「クエリ重くなった？？」ってなった時真犯人はRECOMPILEのばら撒きだった、ってパターンを現場で見ました。

同業から「これでチーム全員ハマった」って聞いたこともあるので業務系ではそこそこ踏まれてる罠っぽいです。

OPTION(RECOMPILE)を入れる前に、まず以下を確認するクセを付けるといい感じです。

チェック項目	確認方法
パラメータ依存性	同じクエリを値変えて2-3回叩いて、実行時間が10倍以上ブレるか
実行頻度	sys.dm_exec_query_stats.execution_countを1時間sample
統計情報の鮮度	STATS_DATE(object_id, stats_id)で最終更新を確認
既存プランの妥当性	sys.dm_exec_cached_plansでプランをダンプして眺める

これ全部見て「やっぱりRECOMPILE要るな」となるクエリは体感で全クエリの1-2%くらい。

残り98%は別の手段で速くなります。

逆に言うと、98%のクエリはRECOMPILE抜きでもいい感じに速くできるってことなんですよね！！

私の物流系基幹での失敗談

ここは現場メモなので、一段カジュアルに書きますね

数年前物流系の基幹で「ピッキングリスト発行SELECTが遅い」という障害票が上がってきました。

商品マスタが2億レコード、在庫テーブルが日次で大量INSERT / DELETEされる構成。朝のピーク時間帯にSELECTが30秒かかる、と。

私の最初の判断は「パラメータスニッフィングっぽい」。統計情報乱れてるしね、と思った。

なのでOPTION(RECOMPILE)を全SELECTに追加。

「これでスニッフィング回避できる、完了！よっしゃーー飲みに行こう！」ってなってました。

結果30秒→1分に悪化。

ピーク時間帯にバッチが詰まってピッキング業務全体が止まりかけてた。

朝礼までに戻さないとアカン。。飲みに行く予定が。。。という時間制約で背中に冷や汗。

何が起きてたかというと:

• 該当SELECTは実は主キーSeek主体で、パラメータ依存性が低かった
• でも実行頻度がめちゃくちゃ高い (ピッキング画面の裏で秒間50発)
• RECOMPILEで毎回コンパイル時間が積み上がり、CPUが天井に張り付いた
• プランキャッシュが使えなくなったので、元の速さに戻る道が消えた

慌ててRECOMPILE全部外して、統計情報をUPDATE STATISTICSで手動更新したら8秒に落ち着きました。マジで良かった。

元の30秒よりは速くなったので結果オーライですが復旧までに半日溶かしたのと業務側に頭下げに行った時間で信頼回復にもう半日。

技術的な復旧時間より、そっちのほうがしんどかったです。

今だったら普通に統計情報更新で済む話やん？？って思いますよ？

でもね。。当時は「RECOMPILE =スニッフィング対策」という単純な式しか頭になかったんですよね。

教訓は1個！！！！！！

困ったときの脳死RECOMPILEはやめる！！！！。

そしてプランキャッシュと実行プラン(Estimated vs Actual)を先に読む。

まとめ

OPTION(RECOMPILE)は「付けときゃ速くなる」おまじないじゃないという話でした。

業務系のOLTPに脳死で撒くとCPUを焼く側に倒れます。

使い分けの軸はシンプルです。

• パラメータ依存性が高い+実行頻度が低い+統計情報が乱れがち →付けてOK
• どれか1つでも欠けてる →別の手段(OPTIMIZE FOR /統計情報更新/インデックス再設計)を先に検討

困ったら実行計画を読みに行くってのが結局いちばん近道。SQL Server実行計画の読み方— Estimated vs Actualで業務SEが最初に見る5箇所で書いた「EstimatedとActualのズレ」を見ると、スニッフィングしてるかどうかが割と一発で読めます。

よくある質問

Q1. OPTION(RECOMPILE)を全部のSELECTに付けると速くなりますか？

速くなりません。
プランキャッシュが効かなくなるので、同じクエリを何度も呼ぶOLTPでは逆にCPUが上がります。効くのは「パラメータ依存性が高い+実行頻度が低い」クエリだけです。

Q2. RECOMPILEとWITH RECOMPILEの違いは？

OPTION(RECOMPILE)は文単位、ストアド側のWITH RECOMPILEは手続き全体で毎回コンパイルです。

どちらもプランキャッシュを使わない方向に倒す点は同じ。

もっともOPTION(RECOMPILE)の方が影響範囲を絞れるのでピンポイントで効かせたい時はこっち。

Q3.パラメータスニッフィングはどう判定すればいいですか？

実行プランのNULL値や偏ったパラメータと統計ヒストグラムを比べます。

で、同じクエリで値によって実行時間が10倍以上ブレるなら、ほぼスニッフィング起因と見てOK。

SET STATISTICS PROFILE ONで実行時のEstimateRowsとActualRowsの差を見るのが手っ取り早いです。

Q4. RECOMPILE以外にスニッフィング対策はないですか？

あります。主な代替は3つ:

• OPTIMIZE FOR (@param = 'よく使う値') —想定値でプランを固定
• OPTIMIZE FOR UNKNOWN —統計ヒストグラムの平均で見積もる(SQL Server 2008+)
• クエリリライト—値ごとに別SPに分けて、アプリ側で振り分け

OLTPの高頻度クエリには、RECOMPILEよりこの3つを先に検討するのが安全です。

Q5.統計情報が古い時の対処は？

UPDATE STATISTICS [テーブル名] WITH FULLSCANで手動更新が基本。大量INSERT / DELETE直後のバッチでは、RECOMPILEよりこの手動更新を入れた方が安定します。自動更新は閾値が高め(20%)なので、業務系の夜間バッチ前には手動更新を組み込んでおくと吉。

関連記事

• SQL Server実行計画の読み方— Estimated vs Actualで業務SEが最初に見る5箇所— RECOMPILE入れるかの判断は実行計画読みから始まる
• SQL Serverのカーソルを使うべきタイミングと書き方（業務SE現場の判断軸）—「とりあえず付ければ速くなる」系のおまじないは他にもある
• SQL ServerのDBNullをC#で安全にハンドリングする5つのイディオム— C#側からSQL Serverを叩くアプリ層のハマりどころ

以上！

同じ罠でハマってる人いたら、どんどんシェア待ってるぜ！！