I have a full-text catalog that is being used in a query.
If I use
contains(MATCH.INDEX_ENTRY_METADATA, '"Warning*" AND "Flood*"')
then the queryplan uses a Table Spool
If i change to
contains(MATCH.INDEX_ENTRY_METADATA,'"light*" AND "rail*"')
then the queryplan uses a Nested Loop
When the table full-text catalog is initially populated the queryplan uses the table spool but a few hours later switches to the Nested Loop.
The performance of the Nested Loop is horrible in that the qyuery takes ~5mins to return. The Table Scan is taking sub second.
Does anyone have any ideas on what we can do to force the query plan to use the Table Scan.
Note that we have copied the database to a test environment and recreated the catalog and associated indexes and the query plan uses the Table Scan.
This is running on SQL Server Standard version (13.0.4001.0)
