Badly performing query plan after adding ORDER BY to Full Text CONTAINS search

Question

The following query is proving beyond my abilities to fix - hope someone can help.

SELECT top 100 Id, Name, InsertDate
FROM Foo
WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"')
ORDER BY InsertDate desc

Without the ORDER BY clause, the query performs fine (< 2 seconds). But when I add the ORDER BY, performance is awful (> 2 minutes).

The problem seems to be an incorrect query plan.

The query plan that performs well is this:

The query plan that performs badly is this:

IX_Foo_InsertDate is defined as: CREATE CLUSTERED INDEX [IX_Foo_InsertDate] ON [Foo] ( [InsertDate] DESC )

What is causing the change in query plans? How can I fix it?

Answer 1

The optimizer is choosing the different plan because it thinks the benefit of having the pre-sorted InsertDate (using IX_Foo_InsertDate) outweighs the cost once you add the order by clause.

The optimizer cannot be perfect, it might choose a slower path even if statistics are accurate - but it will certainly make poor choices if the statistics are not accurate - are AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on?

If necessary, you might be able to 'trick' the optimizer like this:

SELECT top 100 Id, Name, InsertDate
FROM Foo
WHERE CONTAINS(FooText, '"test*" and "hello*" and "goodbye*"')
ORDER BY some_function_of(InsertDate) desc

where some_function_of(InsertDate) has the same order as InsertDate