SQL Serverの互換性レベル100 vs 160：Query Storeの視点から再考する

SQL Server Compatibility Level

SQL Serverにおけるデータベースの互換性レベル（Compatibility Level）の変更は、単なる「バージョン互換性」以上の意味を持っています。実際には、クエリの最適化方式、カーディナリティ推定（Cardinality Estimation）、インテリジェントなクエリ処理（Intelligent Query Processing, IQP）機能の有効化にいたるまで、パフォーマンスに直結する重要なアーキテクチャ上の決定です。

特定のクエリが遅くなった際、互換性レベルを下げることで一時的に復旧させることがあります。しかし、これはデータベース全体として最新エンジンが提供する最適化機能を放棄することになります。本記事では、SQL Serverの互換性レベル100と160の違いをQuery Storeの視点から再考し、最適な移行・運用戦略を解説します。

1. 互換性レベル 100 でも Query Store は動作するのか？

よくある誤解の一つに、**「100のような古い互換性レベルではQuery Storeを使用できない」**というものがあります。結論から言うと、これは誤りです。

互換性レベルが100（SQL Server 2008相当）に設定されていても、SQL Server 2016以降のバージョンで動作しているデータベースであれば、Query Storeを有効にして以下のコア機能を活用できます。

パフォーマンスデータの収集: クエリテキスト、実行計画（Execution Plan）、ランタイム統計（平均実行時間、CPU使用量、論理読み取り数など）の収集。
実行計画の比較: 互換性レベル変更の前後、または特定期間における実行計画の低下（Regression）の検知。
実行計画の強制（Plan Forcing）: 特定の query_id に対して、過去の最適な plan_id を強制適用し、クエリパフォーマンスの安定性を担保。

そのため、古い互換性レベルで動作しているからといって、強力な監視ツールであるQuery Storeを諦める必要はありません。

2. 100 vs 160：インテリジェントなクエリ処理（IQP）の壁

しかし、単なるパフォーマンスの収集を超えて、自動的な最適化の領域に進むと、互換性レベルの差が明確に現れます。SQL Server 2022（互換性レベル160）で導入された最新のIntelligent Query Processing機能は、互換性レベル160での動作が前提となっています。

① PSP（Parameter Sensitive Plan）最適化

パラメータ化されたクエリが、すべての引数に対して常に同じ実行計画を再利用することで発生するパフォーマンス問題です。

問題の背景: tenant_id = 1（少数のデータ）に最適化されたIndex Seek計画がキャッシュされ、その後 tenant_id = 999（数千万件のデータ）を実行する際にも再利用されることで、深刻な遅延が発生します（パラメータスニッフィング）。
IQPによる解決: PSP最適化は、パラメータ値の範囲や統計分布に応じて複数の実行計画をキャッシュ内に管理し、動的にマップします。（互換性レベル160が必須）

② カーディナリティ推定のフィードバック（CE Feedback）

オプティマイザは統計情報に基づいて、条件式で返される行数（カーディナリティ）を予測します。この予測が外れると、結合順序（Join Order）、結合方法（Hash vs Nested Loop）、メモリ確保（Memory Grant）のすべてが非効率になります。

IQPによる解決: 実際の実行結果と予測値の差をQuery Storeに記録し、次回のコンパイル時にモデルを修正（フィードバック）します。（互換性レベル160およびQuery Storeの READ_WRITE 状態が必要）

③ DOPフィードバック＆メモリ付与フィードバック

DOPフィードバック: 並行処理クエリのオーバーヘッドを監視し、最適な並列度（Degree of Parallelism）を自動調整します。
メモリ付与フィードバック: ソートやハッシュ処理時に発生するメモリ不足（TempDB Spill）やメモリの過剰確保を、次回の実行計画コンパイル時に動的に補正します。

3. 互換性レベル 100 に固定するリスク

一部のレガシークエリは、古いカーディナリティ推定（Legacy CE）や古い最適化ルール（互換性レベル100）の下でより高速に動作することがあります。しかし、これら少数のクエリのためにデータベース全体の互換性レベルを100に固定してしまうと、以下のような大きな損失が生じます。

最新の最適化機能の完全ブロック: PSP、CEフィードバック、DOPフィードバック、Batch Mode on Rowstoreなどの恩恵をシステム全体で受けられなくなります。
リソース使用の非効率化: 最新のハードウェアやCPUアーキテクチャに最適化されたクエリエンジンの恩恵を失い、クラウドやオンプレミスのインフラコストが増加します。

4. 移行・運用戦略

特定のクエリでパフォーマンス低下を防止しつつ、安全に互換性レベル160へアップグレードするにはどうすべきでしょうか。**「全体をダウングレードするのではなく、全体はアップグレードし、問題のある個別クエリのみを制御する」**のが正しいアプローチです。

graph TD
    A[1. 互換性レベル 100 で運用] --> B[2. Query Store を有効化し、安定した実行計画を収集]
    B --> C[3. データベースの互換性レベルを 160 に変更]
    C --> D{4. クエリのパフォーマンス低下を検知?}
    D -- Yes --> E[5. Query Store で過去の plan_id を強制適用 (Plan Forcing)]
    D -- Yes --> F[6. Query Store ヒントを使い、該当クエリにのみ FORCE_LEGACY_CARDINALITY_ESTIMATION を適用]
    D -- No --> G[7. PSPやCEフィードバックなどの最新IQP機能の恩恵を最大化]

移行プロトコル

事前準備: 対象DBでQuery Storeを事前に有効化し、本番環境のワークロードから安定した実行計画を収集します。
互換性変更: 互換性レベルを160に引き上げます。
パフォーマンストラッキング: Query Storeの「パフォーマンスが低下したクエリ（Regressed Queries）」ダッシュボードを監視します。
個別対処:
- 実行計画の強制: 新しいエンジンで遅くなったクエリに対し、以前収集した安定した実行計画IDを強制適用します。
- Query Storeヒント: 実行計画を固定できない動的SQLなどの場合、Query Storeヒント機能を使用して、特定のクエリIDに対してのみ FORCE_LEGACY_CARDINALITY_ESTIMATION などのレガシーCEヒントを直接付与します。

5. 統計情報設計の重要성

「クエリが遅くなるたびに UPDATE STATISTICS を実行すると速くなる」という現象は、パフォーマンスチューニング上の重要な兆候です。これは、オプティマイザの行数予測が実際のデータ分布と乖離しており、不適切な実行計画が選択されていることを意味します。

基本設定: AUTO_CREATE_STATISTICS と AUTO_UPDATE_STATISTICS は常に ON にします。
追加設計: データ分布が急激に変化したり、特定のテナントIDや日付条件にデータが集中する場合、自動統計更新だけでは対応しきれない場合があります。以下の設計を導入することをお勧めします。
- フィルターされた統計情報（Filtered Statistics）: 頻繁に使用される特定のデータ範囲や条件に焦点を当てて個別に統計を作成します。
- FULLSCANメンテナンスジョブ: 夜間のバッチ処理時間帯等に、主要なテーブルに対して UPDATE STATISTICS ... WITH FULLSCAN を実行する定期ジョブを構築します。

結論

データベースチューニングの現代的なアプローチは、古い互換性レベルにデータベース全体を固定することではありません。 システム全体を最新レベル（160）に引き上げて高度な最適化エンジンの恩恵を最大化しつつ、例外的なクエリに対してはQuery Store、ヒント、および細やかな統計管理を駆使して個別に制御することです。この洗練された戦略を採用し、システムのパフォーマンスとリソース効率を最大化させましょう。

Talk Lowy(jp)

このブログを検索