Microsoft SQL Serverのデータベース互換性レベルとは?確認・変更方法とパフォーマンスへの影響を解説

データベース互換性レベルは、データベースレベルの設定のひとつで、データベースの動作に大きな影響を与えます。Microsoft® SQL Server®の新バージョンがリリースされるたびに多くの新機能が追加されますが、その多くは新しいキーワードを必要とし、旧バージョンで存在していた動作にも変更をもたらします。最大限の後方互換性を確保するため、Microsoftでは必要に応じて互換性レベルを設定できる仕組みを提供しています。

データベース互換性レベルのデフォルト値

デフォルトでは、すべてのデータベースは、作成元となったmodelデータベースのバージョンから互換性レベルを引き継ぎます。たとえば、SQL Server 2012で作成したデータベースの互換性レベルは、特に変更しない限り110が初期値となります。

復元後の互換性レベル

古いバージョンのSQL Serverで取得したバックアップを復元する場合、ソース側の互換性レベルがサポート対象の最低レベルより低い場合を除き、バックアップ取得元のインスタンスと同じ互換性レベルが維持されます。最低レベルより低い場合は、サポートされる最も低いバージョンのレベルへ自動的に変更されます。たとえば、SQL Server 2005のデータベースバックアップをSQL Server 2017に復元した場合、復元後の互換性レベルは100(SQL Server 2017がサポートする最低レベル)に設定されます。

アップグレード後の互換性レベル

tempdb、model、msdb、resourceの各システムデータベースの互換性レベルは、アップグレード後に現在のバージョンの互換性レベルへ設定されます。一方、masterシステムデータベースについては、アップグレード前の互換性レベルが維持されます。

現在の互換性レベルを確認・変更する方法

現在の互換性レベルを確認するには、sys.databasesビューのcompatibility_level列に対してクエリを実行します。

互換性レベルを変更するには、次の例のようにALTER DATABASEコマンドを使用します。

Use Master
Go
ALTER DATABASE <database name> SET COMPATIBILITY_LEVEL = <compatibility-level>;

ウィザード(GUI)を使って変更することも可能です。ただし、ユーザーがオンラインでアクセスしているデータベースの場合は、まずシングルユーザーモードへ切り替えてから変更を行い、作業完了後にマルチユーザーモードへ戻すのが安全です。

ウィザードで変更するには、対象のデータベースを右クリックし、「プロパティ」→「オプション」→「データベースの互換性レベル」の順に選択します。以下の画像をご参照ください。

各バージョンの既定およびサポートされる互換性レベル

下表は、SQL Serverの各バージョンにおける既定の互換性レベルと、サポートされる互換性レベルの一覧です。

出典：https://www.sqlskills.com/blogs/glenn/database-compatibility-level-in-sql-server/

互換性レベルとパフォーマンスの関係

SQL 2014以前のSQL Serverでは、データベース管理者はパフォーマンスの観点から互換性レベルを意識する必要はほとんどありませんでした。当時、互換性レベルは主として、そのバージョンで導入された新機能の使用可否の制御や非対応機能の無効化、そして後方互換性の管理のための仕組みでした。

しかし現在では、バージョン間の移行時に必ず完全な回帰テスト(リグレッションテスト)を実施し、パフォーマンスの変化を把握することが重要です。移行後も古い互換性レベルの方がクエリのパフォーマンスが良いケースもあれば、逆に新しいレベルの方が優れるケースもあります。どちらになるかは事前に判断できないため、必ず入念な回帰テストを行いましょう。

カーディナリティ推定(Cardinality Estimation)

SQL Server 2014以降、互換性レベル120以上で稼働するデータベースは、新しい「カーディナリティ推定」機能を利用できます。カーディナリティ推定とは、推定コストに基づいてSQL Serverがどのようにクエリを実行するかを決定するロジックであり、その推定値はクエリに関連するオブジェクトの統計情報をもとに計算されます。実務的には、行数の見積もりに、テーブルやオブジェクト内の値の分布、一意な値の数、重複件数などの情報を組み合わせたものです。

これらの推定が誤ると、メモリ許容量(メモリグラント)の不足による不要なディスクI/O(TempDBへのスピルなど)や、並列プランではなく直列プランが選択されるといった問題が発生する可能性があります。カーディナリティ推定については、次回の記事で詳しく解説する予定です。

互換性レベル変更の影響

互換性レベルを変更すると、データベースが使用する機能セットが変わります。つまり、一部の機能が追加されると同時に、古い機能が削除されるのです。たとえば、FOR BROWSE句は互換性レベル100ではINSERT文やSELECT INTO文で使用できませんが、互換性レベル90では記述自体は許容されるものの無視されます。アプリケーションがこのような機能に依存している場合、予期しない結果を招く恐れがあります。

また、データベースを低い互換性レベルから高いレベルへ移行する際、「互換性レベルを変更しなければ新機能は一切使えない」と考えがちですが、これは正確には正しくありません。これはデータベースレベルの機能にのみ当てはまる話で、インスタンスレベルの機能は、互換性レベルを変更しなくても利用できます。

まとめ

データベース互換性レベルは、SQL Serverが特定の機能をどのように扱うかを定義するものです。具体的には、指定したバージョンのSQL Serverと同様に動作させることを目的としており、主に一定水準の後方互換性を確保するために用いられます。これはデータベースのプロパティであるため、その影響は該当データベースのデータベースレベル機能のみに及びます。

• 上位バージョンのサーバーへの移行でも、インプレースでのインスタンスアップグレードでも、その互換性レベルがサポートされている限り、互換性レベルは変更されません。
• 互換性レベルがSQL 2014以上に設定されている場合、SQL Serverは新しいカーディナリティ推定を使用します。2012以下に設定されている場合は、従来のオプティマイザが使用されます。

ご質問やフィードバックがあれば、フィードバックタブからお気軽にお寄せください。

専門家による環境の最適化サポート

RackspaceのApplication Services(RAS)のエキスパートは、幅広いアプリケーションポートフォリオにわたり、以下のようなプロフェッショナルサービスおよびマネージドサービスを提供しています。

• eコマースおよびデジタルエクスペリエンスプラットフォーム
• エンタープライズリソースプランニング(ERP)
• ビジネスインテリジェンス(BI)
• Salesforce顧客関係管理(CRM)
• データベース
• メールホスティングおよび生産性向上ツール

私たちが提供する価値：

• 偏りのない専門知識:即座に価値を生み出す機能に焦点を当て、モダナイゼーションの道のりをシンプルにガイドします。
• Fanatical Experience™:「プロセスファースト、テクノロジーセカンド®」というアプローチと専任のテクニカルサポートを組み合わせ、包括的なソリューションを提供します。
• 比類なきポートフォリオ:豊富なクラウド経験を活かし、適切なクラウド上に最適なテクノロジーを選択・導入できるよう支援します。
• アジャイルなデリバリー:お客様の現在地に合わせて伴走し、私たちの成功をお客様の成功とともに実現します。

まずはチャットでお気軽にお問い合わせください。