SQL Server 2016におけるクエリオプティマイザの改善

過去2つのバージョンのSQL Serverは、新機能追加によりパフォーマンスを向上させることに注力していた。 これに対し、SQL Server2016は、既存の機能の性能向上を図っている。

カーディナリティエスティメータ(Cardinality Estimator)

どのようなクエリオプティマイザにとっても、中心となるのはカーディナリティエスティメータである。このコンポーネントは、 クエリ実行計画の各ステップの行数を見積もるために、検索対象となるテーブルと、実行される処理の統計情報を取得する。 経験を積んだDBAならばご存知の通り、不正確なカーディナリティ見積もりは、データベースのパフォーマンスを劣化させる。 起こりうる問題としては以下のようなものがある。

• 間違ったインデックスを選択する
• 間違った結合操作(ネステッドループ、ハッシュ、マージなど)を選択する
• 過大なメモリを割り当てる。これにより、他のクエリがブロックされてしまう
• 過少なメモリを割り当てる。これにより、あふれたデータがtempdbを圧迫する

これが重要な事であることを理解していれば、SQL Server2012の使用するカーディナリティエスティメータが、1998年にSQL Server7に導入されたものから本質的に変更されていないことを聞いて驚くだろう。 “SQL Serverのクエリオプティマイザのカーディナリティ見積もりプロセスに対する初めての大規模再設計”という文言を見たのは、たった2年前である。 再設計されたバージョンでの違いについて深く知りたい方は、ホワイトペーパー(Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator)を参照して欲しい。

SQL Server 2016は、この成果を元に、さらに正確な見積もりを算出できるように構築されている。 この機能を有効にするには、互換性レベル(Compatibility level)を130に設定する。 ただし、リグレッションが発生する可能性はある。本番データベースの互換性レベルを変更する前に、既存のデータベースを一通りテストすべきである。

互換性レベル(Compatibility Levels)

この用語に馴染みのない方のために説明する。SQL Serverにおける互換性レベルには、2つの重要な役割がある。 第一に、どのようなデータベース機能を有効とするか、である。データベースを低い互換性レベルに設定すると、新機能は利用できなくなる。 これは通常、アップグレードに対しては問題にならない。 データベースは旧機能群を前提として設計しているはずだからである。

互換性レベルにより制御されるもうひとつの要素は、どのクエリオプティマイザとカーディナリティエスティメータを使用するか、である。 ファインチューニングされたデータベース上では、リグレッションが発生することを防ぐため、低い互換性レベルを設定することがある。 これにより、SQL Serverは、旧バージョンのクエリオプティマイザを使用するようになる。

時には、さらに細かい粒度の制御を行うこともある。 例えば、SQL Server 2016の互換性レベルを130に設定し、新機能を使用可能にしつつ、旧バージョンのカーディナリティエスティメータを使用するケースが考えられる。 これを実現するには、以下のコマンドを実行する。

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;

SQL Server 2008 R2を除けば、各バージョンごとの互換性レベル値は、前バージョンに対して10づつ上がっている。 SQL Server 2000は80、SQL Server 2016は130である。 各バージョンは、少なくとも2つ前のバージョンをサポートしている。

マルチスレッドインサート(Multi-threaded Inserts)

SQL Server 2016より前のバージョンでは、Insert-Select文において、セレクトフェーズのマルチスレッド実行が可能なのは、実際のインサート処理がシリアライズされている場合だけだった。 現在は、インサート処理も“マルチスレッド化、または並行実行計画を持つ”ことになった。

メモリ最適化テーブル(Memory-Optimized Tables)

メモリ最適化テーブルもマルチスレッド化の恩恵を受けられる。

統計

SQL Server 2016は、統計に対して2つの変更を持ち込んだ。 一つめは、巨大テーブルに対しても、より頻繁に統計情報が更新されるようになったことである。

これまでは、統計情報を自動更新するトリガとしての、変更行数のしきい値は、20%でした。 これは、巨大テーブルに対しては不十分でした。 SQL Server 2016(互換性レベル130)からは、このしきい値は、テーブル内の行数と連動します。 テーブル内の行数が増加すると、統計情報変更のトリガとなるしきい値は下がります。 なお、この振る舞いは、前リリースのトレースフラグ2371より利用可能です。

例えば、テーブルの行数が10億行の時、以前の振る舞いでは、統計情報の自動更新が発生するには、2億行が更新されねばなりませんでした。 SQL Server2016は、100万行の更新で統計情報の自動更新が発生します。

統計も、並行性の話題と関係する。 統計は、互換性レベルを130に設定した場合、マルチスレッドプロセスによりサンプリングされる。

外部キー制約(Foreign Key Constraints)

リレーショナルデータベースの売りの一つは、テーブル間の関係付けである。 外部キー制約は、テーブル間の関係付けに際し、データ整合性を保障する。 しかし、この機能にはオーバヘッドがかかる。 SQL Server2014およびそれ以前では、1テーブルあたりの外部キー制約数は、253に制限されていた。

この数値は十分大きいように思えるかもしれない。 しかし、“CreatedByKey”のような監査用カラムを考えていくと、巨大データベースではすぐに限界に達する。 この問題を緩和するため、Microsoftは着信参照(incoming)外部キー制約の上限を10000に増やした。 これにより,一つのユーザテーブルを参照する数千のテーブルを作成できるようになった。 ただし、注意すべきことがある。

この制限緩和は、発信参照(outgoing)外部キー制約には適用されない。 更に,自己参照テーブルにも適用されない。 これらは相変わらず、数百個の外部キーが上限となる。

更に、参照されるテーブルは、MERGE操作を使った変更ができない。 DELETEとUPDATE操作のみ可能である。 (技術的には、SELECTとINSERTも可能である。しかし、参考文献はこれらについて触れていない。なぜなら、これらは着信参照外部キー制約の影響を受けないからである。)

注記　“リレーショナル・データベース・マネジメント・システム”における“リレーショナル”は、テーブル間の実際の繋がり自体を指すものではない。 そうではなく、データサイエンス用語における、1行中の1つの値が、同一行の他の値とどれだけ強い関係を持っているか、という考え方に近い。 非リレーショナルテーブルの例としては、ピボットテーブルがある。 それぞれのセルは、独立した合計や平均を表している。

結合と外部キー制約(Joins and Foreign Key Constraints)

上で述べたように、外部キー制約にはコストがかかる。 潜在的に外部キー制約に参照されている行を更新するときには、制約違反でないことを保障するためのチェック処理が実行される。

SQL Server 2014では、このチェックは、対象テーブルを参照する各テーブルを結合することにより実行されていた。 ご想像の通り、このやり方では、直ぐに極めて負荷の高い処理になってしまう。 これに対処するため、SQL Serverは新たな参照整合性オペレータ(Referential Integrity Operator)を導入した。

新たなクエリ実行オペレータは、適切に参照整合性チェックを行います。 チェックは、変更された行と、参照しているテーブル内の行の比較により行います。 この比較において、変更が参照整合性に違反していないかを検証します。 これにより、この種の実行計画のコンパイル時間や比較実行時間が短くなります。