• [アーティクル]

フルテキスト インデックスのパフォーマンスのチューニングと最適化

フルテキスト インデックス作成とフルテキスト クエリのパフォーマンスは、メモリ、ディスク速度、CPU 速度、コンピュータのアーキテクチャなどのハードウェア リソースの影響を受けます。フルテキスト インデックス作成のパフォーマンス低下の主な原因となるのは、ハードウェア リソースの制限です。

  • フィルタ デーモン ホスト プロセス (fdhost.exe) または SQL Server プロセス (sqlservr.exe) の CPU 使用率が 100% に近くなっている場合は、CPU がボトルネックになっています。

  • ディスク待ちのキューの長さが平均でディスク ヘッド数の 2 倍を超えている場合は、ディスクがボトルネックになっています。この場合の主な回避策は、作成するフルテキスト カタログを SQL Server のデータベース ファイルやログから切り離し、ログ、データベース ファイル、およびフルテキスト カタログを別々のディスクに配置することです。その他、高速なディスクを購入したり RAID を使用することも、インデックス作成のパフォーマンス向上に役立ちます。

  • 物理メモリが不足している場合 (3 GB 以下) は、メモリがボトルネックになっている可能性があります。物理メモリ上の制限は、すべてのシステムで発生する可能性があります。32 ビット システムでは、仮想メモリの不足が原因でフルテキスト インデックス作成に時間がかかることがあります。

    注意注意

    SQL Server 2008 以降の Full-Text Engine は sqlservr.exe の一部となったため、AWE メモリを使用できます。

システムにハードウェアのボトルネックがない場合、フルテキスト検索のインデックス作成パフォーマンスは、主に以下の条件に左右されます。

  • SQL Server によるフルテキスト バッチの作成にかかる時間

  • フィルタ デーモンがバッチを処理する速度

注意注意

増分、手動、および自動の変更追跡による作成は、完全作成とは違って、ハードウェア リソースを最大限に活用して処理を高速化するようには作られていません。このため、これらのチューニングのヒントでは、フルテキスト インデックス作成のパフォーマンスを強化できない場合があります。

作成が完了すると、最終的なマージ プロセスが起動され、インデックス フラグメントが 1 つのマスタ フルテキスト インデックスにマージされます。これにより、多数のインデックス フラグメントではなく、1 つのマスタ インデックスのみを照会すれば済むため、クエリのパフォーマンスが向上し、関連順位付けにもより的確なスコア (評価) 統計を適用できます。マスタのマージ処理では、インデックス フラグメントをマージする際に大量のデータを読み書きする必要があるため、大量の I/O が発生しますが、クエリの着信がブロックされることはありません。

重要な注意事項重要

マスタ マージで大量のデータを処理すると、実行時間が長いトランザクションが発生し、チェックポイント時のログの切り捨てが遅れる場合があります。この場合、完全復旧モデルでは、トランザクション ログが非常に大きくなることがあります。完全復旧モデルを使用するデータベースで大きなフルテキスト インデックスを再編成する前に、実行時間が長いトランザクションのための十分な領域をトランザクション ログに割り当てることをお勧めします。詳細については、「トランザクション ログ ファイルのサイズの管理」を参照してください。

フルテキスト インデックスのパフォーマンスのチューニング

フルテキスト インデックスのパフォーマンスを最適化するには、次に示すベスト プラクティスを実装します。

  • すべてのプロセッサまたはコアを最大限に使用するには、sp_configure 'max full-text crawl ranges' をシステム上の CPU の数に設定します。この構成オプションの詳細については、「max full-text crawl range オプション」を参照してください。

  • ベース テーブルにクラスタ化インデックスがあることを確認します。クラスタ化インデックスの最初の列には整数データ型を使用します。GUID は使用しないようにしてください。クラスタ化インデックスで複数の範囲の作成を使用すると、作成速度を最大限に高めることができます。フルテキスト キーとして機能する列は整数データ型にすることをお勧めします。

  • UPDATE STATISTICS ステートメントを使用してベース テーブルの統計を更新します。さらに重要な点は、クラスタ化インデックスの統計や完全作成のフルテキスト キーを更新することです。これにより、複数の範囲の作成によってテーブルに適切なパーティションが生成されるようになります。

  • 増分作成のパフォーマンスを強化するには、timestamp 列のセカンダリ インデックスを作成します。

  • 大型のマルチ CPU コンピュータ上で完全作成を実行する前に、fdhost.exe プロセスおよびオペレーティング システムが使用するメモリを十分に確保するために、max server memory 値を設定してバッファ プールのサイズを一時的に制限することをお勧めします。詳細については、このトピックの後の「フィルタ デーモン ホスト プロセス (fdhost.exe) のメモリ要件の推定」を参照してください。

