現在オフラインです。再接続するためにインターネットの接続を待っています

[FIX] tempdb データベースの同時実行制御の強化

現象

tempdb データベースを頻繁に使用している場合、SQL Server でページの割り当て時に競合が発生することがあります。

sysprocesses システム テーブルの出力では、waitresource が "2:1:1" (PFS ページ) または "2:1:3" (SGAM ページ) として表示されることがあります。競合の度合いによっては、少しの間 SQL Server が応答していないように見えることもあります。


以下の操作では、tempdb が頻繁に使用されます。
  • 繰り返し行われる (ローカルまたはグローバルの) 一時テーブルの作成および削除
  • データの保存に tempdb を使用するテーブル変数
  • CURSORS と関連付けられている作業テーブル
  • ORDER BY 句と関連付けられている作業テーブル
  • GROUP BY 句と関連付けられている作業テーブル
  • HASH PLANS と関連付けられている作業ファイル

これらの操作を頻繁に使用すると、競合の問題が発生することがあります。
原因
オブジェクトの作成中に、新しいオブジェクトに混合エクステントから 2 ページを割り当てる必要があります。1 ページは IAM (Index Allocation Map) 用で、もう 1 ページはオブジェクトの最初のページ用です。SQL Server では、混合エクステントの管理に SGAM (Shared Global Allocation Map) ページを使用します。各 SGAM ページでは、約 4 GB のデータが管理されます。

混合エクステントからページを割り当てる処理の過程で、SQL Server では、PFS (Page Free Space) ページをスキャンして、割り当て可能な未使用の混合ページを検索する必要があります。PFS ページには、混合エクステントの各ページで利用できる空き領域が記録されます。PFS 1 ページあたり約 8000 ページが対象となります。PFS ページと SGAM ページに変更を加える際には適切な同期処理が行われますが、この同期処理により、他の変更操作が短期間停止される場合があります。

割り当てる混合ページを検索するとき、SQL Server では、常に、同じファイルと SGAM ページでスキャンが開始されます。この仕様が原因で、複数の混合ページの割り当て中に SGAM ページで競合が頻繁に発生し、その結果、この資料の「現象」に記載した問題が発生することがあります。

: 割り当ての解除操作でもページに変更が加えられるため、競合が増加する可能性があります。

SQL Server で使用されるさまざまな割り当てメカニズム (SGAM、GAM、PFS、IAM) の詳細については、この資料の「関連情報」を参照してください。

詳細
修正プログラム 8.00.0702 により競合が軽減されるしくみ

修正プログラム 8.00.0702 では、混合ページの割り当てにラウンド ロビン アルゴリズムが導入されています。この修正プログラムを使用すると、連続する (ファイルが複数存在する場合) 混合ページの割り当てごとに、スキャンが開始されるファイルが異なるようになります。その結果、常に SGAM ファイルの同じ場所から同じ順序でスキャンが開始されていた処理の連鎖が断ち切られ、競合問題が回避されます。SGAM の新しい割り当てアルゴリズムは、純粋なラウンド ロビン アルゴリズムであるため、アルゴリズムをファイルの空き領域に比例させ、検索速度を維持するということを行いません。マイクロソフトでは同じサイズに設定した tempdb データ ファイルを作成することをお勧めします。

トレース フラグ -T1118 を実装することにより競合が軽減されるしくみ

-T1118 の使用により競合が軽減されるしくみは以下のとおりです。
  • -T1118 を設定すると、サーバー全体が影響を受けます。
  • SQL Server の起動時のパラメータに -T1118 トレース フラグを含めると、SQL Server をリサイクルした後でもトレース フラグが有効のままになります。
  • -T1118 を実装すると、サーバー上にあるほとんどすべての単一ページの割り当てが削除されます。
  • 大部分の単一ページの割り当てを無効にすることで、SGAM ページの競合を軽減できます。
  • -T1118 をオンにすると、ほとんどすべての新しい割り当てが GAM ページ (たとえば、2:1:2) から実行され、オブジェクトには、一度に 8 ページ (1 エクステント) が割り当てられます。このトレース フラグを使用しない場合は、オブジェクトの最初の 8 ページに対して 1 つのエクステントから単一ページが割り当てられます。
  • -T1118 をオンにした場合でも、IAM ページでは引き続き単一ページが SGAM ページから割り当てられます。ただし、修正プログラム 8.00.0702 を適用して、さらに tempdb データ ファイルの数を増やすと、SGAM ページでの競合を軽減できます。この資料の「データベースのサイズに関する考慮事項」を参照してください。

