この投稿は インタープリズムの面々が、普段の業務に役立つ記事を丹精込めて書き上げる！ Advent Calendar 2016 - Qiitaの25日目 の記事です。

こんにちは。ito.yです。

SQL Serverを使っていると、トレースフラグを使用することでパフォーマンス上の問題を解決できることが度々あります。今回はトレースフラグについて軽く説明した後、設定するだけでSQL Serverのパフォーマンスを向上させる可能性のあるトレースフラグについて簡易的にまとめて紹介したいと思います。

トレースフラグとは

SQL Serverのサーバー固有の特定機能の有効・無効を切り替えるためのフラグです。

設定方法

コマンドで設定する場合

-- セッションで有効化
DBCC TRACEON(1224)

-- セッションで無効化
DBCC TRACEFF(1224)

-- グローバルで使用したい場合は-1を付けます
DBCC TRACEON(1224, -1)

-- トレースフラグの状態を確認する場合
DBCC TRACESTATUS

※コマンドで設定した場合SQLサーバーが再起動されるとリセットされます。設定状態を保ちたい場合はスタートアップオプションで設定する方法がお勧めです。

スタートアップオプションで設定する場合

SQL Server構成マネージャーからスタートアップオプションを設定可能です。画像のように -T オプションを付けることで、指定した番号のトレースフラグを設定することができます。 f:id:interprism:20161219024411p:plain

クエリオプションで設定する場合

SELECT * FROM
(
・・・
)
OPTION(QUERYTRACEON 9481)

のようにオプションで設定することで特定クエリのみにトレースフラグを有効化可能です。ただし、全てのトレースフラグを使用できるわけではなく、実行計画に影響を及ぼす特定フラグのみに限られます。

使用可能なトレースフラグに関しては次のリンクを参照してください。

Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level

トレースフラグによるパフォーマンス改善例

ここで実際に、テーブル変数に一定の変更がある場合にリコンパイルを行う機能を有する2453番のトレースフラグを使用することで改善される例を挙げたいと思います。

次のように「少量のデータが投入されたテーブルA」と「大量のデータが投入されたテーブル変数B」があるとします。 f:id:interprism:20161225215225p:plain 主キーのID同士を内部結合した場合、テーブルAを基点として結合するのが最も効率の良い方法ですが、実行するとテーブル変数Bを基点としてしまい、単純な結合にも時間がかかってしまいます。

実際の実行計画(実行時間：733ミリ秒)

f:id:interprism:20161219024702p:plain

このような実行計画となってしまう原因はテーブル変数の行数を1行と推定してしまっていることにあります。

上記の推定実行計画

f:id:interprism:20161219024714p:plain

2453番のトレースフラグを有効化することで推定実行計画の行数が正しくなり、効率の良い実行計画で実行可能となります。

トレースフラグ有効化後の実際の実行計画(実行時間：3ミリ秒)

f:id:interprism:20161219025004p:plain

※本ケースはRECOMPILEオプションを付けることでも修正可能です。

パフォーマンスに影響のあるトレースフラグ簡易表

私の把握している範囲でのパフォーマンスに関わるトレースフラグを一覧にまとめました。

※全てのトレースフラグを網羅しているわけではありません。

※トレースフラグを使用することで必ずパフォーマンスが向上するわけではありません。使用方法によっては実行プランの悪化など 劣化を招くこともあります。 有効であることを調査してからの使用をお勧めします。

種別	トレースフラグ番号	効果
ロック系	1211 1224	ロックエスカレーションを抑止します。レコード大量更新の際のテーブルロックを防止したい時に有効です。 1211でロックエスカレーションを抑止した際は、ロックリソース枯渇によるエラーが出る可能性があり、1224の使用が推奨されています。
I/O系	661	ゴーストレコード削除処理を無効化します。(通常、DELETEで削除したレコードはフラグを切り替えられただけで一定期間残存した後、削除処理で一掃されています。)
	1117	データファイルの拡張を複数ファイル同時に行います。ファイルサイズの偏りによるI/Oの偏りを防止します。SQL Server2016からはデフォルトで複数ファイル同時拡張を行うため本トレースフラグは機能しません。
	1118	混合エクステントの利用をサーバ全体で禁止します。SGAMなどによる割り当ての競合を避けるために使用されます。SQL Server2016からはデフォルトで単一エクステントが使用されるようになっているため本トレースフラグは機能しません。
	6498	ビッグゲートウェイを使用する規模のクエリの複数個同時コンパイルを可能にします。RESOURCE_SEMAPHORE_QUERY_COMPILEによる待機が発生している場合に有効です。SQL Server 2016からはデフォルトで本機能を有しているため本トレースフラグは機能しません。 Multi-fold increase in throughput for big gateway query compiles in SQL Server
実行計画系	2301	複雑なステートメントを適切に処理するため、オプティマイザのモデリング機能を強化します。パフォーマンスの悪い実行計画が作成される可能性を下げることができますが、コンパイル時間を増加させる可能性があります。 Query Processor Modelling Extensions in SQL Server 2005 SP1
	4136	パラメータスニッフィングにより実行計画が悪化することを防ぐために、パラメータを元にした行数を使用せず、統計情報を元に実行計画を作成します。トレースフラグを使用せずにクエリヒントでOPTIMIZE FORを使用することでも同様の対処が可能です。
	2453	テーブル変数に対する変更が閾値を超えた場合にリコンパイルを行います。テーブル変数のレコード量が正しく見積もられないことによる実行計画の悪化を防止できます。(本記事の例で使用したトレースフラグです。)
	2371	動的閾値を利用した統計情報の自動更新を有効化し、統計情報の乖離を防ぐ可能性を高めます。SQL Server2016からはデフォルトで本機能を有しているため本トレースフラグは機能しません。
	4199	クエリオプティマイザに対して行われた複数の修正をアクティブにするためのフラグです。 SQL Serverクエリプロセッサチームには、クエリの実行計画に影響を与える可能性のある修正はトレースフラグで制御する必要があるというポリシーがあるため、最新の修正プログラムを適用しても有効化されない修正が存在します。(SQL Server Trace Flags for Dynamics AX) SQL Server2016からはデフォルトで有効化された状態となっているため本トレースフラグは機能しません。
	2312	SQL Server2014以降を使用しているが、互換性レベルが110以下のときに指定することでSQL Server2014の基数推定を使用することができます。
	9481	SQL Server2014以降を使用していて、互換性レベルが120以上のときに指定することでSQL Server2012の基数推定を使用することができます。

おわりに

今回パフォーマンスに関わるトレースフラグをいくつか紹介しましたが、SQL Serverを運用する上で役立つトレースフラグやログ解析に役立つトレースフラグなど様々なトレースフラグが存在します。興味を持った方は調べてみることをお勧めします。

インタープリズムの面々が、普段の業務に役立つ記事を丹精込めて書き上げる！ Advent Calendar 2016 - Qiita

interprism's blog

インタープリズム株式会社の開発者ブログです。

SQL Serverのパフォーマンスに関わるトレースフラグ簡易まとめ