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

[アーティクル]
08/30/2023

適用対象: SQL Server Azure SQL データベース

このトピックでは、フルテキストインデントとクエリのパフォーマンス低下の一般的な原因をいくつか説明します。また、このような問題を軽減し、パフォーマンスを改善する提案もいくつか紹介します。

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

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

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

フルテキストインデックス作成のパフォーマンス低下の主な原因となるのは、ハードウェアリソースの制限です。

CPU。フィルターデーモンホストプロセス (fdhost.exe) または SQL Server プロセス (sqlservr.exe) の CPU 使用率が 100% に近くなっている場合は、CPU がボトルネックになっています。
メモリ。物理メモリが不足している場合は、メモリがボトルネックになっている可能性があります。
ディスク。ディスク待ちのキューの長さが平均でディスクヘッド数の 2 倍を超えている場合は、ディスクがボトルネックになっています。この場合の主な回避策は、作成するフルテキストカタログを SQL Server のデータベースファイルやログから切り離し、ログ、データベースファイル、およびフルテキストカタログを別々のディスクに配置することです。その他、高速なディスクの導入や RAID の使用も、インデックス作成のパフォーマンス向上に役立ちます。

Note

SQL Server 2008 (10.0.x) 以降、Full-Text Engine は sqlservr.exe プロセスの一部となったため、AWE メモリを使用できます。詳細については、「フルテキスト検索のアーキテクチャ」を参照してください。

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

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

SQL Server によるフルテキストバッチの作成にかかる時間
フィルターデーモンがバッチを処理する速度

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

作成の種類。増分、手動、および自動の変更追跡による作成は、完全作成とは違って、ハードウェアリソースを最大限に活用して処理を高速化するようには作られていません。そのため、このトピックで提案する調整を実行しても、増分、手動、または自動の変更追跡を使用したときのフルテキストインデックスのパフォーマンスが改善しない場合があります。
マスターマージ。作成が完了すると、最終的なマージプロセスが起動され、インデックスフラグメントが 1 つのマスターフルテキストインデックスにマージされます。これにより、多数のインデックスフラグメントではなく、1 つのマスターインデックスのみをクエリすれば済むため、クエリのパフォーマンスが向上し、関連順位付けにもより的確なスコア (評価) 統計を適用できます。ただし、マスターのマージ処理では、インデックスフラグメントをマージする際に大量のデータを読み書きする必要があるため、大量の I/O が発生しますが、クエリの着信がブロックされることはありません。

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

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

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

すべての CPU プロセッサまたはコアを最大限に使用するには、sp_configure 'max full-text crawl range' をシステム上の CPU の数に設定します。構成オプションの詳細については、「 max full-text crawl range サーバー構成オプション」を参照してください。
ベーステーブルにクラスター化インデックスがあることを確認します。クラスター化インデックスの最初の列には整数データ型を使用します。 GUID は使用しないようにしてください。クラスター化インデックスで複数の範囲の作成を使用すると、作成速度を最大限に高めることができます。フルテキストキーとして機能する列は整数データ型にすることをお勧めします。
UPDATE STATISTICS ステートメントを使用してベーステーブルの統計を更新します。さらに重要な点は、クラスター化インデックスの統計や完全作成のフルテキストキーを更新することです。これにより、複数の範囲の作成によってテーブルに適切なパーティションが生成されるようになります。
大型のマルチ CPU コンピューター上で完全作成を実行する前に、fdhost.exe プロセスおよびオペレーティングシステムが使用するメモリを十分に確保するために、 max server memory 値を設定してバッファープールのサイズを一時的に制限することをお勧めします。詳細については、このトピックの後半の「フィルターデーモンホストプロセス (fdhost.exe) のメモリ要件の推定」を参照してください。
timestamp 列に基づいて増分作成を使用する場合は、timestamp 列にセカンダリインデックスを構築し、増分作成のパフォーマンスを向上します。

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

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

パフォーマンスの問題を診断するには、フルテキストクロールログを調べます。

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

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

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

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

<DatabaseID> - データベースの ID。 <dbid> は、ゼロで始まる 5 桁の数字です。
<FullTextCatalogID> - フルテキストカタログ ID。 <catid> は、ゼロで始まる 5 桁の数字です。
<n> - 同じフルテキストカタログに 1 つ以上のクロールログが存在することを示す整数です。

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

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

フルテキスト作成時は、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 プロセスのメモリ不足や共有メモリの割り当ての失敗が発生することがあります。

