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)。

若要减少碎片，可以重新组织或重新生成聚集索引。有关详细信息，请参阅重新组织和重新生成索引。