Tempdb データベースを SQL Server に割り当て時の競合を減らすための推奨事項

適用対象: SQL Server 2008 DeveloperSQL Server 2008 EnterpriseSQL Server 2008 Express

現象


Microsoft SQL Server を実行しているサーバーでは、サーバーに大きな負荷が発生している場合は、重大なブロックを確認します。(Sys.dm_exec_request または sys.dm_os_waiting_tasks) の動的管理ビューでは、これらの要求またはタスクがtempdbのリソースを待機していることを示します。また、の待機のタイプは、PAGELATCH_UP、およびtempdb内のページへの待機リソース ポイントです。形式の 2 分の 1 のこれらのページがあります: 1、2:1:3、および (PFS ページと SGAM ページのtempdb) のようにします
注: 8088 でページが割り切れない場合は、PFS ページがあります。ページ 2:3:905856 file_id の PFS は、たとえば、 tempdb内の 3 を = します。
次の操作は、 tempdbを広範囲に使用します。
  • (ローカルまたはグローバル) の一時テーブルの repetative の作成・ アンド ・ ドロップ操作です。
  • テーブル ストレージにtempdbを使用する変数です。
  • カーソルに関連付けられているテーブルを使用します。
  • ORDER BY 句に関連付けられているテーブルを使用します。
  • GROUP by 句に関連付けられているテーブルを使用します。
  • ハッシュの計画に関連付けられているファイルを使用します。
これらの活動の競合問題が発生する可能性があります。

原因


Tempdbデータベースが使用される頻度の高いと、SQL Server が競合が発生するページを割り当てるしようとします。競合の度合い、によってクエリと応答を簡単に説明するtempdbに関連する要求があります。
オブジェクトの作成時にページ 2 (2) 必要がある混合エクステントから割り当てられるし、新しいオブジェクトに割り当てられています。1 つのページは、インデックス アロケーション マップ (IAM) の 2 番目のオブジェクトの最初のページです。SQL Server では、共有グローバル割り当てマップ (SGAM) ページを使用して、混合エクステントを追跡します。各 SGAM ページでは、約 4 ギガバイトのデータを追跡します。SQL Server、混合エクステントからページを割り当てるを確認するに混在するページを自由に割り当てられるページ空き領域 (PFS) ページをスキャンする必要があります。PFS ページは、すべてのページで利用できる空き領域の追跡し、各 PFS ページは、約 8000 のページを追跡します。適切な同期は、PFS ページと SGAM ページを変更するのには短時間の他の修飾子を失速することができます。混合ページを割り当てることと、SQL Server の検索する際、常に SGAM ページと同じファイルのスキャンを開始しています。いくつかの混合ページの割り当ては、進行中であるときに SGAM ページで大量の競合が発生します。これにより、「現象」に記載されている問題が発生することができます。割り当ての解除操作でもページに変更する必要があります。これは、競合の増加に投稿できます。(SGAM、GAM、PFS では、IAM) の SQL Server で使用されている別の割り当てメカニズムの詳細については、「関連情報」セクションを参照してください

解決方法


Tempdb の同時実行性を高めるためには、次の方法を試してください。

  • ディスクの帯域幅を最大化し、構造体の割り当ての競合を減らすには、 tempdb内のデータ ファイルの数を増やします。原則として、論理プロセッサの数の論理プロセッサとしてファイル 8 (8) を使用してデータの同じ数に等しいかそれより小さい場合です。論理プロセッサの数は、8 (8)、8 つのデータ ファイルの使用を超えています。 場合、競合が解決しない場合、競合が許容レベルに減少するまで、4 個の論理プロセッサの数が最大の倍数単位でデータ ファイルの数を増やします。また、ワークロードまたはコードに変更を加えます。
  • TechNet の次のトピックで推奨されるベスト プラクティスを実装することを検討してください。
  • 前の手順は、割り当て時の競合を大幅に削減しないと、競合が SGAM ページには、トレース フラグを実装する--t1118 を実装。このトレース フラグを設定] の下は、SQL Server は、データベースの各オブジェクトにエクステントを完全に対し、SGAM ページの競合を回避を割り当てます。 メモ  
  • 2016 の SQL Server と 2017 を利用できる最新の更新プログラムの関連する CU を適用します。さらに 2016 の SQL Server と SQL Server の 2017 での競合を削減する改善が行われました。だけでなく、すべてのtempdbデータ ファイルの間でラウンド ロビン割り当ては、修正プログラムは、同じデータ ファイル内のいくつかの PFS ページの間でラウンド ロビン割り当てを実行することで PFS ページ割り当てを向上します。詳細については、次の内容を参照してください。

