Performance Tuning and Optimization of Full-Text Indexes

During a full-text population, it is possible for fdhost.exe or sqlservr.exe to run low on memory or to run out of memory. If the full-text crawl log shows that fdhost.exe is being restarted often or that error code 8007008 is being returned it means one of these processes is running out of memory. If fdhost.exe is producing dumps, particularly on large, multi-CPU computers, it might be running out of memory.

The possible causes are as follows:

• If amount of physical memory that is available during a full population is zero, the SQL Server buffer pool might be consuming most of the physical memory on the system. 

  The sqlservr.exe process tries to grab all available memory for the buffer pool, up to the configured maximum server memory. If the max server memory allocation is too large, out-of-memory conditions and failure to allocate shared memory can occur for the fdhost.exe process.

  Note

  During a full-text population on a multi-CPU computer, such as a 64-way IA64 computer, contention for the buffer pool memory can occur between fdhost.exe or sqlservr.exe. The resulting lack of shared memory causes batch retries, memory thrashing, and dumps by the fdhost.exe process.

  You can solve this problem by setting the max server memory value of the SQL Server buffer pool appropriately. For more information, see "Estimating the Memory Requirements of the Filter Daemon Host Process (fdhost.exe)," later in this topic. Reducing the batch size used for full-text indexing may also help. 

• A paging issue

  Insufficient page-file size, such as on a system that has a small page file with restricted growth, can also cause the fdhost.exe or sqlservr.exe to run out of memory.

  If the crawl logs do not indicate any memory-related failures, it is likely that performance is slow due to excessive paging.

Estimating the Memory Requirements of the Filter Daemon Host Process (fdhost.exe)

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

We expect that the performance of full populations is not optimal when the average CPU consumption is lower than about 30 percent. This section discusses some factors that affect CPU consumption.

• High wait for pages

  To find out whether a page wait time is high, execute the following Transact-SQL statement:

  Execute SELECT TOP 10 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;
  

  The following table describes the wait types of interest here.

  Wait type	Description	Possible resolution
  PAGEIO_LATCH_SH (_EX or _UP)	This could indicate an IO bottleneck, in which case you would typically also see a high average disk-queue length.	Moving the full-text index to a different filegroup on a different disk could help reduce the IO bottleneck.
  PAGELATCH_EX (or _UP)	This could indicate a lot of contention among threads that are trying to write to the same database file.	Adding files to the filegroup on which the fulltext index resides could help alleviate such contention.

  For more information, see sys.dm_os_wait_stats (Transact-SQL).

• Inefficiencies in scanning the base table

  A full population scans the base table to produce batches. This table scanning could be inefficient in the following scenarios:

  • If the base table has a high percentage of out-of-row columns that are being full-text indexed, scanning the base table to produce batches might be the bottleneck. In this case, moving the smaller data in-row using varchar(max) or nvarchar(max) might help.

  • If the base table is very fragmented, scanning might be inefficient. For information about computing out-of-row data and index fragmentation, see sys.dm_db_partition_stats (Transact-SQL) and sys.dm_db_index_physical_stats (Transact-SQL).

    To reduce fragmentation, you can reorganize or rebuild the clustered index. For more information, see Reorganizing and Rebuilding Indexes.