Edit

Improve the performance of full-text queries

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

The following list of recommendations can help you to improve the performance of full-text queries.

Hardware resources such as memory, disk speed, CPU speed, and machine architecture can influence the performance of full-text queries.

  • Defragment the index of the base table by using ALTER INDEX REORGANIZE.

  • Reorganize the full-text catalog by using ALTER FULLTEXT CATALOG REORGANIZE. Too many full-text index fragments can lead to substantial degradation in query performance. This statement merges all the fragments into a single larger fragment per index, removing all stale occurrence information. Make sure that you run this statement before performance testing because running this statement causes a master merge of the full-text indexes in that catalog.

  • Restrict your choice of full-text key columns to a small column. Although a 900-byte column is supported, use a smaller key column in a full-text index. int and bigint provide the best performance.

  • Using an integer full-text key avoids a join with the docid mapping table, which improves query and crawl performance. Additional performance improvements are possible if the full-text key is also the clustered index key.

  • Combine multiple CONTAINS predicates into one CONTAINS predicate. In SQL Server, you can specify a list of columns in the CONTAINS query.

  • If you only require full-text key or rank information, use CONTAINSTABLE or FREETEXTTABLE instead of CONTAINS or FREETEXT, respectively.

  • To limit results and increase performance, use the top_n_by_rank parameter of the FREETEXTTABLE and CONTAINSTABLE functions. top_n_by_rank allows you to recall only the most relevant hits. Use this parameter only if your business scenario doesn't require recalling all possible hits (that is, it doesn't require total recall).

    Note

    Total recall is typically necessary for legal scenarios, but might be less important than performance for business scenarios such as an e-business.

  • Check the full-text query plan to make sure that the appropriate join plan is chosen. Use a join hint or query hint if you have to. If a parameter is used in the full-text query, the first-time value of the parameter determines the query plan. You can use the OPTIMIZE FOR query hint to force the query to compile with the value you want. This helps achieve a deterministic query plan and better performance.

  • In full-text search, logical operators specified in CONTAINSTABLE (AND, OR) can be implemented either as Transact-SQL joins or inside the full-text execution streaming table-valued functions (STVF). Typically, queries with only one type of logical operator 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. For this reason, where possible, frame your queries using only a single type of logical operator.

  • For applications that contain selective relational predicates, queries that use selective relational predicates and unselective full-text predicates might perform best when they're written to use the query optimizer. This approach allows the query optimizer to decide whether it can exploit predicate or range pushdown to produce an effective query plan. This approach is simpler and often more efficient than indexing relational data as full-text data.

SQL Server 2008 Full-Text Search: Internals and Enhancements

Related content

  • Last updated on