完全作成のパフォーマンスに関する問題のトラブルシューティング

パフォーマンスの問題を診断するには、フルテキスト クロール ログを調べます。クロール ログの詳細については、「フルテキスト作成 (クロール) で発生したエラーのトラブルシューティング」を参照してください。

完全作成のパフォーマンスが不十分な場合は、次の順序でトラブルシューティングを行うことをお勧めします。

物理メモリの使用量

フルテキスト作成時は、fdhost.exe または sqlservr.exe がメモリ不足またはメモリ枯渇の状態で実行される可能性があります。フルテキスト クロールのログを確認した結果、fdhost.exe が頻繁に再起動されているか、エラー コード 8007008 が返されていることが判明した場合は、これらのプロセスのいずれかでメモリ不足が生じています。特に大型のマルチ CPU コンピュータ上で fdhost.exe がダンプを生成している場合、メモリが不足してきている可能性があります。

注意注意

フルテキスト クロールで使用されるメモリ バッファに関する情報を取得する方法については、「sys.dm_fts_memory_buffers (Transact-SQL)」を参照してください。

次のような原因が考えられます。

  • 完全作成時に使用可能な物理メモリの量がゼロの場合、システム上の物理メモリのほとんどを SQL Server バッファ プールが消費している可能性があります。

    sqlservr.exe プロセスは、構成されている最大サーバー メモリ量に達するまで、バッファ プールで使用できるすべてのメモリを獲得しようとします。max server memory の割り当てが大きすぎる場合は、fdhost.exe プロセスのメモリ不足や共有メモリの割り当ての失敗が発生することがあります。

    注意注意

    64 ウェイ IA64 コンピュータなどのマルチ CPU コンピュータ上でのフルテキスト作成時、fdhost.exe または sqlservr.exe との間でバッファ プール メモリの競合が発生する場合があります。その結果、共有メモリが不足すると、バッチの再試行、メモリ スラッシング、および fdhost.exe プロセスによるダンプが発生します。

    SQL Server バッファ プールの max server memory 値を適切に設定することにより、この問題を解決できます。詳細については、このトピックの後の「フィルタ デーモン ホスト プロセス (fdhost.exe) のメモリ要件の推定」を参照してください。フルテキスト インデックスの作成に使用されるバッチのサイズを小さくすると、有効な場合があります。

  • ページングの問題

    拡張が制限された小さなページ ファイルが使用されているシステムにおいてページ ファイルのサイズが不足した場合、fdhost.exe または sqlservr.exe でメモリ不足が発生します。

    クロールのログにメモリ関連の障害が見当たらない場合、過剰なページングが原因でパフォーマンスが低下していることが考えられます。

フィルタ デーモン ホスト プロセス (fdhost.exe) のメモリ要件の推定

fdhost.exe プロセスが作成のために必要とするメモリ量は、主に、プロセスが使用するフルテキスト クロール範囲の数、受信共有メモリ (ISM) のサイズ、および ISM インスタンスの最大数に依存します。

フィルタ デーモン ホストによって使用されるメモリ量 (バイト単位) は、次の式を使用して概算できます。

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

この式の変数の既定値は次のとおりです。

変数

既定値

number_of_crawl_ranges

CPU の数

ism_size

1 MB (x86 コンピュータの場合)

合計物理メモリにより、4 MB、8 MB、または 16 MB (x64 コンピュータの場合)

max_outstanding_isms

25 (x86 コンピュータの場合)

5 (x64 コンピュータの場合)

fdhost.exe のメモリ要件の推定方法に関するガイドラインを、以下の表に示します。この表の数式では次の値を使用します。

  • F: fdhost.exe に必要なメモリの推定値 (MB 単位)

  • T: システムで使用できる合計物理メモリ (MB 単位)

  • M: max server memory の最適な設定

