Sql-server – Warning in Execution Plan

Differences noticed:

• The slow plan uses version 120 cardinality estimator
• The fast plan uses version 70 cardinality estimator

You could try using query trace flag 9481 to force use of version 70 - just append OPTION (QUERYTRACEON 9481).

Next, the ItemProperties table is handled differently by both cases. The fast plan assumes that there will be at most one property in each join, while the slow plan assumes that there will be two. Assuming that there is in fact only one property value for all cases in question, you could replace the left joins with inner queries. This should result in identical plans on both servers.

EDIT Another optimization opportunity involves using PIVOT operator to fetch all properties at once, like so:

SELECT
vw_Items.ID
,vw_Items.ResolvedEntityID
,vw_Items.ResolvedDescription
,vw_Items.ItemProductNumber
,vw_Items.ItemDescription
,vw_Items.FilteringType
,vw_Items.ItemCategory
,vw_Items.IsDefault
,vw_Items.IsInventoryType
,vw_Items.Active
,vw_Items.PlantFloorVisible
,[dbo].f_ParseDistanceValue([Length]) AS 'Length'
,[dbo].f_ParseDistanceValue(Width) AS Width
,ISNULL(MaterialDescription.[Description], '(none)') AS 'Material'
,ISNULL(GradeDescription.[Description], '(none)') AS 'Grade'
,ISNULL(MillingDescription.[Description], '(none)') AS 'Milling'
,ISNULL(StateDescription.[Description], '(none)') AS 'State'
FROM
vw_Items
-- Join to get properties
left join (
    select [ItemEntityID],[Property],[Value] from [dbo].[ItemProperties]) p
    PIVOT (MAX(VALUE) for [Property] in (Material,Grade,Milling,State,Length,Width))properties on vw_Items.[EntityID] = properties.[ItemEntityID]
LEFT JOIN DescriptionEntity  AS MaterialDescription ON properties.Material = MaterialDescription.EntityID
LEFT JOIN DescriptionEntity  AS GradeDescription ON properties.Grade = GradeDescription.EntityID
LEFT JOIN DescriptionEntity  AS MillingDescription ON properties.Milling = MillingDescription.EntityID
LEFT JOIN #DescriptionEntity  AS StateDescription ON properties.[State] = StateDescription.EntityID

With PIVOT the server will access the ItemProperties table only once, which will likely result in reduced IO.

(summarizing my comments and putting as answer)

A query rewrite will solve the issue of getting low row estimates. As Joe Chang explains in his blog post Query Optimizer Gone Wild - Full-Text

CONTAINS is "a predicte used in a WHERE clause" per Microsoft documentation, while CONTAINSTABLE acts as a table.

You get a much better plan (merge join) using CONTAINSTABLE vs the actual plan using contains uses a nested loop join with low row estimates.

You can rewrite the query as :

SELECT TOP 30 p.PersonId,
              p.PersonParentId,
              p.PersonName,
              p.PersonPostCode
FROM dbo.People p
left join containstable (ContactFullText, '"mr" AND "ch*"') cf on cf.[yourKey] = p.PersonId
WHERE p.PersonDeletionDate IS NULL
      AND p.PersonCustomerId = 24
      --AND CONTAINS(ContactFullText, '"mr" AND "ch*"')
      AND p.PersonGroupId IN(197, 206, 186, 198)
      AND [RANK] > 0
ORDER BY p.PersonParentId,
         p.PersonName;