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

現象

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

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

Tempdbは、次の操作で広く使用されます。

• Repetative の一時テーブル (ローカルまたはグローバル) の作成とドロップの操作を行います。

• 記憶域でtempdbを使うテーブル変数。

• カーソルに関連付けられている作業テーブル。

• 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、IAM) で使用されるさまざまな割り当てメカニズムの詳細については、「参照」セクションを参照してください。

解決方法

Tempdb の同時実行性を向上させるには、次の方法を試してください。

• Tempdbでデータファイルの数を増やすと、ディスクの帯域幅が最大化され、アロケーション構造の競合が少なくなります。 一般的な規則として、論理プロセッサの数が 8 (8) 以下の場合は、論理プロセッサと同じ数のデータファイルを使用します。 論理プロセッサの数が 8 (8) を超える場合は、8個のデータファイルを使用します。 競合が継続する場合は、競合が許容されるレベルまで減少するまで、4 (4) の倍数でデータファイルの数を増やします。 または、ワークロードまたはコードに変更を加えます。

• 次の TechNet トピックでベストプラクティスの推奨事項を実施することを検討してください。

  SQL Server 2005 での tempdb の操作

• 上記の手順を実行しても、割り当ての競合を減らすことはできません。また、競合が SGAM ページで行われている場合は、trace flag -T1118を実装します。 このトレースフラグでは、SQL Server は各データベースオブジェクトに完全なエクステントを割り当てます。これにより、SGAM ページ上の競合を回避できます。 注意事項

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

  • SQL Server 2016 以降では、これらの構成変更の一部が自動的に行われるため、ユーザーの操作は必要ありません。 詳細については、次の CSS SQL Server エンジニアのブログ記事を参照してください。

    • SQL Server 2016 – TEMPDB とユーザーデータベースの T1117 と-T1118 の変更のみを実行します。

    • SQL Server 2016 –短時間で実行できます。 TEMPDB の自動構成

• 最新の更新プログラムを利用するには、SQL Server 2016 および2017に関連する CU を適用します。 SQL Server 2016 と SQL Server 2017 での競合を減らすために、改善が加えられました。 この修正プログラムでは、すべてのtempdbデータファイルにわたるラウンドロビンの割り当てに加えて、同じデータファイル内の複数の pfs ページでラウンドロビンの割り当てが実行され、pfs ページの割り当てが向上しています。 詳細については、次のコンテンツを参照してください。

  • Microsoft サポート技術情報: SQL Server 2014、2016、2017での4099472 PFS ページのラウンドロビンアルゴリズムの改善

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

詳細情報

参照情報

• 328551 Tempdb データベースの同時実行性の向上

• TempDB の監視とトラブルシューティング: 割り当てのボトルネック

• SQL Server での TempDB の管理: TempDB の構成

• SQL Server TempDB –ファイルの数–生の真理

• SQL Server (2005 および 2008) トレースフラグ 1118 (-T1118) の使用状況

SQL Server のインスタンスおよび SQL Server 製品のバージョンで、この条件を自動的にチェックする製品またはツールの詳細については、次の表を参照してください。