SQL Server full-text index performance problems

SqlServer full-text index performance has never been very good. I heard from my colleagues today and found a skill. I went back and tried it. I found that it can significantly improve the full-text index performance, generally, it can be increased by 2 times + original SQL statement: select * fromTable1whereContants (*, test) optimized SQL statement: viewsource

The full-text index performance of SQL Server has never been very good. I heard from my colleagues today and found a skill. I went back and tried it, and found that it can significantly improve the full-text index performance, generally, it can be increased by 2 times + original SQL statement: select * from Table1 where Contants (*, 'test') optimized SQL statement: view source

SQL ServerFull textIndexOfPerformanceIt has never been so good. I heard my colleagues share this article today and found a skill. I went back and tried it, and found that it could indeed improve significantly.Full textIndexOfPerformanceGenerally, it can be increased by 2 times +

Original SQL statement:

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

Optimized SQL statement:

View sourceprint? Select * from Table1 where Contains (Content, Title), 'test ')

The difference between the two SQL statements is whether or not the column names after Contanis are specified.Full textIndexSQL ServerFull textIndexOfPerformance

To use all processors or kernels to the maximum extent, set sp_configure 'max full-text crawl ranges' to the number of CPUs of the system. For more information about the configuration options, see max full-text crawl range options.

Make sure that the base table has ClusteringIndex. AggregateIndexThe first column uses the integer data type. Avoid AggregationIndexUse GUID In the first column. AggregateIndexMulti-range filling can produce the highest filling speed. We recommend that you actFull textThe key column uses the integer data type.

Use the update statistics statement to UPDATE the STATISTICS of the base table. More importantly, update AggregationIndexOrFull textKey statistics for full filling. This helps to fill in multiple ranges to generate good partitions on the table.

If you want to increase the incremental fillingPerformance, Please generate a secondary tool for the timestamp ColumnIndex.

Before full filling on a large multi-CPU computer, we recommend that you set the max server memory value to temporarily limit the size of the buffer pool so that sufficient memory is available for the fdhost.exe process and operating system. For more information, see "estimate memory requirements for the filter background program host process (fdhost.exe)" next to this topic ".

Complete FillingPerformanceProblem
--------------------------------------------------------------------------------

To diagnosePerformanceProblem, ViewFull textNetwork crawling logs. For more information about crawling logs, seeFull textTroubleshooting errors in padding (crawling.

IfPerformanceIf you are not satisfied, we recommend that you perform the following troubleshooting steps in sequence.

Physical memory usage
InFull textDuring the padding period, the memory of fdhost.exe or sqlservr.exe may be insufficient. IfFull textThe Network crawling log shows that fdhost.exe is being restarted repeatedly, or the system Returns Error Code 8007008, which means that one of these processes has insufficient memory. If fdhost.exe is generating a dump (especially on a large multi-CPU computer), the memory of the process may be insufficient.

Note:
To obtainFull textFor details about the memory buffer used for network crawling, see sys. dm_fts_memory_buffers (Transact-SQL ).

Possible reasons are as follows:

If the amount of physical memory available during full filling is zero, the SQL Server Buffer Pool may be occupying most of the system's physical memory.

The sqlservr.exe process attempts to occupy all available memory of the buffer pool (the maximum Server Memory configured ). If the memory size of the allocated hosts server is too large, the fdhost.exe process may face insufficient memory and shared memory allocation failure.

Note:
On multiple CPU computers (such as 64-channel IA64 computers)Full textDuring the padding period, the buffer pool memory contention may occur between fdhost.exe and sqlservr.exe. Insufficient shared memory will cause batch retry, memory jitter, And the fdhost.exe process to be dumped.

You can set the "Maximum Server Memory" value of the SQL Server Buffer Pool to solve this problem.Problem. For more information, see "estimate memory requirements for the filter background program host process (fdhost.exe)" next to this topic ". Used to reduceFull textIndexThe batch size may also be useful.

PagingProblem

If the page file size is insufficient, the memory of fdhost.exe or sqlservr.exe may also be insufficient, for example, on a system with a small page file with limited growth.

If the crawling log does not indicate any memory-related faults, it is probably caused by excessive paging.PerformanceDecrease.

Estimated memory demand for the filter background program host process (fdhost.exe)
The amount of memory required by the fdhost.exe process is mainly determined byFull textThe number of network crawling ranges, the size of the inbound shared memory (ISM), and the maximum number of ISM instances.

You can use the following formula to roughly estimate the memory occupied by the filter background program host (in bytes ):

Number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

The default values of the variables in the preceding formula are as follows:

Variable
Default Value

Number_of_crawl_ranges
Number of CPUs

Ism_size
The x86 computer is 1 MB

X64 computers are 4 MB, 8 MB, or 16 MB, depending on the total physical memory

Max_outstanding_isms
X86 computer is 25

X64 is 5

The following table lists the criteria for estimating the memory demand for fdhost.exe. The formulas in this table use the following values:

F, which is the estimated memory size (MB) required by fdhost.exe ).

