SQL Server 2012 等から SQL Server 2014 に移行・アップグレードした際のパフォーマンス低下の回避方法

SQL Server 2014 に移行・アップグレードして性能が低下した？

SQL Server 2008R2 や SQL Server 2012 から SQL Server 2014 に移行・アップグレードした後で、ストアド・プロシジャーの実行時間が長くなった等のパフォーマンス低下が起こった場合の回避策のひとつをご紹介します。

SQL Server 2014 の基数推定機能 (Cardinality Estimator) がマイナスに働くケースがある

SQL Server 2014 ではオプティマイザが使用する基数推定機能 (Cardinality Estimator) が「クエリ プランの品質を向上させ、その結果、クエリのパフォーマンスを向上させる」という目的で再設計されました。

マイクロソフトによると大半のクエリが性能向上されるか、変化しないとのことですが、同時に少数のクエリでの性能が低下の可能性が示唆されています。

SQL Server 2014 の基数推定機能 (Cardinality Estimator) で性能が低下した場合の回避方法

SQL Server 2014 の基数推定機能 (Cardinality Estimator) で性能が低下した場合の回避方法のひとつとして、トレースフラグ (TRACE FLAG) を指定して、オプティマイザーの動作を指定することができます。

SQL Server 2012 をデータベース互換性レベル (Compatibility Level) 110等、下位の互換レベルを使用していて、SQL Server 2014 に移行してパフォーマンスの低下が見られた場合、TRACE FLAG 2312 を指定して、オプティマイザがSQL Server 2014 の基数推定機能 (Cardinality Estimator) を使うように指定することができます。

反対に SQL Server 2014 のデフォルトであるデータベース互換性レベル(Compatibility Level) 120 で使用していて、性能の低下が見られた場合はトレースフラグ TRACE FLAG 9481 で、SQL Server 2012 バージョンのオプティマイザーを使うようにしてすることが可能です。

トレースフラグ（TRACE FLAG 2312・TRACE FLAG 9481）の設定方法

トレースフラグ（TRACE FLAG） は遅くなった特定のクエリーに、クエリーヒント OPTION の QUERYTRACEON を使ってとして個別に指定することができます。

以下のクエリーヒントをクエリーの最後につければOKです。

クエリーひとつひとつではなく、全体的（グローバル）に TRACE FLAG を設定したい場合は以下のスクリプトで設定することができます。

ただ、SQL Server がリスタートするとトレースフラグがリセットされてしまうので、 SQL Server の Configuration Manager の SQL Server Services で Database Engine を選択して Properties を表示し、Startup Parameters に起動時に ON にしたいトレースフラグを以下のように追加しておくと良いと思います。

[参考] 基数推定 (SQL Server)
https://msdn.microsoft.com/ja-jp/library/dn600374.aspx