SQL Server 2008 から tempdb の同時実行性の強化が進んでいますが、SQL Server 2016 になり、さらに強化が進んでいます。
CTP 3.0 がリリースされたタイミングで、いくつか公開されている情報が目につきましたので、ここで一度まとめてみたいと思います。
SQL Server の tempdb の同時実行性の強化のポイントとして、きおつけておきたいポイントは 4 点ほどあります。
Contents
■-T1118 による混合エクステントの抑制
まず、一つ目のポイントが混合エクステントの抑制です。
大量のデータを一時テーブル等に入れる場合は、さほど影響しないような気もしますが、少量のデータを一時テーブルに入れるようなケースで、単一エクステントが使用されると、同一のエクステントに情報が格納されるため、アロケーションページの競合が発生しやすくなります。
このための対応として、トレースフラグ「-T1118」設定があります。
SQL Server 2008 ぐらいからは、tempdb の同時実行性の強化が図られていたため、1118 を使用しなくてもある程度最適化されていたかと思いますが、明示的に指定することで抑制することが可能です。
Recommendations to reduce allocation contention in SQL Server tempdb database
[FIX] tempdb データベースの同時実行制御の強化
■-T1117 によるデータファイルの同時自動拡張
トレースフラグ系のもう一つのポイントとしては「-T1117」があります。
これは、データファイルに自動拡張の必要性が発生した場合に、同時拡張をするためのトレースフラグとなります。
Consider Enabling Trace Flag 1117 on Dynamics AX SQL Server
デフォルトの動作では、データベースを複数のデータファイルで構成した場合、自動拡張の必要性が発生した場合に拡張されるファイルは一つとなります。
そのため、自動拡張が行われると特定のファイルのみが大きくなり、データファイルを同一のサイズで構成していた場合の、ファイルの均等性が崩れることになり、特定のデータファイルに I/O が発生しやすくなります。
これを防ぐために、自動拡張が発生した場合にファイルグループ内のすべてのデータファイルを同時に拡張するためのトレースフラグが 1117 となります。
■CTP 3.0 によるトレースフラグ相当の動作のデフォルトへの組み込み
ここで紹介した二つのトレースフラグについてですが、SQL Server 2016 CTP 3.0 のドキュメントに以下のような記載が明記されるようになりました。
TempDB Database
Trace Flags 1117 and 1118 are not required for tempdb anymore. If there are multiple tempdb database files all files will grow at the same time depending on growth settings. In addition, all allocations in tempdb will use uniform extents.
CTP 2.4 ぐらいの段階で T1117 相当の動作は実装されていたようなのですが、現状の最新の CTP では、tempdb を複数のデータファイルで構成した場合の全データファイルの自動拡張は、デフォルトの動作として行われるようになっています。
また、割り当てに関しては単一エクステントでの割り当てが基本となっているということが記載されるようになりました。
# 多少は使われるようですが。
ただし、これらの動作は「tempdb」に限定されていますので、他のユーザーデータベースでも同様の動作としたい場合には、依然としてトレースフラグの設定が必要となります。
■インストール時に tempdb のデータファイルを複数で構成
こちらも、TempDB Database に記載されている内容ですね。
- By default, setup adds as many tempdb files as the CPU count or 8, whichever is lower.
- During setup, you can configure the number of tempdb database files, initial size, autogrowth and directory placement using the new UI input control on the Database Engine Configuration – TempDB section of SQL Server Installation Wizard.
- The default initial size is 8MB and the default autogrowth is 64MB.
- You can specify multiple volumes for tempdb database files. If multiple directories are specified tempdb data files will be spread across the directories in a round-robin fashion.
tempdb のデータファイルについては、CPU のコア数に合わせて、最大で 32 データファイルぐらいまでの分割が最新のトレンドになっていたかと思います。
SQL Server 2016 のインストーラーでは、デフォルトで CPU のコア数か 8 のいずれか小さい方に合わせて分割された状態がデフォルトとなりました。
初期サイズに関して変更ができるため、インストール時にデータファイルを均等にすることが可能となっています。
# CTP 3.0 から UI が日本語になっています。
■インストール時に SQL Server のサービスアカウントに瞬時初期化を有効化
こちらは CTP 3.0 から追加された機能になります。
SQL Server では「ボリュームの保守タスクを実行」の権限を付与することで、データファイルに対してファイルの瞬時初期化を実行することができるようになります。
# TDE を使用している場合は使用できませんが。
データベース ファイルの初期化
この設定がインストール時に実施できるようになりました。
これは、データファイルの領域を初期化するタイミングを「ファイルの領域作成時」→「実際にデータを書き込む時」に変更することで、拡張が発生した際のオーバーヘッドを減らす目的で使用することができます。
新しいディスクを使用しているのであれば、既存のデータ書き込みについては考慮の必要は薄いかと思いますが、頻繁にデータを書き込んでいるディスクで、領域の再利用をする場合に、どのタイミングでデータを「0 書き込み」するかの制御となります。
# データの領域を使用する際には、設定の有無にかかわらず初期化されます。
tempdb が頻繁に自動拡張する場合、この設定が有効だったりするのですが、これについてもインストール時に設定が可能となりました。
SQL Server 2016 では、内部的に自動で設定、インストール時に設定できる項目として指定することで、最適化に対してのアプローチがいろいろと実施されているのが確認できますね。