現象
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 つの (2) ページを割り当て、新しいオブジェクトに割り当てる必要があります。 1 つのページはインデックス割り当てマップ (IAM) 用で、2 番目のページはオブジェクトの最初のページ用です。 SQL Server共有グローバル割り当てマップ (SGAM) ページを使用して、混合エクステントを追跡します。 各 SGAM ページは、約 4 ギガバイトのデータを追跡します。
混合エクステントからのページの割り当ての一環として、SQL Serverはページ空き領域 (PFS) ページをスキャンして、どの混合ページが自由に割り当てられるかを調べる必要があります。 PFS ページは、すべてのページで使用可能な空き領域を追跡し、各 PFS ページは約 8,000 ページを追跡します。 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) は、次の Microsoft Web サイトで入手できます。
http://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 を使用します。トレース フラグを使用する前に、Microsoft サポート技術情報の次の記事を参照してください。
813492 FIX: トレース フラグ 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 の新しい割り当てアルゴリズムは純粋なラウンド ロビンであり、速度を維持するために比例フィルを考慮しません。 Microsoft では、同じサイズの tempdb データ ファイルを作成することをお勧めします。
トレース フラグ -T1118 を実装すると競合が軽減される方法
-T1118 を使用して競合を軽減する方法の一覧を次に示します。
-T1118 はサーバー全体の設定です。
SQL Serverがリサイクルされた後もトレース フラグが有効になるように、SQL Serverのスタートアップ パラメーターに -T1118 トレース フラグを含めます。
-T1118 は、サーバー上のほぼすべての単一ページ割り当てを削除します。
ほとんどの単一ページ割り当てを無効にすると、SGAM ページでの競合を減らすことができます。
-T1118 がオンの場合、ほぼすべての新しい割り当ては、オブジェクトの最初の 8 ページ (8) ページの範囲から 1 ページではなく、オブジェクトに一度に 8 ページ (1 エクステント) を割り当てる GAM ページ (2:1:2 など) から実行されます。トレース フラグはありません。
-T1118 がオンになっている場合でも、IAM ページでは SGAM ページからの単一ページ割り当てが引き続き使用されます。 ただし、修正プログラム 8.00.0702 と組み合わせて tempdb データ ファイルを増やすと、SGAM ページでの競合が減少します。 領域の問題については、この記事の「欠点」セクションを参照してください。
同じサイズ設定で tempdb データ ファイルの数を増やす
tempdb のデータ ファイル サイズが 5 GB で、ログ ファイルのサイズが 5 GB の場合は、単一のデータ ファイルを 10 に増やし (サイズを同じサイズに保つには 500 MB ずつ)、ログ ファイルをそのままにすることをお勧めします。 別のディスクに異なるデータ ファイルを配置することをお勧めします。 ただし、これは必須ではなく、同じディスク上に共存できます。
tempdb データ ファイルの最適な数は、tempdb で見られる競合の程度によって異なります。 開始点として、tempdb を少なくともSQL Serverに割り当てられたプロセッサの数と同じになるように構成できます。 上位のシステム (16 または 32 プロシージャなど) の場合、開始番号は 10 です。 競合が軽減されない場合は、データ ファイルの数をさらに増やす必要があります。
注 デュアル コア プロセッサは、2 つのプロセッサと見なされます。
比例フィル アルゴリズムはファイルのサイズに基づいているため、データ ファイルのサイズが同じであることが重要です。 等しくないサイズのデータ ファイルが作成された場合、比例フィル アルゴリズムは、すべてのファイル間で割り当てを分散するのではなく、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 ページ) を超える値を使用する場合、トレース フラグに欠点はありません。 したがって、最悪のシナリオでは、SQL Serverは、1 ページを超えない新しいオブジェクトに対してのみ、最初の割り当て中に 7 ページ (7) の追加ページを割り当てる可能性があります。
参考資料
GAM、SGAM、PFS、IAM の詳細については、次の SQL Server 2000 オンライン ブックトピックを参照してください。
- "オブジェクトで使用される領域の管理"
- "エクステント割り当てと空き領域の管理"
- "テーブルとインデックスのアーキテクチャ"
- "ヒープ構造体"
関連情報
SQL Server 2005 の tempdb データベースの詳細については、次の MSDN Web サイトを参照してください。
http://technet.microsoft.com/en-us/library/cc966545.aspx
tempdb データベース ファイルとトレース Tlag 1118 の詳細については、次の MSDN Web サイトを参照してください。
SQL Server 2005 および SQL Server 2008 でトレース フラグ 1118 を使用する方法の詳細については、次の MSDN Web サイトを参照してください。
tempdb データベースの割り当てボトルネックを監視およびトラブルシューティングする方法の詳細については、次の MSDN Web サイトを参照してください。