現象
tempdb データベースが頻繁に使用されている場合、SQL Server はページを割り当てようとするときに競合が発生する可能性があります。
sysprocesses システム テーブルの出力から、waitresource は "2:1:1" (PFS ページ) または "2:1:3" (SGAM ページ) として表示される場合があります。 競合の程度によっては、これにより SQL Server が短時間応答しないように見える場合もあります。
これらの操作では tempdb が多用されます。
- テンポラリ テーブル (ローカルまたはグローバル) の作成と削除を繰り返します。
- ストレージ目的で tempdb を使用するテーブル変数。
- CURORS に関連付けられたワーク テーブル。
- ORDER BY 句に関連付けられているワーク テーブル。
- GROUP BY 句に関連付けられているワーク テーブル。
- HASH プランに関連付けられている作業ファイル。
これらのアクティビティを大量かつ大量に使用すると、競合の問題が発生する可能性があります。
原因
オブジェクトの作成中に、混合エクステントから 2 ページを割り当て、新しいオブジェクトに割り当てる必要があります。 1 ページ目はインデックス割り当てマップ (IAM) 用で、2 ページ目はオブジェクトの最初のページ用です。 SQL Server では、Shared Global Allocation Map (SGAM) ページを使用して、混在したエクステントが追跡されます。 各 SGAM ページでは、約 4 GB のデータが追跡されます。
混在エクステントからのページの割り当ての一環として、SQL Server はページ空き領域 (PFS) ページをスキャンして、空き割り当て可能な混在ページを見つける必要があります。 PFS ページはすべてのページで使用可能な空き領域を追跡し、各 PFS ページは約 8000 ページを追跡します。 PFS ページと SGAM ページを変更するために、適切な同期が維持されます。これにより、他の修飾子が短時間ストールする可能性があります。
SQL Server は、割り当てる混合ページを検索するときに、常に同じファイルと SGAM ページでスキャンを開始します。 このため、複数の混合ページ割り当てが行われているときに SGAM ページで激しい競合が発生し、この資料の「現象」に記載されている問題が発生する可能性があります。
注: 割り当て解除アクティビティではページも変更する必要があります。これにより、競合が大きくなる原因となります。
SQL Server で使用されるさまざまな割り当てメカニズム (SGAM、GAM、PFS、IAM) の詳細については、この資料の「関連情報」を参照してください。
解決策
Microsoft SQL Server 2000
頻繁に使用されている tempdb の割り当てリソース競合を減らすには、次のすべての手順を実行します。
Microsoft SQL Server 2000 Service Pack 4 を適用します。 SQL Server 2000 Service Pack 4 (SP4) は、次のマイクロソフト Web サイトから入手できます。
https://www.microsoft.com/download/details.aspx?familyid=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5
関連情報を参照するには、以下の「サポート技術情報」 (Microsoft サポート技術情報) をクリックしてください。
290211 最新の SQL Server 2000 Service Pack を入手する方法
トレース フラグ -T1118 を実装します。
tempdb のデータ ファイルの数を増やしてディスク帯域幅を最大化し、割り当て構造の競合を減らします。 原則として、論理プロセッサの数が 8 未満または 8 等しい場合は、論理プロセッサと同じ数のデータ ファイルを使用します。 論理プロセッサの数が 8 を超える場合は、8 つのデータ ファイルを使用します。競合が続く場合は、競合が許容レベルまで減少するまで、データ ファイルの数を 4 の倍数 (論理プロセッサの数まで) 増やすか、ワークロード/コードに変更を加えます。
注: これらの手順は Microsoft SQL Server 7.0 にも適用されます。 唯一の例外は、SQL Server 7.0 の修正プログラムがないため、手順 1 は適用されません。
手順 2、Microsoft SQL Server 7.0 でのトレース フラグ -T1118 の使用に関しては、トレース フラグを使用する前に、マイクロソフト サポート技術情報の次の記事を参照してください。
813492 修正: トレース フラグ 1118 が有効な場合、SQL Server 7.0 でインデックスの作成が失敗する
Microsoft SQL Server 2005 以降のバージョン
使用率が高い tempdb の割り当てリソース競合を減らすには、次の手順を実行します。
- トレース フラグ -T1118 を実装します。
- tempdb のデータ ファイルの数を増やしてディスク帯域幅を最大化し、割り当て構造の競合を減らします。 原則として、論理プロセッサの数が 8 以下の場合は、論理プロセッサと同じ数のデータ ファイルを使用します。 論理プロセッサの数が 8 を超える場合は、8 つのデータ ファイルを使用します。競合が続く場合は、競合が許容レベルまで減少するまで、データ ファイルの数を 4 の倍数 (論理プロセッサの数まで) 増やすか、ワークロード/コードに変更を加えます。
追加情報
SQL 2000 SP4 以降のバージョンの修正プログラムによって競合が減少する方法
SQL Server 2000 SP4 以降のバージョンでは、混合ページ割り当てにラウンド ロビン アルゴリズムを導入する修正プログラムがあります。 修正により、開始ファイルは、連続する混合ページ割り当てごとに異なるようになりました (複数のファイルが存在する場合)。 これにより、SGAM を毎回同じ順序で同じ始点で通過した列車を分割することで、競合の問題を回避できます。 SGAM の新しい割り当てアルゴリズムは純粋なラウンドロビンであり、速度を維持するために比例塗りつぶしを優先しません。 同じサイズの tempdb データ ファイルを作成することをお勧めします。
トレース フラグ -T1118 の実装による競合の軽減方法
-T1118 を使用して競合を減らす方法の一覧を次に示します。
-T1118 はサーバー全体の設定です。
SQL Server がリサイクルされた後もトレース フラグが有効になるように、SQL Server のスタートアップ パラメーターに -T1118 トレース フラグを含めます。
-T1118 は、サーバー上のほぼすべての単一ページ割り当てを削除します。
単一ページの割り当てのほとんどを無効にすることで、SGAM ページの競合を減らすことができます。
-T1118 が有効になっていると、ほとんどすべての新しい割り当てが、オブジェクトに一度に 8 ページ (1 エクステント) を割り当てる GAM ページ (たとえば 2:1:2) から実行されます。それに対して、オブジェクトの最初の 8 ページについてはエクステントから 1 ページずつ割り当てます (トレース フラグなし)。
IAMページは、-T1118がオンになっている場合でも、SGAMページからの単一ページ割り振りを引き続き使用します。 ただし、修正プログラム 8.00.0702 と組み合わせて tempdb データ ファイルを増やした場合、正味の効果は SGAM ページでの競合を減らすことです。 スペースに関する問題については、この記事の「デメリット」を参照してください。
tempdb データ ファイルの数を等しいサイズで増やす
tempdb のデータ ファイル サイズが 5 GB で、ログ ファイルのサイズが 5 GB の場合、1 つのデータファイルを 10 (サイズを等しい状態に保つためにそれぞれ 500 MB) に増やし、ログ ファイルをそのままにしておくことをお勧めします。 異なるデータ ファイルを別々のディスクに配置すると便利です。 ただし、これは必須ではなく、同じディスク上で共存できます。
tempdb データ ファイルの最適な数は、tempdb に表示される競合の程度によって異なります。 開始点として、少なくとも SQL Server に割り当てられているプロセッサの数と等しくなるように tempdb を構成できます。 ハイエンド システム (16 や 32 プロセッサなど) の場合、開始番号は 10 である可能性があります。 競合が減らない場合は、データ ファイルの数をさらに増やさなければならない場合があります。
注: デュアルコア プロセッサは 2 つのプロセッサと見なされます。
比例塗りつぶしアルゴリズムはファイルのサイズに基づいているため、データ ファイルのサイズを均等にすることが重要です。 サイズが等しくないデータ ファイルが作成された場合、比例塗りつぶしアルゴリズムは、すべてのファイルに割り当てを分散するのではなく、GAM 割り当てに最大のファイルを使用しようとするため、複数のデータ ファイルを作成する目的が失われます。
tempdb データ ファイルの自動拡張も、比例塗りつぶしアルゴリズムに干渉する可能性があります。 したがって、tempdb データ ファイルの自動拡張機能を無効にすることをお勧めします。 自動拡張オプションがオフになっている場合は、サーバーで 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 ページのリソース競合が減少します。
欠点
前述の推奨事項の唯一の欠点は、次の条件に該当する場合にデータベースのサイズが大きくなる可能性があることです。
- ユーザー データベースに新しいオブジェクトが作成されます。
- 新しいオブジェクトの保存スペースはそれぞれ 64 KB 未満です。
これらの条件が当てはまる場合、領域が 8 KB のみを必要とするオブジェクトに 64 KB (8 ページ × 8 KB = 64 KB) を割り当てる可能性があり、そのため 56 KB のストレージが無駄になります。 ただし、新しいオブジェクトの有効期間で 64 KB (8 ページ) を超えて使用する場合、トレース フラグの利点はありません。 したがって、最悪のシナリオでは、SQL Server は、1 ページを超えることのない新しいオブジェクトに対してのみ、最初の割り当て中に 7 つの追加ページを割り当てることになる可能性があります。
参考資料
GAM、SGAM、PFS、IAM の詳細については、以下の SQL Server 2000 Books Online のトピックを参照してください。
- "オブジェクトが使用する領域の管理"
- 「エクステントの割り当てと空き領域の管理」
- "テーブルとインデックスのアーキテクチャ"
- "ヒープ構造体"
関連情報
SQL Server 2005 の tempdb データベースの詳細については、次の MSDN Web サイトを参照してください。
https://technet.microsoft.com/library/cc966545.aspx
tempdb データベース ファイルとトレース エラー 1118 の詳細については、次の MSDN Web サイトを参照してください。
SQL Server 2005 および SQL Server 2008 でトレース フラグ 1118 を使用する方法の詳細については、次の MSDN Web サイトを参照してください。
tempdb データベースでの割り当てのボトルネックを監視およびトラブルシューティングする方法の詳細については、次の MSDN Web サイトを参照してください。