提升全文索引的效能

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控實例

本文將介紹全文索引與查詢效能不佳的常見原因，以及如何減輕這些問題。

常見的效能問題原因

本節說明使用全文索引時常見的效能問題原因。

硬體資源問題

硬體資源如記憶體、磁碟速度、CPU 速度及機器架構會影響全文索引與全文查詢的效能。

硬體資源限制導致全文索引效能下降。

• CPU。 如果篩選器守護程式主機處理程序 (fdhost.exe) 或 SQL Server 處理程序 (sqlservr.exe) 的 CPU 使用率接近 100%，則 CPU 即為瓶頸。

• 記憶體。 實體記憶體不足會造成瓶頸。

• 磁碟。 如果平均等待佇列長度超過磁碟磁頭數量的兩倍，則表示磁碟存在瓶頸。 主要的解決方式是建立和 SQL Server 資料庫檔案和記錄分開的全文檢索目錄。 請將記錄、資料庫檔案和全文檢索目錄放在不同的磁碟上。 安裝較快速的磁碟以及使用 RAID 也有助於改善索引效能。

全文批次處理問題

如果系統沒有硬體瓶頸，全文搜尋的索引效能主要取決於以下因素：

• 資料庫引擎 建立全文批次所需要的時間。

• 過濾守護靈消耗這些批次的速度。

全文檢索索引母體擴展問題

• 人口類型. 與完整人口不同，增量式、手動和自動變更追蹤人口並非為了最大化硬體資源以達到更快速度而設計。 因此，本文中的調整建議在使用增量式、手動或自動變更追蹤族群時，可能無法提升全文索引的效能。

• 主分支合併 當一個族群結束後，最後的合併過程會將索引片段合併成一個 主 全文索引。 此過程提升查詢效能，因為只需查詢 主 索引，而非多個索引片段。 更好的評分統計數據可能會用於相關性排名。 然而，主合併可能對輸入輸出（I/O）需求較大，因為合併索引片段時必須寫入與讀取大量資料。 不過，它不會阻擋輸入查詢。

  主要合併大量資料可能會產生長時間執行的交易，因而延遲在檢查點期間對交易記錄的截斷。 在此情況下，交易記錄可能會在完整復原模式下明顯成長。 在使用完整復原模式的資料庫中重新組織大型全文檢索索引之前，最佳作法是確認您的異動記錄包含足夠的空間，以供長時間執行的異動使用。 如需詳細資訊，請參閱 管理交易記錄檔的大小。

微調全文檢索索引的效能

若要將全文檢索索引的效能發揮至極限，請實作下列最佳作法：

• 要將所有 CPU 核心發揮到最大效能，請更改 max full-text crawl range 為系統核心數量。 欲了解更多資訊，請參閱 伺服器配置：最大全文爬取範圍。

• 請確定基底資料表具有叢集索引。 對叢集索引的第一個資料行使用整數資料類型。 避免在叢集索引的第一個資料行使用 GUID。 使用叢集索引中的多範圍資料集可以產生最高的操作速度。 使用整數資料型態作為全文鍵的欄位。

• 使用 UPDATE STATISTICS 陳述式更新基底表的統計資料。 更重要的是，請更新叢集索引上的統計資料或完整資料集合的全文檢索鍵。 此動作有助於多區塊族群在表格上產生良好的分割。

• 在大型多核心電腦執行完整母體之前，暫時限制緩衝池大小，方法是設定 max server memory 緩衝池的值，保留足夠記憶體供 fdhost.exe 程序和作業系統使用。 更多資訊請參閱本文後面的 「估計濾波守護程序宿主程序fdhost.exe的記憶體需求」（）。

• 如果您使用基於時間戳記欄位的增量資料載入，請在時間戳記欄位上建立次要索引以改善效能。

為全體人口的效能進行疑難排解

請參考以下章節以解決完整族群的效能問題。

查看完整的爬網日誌

為了協助診斷效能問題，請查看全文爬取日誌。

搜耙發生錯誤時，「全文檢索搜尋」搜耙記錄功能會建立並維護搜耙記錄檔，此記錄檔是一個純文字檔。 每個抓取記錄檔都對應至特定的全文檢索目錄。 所指定執行個體 (在此範例中為預設執行個體) 的編目記錄檔預設位於 %ProgramFiles%\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\LOG 資料夾中。

爬蟲記錄檔會遵循下列命名方案：

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

以下是爬行日誌檔案名稱的變動部分。

• <DatabaseID>資料庫的識別碼，為一個五位數的數字，前置零。

