Why does SQL Server use AWE memory for full text search?
Beginning in SQL Server 2008, the Full-Text Engine can use AWE memory because the Full-Text Engine is part of the sqlservr.exe process. For more information, see Full-Text Search architecture. If the system has no hardware bottlenecks, the indexing performance of full-text search mostly depends on the following:
How to manage the size of full text indexes?
As a best practice, before reorganizing a large full-text index in a database that uses the full recovery model, ensure that your transaction log contains sufficient space for a long-running transaction. For more information, see Manage the Size of the Transaction Log File.
Why are full text indexes so slow in SQL Server?
The main cause for reduced full-text indexing performance is hardware-resource limits. CPU. If CPU usage by the filter daemon host process (fdhost.exe) or the SQL Server process (sqlservr.exe) is close to 100 percent, the CPU is the bottleneck.
Why is memory a bottleneck in SQL Server?
CPU. If CPU usage by the filter daemon host process (fdhost.exe) or the SQL Server process (sqlservr.exe) is close to 100 percent, the CPU is the bottleneck. Memory. If there is a shortage of physical memory, memory might be the bottleneck.
How to improve the performance of full text queries?
Typically, queries with only one type of logical operators are implemented purely by full-text execution, whereas queries that mix logical operators also possess SQL joins. Implementation of a logical operator inside the full-text execution STVF uses some special index properties that make it much faster than SQL joins.
Are there logical operators in full text search?
In full-text search, logical operators specified in CONTAINSTABLE (AND, OR) can be implemented either as SQL joins or inside the full-text execution streaming table-valued functions (STVF).