全文索引のパフォーマンス向上

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

本記事では、全文索引やクエリの性能低下の一般的な原因と、それを緩和する方法について説明します。

パフォーマンスの問題の一般的な原因

このセクションでは、全文索引を使用する際の一般的なパフォーマンス問題の原因について説明します。

ハードウェア リソースの問題

メモリ、ディスク速度、CPU速度、機械アーキテクチャなどのハードウェアリソースは、全文インデックス作成や全文クエリのパフォーマンスに影響を与えます。

ハードウェアのリソース制限により、全文インデックスの性能が低下します。

  • CPU。 フィルターデーモンホストプロセス(fdhost.exe)やSQL Serverプロセス(sqlservr.exe)によるCPU使用率がほぼ100%に達している場合、CPUがボトルネックとなります。

  • メモリ。 物理メモリの不足はボトルネックを引き起こすことがあります。

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

フルテキスト バッチ処理の問題

システムにハードウェアのボトルネックがなければ、全文検索のインデックス性能は主に以下の要因に依存します。

  • データベース エンジンが全文バッチを作成するのにかかる時間。

  • フィルターデーモンがどれだけ速くそのバッチを消費するか。

フルテキスト インデックスの作成の問題

  • 母集団の種類。 すべてが対象となる追跡とは異なり、増分追跡、手動追跡、自動変更追跡は、ハードウェアのリソースを最大限に活用して処理速度を向上させるようには設計されていません。 したがって、本記事のチューニング提案は、増分的、手動、または自動変更トラッキングの集団を用いる全文インデックス作成のパフォーマンス向上に寄与しない可能性があります。

  • マスター マージ。 母集団が完了すると、最終マージ処理でインデックス断片が一つの マスター 全文インデックスに統合されます。 このプロセスにより、複数のインデックスフラグメントではなく マスター インデックスのみを照会すればよいため、クエリ性能が向上します。 関連性ランキングには、より良いスコア統計が使われることもあります。 しかし、インデックスフラグメントをマージする際に大量のデータを書き込み読み取る必要があるため、マスターマージはI/O負荷がかかることがあります。 ただし、着信クエリはブロックされません。

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

フルテキスト インデックスのパフォーマンスの調整

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

  • すべてのCPUコアを最大限に活用するには、 max full-text crawl range をシステム上のコア数に変更してください。 詳細については、「 Server configuration: max full-text crawl range」をご覧ください。

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

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

  • 大規模なマルチコアコンピュータでフルピュレーションを行う前に、max server memoryプロセスやオペレーティングシステム用のメモリを残すためにfdhost.exe値を設定してバッファプールのサイズを一時的に制限してください。 詳細については、この記事の後半にある 「フィルターデーモンホストプロセスのメモリ要件の推定(fdhost.exe)」をご覧ください。

  • timestamp 列に基づいて増分作成を使用する場合は、timestamp 列にセカンダリ インデックスを構築し、増分作成のパフォーマンスを向上します。

フル ポピュレーションのパフォーマンスをトラブルシューティングする

フル人口でのパフォーマンス問題を解決するには、以下のセクションを参照してください。

フルテキスト クロール ログを確認する

パフォーマンスの問題を診断するために、全文クロールログを確認してください。

クロール時にエラーが発生すると、フルテキスト検索クロール ログ記録機能によってクロール ログが作成および保持されます。このログはプレーンテキスト ファイルです。 各クロール ログは特定のフルテキスト カタログに対応します。 既定では、所与のインスタンス (この例では、既定のインスタンス) のクロール ログは %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG フォルダーにあります。

クロール ログは次のような規則に従って命名されます。

SQLFT<DatabaseID><FullTextCatalogID>.[<n>]

クロール ログ ファイルの可変部分は次のようになります。

  • <DatabaseID>データベースのIDで、先頭にゼロがついた5桁の数字です。

  • <FullTextCatalogID>: 全文カタログID、先頭にゼロを付けた5桁の番号。

  • <n>: は、同じ全文カタログのクロールログが1つ以上存在することを示す整数です。

たとえば、SQLFT0000500008.2 はデータベース ID が 5 で、フルテキスト カタログ ID が 8 のクロール ログ ファイルです。 ファイル名の最後の 2 は、このデータベースとカタログのペアに 2 つのクロール ログ ファイルが存在することを示しています。

物理メモリの使用量を確認する

フルテキストの作成中は、fdhost.exe または sqlservr.exe プロセスでメモリが不足したり、場合によってはメモリを使い果たしたりすることがあります。

  • 全文クロールログで fdhost.exe が頻繁に再起動したりエラーコードが返8007008された場合、それはこれらのプロセスのいずれかがメモリ不足を起こしていることを意味します。

  • fdhost.exeがダンプを発生させる場合、特に大規模でマルチコアのシステムではメモリ不足かもしれません。

  • 全文クロールで使用されるメモリバッファについては、 sys.dm_fts_memory_buffersを参照してください。

メモリ不足やメモリ不足の問題の可能な原因には以下の項目があります。

  • 不十分なメモリ。 満員時に利用可能な物理メモリの量がゼロの場合、データベース エンジンバッファプールがシステムの物理メモリの大部分を消費している可能性があります。

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

    この問題を解決するために、データベース エンジンバッファプールのmax server memory値を適切に設定してください。 詳細については、この記事の後半にある 「フィルターデーモンホストプロセスのメモリ要件の推定(fdhost.exe)」をご覧ください。 全文索引に使うバッチサイズを減らすことも効果的かもしれません。

  • メモリの競合。 マルチコアシステム上の全文集団では、 fdhost.exe と sqlservr.exe がバッファプールメモリを争うことがあります。 その結果、共有メモリが失われると、 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 コンピューターの場合)

