Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012:

FROM dbo.Person 
WHERE CONTAINS(Name, '"John" AND "Smith"')

Executing this query alone gives me about 1,300 results in less than two seconds (there's a fulltext index on Name)

However, when I change the query to this:

FROM dbo.Person 
WHERE CONTAINS(Name, '"John" AND "Smith"')
OFFSET 0 rows

It takes more than 20 seconds to give me 10 results.

The following query is even worse:

    FROM dbo.Person
    WHERE CONTAINS(Name, '"John" AND "Smith"') ) AS RowConstrainedResult 
WHERE RowNum >= 0 AND RowNum < 11 

It takes more than 1.5 minutes to complete!

Any ideas?

Slow plan


Fast plan


share|improve this question

migrated from Jul 10 '12 at 16:25

This question came from our site for professional and enthusiast programmers.

What happens if you change your second query to SELECT TOP 10 * .... ORDER BY Name? – Lamak Jul 4 '12 at 18:57
On what columns is index IX_PersonSearch... made? You're getting a key lookup because you're selecting * from the table and the used index does not contain all the output columns. I think you should select only the columns you need and then include them in the non-clustered index as included columns, not index columns. – Marcel N. Jul 4 '12 at 18:58
Can you post you indexes on the table (create script)? – Peter Kiss Jul 4 '12 at 18:59
The ID is always included in every nonclustered index. This is the way SQL Server is able to to key lookups (by ID). – usr Jul 4 '12 at 19:07
What I forgot to mention: When i do the same query with LIKE instead of CONTAINS, is fast, too. (Paginated or not) – vrs Jul 4 '12 at 19:13

As you just want the TOP 10 ordered by name it thinks it will be quicker to work down the index on name in order and look to see if each row matches the CONTAINS(Name, '"John" AND "Smith"') ) predicate.

Presumably it takes many more rows to find the 10 matches required then it expects and this cardinality issue is compounded by the number of key lookups.

A quick hack to stop it using this plan would be to change the ORDER BY to ORDER BY Name + '' although using CONTAINSTABLE in conjunction with FORCE ORDER should also work.

share|improve this answer

This looks like classic selectivity misestimation. Not sure what can be done about it as the "driver" of the query is fulltext search which you cannot augment with statistics.

Try rewriting the where contains predicate to an inner join containstable (CONTAINSTABLE) and apply join order hints to force the shape of the plan.

That is not a perfect solution because it has maintenance issues, but I cannot see another way.

share|improve this answer
Thanks for your answer, I tried it. Same result though: When not using pagination, the query is really fast. When Paginating it suddenly becomes really slow again :/ – vrs Jul 4 '12 at 20:46
Ok can you post the plan as an image and your query? My guess it that we didn't yet succeed to generate the desired shape. – usr Jul 4 '12 at 20:53
up vote 3 down vote accepted

I managed to solve the problem:

As I said in the question there were indizes on all columns + statistics for each column. (Because of legacy LIKE queries) I removed all indizes and statistics, added the fulltext search and voilà, the query became really fast.

It seems the indizes led to a different Execution Plan.

Thank you all very much for your help!

share|improve this answer
Please accept your answer so we know you solved the problem. Thanks! – Jon Seigel Jul 10 '12 at 17:03
Well deleting the index entirely is one way of preventing it from being used I guess! – Martin Smith Jul 10 '12 at 17:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.