tempdb データベースでの割り当ての競合を減らすための推奨事項SQL Server

  • [アーティクル]

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

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

現象

Microsoft SQL Serverを実行しているサーバーでは、サーバーで負荷が高い場合に重大なブロックが発生します。 動的管理ビュー [sys.dm_exec_request または sys.dm_os_waiting_tasks] は、これらの要求またはタスクが tempdb リソースを待機していることを示します。 さらに、待機の種類は です。待機リソースは PAGELATCH_UPtempdb 内のページを指します。 これらのページの形式は 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 句に関連付けられている作業テーブル。
  • HASH PLANS に関連付けられている作業ファイル。

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

原因

tempdb データベースが頻繁に使用されている場合、ページの割り当てを試みると、SQL Serverで競合が発生する可能性があります。 競合の程度によっては、 tempdb に関連するクエリと要求が一時的に応答しなくなる可能性があります。

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

混合エクステントからページを割り当てるには、SQL Serverはページ空き領域 (PFS) ページをスキャンして、割り当てられる自由な混在ページを判別する必要があります。 PFS ページは、すべてのページで使用可能な空き領域を追跡し、各 PFS ページは約 8,000 ページを追跡します。 PFS ページと SGAM ページを変更するために、適切な同期が維持されます。短い期間、他の修飾子がストールする可能性があります。

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

注:

割り当て解除アクティビティでは、ページも変更する必要があります。 これにより、競合が増加する可能性があります。

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

解決方法

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

    レビュー

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

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

    tempdb のコンカレンシーを向上させるには、次の方法を試してください。

    • tempdb 内のデータ ファイルの数を増やして、ディスク帯域幅を最大化し、割り当て構造の競合を減らします。 原則として、論理プロセッサの数が 8 (8) 以下の場合は、論理プロセッサと同じ数のデータ ファイルを使用します。 論理プロセッサの数が 8 (8) を超える場合は、8 つのデータ ファイルを使用します。 競合が続く場合は、競合が許容可能なレベルに減るまで、論理プロセッサの数まで、データ ファイルの数を 4 の倍数 (4) ずつ増やします。 または、ワークロードまたはコードに変更を加えます。

    • SQL Server 2005 での tempdb の操作に関するベスト プラクティスの推奨事項を実装することを検討してください。

    • 前の手順で割り当ての競合が大幅に減少せず、競合が SGAM ページにある場合は、トレース フラグ -T1118 を実装します。 このトレース フラグの下で、SQL Serverは各データベース オブジェクトに完全なエクステントを割り当てることで、SGAM ページでの競合を排除します。

      注:

サイズが等しい 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 つの場合、GAM ページは 1 つだけ、4 GB の領域ごとに 1 つの SGAM ページがあります。

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

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

  • GAM によってページが割り当てられるため、一度に 8 ページが FULL としてマークされるため、PFS ページのリソース競合が軽減されます。

トレース フラグ -T1118 を実装すると競合が軽減される方法

注:

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

次の一覧では、トレース フラグ -T1118 を 使用して競合を減らす方法について説明します。

  • -T1118 はサーバー全体の設定です。
  • SQL Serverのスタートアップ パラメーターに -T1118 トレース フラグを含めて、SQL Serverがリサイクルされた後もトレース フラグが有効なままになるようにします。
  • -T1118 は、サーバー上のほぼすべての単一ページ割り当てを削除します。
  • ほとんどの単一ページ割り当てを無効にすると、SGAM ページでの競合を減らすことができます。
  • -T1118 がオンの場合、ほぼすべての新しい割り当ては、オブジェクトの最初の 8 ページ (8) ページのエクステントからではなく、オブジェクトに一度に 8 ページ (1 エクステント) を割り当てる GAM ページ (例: 2:1:2) から行われます。トレース フラグはありません。
  • -T1118 がオンになっている場合でも、IAM ページでは 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は、1 ページを超えない新しいオブジェクトに対してのみ、最初の割り当て中に 7 ページ (7) の追加ページを割り当てることができます。

関連情報