• [アーティクル]

tempdb のパフォーマンスの最適化

tempdb データベースのサイズと物理的な配置場所は、システムのパフォーマンスに影響を与える可能性があります。たとえば、tempdb に定義されているサイズが小さすぎると、SQL Server のインスタンスを再起動するたびに、tempdb のサイズがワークロードのサポートに必要なサイズまで自動的に拡張されるので、システムの処理負荷の一部が占有されます。このオーバーヘッドは、tempdb データとログ ファイルのサイズを増やすことで回避できます。tempdb に必要とされるディスク領域の適切な量の決定については、「tempdb に使用するディスク領域の計画」を参照してください。

推奨される tempdb のサイズと配置場所

tempdb のパフォーマンスを最適にするには、運用環境で tempdb の構成を次のようにすることをお勧めします。

  • tempdb の復旧モデルを SIMPLE に設定します。このモデルでは、必要な領域が少なくなるように、ログ領域が自動的に再要求されます。

    詳細については、「ALTER DATABASE (Transact-SQL)」または「データベースの復旧モデルを表示または変更する方法 (SQL Server Management Studio)」を参照してください。

  • 必要に応じて、tempdb ファイルの自動拡張を許可します。これによって、ディスクがいっぱいになるまで、ファイルを拡張できるようになります。

    注意注

    自動拡張操作の間に発生する可能性があるアプリケーションのタイムアウトを許容できない運用環境の場合、予測されるワークロードを許容するための領域を事前に割り当てます。

  • tempdb データベース ファイルの拡張単位が小さすぎることのないように、ファイル拡張の増分値を妥当なサイズに設定します。tempdb に書き込まれたデータ量と比較してファイルの拡張単位が小さすぎると、tempdb を頻繁に拡張する必要が生じる場合があります。このことは、パフォーマンスに影響します。tempdb ファイルの FILEGROWTH 増分値の設定については、次の一般的なガイドラインに従うことをお勧めします。

    tempdb ファイル サイズ

    FILEGROWTH 増分値

    0 ～ 100 MB

    10 MB

    100 ～ 200 MB

    20 MB

    200 MB 以上

    10%*

    * tempdb ファイルが配置される I/O サブシステムの速度に基づいて、この比率の調整が必要になる場合があります。ラッチ タイムアウトが発生する可能性を回避するには、自動拡張操作を約 2 分までに制限することをお勧めします。たとえば、I/O サブシステムが 1 秒あたり 50 MB でファイルを初期化できる場合は、tempdb ファイルのサイズに関係なく、FILEGROWTH 増分値は最大 6 GB に設定する必要があります。可能な場合は、データベース ファイルの瞬時初期化を使用して、自動拡張操作のパフォーマンスを向上します。

  • すべての tempdb ファイルに対する領域をあらかじめ割り当てるには、環境における一般的なワークロードに十分に対応できる大きさの値にファイル サイズを設定します。これにより、パフォーマンスに影響を与える可能性がある tempdb の過度に頻繁な拡張が回避されます。tempdb データベースは自動拡張が行われるように設定する必要がありますが、これは想定外の例外に対してディスク領域を増加するために使用する必要があります。

  • ディスク帯域幅を最大化するために必要な数のファイルを作成します。複数のファイルを使用すると、tempdb ストレージの競合が削減され、その結果、スケーラビリティが大幅に向上します。ただし、作成するファイルが多くなりすぎないようにします。作成するファイルが多すぎると、パフォーマンスが低下し、管理のオーバーヘッドが増加します。通常は、サーバー上の各 CPU (affinity mask 設定の記載) に対して 1 つのデータ ファイルを作成し、必要に応じてファイル数の増減を調整します。デュアルコア CPU は 2 つの CPU と見なされることに注意してください。

  • 各データ ファイルは同じサイズにします。これによって、最適な比例配分のパフォーマンスになります。

  • 高速な I/O サブシステムに tempdb データベースを配置します。直接アタッチされたディスクが多数ある場合は、ディスク ストライピングを使用します。

  • ユーザー データベースによって使用されるディスクとは異なるディスクに tempdb データベースを配置します。

tempdb のサイズ パラメータと拡張パラメータの変更

次のいずれかの方法を使用して、tempdb データ ファイルまたはログ ファイルのサイズ パラメータとファイル拡張パラメータを変更できます。

tempdb が作成されるたびに、ファイル サイズ パラメータとファイル拡張パラメータの値が使用されます。たとえば、tempdb データ ファイルのサイズを 20 MB に増やし、ファイル拡張増分値を 15% に増やすと、新しい値はすぐに有効になります。後続のトランザクションの動作によって tempdb のサイズが拡張されると、SQL Server のインスタンスを再起動するたびに、データ ファイルのサイズは 20 MB に戻ります。

tempdb のサイズ パラメータと拡張パラメータの表示

次のいずれかの方法を使用して、tempdb データ ファイルまたはログ ファイルのサイズ パラメータとファイル拡張パラメータを表示できます。

  • SQL Server Management Studio

  • 次のクエリの実行。

    SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

ディスク I/O パスのエラーの検出

CHECKSUM に設定されている場合、PAGE_VERIFY オプションは、ディスク I/O パスのエラーが原因で破損したデータベース ページを検出し、MSSQLSERVER_823、MSSQLSERVER_824、または MSSQLSERVER_825 などのエラーを SQL エラー ログでレポートします。ディスク I/O パスのエラーは、データベースの損傷による問題の原因となる可能性があり、一般にはページがディスクに書き込まれている時点で発生した電源障害やディスクのハードウェア障害によって引き起こされます。I/O エラーの詳細については、「Microsoft SQL Server I/O Basics, Chapter 2」を参照してください。

SQL Server の以前のバージョンでは、PAGE_VERIFY データベース オプションは tempdb データベースについては NONE に設定されており、変更できません。SQL Server 2008 では、SQL Server の新規インストールに対する tempdb データベースの既定値は CHECKSUM です。インストール済みの SQL Server をアップグレードした場合、既定値は NONE のままです。tempdb データベースについては、PAGE_VERIFY オプションを CHECKSUM に設定することをお勧めします。