次の方法で共有

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

  • [アーティクル]

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

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

現象

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

Note

ページが 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 ページで激しい競合が発生します。 これにより、「 Symptoms 」セクションに記載されている問題が発生する可能性があります。

Note

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

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

解決方法

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

    確認

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

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

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

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

    • sql Server 2005 の tempdb を使用した作業でベスト プラクティスの推奨事項を実装することを検討してください。

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

      Note

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

      • SQL Server 2014 環境の場合は、Service Pack 3 を適用して、次の KB 記事に記載されている修正プログラムを利用してください。 この機能強化により、SQL Server 2014 環境での競合がさらに軽減されます。 この修正により、すべての tempdb データ ファイルに対するラウンド ロビン割り当てに加えて、同じデータ ファイル内の複数の PFS ページでラウンド ロビン割り当てを実行することで、PFS ページの割り当てが向上します。

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

      • MSSQL Tiger チーム ブログ: SQL Server tempdb の Files とトレース フラグと更新

サイズが等しい 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 を実装して競合を減らす方法

Note

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

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

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

関連情報