• <FullTextCatalogID>：全文目錄編號，為五位數，前置零。

• <n>：一個整數表示存在一個或多個相同全文目錄的爬取日誌。

例如，SQLFT0000500008.2 是指資料庫識別碼 = 5 而且全文檢索目錄識別碼 = 8 之資料庫的編目記錄檔。 檔案名稱尾數是 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

前述公式中變數的預設值如下：

下表提供了估算 記憶體 fdhost.exe需求的指引。 此表中的公式會使用下列值：

• F，是估計所需記憶體（ fdhost.exe 以 MB 為單位）。

• T，這是系統上可用的實體記憶體總計 (以 MB 為單位)。

• M，這是最佳 max server memory 設定。

如需下列公式的基本資訊，請參閱資料表後面的附註。

1. 如果同時進行多個完整族群，則分別計算 fdhost.exe 每個族群的記憶體需求，如 F1、 F2 依此類推。 然後將 M 計算為 T - Σ（Fi）。

2. 500 MB 是系統中其他處理序所需記憶體的估計值。 如果系統正在進行其他工作，請據此增加這個值。

3. 在 x64 平台上，ism_size 假設為 8 MB。

範例：估算 fdhost.exe 的記憶體需求

這個例子是針對一台擁有 8 GB RAM 和 4 顆雙核心處理器的 64 位元電腦。 第一項計算會估算 fdhost.exeF 所需的記憶體。檢索範圍的數量為 8。

F = 8 * 10 * 8 = 640

下一次計算得到 （max server memory） 的最佳值。 此系統可用的總物理記憶體（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。

• 掃描基底資料表的效率不足

  完整母體擴展會掃描基底資料表來產生批次。 此表格掃描在以下情境下可能效率不佳：

  • 如果基表中的列外欄被全文索引且其百分比很高，掃描基表以產生批次可能會成為瓶頸。 在這種情況下，透過使用 varchar（max） 或 nvarchar（max） 來將較小的資料移到列中可能會有幫助。

  • 如果基底資料表嚴重片段化，掃描可能就沒有效率。 關於計算列外資料與索引碎片的資訊，請參見 sys.dm_db_partition_stats 與 sys.dm_db_index_physical_stats。

    若要減少片段，您可以重新組織或重建叢集索引。 如需詳細資訊，請參閱 最佳化索引維護以改善查詢效能並減少資源耗用量。

針對文件索引速度緩慢進行疑難排解

擴展全文檢索索引時，全文檢索引擎會使用兩種篩選：多執行緒篩選和單一執行緒篩選。

• 有些文件，例如 Word 文件，會使用多線程篩選器。
• 其他文件，如 Adobe Acrobat 可攜式文件格式（PDF）文件，則使用單執行緒過濾器。

基於安全性考量，過濾器是由主機服務程式處理序載入。 伺服器執行個體會針對所有多執行緒篩選使用多執行緒處理序，而針對所有單一執行緒篩選使用單一執行緒處理序。 當使用多執行緒篩選的文件包含使用單一執行緒篩選的內嵌文件時，全文檢索引擎就會針對內嵌文件啟動單一執行緒處理序。 例如，如果遇到包含 PDF 文件的 Word 文件，全文檢索引擎就會針對 Word 內容啟動多執行緒處理序，而針對 PDF 內容啟動單一執行緒處理序。 然而，單執行緒濾波器在此環境中可能效果不佳，且可能使過濾過程不穩定。

在經常會有這類內嵌的特定情況下，不穩定可能會導致處理序損毀。 當發生此情況時，Full-Text 引擎會將任何失敗的文件（例如包含嵌入 PDF 內容的 Word 文件）重新導向到單執行緒過濾程序。 如果經常發生重新路由傳送的狀況，則會導致全文檢索索引處理序的效能降低。

為了解決這個問題，請將容器文件（此例中的 Word 文件）的過濾器標記為單執行緒過濾器。 要將濾波器標記為單執行緒濾波器，請將該濾波器的登錄值設 ThreadingModel 為 Apartment Threaded。 關於單執行緒公寓的資訊，請參閱 理解與使用 COM 執行緒模型。

相關內容

• 伺服器記憶體組態選項
• 伺服器配置：最大全文爬取範圍
• 填充全文索引
• 建立及管理全文檢索索引
• sys.dm_fts_memory_buffers（Transact-SQL）
• sys.dm_fts_memory_pools（Transact-SQL）
• 全文檢索疑難排解
• 全文檢索搜尋架構