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

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

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

現象

サーバーを実行しているサーバー Microsoft SQL Server負荷が大きかったときに重大なブロックが発生します。 動的管理ビュー [ または ] は、これらの要求またはタスクが tempdb リソースを待 sys.dm_exec_request sys.dm_os_waiting_tasks つことを示 します。 さらに、待機の種類は PAGELATCH_UP 、tempdb のページを待機リソースが ポイントします。 これらのページの形式は 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 ページは約 8000 ページを追跡します。 PFS ページと SGAM ページを変更するために適切な同期が維持されます。また、他の修飾子が短期間停止する可能性があります。

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

注意

割り当て取りを行うアクティビティもページを変更する必要があります。 これにより、コンテンツの増加に寄与する可能性があります。

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

解決方法

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

    レビュー

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

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

    tempdb の同時実行を改善するには、次の方法を試してください。

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

    • 「2005年の tempdb の操作」のベスト プラクティスSQL Server検討してください。

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

      注意

      • このトレース フラグは、データベース のインスタンス上のすべてのデータベースにSQL Server。 割り当ての不一部が SGAM ページ上にあるかどうかを判断する方法については 、「DML操作によって引き起こされた監視の問題」を参照してください。

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

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

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

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

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

tempdb データ ファイル の最適な 数は、tempdb で見られるコンテンツの程度 によって異なります。 開始点として 、tempdb を 1 つのプロセッサに割り当てられている論理プロセッサの数と少なくとも同じ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 などです。

  • 一度に 8 ページが FULL とマークされているのは GAM がページを割り当てるため、PFS ページのリソースのコンテンツが減少します。

トレース フラグ -T1118 を実装すると、どのように問題が軽減されるのか

注意

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

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

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

デメリット

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

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

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

関連情報