tempdb データベースでの割り当てのSQL Serverを削減する推奨事項

この記事は、サーバーに大きな負荷が発生している場合に重大なブロックが発生した場合の問題の解決に役立ちます。

元の製品バージョン:   SQL Server
元の KB 番号:   2154845

現象

サーバーが実行中のサーバーでMicrosoft SQL Server負荷が大きい場合に重大なブロックが発生します。 動的管理ビュー [ または ] は、これらの要求またはタスクが tempdb リソースを待機 sys.dm_exec_request sys.dm_os_waiting_tasks 中を示 します。 さらに、待機の種類は、tempdb 内のページを待機リソース PAGELATCH_UPポイントします。 これらのページの形式は 2:1:1、2:1:3 などです (tempdb の PFS ページと SGAM ページ)。

注意

ページが 8088 で割り切り可能な場合は、PFS ページになります。 たとえば、2:3:905856 ページは tempdb の file_id=3 の PFS です

次の操作では 、tempdb を広範囲に 使用します。

  • 一時テーブル (ローカルまたはグローバル) の繰り返し作成およびドロップ操作。
  • ストレージに tempdb を使用するテーブル 変数。
  • CURSORS に関連付けられている作業テーブル。
  • ORDER BY 句に関連付けられている作業テーブル。
  • GROUP BY 句に関連付けられている作業テーブル。
  • ハッシュ計画に関連付けられている作業ファイル。

これらのアクティビティは、コンテンツの問題を引き起こす可能性があります。

原因

tempdb データベースが 多用されている場合、ページSQL Serverを割り当てようとすると、コンテンツの問題が発生する可能性があります。 この場合、コンテンツの程度によっては 、tempdb に関連するクエリと要求が一時的に応答しなくなる可能性があります。

オブジェクトの作成時に、2 つのページを混在範囲から割り当て、新しいオブジェクトに割り当てる必要があります。 1 ページはインデックス割り当てマップ (IAM) 用で、2 ページ目はオブジェクトの最初のページです。 SQL Server共有グローバル割り当てマップ (SGAM) ページを使用して、複数の範囲を追跡できます。 各 SGAM ページは、約 4 GB のデータを追跡します。

混在範囲からページを割り当てるには、SQL Server がページの空き領域 (PFS) ページをスキャンして、割り当て可能な混在ページを特定する必要があります。 PFS ページは、すべてのページで使用可能な空き領域を追跡し、各 PFS ページは約 8000 ページを追跡します。 PFS ページと SGAM ページを変更するために、適切な同期が維持されます。その場合、他の修飾子を短期間停止できます。

複数SQL Serverページを検索して割り当てると、常に同じファイルと SGAM ページでスキャンが開始されます。 これにより、複数の混在ページの割り当てが進行中の場合に、SGAM ページで大混乱が発生します。 これにより、現象に関するセクションに記載されている問題 が発生する可能性 があります。

注意

割り当て取りを取り分けずに、ページを変更する必要があります。 これにより、コンテンツの増大が生じ得る可能性があります。

SQL SERVER (SGAM、GAM、PFS、THEIA) で使用されるさまざまな割り当てメカニズムの詳細については、「参照」セクションを 参照 してください。

解決方法

  • SQL Server 2016 以降のバージョン:

    レビュー

    これらの推奨事項と、2016 年 2016 年のレビューで導入されたその他の変更SQL詳細

  • SQL Server 2014 以前のバージョン:

    tempdb の同時実行を改善するには、次のメソッドを試してください。

    • tempdb 内のデータ ファイルの数を増やしてディスク帯域幅を最大化し、割り当て構造の問題を減らします。 一般的に、論理プロセッサの数が 8 以下の場合は、論理プロセッサと同じ数のデータ ファイルを使用します。 論理プロセッサの数が 8 より大きい場合は、8 つのデータ ファイルを使用します。 If contention continues, increase the number of data files by multiples of four (4) up to the number of logical processors until the contention is reduced to acceptable levels. または、ワークロードまたはコードを変更します。

    • 2005 年 1 月の tempdb の操作に関するベスト SQL Server実装を検討してください。

    • 前の手順で割り当ての問題が大幅に軽減されない場合、SGAM ページ上にコンテンツが含まれています。トレース フラグ -T1118 を実装します。 このトレース フラグのSQL Server各データベース オブジェクトに全範囲を割り当てる必要があります。これにより、SGAM ページのコンテンツがなくなります。

      注意

      • このトレース フラグは、データベースのインスタンス上のすべてのデータベースにSQL Server。 割り当てのコンテンツが SGAM ページにあるかどうかを判断する方法については、DML 操作によって発生する監視のコンテンツ を参照してください

      • 2014 SQL Serverの場合は、Service Pack 3 を適用して、次の KB 記事に記載されている修正プログラムを利用してください。 この改善により、2014 年 2014 SQL Serverの問題が軽減されます。 すべての tempdb データ ファイルに対するラウンドロビン割り当てに加えて、同じデータ ファイル内のいくつかの PFS ページに対してラウンドロビン割り当てを実行することで PFS ページの割り当てが改善されます。

        KB4099472 - 2014 年、2016 年、2017 年SQL Server PFS ページ ラウンド ロビン アルゴリズムの改善

      • MSSQL のチーム ブログ: tempdb 内のファイルと トレース フラグSQL Server更新

