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

文書翻訳 文書翻訳
文書番号: 328551 - 対象製品
すべて展開する | すべて折りたたむ

現象


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 - 最終更新日: 2006年8月2日 - リビジョン: 4.1
この資料は以下の製品について記述したものです。
  • Microsoft SQL Server 2000 Standard Edition
キーワード:?
kbsqlserv2000presp4fix kbfix kbbug KB328551
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com