x64コンピュータでは、物理メモリの総容量に応じて4MB、8MB、または16MBとなります
max_outstanding_isms 25 (x86 コンピューターの場合)

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

以下の表は、 fdhost.exeのメモリ要件を見積もるためのガイドラインを示しています。 この表の数式では次の値を使用します。

  • Fは、必要なメモリの推定値で、 fdhost.exe (MB単位)です。

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

  • Mは最適な max server memory 設定です。

次の式の基本情報については、表の後の注意事項を参照してください。

プラットフォーム MBでのメモリ要件 fdhost.exe 推定: F^1 最大サーバーメモリの計算式: M^2
x86 F = クロール範囲の数 * 50 M = 最低(T, 2000) - F - 500
x64 F = クロール範囲の数 * 10 * 8 M = T - F - 500

  1. 複数のフル集団が進行中の場合は、それぞれのメモリ要件をfdhost.exe、F2など、それぞれのメモリ要件を個別に計算します。 次に M を T - Σ(Fi)として計算します。

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

  3. x64プラットフォームではism_size 8MBと想定されています。

例:のメモリ要件を推定します fdhost.exe

この例は、8GBのRAMと4つのデュアルコアプロセッサを持つ64ビットコンピュータです。 最初の計算は必要なメモリを fdhost.exeF で推定します。クロールレンジの数は 8です。

F = 8 * 10 * 8 = 640

次の計算では max server memory (M)の最適値を得ます。 このシステムで利用可能な物理メモリの総容量はMB(T)で 8192です。

M = 8192 - 640 - 500 = 7052

例: 設定 max server memory

この例では、sp_configure および RECONFIGURE Transact-SQL ステートメントを使用して、前の例で M に対して計算された値に max server memory を設定します7052:

USE master;
GO

EXECUTE sp_configure 'max server memory', 7052;
GO

RECONFIGURE;
GO

サーバーメモリオプションの詳細については、「 サーバーメモリ設定オプション」をご覧ください。

CPU 使用量を確認する

平均CPU消費率が約30%未満の場合、フル集団のパフォーマンスは最適とは言えません。 CPU 消費率に影響するいくつかの要因を次に示します。

  • ページの待機時間が長い

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

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

    以下の表では、重要な待機タイプについて説明します。

    待機の種類 説明 考えられる解決策
    PAGEIO_LATCH_SH (_EX または _UP) この待機タイプはI/Oのボトルネックを示す可能性があり、その場合通常は平均ディスクキューの長さが高いことになります。 全文インデックスを別のディスクのファイルグループに移動することで、I/Oのボトルネックを減らすのに役立つかもしれません。
    PAGELATCH_EX (または _UP) この待機タイプは、同じデータベースファイルに書き込みを試みるスレッド間で多くの競合がある可能性を示しています。 全文索引が存在するファイルグループにファイルを追加することで、このような競合を緩和できるかもしれません。

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

  • ベース テーブルのスキャンにおける非効率性

    完全作成では、バッチを生成するためにベース テーブルをスキャンします。 このテーブルスキャンは以下の状況では非効率になる可能性があります:

ドキュメントのインデックス作成が遅い問題のトラブルシューティング

注

ここでは、他のドキュメントの種類が埋め込まれているドキュメント (Microsoft Word 文書など) のインデックスを作成する場合にのみ影響する問題について説明します。

Full-Text Engine では、フルテキスト インデックスを作成するときに、マルチスレッド フィルターとシングル スレッド フィルターの 2 種類のフィルターを使用します。

  • Word文書のようにマルチスレッドフィルターを使用する文書もあります。
  • Adobe Acrobatポータブルドキュメントフォーマット(PDF)などの他のドキュメントはシングルスレッドフィルターを使用します。

セキュリティ上の理由から、フィルターはフィルター デーモン ホスト プロセスによって読み込まれます。 サーバー インスタンスでは、マルチスレッド フィルターに対してはすべてマルチスレッド処理が使用され、シングル スレッド フィルターに対してはすべてシングル スレッド処理が使用されます。 マルチスレッド フィルターを使用するドキュメントにシングル スレッド フィルターを使用するドキュメントが埋め込まれていると、Full-Text Engine では埋め込まれたドキュメントに対してシングル スレッド処理を開始します。 たとえば、PDF ドキュメントが埋め込まれた Word 文書の場合、Full-Text Engine は、Word コンテンツに対してはマルチスレッド プロセスを使用し、PDF の内容に対してはシングル スレッド プロセスを開始します。 しかし、シングルスレッドフィルターはこの環境でうまく機能せず、フィルタリングプロセスを不安定にする可能性があります。

このような埋め込みが通例であるような特定の状況では、不安定になった結果、プロセスがクラッシュすることもあります。 この条件が発生すると、Full-Text エンジンは失敗した文書(例えば、埋め込みPDFコンテンツを含むWord文書)をシングルスレッドフィルタリングプロセスに再ルーティングします。 再ルーティングが頻繁に起こると、フルテキスト インデックス作成処理のパフォーマンスが低下します。

この問題を回避するには、コンテナドキュメント(この例ではWordドキュメント)のフィルターをシングルスレッドフィルターとしてマークしてください。 フィルターをシングルスレッドフィルターとしてマークするには、そのフィルターの ThreadingModel レジストリ値を Apartment Threadedに設定します。 シングルスレッドアパートメントに関する情報は 、「COM スレディングモデルの理解と使用」を参照してください。

関連するコンテンツ

  • Last updated on