この問題は、SQL Server バッファープールの最大サーバーメモリ値を適切に設定することで解決できます。詳細については、このトピックの後半の「フィルターデーモンホストプロセス (fdhost.exe) のメモリ要件の推定」を参照してください。フルテキストインデックスの作成に使用されるバッチのサイズを小さくすると、有効な場合があります。
メモリの競合。マルチ CPU コンピューター上でのフルテキスト作成時、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 コンピューターの場合) 合計物理メモリにより、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 の最適な設定

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

プラットフォーム	fdhost.exe のメモリ要件の推定 (MB 単位) - F^1	max server memory の計算式 - M^2
x86	F = クロール範囲の数 * 50	M =minimum(T, 2000) - F - 500
X64	F = クロール範囲の数 * 10 * 8	M = T - F - 500

式に関する注意事項

複数の完全作成を実行中の場合は、それぞれの fdhost.exe のメモリ要件を、F1、F2 などのように個別に計算してください。その後、M as T- sigma**(_F_i)** で計算します。
500 MB は、システムの他のプロセスに必要なメモリの推定値です。システムで追加の作業を実行している場合、適宜この値を大きくします。
」を参照してください。ism_size は 8 MB と見なされます (x64 プラットフォームの場合)。

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

この例は、8 GB の RAM と 4 つのデュアルコアプロセッサを搭載した 64 ビットコンピューターを対象としています。最初の計算では、fdhost.exe に必要なメモリ (F) を推定します。クロール範囲の数は 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  
EXEC sp_configure 'max server memory', 7052;  
GO  
RECONFIGURE;  
GO

サーバーメモリオプションの詳細については、「Server Memory Server Configuration Options」(サーバーメモリに関するサーバー構成オプション) を参照してください。

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

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

Note

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

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

Microsoft Word ドキュメントなどの一部のドキュメントは、マルチスレッドフィルタを使用してフィルタリングされます。
シングルスレッドフィルターを使用する場合 (Adobe Acrobat Portable Document Format (PDF) ドキュメントなど) があります。

セキュリティ上の理由から、フィルターはフィルターデーモンホストプロセスによって読み込まれます。サーバーインスタンスでは、マルチスレッドフィルターに対してはすべてマルチスレッド処理が使用され、シングルスレッドフィルターに対してはすべてシングルスレッド処理が使用されます。マルチスレッドフィルターを使用するドキュメントにシングルスレッドフィルターを使用するドキュメントが埋め込まれていると、Full-Text Engine では埋め込まれたドキュメントに対してシングルスレッド処理を開始します。たとえば、PDF ドキュメントが埋め込まれた Word 文書の場合、Full-Text Engine は、Word コンテンツに対してはマルチスレッドプロセスを使用し、PDF の内容に対してはシングルスレッドプロセスを開始します。ただし、このような環境では、シングルスレッドフィルターが適切に機能しない場合があり、フィルター処理が不安定になることがあります。このような埋め込みが通例であるような特定の状況では、不安定になった結果、プロセスがクラッシュすることもあります。クラッシュが発生すると、エラーが発生したドキュメント (たとえば、PDF の内容が埋め込まれた Word 文書) がシングルスレッドフィルター処理に再ルーティングされます。再ルーティングが頻繁に起こると、フルテキストインデックス作成処理のパフォーマンスが低下します。

この問題を回避するには、コンテナードキュメント (この例では Word 文書) に対するフィルターとして、シングルスレッドフィルターを設定します。シングルスレッドフィルターとして設定するには、フィルターの ThreadingModel レジストリ値を Apartment Threaded に設定します。シングルスレッドアパートメントの詳細については、ホワイトペーパー「 COM スレッドモデルの概要と使用方法」を参照してください。

参照

サーバーメモリに関するサーバー構成オプション
 max full-text crawl range サーバー構成オプション
 フルテキストインデックスの作成
 フルテキストインデックスの作成と管理
 sys.dm_fts_memory_buffers (Transact-SQL)
sys.dm_fts_memory_pools (Transact-SQL)
フルテキストインデックスの作成のトラブルシューティング
 フルテキスト検索のアーキテクチャ

次の方法で共有

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

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

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

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

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

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

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

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

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

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

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

例 : max server memory の設定

CPU 使用量を確認する

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

参照

フィードバック

その他のリソース

次の方法で共有

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

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

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

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

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

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

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

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

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

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

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

例 : max server memory の設定

CPU 使用量を確認する

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

参照

フィードバック

その他のリソース

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

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

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

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

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

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

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