同じサイズに設定した tempdb データ ファイル数を増やす

tempdb のデータ ファイル サイズが 5 GB で、ログ ファイル サイズが 5 GB の場合、データ ファイル数を 10 個まで増やし (各ファイルは 500 MB に固定してサイズを同一に保ちます)、ログ ファイルはそのままにしておくことをお勧めします。データ ファイルを複数のディスクに分割することは有効ですが、それは必須ではありません。すべてのデータ ファイルを同じディスクに共存させることができます。

tempdb データ ファイルの最適数は、tempdb で発生する競合の度合いによって異なります。最初は、少なくとも SQL Server に割り当てられているプロセッサと同数の tempdb データ ファイルを構成できます。高性能なハイエンド システム (たとえば、16 または 32 プロセッサ) の場合、tempdb データ ファイル数は 10 個から設定を開始できます。競合が軽減されないときは、場合によっては、データ ファイル数を増やす必要があります。

SQL Server では、1 つのファイル グループ内のすべてのファイルに各ファイルの空き容量に比例した量のデータを書き込むアルゴリズム (proportional fill algorithm) が使用されているため、データ ファイルを同じサイズにすることが重要です。サイズの異なるデータ ファイルを作成すると、割り当てはすべてのファイルに分散されず、GAM 割り当てには、最も大きなサイズのファイルが使用されます。その結果、複数のデータ ファイルを作成した意味がなくなります。

また、このアルゴリズムは、tempdb データ ファイルの自動拡大の影響を受けるため、tempdb データ ファイルの自動拡大機能を無効にする方法もあります。ただし、自動拡大機能を無効にした場合は、サーバーで tempdb のディスク領域が不足することがないように、十分な容量のデータ ファイルを作成する必要があります。

同じサイズに設定した tempdb データ ファイル数を増やすことにより競合が軽減されるしくみ

同じサイズに設定した tempdb データ ファイル数を増やすことにより、競合が軽減されるしくみは以下のとおりです。
  • tempdb データ ファイルが 1 つの場合、保持できるのは、領域がそれぞれ 4 GB の 1 つの GAM ページと 1 つの SGAM ページのみです。
  • 同じサイズに設定した tempdb データ ファイルの数を増やすと、データ ファイルごとに 1 つ以上の GAM ページと SGAM ページが効率的に作成されます。
  • GAM の割り当てアルゴリズムでは、ファイルの空き容量に比例しながら、ファイルの番号からラウンド ロビン方式で一度に 1 つのエクステント (連続する 8 ページ) が割り当てられます。つまり、同じサイズのファイルが 10 個ある場合、最初の割り当ては File1 から行われ、2 番目の割り当ては File2 から、3 番目の割り当ては File3 からというように行われます。
  • GAM によるページの割り当て中は一度に 8 ページが FULL としてマークされるため、PFS ページのリソース競合は軽減されます。

データベースのサイズに関する考慮事項

このトレース フラグを使用する場合の唯一の問題点は、以下の条件に該当する場合に、データベース全体のサイズが大きくなる可能性があることです。
  • ユーザー データベースに新しいオブジェクトが作成される場合
  • 新しいオブジェクトのそれぞれが占有する記憶容量が 64 KB 未満の場合

これらの条件に該当する場合、8 KB の領域しか必要としないオブジェクトに 64 KB (8 ページ * 8 KB = 64 KB) が割り当てられ、56 KB の記憶領域が無駄になる可能性があります。新しいオブジェクトがその存続期間中に 64 KB (8 ページ) 以上を使用するのであれば問題はありませんが、最悪の場合、1 ページを超えることのない新しいオブジェクトに 8 ページが割り当てられ、最終的に 7 ページが未使用のまま残ることがあります。
関連情報
GAM、SGAM、PFS、および IAM の詳細については、SQL Server Books Online のトピック、「オブジェクトに使用されている領域の管理」、「エクステント割り当てと空き領域の管理」、「テーブルとインデックスのアーキテクチャ」、「ヒープ構造」を参照してください。
関連情報
この資料は米国 Microsoft Corporation から提供されている Knowledge Base の Article ID 328551 (最終更新日 2003-05-20) を基に作成したものです。
プロパティ

文書番号:328551 - 最終更新日: 08/02/2006 01:28:37 - リビジョン: 4.1

  • Microsoft SQL Server 2000 Standard Edition
  • kbsqlserv2000presp4fix kbfix kbbug KB328551
フィードバック