Original 製品バージョン: SQL Server 2014、SQL Server 2016、SQL Server 2017、SQL Server 2019、SQL Server 2022
元の KB 番号: 2154845
まとめ
この記事では、SQL Server tempdb データベースの割り当ての競合を診断して減らす方法について説明します。 ページ空き領域 (PFS)、グローバル割り当てマップ (GAM)、および共有グローバル割り当てマップ (SGAM) ページでの PAGELATCH_UP 待機について説明します。 ここでは、複数の等しいサイズの tempdb データ ファイルを使用する方法と、古いバージョンではトレース フラグ 1118 と 1117 を使用して、負荷の高い同時実行ワークロードでのSQL Serverパフォーマンスを向上させる方法について説明します。
症状
Microsoft SQL Server実行されているサーバーでは、サーバーの負荷が高い場合に重大なブロックが発生します。 動的管理ビューsys.dm_exec_requestssys.dm_os_waiting_tasks、要求またはタスクがリソースのtempdbを待機していることを示します。 待機の種類は PAGELATCH_UPされ、待機リソースは tempdb内のページを指します。 これらのページの形式は 2:1:1、2:1:3 などです ( tempdbの PFS ページと SGAM ページ)。
注記
ページ番号が 8088 で均等に割り切れる場合は、PFS ページです。 たとえば、2:3:905856 ページは、file_id=3の tempdb の PFS ページです。
次の操作では、 tempdb が広範囲に使用されます。
- 一時テーブル (ローカルまたはグローバル) に対する繰り返しの作成および削除操作。
- ストレージに
tempdbを使用するテーブル変数。 - カーソルに関連付けられている作業テーブル。
-
ORDER BY句に関連付けられている作業テーブル。 -
GROUP BY句に関連付けられている作業テーブル。 - ハッシュ プランに関連付けられている作業ファイル。
これらのアクティビティによって競合が発生する可能性があります。
原因
tempdb が頻繁に使用されている場合、SQL Serverはページを割り当てるときに競合が発生する可能性があります。 競合の程度によっては、 tempdb を使用するクエリと要求が一時的に応答しなくなる可能性があります。
以前のバージョンのSQL Serverでのオブジェクトの作成中に、システムは混合エクステントから 2 つのページを割り当て、それらを新しいオブジェクトに割り当てます。 1 ページ目は インデックス割り当てマップ (IAM) ページで、2 番目のページはオブジェクトの最初のページです。 SQL Server は、共有グローバル割り当てマップ (SGAM) ページを使用して、混合エクステントを追跡します。 各 SGAM ページでは、約 4 GB のデータが追跡されます。
混在エクステントからページを割り当てるには、SQL Serverページ空き領域 (PFS) ページをスキャンして、空き混在ページを見つけます。 PFS ページは、すべてのページの空き領域を追跡し、各 PFS ページは約 8,000 ページを追跡します。 同期は、SQL SERVERが PFS ページと SGAM ページを変更し、その同期によって他の修飾子が短時間ストールする可能性がある場合に必要です。
SQL Server は、割り当てる混合ページを検索すると、常に同じファイルと SGAM ページでスキャンを開始します。 この動作により、多数の混合ページ割り当てが同時に実行されると、SGAM ページで激しい競合が発生し、前述の現象が発生します。
注記
割り当て解除処理でもこれらのページが変更されるため、競合が増大する可能性があります。
SQL Serverが使用する割り当てメカニズム (SGAM、GAM、PFS、IAM) の詳細については、「関連コンテンツ」セクションを参照してください。
ソリューション
SQL Server 2014 年 7 月 9 日に延長サポートが終了しました。そのため、ほとんどの読者は、SQL Server 2016 以降のバージョンのガイダンスに従う必要があります。
SQL Server 2016 以降のバージョン
- 現在のパフォーマンス ガイダンスについては 、tempdb データベース を確認してください。
-
tempdbに複数の等しいサイズのデータ ファイルがあることを確認します。 まずは、論理プロセッサごとに 1 つのデータ ファイルを使用し、最大 8 ファイルまでとします。 競合が続く場合は、論理プロセッサの数まで 4 つのグループにファイルを追加します。 - SQL Server インスタンスを最新の累積的な更新プログラムで最新の状態に保ち、割り当ての改善を図ります。 たとえば、「KB 4099472: SQL Server 2014、2016、および 2017 での PFS ページ ラウンド ロビン アルゴリズムの改善を参照してください。
- SQL Server 2019 以降のバージョンで競合が続く場合は、tempdb データベース同時 PFS 更新とメモリ最適化
tempdbメタデータに関するガイダンスを確認してください。
SQL Server 2014 およびそれ以前のバージョン
tempdbのコンカレンシーを向上させるには、次の方法を試してください。
ディスク帯域幅を最大化し、割り当て構造の競合を減らすために、
tempdb内のデータ ファイルの数を増やします。 原則として、論理プロセッサの数が 8 個以下の場合は、論理プロセッサと同じ数のデータ ファイルを使用します。 論理プロセッサの数が 8 を超える場合は、8 つのデータ ファイルから始めます。 競合が続く場合は、論理プロセッサの数まで 4 つのグループにデータ ファイルを追加するか、ワークロードまたはコードを変更します。SQL Server 2005 の tempdb での作業に関するベスト プラクティス ガイダンスを確認します。
前の手順で割り当ての競合が大幅に減少せず、競合が SGAM ページにある場合は、トレース フラグの
-T1118を有効にします。 このトレース フラグでは、SQL Serverは各データベース オブジェクトに完全なエクステントを割り当てます。これにより、SGAM ページでの競合が大幅に軽減されます。注記
- このトレース フラグは、SQL Server インスタンス上のすべてのデータベースに影響します。 割り当ての競合が SGAM ページにあるかどうかを確認するには、 DML 操作による競合の監視を参照してください。
- SQL Server 2014 の場合は、Service Pack 3 を適用して KB 4099472 の修正プログラムを取得します。これにより、同じデータ ファイル内の複数の PFS ページ間でラウンド ロビン PFS ページ割り当てを実行することで競合がさらに軽減されます。
tempdb の割り当ての詳細
SQL Server 2016 以降のバージョンでの tempdb の既定の動作
SQL Server 2016 以降では、いくつかの機能強化により、既定で tempdb 割り当ての競合が減ります。そのため、通常、トレース フラグ 1118 または 1117 を有効にする必要はありません。
-
既定では、混合エクステント割り当ては
tempdbに対して無効になっています (トレース フラグ 1118 で以前に有効にされていた動作)。 すべての新しい割り当ては、均一エクステントから取得されます。 -
すべての
tempdbデータ ファイルが自動的に拡張 されます (トレース フラグ 1117 によって以前に有効にされていた動作)。 -
SQL Server セットアップでは、論理プロセッサの数 (最大 8 個) に基づいて、複数の等しいサイズの
tempdbデータ ファイルを推奨します。 - PFS の同時更新は、SQL Server 2019 以降で PFS ページ ラッチの競合をさらに軽減します。
2016 以降のバージョンSQL Server実行する場合は、サイズとtempdb ファイルの数に注目してください。 トレース フラグは、SQL Server 2014 以前でのみ使用してください。
等しい tempdb データ ファイルのサイズ設定の例
たとえば、1 つの tempdb データ ファイルが 8 GB で、ログ ファイルが 2 GB の場合は、データ ファイルの数を 8 に増やし (サイズを同じにするには 1 GB ずつ)、ログ ファイルは変更しません。 データ ファイルを別のディスクに配置すると、パフォーマンス上のメリットが得られますが、必須ではありません。 ファイルは同じディスク ボリューム上に共存できます。
tempdbデータファイルの最適な数は、発生している競合の程度によって異なります。 開始点として、ファイル数を、SQL Serverに割り当てられた論理プロセッサの数と同じ数に設定します。 ハイエンド システムの場合は、8 つのファイルから始めます。 競合が軽減されない場合は、さらにデータ ファイルを追加します。
すべてのデータ ファイルに同じサイズ設定を使用します。 SQL Server 2000 Service Pack 4 (SP4) では、ページが混在する割り当てにラウンド ロビン アルゴリズムを使用する修正プログラムが導入されました。 この改善により、複数のファイルが存在する場合、連続するページの混在割り当てごとに開始ファイルが変更されます。 SGAM 割り当てアルゴリズムは純粋なラウンド ロビンであり、比例フィルを受け入れないので、すべての tempdb データ ファイルを同じサイズで作成します。
tempdb データ ファイルの数を増やして競合を減らす方法
同じサイズの tempdb データ ファイルを追加すると、次の方法で競合が軽減されます。
-
tempdb用に 1 つのデータ ファイルがある場合は、4 GB の領域ごとに 1 つの GAM ページと 1 つの SGAM ページがあります。 - 同じサイズのデータ ファイルを増やすと、データ ファイルごとに 1 つ以上の GAM ページと SGAM ページが効果的に作成されます。
- GAM 割り当てアルゴリズムは、プロポーショナル フィルを受け入れながら、ラウンド ロビン方式でファイルから一度に 1 つのエクステント (連続する 8 ページ) を割り当てます。 たとえば、同じサイズのファイルが 10 個ある場合、最初の割り当ては File1、2 番目は File2、3 番目は File3 などです。
- GAM がページを割り当てる間、一度に 8 ページが FULL としてマークされるため、PFS ページの競合が減少します。
トレース フラグ -T1118 が競合を減らす仕組み
注記
このセクションは、SQL Server 2014 以前のバージョンにのみ適用されます。 SQL Server 2016 以降のバージョンでは、この動作は tempdb の既定値です。
トレース フラグ -T1118 は、次の方法で競合を減らします。
-
-T1118はサーバー全体の設定です。 -
-T1118をSQL Serverスタートアップ パラメーターに追加して、SQL Server再起動後もトレース フラグが有効になるようにします。 -
-T1118は、サーバー上のほぼすべてのシングルページ割り当てを削除します。 - ほとんどの単一ページ割り当てを無効にすると、SGAM ページの競合が減少します。
-
-T1118オンの場合、ほぼすべての新しい割り当ては、オブジェクトの最初の 8 ページのエクステントから 1 ページではなく、一度に 8 ページ (1 エクステント) をオブジェクトに割り当てる GAM ページ (2:1:2 など) から取得されます。 - IAM ページでは、
-T1118がオンになっている場合でも、SGAM ページからのシングルページ割り当てが引き続き使用されます。 ただし、修正プログラム 8.00.0702 およびより多くのtempdbデータ ファイルと組み合わせると、SGAM ページの競合が減少します。 スペースの問題については、次のセクションを参照してください。
トレース フラグ 1118 と最新のSQL Serverの既定の動作の比較
| Behavior | SQL Server 2014 以前 (-T1118 なし) |
SQL Server 2014 以前 (-T1118 を使用) |
SQL Server 2016 以降 (既定値tempdb) |
|---|---|---|---|
| 新しいオブジェクトの最初の 8 ページ | 混合エクステントからの単一ページの割り当て (SGAM) | 完全な均一エクステント (GAM) | 完全な均一エクステント (GAM) |
| SGAM ページ競合のリスク |
tempdb の高負荷使用時に高い |
低 | 低 |
| Scope | 適用なし | サーバー全体 |
tempdb のみ |
| アクションが必要 | None | スタートアップ パラメーター -T1118 追加する |
None |
トレース フラグ -T1118 の欠点
-T1118を使用する場合の欠点は、次の両方の条件が当てはまる場合にデータベース サイズが大きくなる可能性があることです。
- ユーザー データベースに新しいオブジェクトを作成します。
- 新しい各オブジェクトは、64 KB 未満のストレージを占有します。
このような状況では、SQL Serverは 8 KB しか必要とされていないオブジェクトに 64 KB (8 ページ * 8 KB) を割り当てることができます。これにより、56 KB のストレージが無駄になります。 新しいオブジェクトの有効期間中に 64 KB (8 ページ) を超える値を使用する場合、トレース フラグに欠点はありません。 最悪の場合、SQL Serverは最初の割り当て時に 7 つのページを割り当てますが、1 ページを超えて拡大しない新しいオブジェクトに対してのみ割り当てられます。