KB2491510 - FIX:A query that has a CONTAINS predicate in a WHERE clause takes a long time to compile in SQL Server 2008 or SQL Server 2008 R2

Symptoms

Consider the following scenario:

In Microsoft SQL Server 2008 or SQL Server 2008 R2, you run a query against a specific column in a table that has many rows. The query has a CONTAINS predicate in a WHERE clause.
The term that you use for the CONTAINS predicate contains a word. The word appears in some columns that are included in a full-text index of the table.

In this scenario, the query takes a long time to compile.

For example, this issue may occur when you run the following query:

SELECT count(*)
FROM dbo.MyLargeTable where
CONTAINS (email_address, '"name@contoso.com"')

Notes

Assume that ".com" appears in some columns that are included in a full-text index of the table.
You can track the compile time of your query to determine whether you are encountering this issue. For detailed instructions about how to track the compile time, refer to the "More Information" section.

Cause

This issue occurs because of an error in the Cardinality Estimation logic for full-text indexes. In this case, a scan of multiple columns that contain a frequently occurring word is performed before the search scope for the expected columns is narrowed.

Resolution

Cumulative update information

SQL Server 2008 Service Pack 2

The fix for this issue was first released in Cumulative Update 4 for SQL Server 2008 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2527180 Cumulative update package 4 for SQL Server 2008 Service Pack 2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2402659 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 2 hotfix to an installation of SQL Server 2008 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.
The fix for this issue was first released in Cumulative Update 13. For more information about how to obtain this cumulative update package for SQL Server 2008 SP1, click the following article number to view the article in the Microsoft Knowledge Base:

2497673 Cumulative update package 13 for SQL Server 2008 Service Pack 1
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 SP1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

970365 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released

SQL Server 2008 R2 Service Pack 1

The fix for this issue was first released in Cumulative Update 1 for SQL Server 2008 R2 Service Pack 1. For more information about how to obtain this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2544793 Cumulative Update package 1 for SQL Server 2008 R2 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2567616 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released

SQL Server 2008 R2

The fix for this issue was first released in Cumulative Update 7. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, click the following article number to view the article in the Microsoft Knowledge Base:

2507770 Cumulative Update package 7 for SQL Server 2008 R2 Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

981356 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Workaround

To work around this issue, use one of the following methods:

Use * to include all the columns in the search instead of one column. This changes how many rows are actually evaluated before the Integrated Full Text compiles its cardinality estimation.
For example, run the following query:
SELECT count(*) FROM dbo.MyLargeTabel where CONTAINS (*, '"test@contoso.com"')
Make the frequently-occurring word a stop word by using an ALTER FULLTEXT STOPLIST statement.
For more information about the ALTER FULLTEXT STOPLIST statement for Transact-SQL, visit the following Microsoft Developer Network (MSDN) website:
General information about the ALTER FULLTEXT STOPLIST statement for Transact-SQL

More Information

To track the compile time of the query, follow these steps:

Capture a SQL Profiler trace when you run the query. To do this, monitor the TSQL:BatchCompleted and TSQL:StmtCompleted events, or the RPC:Completed and SP:StmtCompleted events.
Note the batch CPU time and the Statement CPU time.
Subtract the Statement CPU time from the Batch CPU time. The result indicates the CPU time that is taken to parse and compile the batch.

For more information about how to use a CONTAINS predicate for Transact-SQL, visit the following Microsoft Development Network (MSDN) website:

How to use a CONTAINS predicate for Transact-SQL