question

glavers avatar image
glavers asked

My queryplan performance in full-text query is problematic

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)

queryperformancefull-textquery-plansql-server-2017
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.