サイズが等しい tempdb データ ファイルの数を増やす

たとえば 、tempdb の 1 つのデータ ファイル サイズが 8 GB で、ログ ファイルのサイズが 2 GB の場合は、データ ファイルの数を 8 (8) (サイズを同じに維持するためにそれぞれ 1 GB) に増やし、ログ ファイルはそのままにしてください。 異なるデータ ファイルを別々のディスクに保存すると、パフォーマンスに大きなメリットが生まれます。 ただし、これは必須ではありません。 ファイルは同じディスク ボリューム上に共存できます。

tempdb データ ファイル の最適な 数は、tempdb に表示されるコンテンツの程度 によって異なります。 開始点として 、tempdb を、少なくとも、割り当てられている論理プロセッサの数と同じ数に構成SQL Server。 上位システムの場合、開始番号は 8 (8) になります。 コンテンツが縮小されない場合は、データ ファイルの数を増やす必要があります。

同じサイズのデータ ファイルを使用することをお勧めします。 SQL Server 2000 Service Pack 4 (SP4) では、ページ割り当ての混在にラウンド ロビン アルゴリズムを使用する修正プログラムが導入されました。 この改善により、開始ファイルは、連続して混在するページ割り当てごとに異なります (複数のファイルが存在する場合)。 SGAM の新しい割り当てアルゴリズムは純粋なラウンド ロビンであり、速度を維持するために比例フィルは使用しません。 すべての tempdb データ ファイル を同じサイズ で作成することをお勧めします。

tempdb データ ファイルの数を増やすと、コンテンツの問題を減らす方法

次の一覧では、サイズが等しい tempdb データ ファイルの数を増やすと、どのようにコンテンツの問題が減るのかについて説明します。

  • tempdb に 1 つのデータ ファイルがある場合は、1 つの GAM ページと、4 GB の領域ごとに 1 つの SGAM ページのみがあります。

  • tempdb のサイズが同じデータ ファイルの数を増やすと、データ ファイルごとに 1 つ以上の GAM ページと SGAM ページが効果的に作成されます。

  • GAM の割り当てアルゴリズムは、比例フィルを受け入れながら、ラウンド ロビン方式でファイル数から一度に 1 つの範囲 (連続する 8 ページ) を割り当てる。 したがって、同じサイズのファイルが 10 の場合、最初の割り当ては File1、2 番目は File2、3 番目は File3 です。

  • PFS ページのリソースのコンテンツは、GAM がページを割り当て中なので、一度に 8 ページが FULL としてマーク付けされ、削減されます。

トレース フラグ -T1118 を実装して、どのように問題を減らすか

注意

このセクションは、2014 SQL Serverバージョンにのみ適用されます。

次の一覧では、トレース フラグ -T1118 を使用して、どのように問題を減らすかについて説明します。

  • -T1118 はサーバー全体の設定です。
  • トレース フラグがリサイクルされた後でもトレース フラグが有効なままにSQL Serverのスタートアップ パラメーターに -T1118 トレース フラグSQL Server含める。
  • -T1118 は 、サーバー上のほぼすべての単一ページ割り当てを削除します。
  • 単一ページの割り当てのほとんどを無効にすると、SGAM ページのコンテンツが減ります。
  • -T1118 をオンにすると、ほぼすべての新しい割り当てが GAM ページ (たとえば、2:1:2) から作成されます。このページでは、トレース フラグを使用せずに、オブジェクトの最初の 8 ページの範囲から 1 ページではなく、一度に 8 ページ (1 つの範囲) を割り当てる必要があります。
  • -T1118 がオンになっている場合でも、THEIA ページでは SGAM ページからの単一ページ割り当てが使用 されます。 ただし、修正プログラム 8.00.0702 と tempdb データ ファイルの増加を組み合わせると、SGAM ページでのコンテンツの減少が大きな影響を受ける可能性があります。 スペースに関する問題については、次のセクションを参照してください。

欠点

-T1118 を 使用する欠点は、次の条件に当てはまる場合、データベース サイズが増加する可能性がある点です。

  • ユーザー データベースに新しいオブジェクトが作成されます。
  • 新しい各オブジェクトの記憶域は 64 KB 未満です。

これらの条件が当てはまる場合は、8 KB の領域しか必要ないオブジェクトに 64 KB (8 ページ * 8 KB = 64 KB) を割り当て、56 KB の記憶域を無駄にできます。 ただし、新しいオブジェクトの有効期間内に 64 KB (8 ページ) を超える値を使用する場合、トレース フラグに欠点はありません。 したがって、最悪の場合、SQL Server は、最初の割り当ての間に 7 ページを追加で割り当てる場合があります。これは、1 ページを超える新しいオブジェクトに対してのみです。

関連情報