T, which is the total amount of physical memory available in the system (MB ).

M, which is the best "Maximum Server Memory" setting.

Important
For basic information about formulas, see 1, 2, and 3 below.

Platform
Estimated memory demand for fdhost.exe (MB)-F1
Formula for calculating the maximum server memory-M2

Disable AWE x86
F = Number of crawl ranges * 50
M = minimum (T, 2000)-F-500

Enable AWE x86
F = Number of crawl ranges * 50
M = T-F-500

X64 or IA643
F = Number of crawl ranges * 10*8
M = T-F-500

1. if multiple full fills are in progress, calculate the memory demand for each fully filled fdhost.exe, such as F1 and F2. Then M is calculated based on T-sigma (Fi.

2 500 MB is the estimated memory size required by other processes in the system. If the system is performing other work, add this value accordingly.

3. ism_size is assumed to be 8 MB for x64 platforms.

Example: estimate the memory demand for fdhost.exe

This example is applicable to AMD64 computers with 8 gm RAM and 4 dual-core processors. Calculate the estimated memory value F required by fdhost.exe. The number of network crawlers is 8.

F = 8*10*8 = 640

Then calculate the optimum value of "Max Server Memory" M. The total available physical memory (MB) T of the system is 8192.

M = 8192-640-500 = 7052

Example: set the maximum Server Memory

In this example, use the sp_configure and RECONFIGURETransact-SQL statements to set "Maximum Server Memory" to the M value calculated in the previous example, that is, 7052.

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

Set maximum server memory configuration options

Server Memory options

How to view or change Server properties (SQL Server Configuration Manager)

How to set a fixed amount of memory (SQL Server Management Studio)

Factors that can reduce CPU usage
We want to be fully filled when the average CPU usage is less than about 30%.PerformanceNot optimal. This section discusses some factors that affect CPU usage.

Long wait page

To see if the waiting time for the page is too long, execute the following statements:

Copy
Execute select top 10 * FROM sys. dm_ OS _wait_stats ORDER BY wait_time_ms DESC;

The following table describes the types of waiting.

Wait type
Description
Possible solutions

PAGEIO_LATCH_SH (_ EX or _ UP)
This may indicate an IO bottleneck. In this case, the average disk queue length is usually high.
SetFull textIndexMoving to other file groups on other disks may help reduce IO bottlenecks.

PAGELATCH_EX (or _ UP)
This may indicate a large number of contention among multiple threads attempting to write the same database tutorial file.
Add a fileFull textIndexThe file group may help mitigate such contention.

For more information, see sys. dm_ OS _wait_stats (Transact-SQL ).

Low Efficiency in scanning base tables

Full filling scans the base table to generate batches. In the following cases, such a table scan may be inefficient:

If the base table has a high percentage of non-row columnsFull textIndex, Scanning the base table to generate batches may become a bottleneck. In this case, using varchar (max) or nvarchar (max) to move small data in rows may be useful.

If the base table is very fragmented, scanning may be inefficient. For non-row computing data andIndexFor details about the fragmentation, see sys. dm_db_partition_stats (Transact-SQL) and sys. dm_db_index_physical_stats (Transact-SQL ).

To reduce fragments, You can reorganize or regenerate the clustering.Index. For more information, see reorganize and regenerateIndex.