詳細


同じサイズの tempdb データ ファイルの数を増やす

たとえば、1 つのデータ ファイルのサイズの場合tempdbは、8 GB をログ ファイルのサイズは、2 GB をお勧めする 8 つのデータ ファイルの数を増やす (各と同じサイズを維持するために 1 GB)、(8) し、ログ ファイルが格納されます。別々 のディスク上の別のデータ ファイルは、追加のパフォーマンス上の利点と、します。ただし、これは必要ではありません。同じディスク ボリューム上のファイルは共存できます。、最適な数の tempdbデータ ファイルは、 tempdbに競合の度合いによって異なります。。、出発点としてtempdbを構成することができますには、少なくとも SQL Server に割り当てられている論理プロセッサの数と同じにするのにはです。ハイエンド ・ システムでは、開始番号は、8 (8)。競合は減少しませんが場合、は、データ ファイルの数を増やす必要がありますのデータ ・ ファイルと同じサイズを使用することをお勧めします。SQL Server 2000 Service Pack 4 (SP4) では、混合ページの割り当てをラウンド ロビン アルゴリズムを使用している修正プログラムが導入されています。この改善のため、開始ファイルは、(1 つ以上のファイルが存在する場合各連続の混合ページの割り当てのさまざまな。SGAM の新しい割り当てアルゴリズムは、純粋なラウンド ロビン方式し、比例速度を維持することを認めません。すべてを作成することをお勧めしますtempdbデータ ファイルを同じサイズにします。  

どのように競合が軽減される tempdb データ ファイルの数を増やす

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

トレース フラグを実装する方法--t1118 を実装には、競合が軽減されます。

次の一覧では、トレース フラグを使用する方法について説明--t1118 を実装の競合を削減:
  • --T1118 を実装は、サーバー全体の設定です。
  • 含む、 --t1118 を実装トレース フラグは SQL Server が再利用した後でも、有効にするために SQL Server のスタートアップ パラメーターのフラグをトレースします。
  • --T1118 を実装サーバーのほぼすべての単一ページの割り当てを削除します。
  • 単一ページの割り当てのほとんどを無効にすると、SGAM ページの競合を削減できます。
  • 場合--t1118 を実装がオンにすると、ほとんどすべての新しい割り当てが行われる GAM ページから (たとえば、2:1:2) が割り当てられます 8 ページ (1 エクステント) 時に、1 つのページではなく、オブジェクトをオブジェクトの最初 8 (8) ページの範囲から、トレース フラグを設定しません。
  • IAM ページが SGAM ページでは、単一ページの割り当てを使用場合でも--t1118 を実装になっています。ただし、修正プログラム 8.00.0702 と増加tempdbデータ ファイルと結合されて、最終的な結果は SGAM ページでの競合を削減することをします。サイズに関する考慮事項は、次のセクションを参照してください。
短所

使用することの欠点--t1118 を実装は次の条件に該当する場合、データベース サイズの増加を表示可能性があります。

  • ユーザー データベースでは、新しいオブジェクトが作成されます。
  • それぞれの新しいオブジェクトは、ストレージの 64 KB より小さいを占有します。

これらの条件が true の場合は、64 KB を割り当てることがあります (8 ページ * 8 KB = 64 KB) のみ 8 KB の空き容量を必要とするオブジェクトは、56 KB の記憶域のため浪費します。ただし、新しいオブジェクトは、64 KB 以上を使用している場合 (8 ページ)、有効期間内、トレース フラグの欠点はありません。したがって、最悪のシナリオでは、SQL Server の可能性がありますを割り当てる 7 (7) 他のページ 1 ページを超えることはありませんが新しいオブジェクトの最初の割り当て中に。

関連情報


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

ルールのソフトウェア ルール タイトル ルールの説明 ルールが評価される対象である製品バージョン
System Center Advisor SQL Server データベースの構成: tempdb データ ファイルの数がブロックを発生する可能性があります System Center のアドバイザーでは、tempdb データベース用に構成されているデータ ファイルの数を確認します。実行しているサーバーの 1 つだけがある場合、SQL Server は複数のプロセッサが生成された場合、このアラートを使用します。この資料の情報を確認し、tempdb データベースにデータ ファイルを追加します。 SQL Server 2008 SQL Server 2008 R2 SQL Server 2012