I have a query which involves a full-text search like this:


 SELECT TOP 30 PersonId,
 PersonParentId,
 PersonName,
 PersonPostCode
 FROM dbo.People
 WHERE PersonDeletionDate IS NULL
 AND PersonCustomerId = 24
 AND CONTAINS(ContactFullText, '"mr" AND "ch*"')
 AND PersonGroupId IN(197, 206, 186, 198)
 ORDER BY PersonParentId,
 PersonName;

This generates two main plans, one is very fast in all cases, the other is very slow in most cases.

I have experimented with this query such that the FT search is not included and what I found is that the row estimates are always way lower than they should be.

If I run ```update statistics...with fullscan``` I still see extremely inaccurate row estimates from NC index seek operations in the execution plan.

When the row estimates are low enough, a loop join is selected, which is normally very slow (30+ seconds). Higher estimates seem to produce a good plan involving a merge join instead of a loop join.

Why is SQL Server still not estimating the rowcounts despite still having up to date statistics?