SQL Server の tempdb のチューニングとして、次のような考え方があります。
- tempdb のデータファイルを CPU の論理コア数に分割する
- 各データファイルのサイズ / 自動拡張サイズを均等にする
- SQL Server 2014 以前のバージョンであれば、-T1117 / -T1118 を使用し、データファイルの同時拡張並びに単一エクステントを利用
- tempdb はグローバルリソースとなるため高速なディスクに配置する
日本語の情報としては、DO’s&DONT’s #17: やっておいた方がいいこと – tempdb データファイル数を CPU 数に一致させる が有名ではないでしょうか。
昨今の CPU は 1 ソケットで、40 物理コア / HT 80 論理コアのモデルもあり、複数ソケットを使用すると 100 コアを超える環境も出てきます。
そのような Many Core 時代でも tempdb のデータファイルの分割は「論理 CPU コア数」で行うべきでしょうか？
本投稿では現在の tempdb のデータファイルの分割について触れたいと思います。
Contents
tempdb のデータファイルを分割する理由
そもそもの話として、tempdb のデータファイルを分割する理由は何でしょうか？
「ラッチの競合を減少するため」という回答が多いと思いますが、どのようなラッチ競合を解消するかについては、ページ空き領域 (PFS) ページでのラッチの競合 で解説が行われています。
データファイルにデータを書き込む際には、PFS (Page Free Space) という 8088 ページ 毎に存在するページから、「どのページが開いているか」を確認する必要があります。
このようなデータベースを管理するページは「システムアロケーションページ」と呼ばれ、データベースにデータを書き込む際には、アクセスが集中する可能性があります。
tempdb のデータファイルを分割する理由は「システムアロケーションページの競合の緩和」を行うためです。
データファイルの分割は「CPU コア数に分割する」ことが目的ではなく、「システムアロケーションページの競合の緩和」が目的であるということを意識しておきます。
SQL Server の進化による tempdb の同時実行性の向上
SQL Server はバージョンの進化に合わせて、tempdb の効率化も行われています。
- SQL Server 2016
- -T1117 / -T1118 がデフォルトの操作となり TF の設定が不要
- インストール時に tempdb のデータファイルを自動的に構成
- 8 または論理コア数の少ない値でデータファイルを自動的に構成
- SQL 2016 – It Just Runs Faster: Automatic TEMPDB Configuration
- SQL Server 2014 SP3 / 2016 SP1 CU9 , SP2 CU1 / 2017 CU7
- 同一データファイルで複数の PFS を使用することで、少量のデータファイルで PFS の競合を抑制
- KB4099472-SQL Server 2014、2016、2017での PFS ページのラウンドロビンアルゴリズムの改善
- 同一データファイル内の複数の PFS が使用できるようになるため、データファイルの拡張時には、新しい PFS も確保されるサイズが好ましく、SQL Server 2016 の tempdb の自動構成は 64 MB (1 回の拡張で新しい PFS も確保される) となっているのかと思います。
- 同一データファイルで複数の PFS を使用することで、少量のデータファイルで PFS の競合を抑制
- SQL Server 2019
- PFS の並行処理を共有ラッチで更新できるようにすることで同時実行性を向上
- メモリ最適化 tempdb メタデータによるラッチ競合の削減
SQL Server 2019 までは、PFS (Page Free Space) 関連のラッチの競合を減らすための様々なアプローチが行われています。
SQL Server 2022 では、SQL Server 2022: System Page Latch Concurrency Enhancements (Ep. 6) | Data Exposed で解説されているように、tempdb の競合の改善が行われており、GAM / SGAM の更新についても最適化が行われるようになるとアナウンスされています。
SQL Server のバージョンの進化によって、このような tempdb の同時実行性の向上が行われており、初期状態でもある程度、最適化が行われた状態で使用することができるようになっています。
最新の tempdb のデータファイルの分割について
最新の tempdb のデータファイルの分割の考え方については tempdb データベースでの割り当てのSQL Serverを削減する推奨事項 に記載されています。
SQL Server 2014 以前のバージョンについての記述として、次の内容が記載されています。
- 論理プロセッサの数が 8 個以下の場合は、論理プロセッサと同じ数のデータ ファイルを使用
- 論理理プロセッサの数が 8 (8) を超える場合は、8 つのデータ ファイルを使用
- 競合が続く場合は、データ ファイルの数を 4 ずつ増やし、論理プロセッサの数まで増やして、競合を許容可能なレベルに減らす
SQL Server 2016 以降は、インストール時に自動で tempdb の分割が行われるため、SQL Server 2016 以降のバージョンの記述については、上記の内容が含まれていませんが、2016 でもデータファイルの分割の考え方は同一となります。
最新の情報では、「8 データファイルではじめ、競合 (ページラッチ競合) が発生する場合は、4 ずつ増やし、最大は論理プロセッサ数までとする」という考え方となっており「最初から CPU コア数に分割する」ということは記載されていません。
この考え方は PASS 2011 の Bob Ward の Inside Tempdb 解説されたのが最初となっていたかと思います。
Optimizing tempdb configuration with SQL Server 2012 Extended Events でもこの内容に触れられています。
PASS 2011 での検証時には、64 コア環境で、32 データファイルで分割の効果が飽和し、32 と 64 データファイルでは、大きな変化は出なかったようです。
Many Core の環境では、tempdb のデータファイルの分割の効果より、他の箇所 / 要因がボトルネックとなり、データファイルの分割の効果はある一定の個数で飽和してくると思います。
冒頭で紹介した、DO’s&DONT’s #17: やっておいた方がいいこと – tempdb データファイル数を CPU 数に一致させる でも「では、データファイルは多ければ多いほどいいのでしょうか？」で「いいえ、多ければいいというものではありません。」と記載されており、「バランスが重要である」と解説されています。
不必要なデータファイルの分割を行うことは推奨しておらず「競合の緩和が確認できる範囲でデータファイルを追加する」のが現在の基本的な方針となっています。
まとめ
現在の SQL Server は、インストール時に複数のデータファイルで構成されるようになっており、最新の SQL Server では、1 ファイルでも tempdb の同時実行性はある程度確保できるようになっています。
tempdb のデータファイルは、「システムアロケーションページの競合の緩和」される範囲で実施し、不必要なファイルの分割は抑えることで最適なパフォーマンスを発揮することができるものとするのが、現在の tempdb のデータファイルの分割の基本方針となります。
「CPU コア数に分割する」は CPU のコア数が少なかった時代の考え方であり、Many Core の場合には、最初から CPU コア数にデータファイルを分割するのではなく、「競合の解消が確認できる範囲」でデータファイルを分割することで、最適なパフォーマンスを発揮するようにすることが重要ではないでしょうか。
なお、「Page Life Expectancy (PLE) が 300 秒以上となっていることが推奨」というのも、初期に提唱されていた時代のメモリサイズが現在と比較して小さかった時代のものであり、現在の推奨は「高い状態を維持しており、頻繁に低下しない」ことが推奨され、xxx 秒以上であるということは言われないようになっています。
「設定の目的を理解し、時代に沿った設定 / 状態を考える」ということは、常に意識しておきたいですね。