重要な注意事項重要

数式に関する基本情報については、下記の 1、2、および 3 を参照してください。

プラットフォーム

fdhost.exe のメモリ要件の推定 (MB 単位)—F1

max server memory の計算式—M2

AWE が無効になっている x86

F=Number of crawl ranges* 50

M=minimum(T, 2000)–F– 500

AWE が有効になっている x86

F=Number of crawl ranges* 50

M=T–F– 500

x64 または IA643

F=Number of crawl ranges* 10 * 8

M=T–F– 500

1 複数の完全作成を実行中の場合は、それぞれの fdhost.exe のメモリ要件を、F1、F2 などのように個別に計算してください。その後、M を T**–** sigma**(Fi)** で計算してください。

2 500 MB は、システムの他のプロセスに必要なメモリの推定値です。システムで追加の作業を実行している場合、適宜この値を大きくします。

3ism_size は 8 MB と見なされます (x64 プラットフォームの場合)。

例 : fdhost.exe のメモリ要件の推定

この例は、8 GM の RAM と 4 つのデュアル コア プロセッサを搭載した AMD64 コンピュータを対象としています。最初の計算では、fdhost.exe に必要なメモリ (F) を推定します。クロール範囲の数は 8 です。

F = 8*10*8=640

次の計算では、最適な max server memory 値 (M) を算出します。このシステムで使用可能な合計物理メモリ (T) は 8192 MB です。

M = 8192-640-500=7052

例 : max server memory の設定

この例では、sp_configure ステートメントおよび RECONFIGURETransact-SQL ステートメントを使用して、前の例で計算した M の値 7052 を max server memory として設定します。

USE master;
GO
EXEC sp_configure 'max server memory', 7052;
GO
RECONFIGURE;
GO

max server memory 構成オプションを設定するには

CPU 消費率の低下を招く要因

平均 CPU 消費率が約 30% 未満になると、完全作成のパフォーマンスが低下すると考えられます。ここでは、CPU 消費率に影響するいくつかの要因について説明します。

  • 長いページ待機

    ページ待機時間が長いかどうかを調べるには、次の Transact-SQL ステートメントを実行します。

    Execute SELECT TOP 10 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;

    次の表で、主な待機の種類について説明します。

    待機の種類

    説明

    解決方法

    PAGEIO_LATCH_SH (_EX または _UP)

    IO がボトルネックとなっている可能性があります。この場合は通常、平均のディスク キューも長くなります。

    別のディスクの別のファイル グループにフルテキスト インデックスを移動すると、IO のボトルネックを軽減できる場合があります。

    PAGELATCH_EX (または _UP)

    複数のスレッドが同じデータベース ファイルへの書き込みを試行し、多数の競合が発生している可能性があります。

    フルテキスト インデックスが格納されているファイル グループにファイルを追加すると、このような競合を軽減できる場合があります。

    詳細については、「sys.dm_os_wait_stats (Transact-SQL)」を参照してください。

  • 非効率的なベース テーブル スキャン

    完全作成では、バッチを生成するためにベース テーブルをスキャンします。次のようなシナリオでは、このテーブル スキャンの効率が下がる可能性があります。

    • フルテキスト インデックスが作成される行外の列がベース テーブルに高い比率で含まれている場合、バッチ生成のためのベース テーブル スキャンがボトルネックとなることがあります。その場合、varchar(max) または nvarchar(max) を使用して、比較的小さなデータを行内に移動すると解決することがあります。

    • ベース テーブルが過度に断片化されていると、スキャンの効率が下がります。行外データの計算とインデックスの断片化の詳細については、「sys.dm_db_partition_stats (Transact-SQL)」および「sys.dm_db_index_physical_stats (Transact-SQL)」を参照してください。

      断片化を解消するには、クラスタ化インデックスを再構成または再構築します。詳細については、「インデックスの再編成と再構築」を参照してください。

変更履歴

変更内容

完全復旧モデルで大量のインデックス データに対するマスタ マージを準備するためのベスト プラクティスの説明を重要事項として追加しました。

大型のマルチ CPU コンピュータ上でマスタ マージを準備するためのベスト プラクティスを「フルテキスト インデックスのパフォーマンスのチューニング」セクションに追加しました。

「物理メモリの使用量」セクションを明確にして拡張しました。