sql server全文索引的效能問題

Sql Server 全文索引的效能一直都不是太好，今天聽同事分享，發現了一個技巧，回去試了下，發現確實能明顯的提升全文索引的效能，一般都能提升 2倍+

 

原 SQL 陳述式：

select * from Table1 where Contants(*,'test')
　　

最佳化後的 SQL 陳述式：

view sourceprint?select * from Table1 where Contains((Content,Title),'test')

 

兩條 SQL 的不同之處，就是在 Contanis 後面的列名是否指定了，經過測試，明確指定 全文索引的列，確實能較大的提升 Sql Server 全文索引的效能

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

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

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

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

在大型多 CPU 電腦上執行完全填充之前，建議您通過設定 max server memory 值來暫時限制緩衝池的大小，從而留出足夠的記憶體供 fdhost.exe 進程及作業系統使用。有關詳細資料，請參閱本主題後面的“估計篩選器背景程式宿主進程 (fdhost.exe) 的記憶體需求量”。

解決完全填充效能問題
--------------------------------------------------------------------------------

若要診斷效能問題，請查看全文編目記錄。有關編目記錄的資訊，請參閱全文填充（耙梳）中錯誤的故障排除。

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

實體記憶體使用量
在全文填充期間，fdhost.exe 或 sqlservr.exe 的記憶體有可能不足。如果全文編目記錄顯示 fdhost.exe 正在反覆重新啟動，或系統返回錯誤碼 8007008，則意味著這些進程中的某一個進程記憶體不足。如果 fdhost.exe 在產生轉儲（特別是在大型多 CPU 電腦上），則該進程的記憶體可能不足。

 注意 
若要獲得有關全文耙梳所用的記憶體緩衝區的資訊，請參閱 sys.dm_fts_memory_buffers (Transact-SQL)。
 

可能的原因如下：

如果在完全填充期間可用的實體記憶體數量是零，則 SQL Server 緩衝池可能正佔用系統的大部分實體記憶體。

sqlservr.exe 進程試圖侵佔緩衝池的所有可用記憶體（最大為配置的最大伺服器記憶體）。如果分配的“最大伺服器記憶體”過大，fdhost.exe 進程可能會面臨記憶體不足的狀況及共用記憶體配置失敗。

 注意 
在多 CPU 電腦（如 64 路 IA64 電腦）上進行全文填充期間，fdhost.exe 或 sqlservr.exe 之間可能出現緩衝池記憶體爭用。由此造成的共用記憶體不足會導致批次重試、記憶體抖動並讓 fdhost.exe 進程進行轉儲。
 

可以通過適當設定 SQL Server 緩衝池的“最大伺服器記憶體”值來解決此問題。有關詳細資料，請參閱本主題後面的“估計篩選器背景程式宿主進程 (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
 x86 電腦為 1 MB

x64 電腦為 4 MB、8 MB 或 16MB，具體取決於實體記憶體總量
 
max_outstanding_isms
 x86 電腦為 25

x64 電腦為 5
 

下表列出了有關如何估算 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 .ism_size 對於 x64 平台假定為 8 MB。

樣本：估計 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 和 RECONFIGURETransact-SQL 陳述式將“最大伺服器記憶體”設定為上一個樣本中計算得到的 M 值，即 7052。

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

設定最大伺服器記憶體配置選項

伺服器記憶體選項

如何查看或更改伺服器屬性（SQL Server 組態管理員）

如何設定固定記憶體量 (SQL Server Management Studio)

可以降低 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)。

若要減少片段，可以重新組織或重建叢集索引。有關詳細資料，請參閱重新組織和重建索引。