全文索引的效能優化

硬體資源（比如記憶體、磁碟速度、CPU 速度和計算機體系結構）會影響全文索引和全文查詢的效能。導致全文索引效能降低的主要原因是硬體資源的限制：

如果篩選器後臺程式宿主程式 (fdhost.exe) 或 SQL Server 程式 (sqlservr.exe) 的 CPU 使用率接近 100%，則 CPU 會成為瓶頸。

如果平均磁碟等待佇列長度是磁碟頭數量的兩倍以上，則磁碟將成為瓶頸。主要的解決方法是建立獨立於 SQL Server 資料庫檔案和日誌的全文目錄。將日誌、資料庫檔案和全文目錄分別放在不同的磁碟上。購買執行速度更快的磁碟和使用 RAID 也能幫助改善索引效能。

如果實體記憶體不足（3GB 限制），則記憶體可能是瓶頸。實體記憶體限制可能存在於所有系統上，而在 32 位系統上，虛擬記憶體壓力可導致全文索引速度降低。
注意：
從 SQL Server 2008 開始，全文引擎可以使用 AWE 記憶體，因為全文引擎是 sqlservr.exe 的一部分。
 

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

SQL Server 建立全文批次花費的時間。

篩選器後臺程式能以多快的速度處理這些批次。

注意：
與完全填充不同，增量、手動和自動更改跟蹤填充的設計目的並不是為了最大程度地利用硬體資源以獲得更高速度。因此，這些優化建議可能不會增強全文索引的效能。
 

 優化全文索引的效能
若要最大限度地提高全文索引的效能，請採用下列最佳方法：

若要最大程度地使用所有處理器或核心，請將 sp_configure ‘max full-text crawl ranges' 設定為系統的 CPU 數。有關該配置選項的資訊，請參閱 max full-text crawl range 選項。

請確保基表具有聚集索引。對聚集索引的第一列使用整數資料型別。避免在聚集索引的第一列使用 GUID。對聚集索引執行多範圍填充可以產生最高的填充速度。我們建議充當全文鍵的列採用整數資料型別。

使用 UPDATE STATISTICS 語句更新基表的統計資訊。更重要的是，更新聚集索引或全文鍵的統計資訊以進行完全填充。這有助於多範圍填充在表上生成良好的分割槽。

如果要提高增量填充的效能，請對 timestamp 列生成輔助索引。

 完全填充效能問題的故障排除
如果對完全填充的效能不滿意，則建議按順序執行以下故障排除步驟。

實體記憶體使用量
如果在完全填充期間可用的實體記憶體數量是零，則 SQL Server 緩衝池可能正佔用系統的大部分實體記憶體。這可能導致兩個問題中的一個，可通過檢視全文爬網日誌來診斷此類問題（有關詳細資訊，請參閱確定純文字爬網日誌檔案的名稱）。

可能出現的問題如下：

頁檔案大小不足可能導致 fdhost.exe 或 sqlservr.exe 記憶體不足。

如果全文爬網日誌顯示 fdhost.exe 正在反覆重新啟動，或系統返回錯誤程式碼 8007008，則意味著這些程式中的某一個程式記憶體不足。若要消除這些故障，請增加系統的頁檔案大小。

如果爬網日誌未指示存在任何與記憶體相關的故障，則很可能是因為過度分頁導致效能不在最佳狀態。可以通過適當設定 SQL Server 緩衝池的“最大伺服器記憶體”值來解決該問題。有關設定“最大伺服器記憶體”的資訊，請參閱伺服器記憶體選項。
下表列出了有關如何估計 fdhost.exe 的記憶體需求的準則。該表中的公式使用以下值：

F，它是 fdhost.exe 所需記憶體的估計值 (MB)。

T，它是系統中可用實體記憶體的總量 (MB)。

M，它是最佳“最大伺服器記憶體”設定。

重要提示：
有關公式的基本資訊，請參閱下面的 1、2 和 3。
 

平臺  估計 fdhost.exe 記憶體需求量 (MB) - F1  用於計算最大伺服器記憶體的公式 - 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 等等。然後按照 T – sigma(Fi) 計算得到 M。
2 500 MB 是系統中其他程式所需記憶體的估計值。如果系統正在執行其他工作，請相應地增加該值。
3 8 表示入站共享記憶體 (ISM) 的典型大小。
示例：估計 fdhost.exe 的記憶體需求量
此示例針對具有 8GM RAM 和 4 個雙核處理器的 AMD64 計算機。首先計算出 fdhost.exe 所需記憶體的估計值 F。爬網範圍數是 8。
F = 8*10*8=640
然後計算出“最大伺服器記憶體”的最佳值 M。該系統的可用實體記憶體總量 (MB) T 是 8192。
M = 8192-640-500=7052
示例：設定最大伺服器記憶體
此示例使用 sp_configure 和 RECONFIGURE Transact-SQL 語句將“最大伺服器記憶體”設定為上例中計算得到的 M 值，即 7052：

 
USE master;
GO
EXEC sp_configure 'max server memory', 7052;
GO
RECONFIGURE;
GO可以降低 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 和 sys.dm_db_index_physical_stats (Transact-SQL)。
若要減少碎片，可以重新組織或重新生成聚集索引。有關詳細資訊，請參閱重新組織和重新生成索引。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-520811/，如需轉載，請註明出處，否則將追究法律責任。