Query produces two query plans (at different times). How do I always produce the better query plan? I have a fairly straightforward query that usually performs well (i.e. returns in a couple of seconds) but sometimes it will timeout and I need to flush the query plan before it will start behaving again.

I was able to get both query plans through SQL Profiler - so I know what a good plan looks like, but I'm not sure how to encourage SQL Server to always produce the better plan.

The query plans are very similar (and big) so I will include a screenshot of only the parts that are different.  I can post the entire XML if requested.

Partial query plan - before flush (bad performance)


Edited: Link to full query plan (sorry, it doesn't seem to have rendered as well as some query plans I've seen)

Partial query plan - after flush (good performance)

Edited Link to full query plan

Query

SELECT Records.[Id],
   Property.Id AS [PropertyId],
   CASE
       WHEN Records.Id IS NOT NULL THEN Records.[Name]
       ELSE '(In Transit)'
   END AS [Name],
   Records.[Area],
   Records.[AreaUnitId],
   Records.[Lng],
   Records.[Lat],
   Records.[Zoom],
   Records.[Code],
   Records.[Guid],
   [Property].[SecurityId],
   Records.[DateCreated],
   Records.[Modified],
   Records.[DateModified],
   Records.[ModifiedByUserId],
   Records.[SortOrder],
   Records.[Deleted],
   COALESCE(Property.Name, '') AS PropertyName,
   COALESCE(Unit.Name, '') AS UnitName,
   COALESCE(Unit.Code, '') AS UnitCode,
   COALESCE(AggregateData.Head, 0) AS Head,
   COALESCE(AggregateData.TotalWeight, 0) AS TotalWeight,
   CASE
       WHEN COALESCE(AggregateData.Head, 0) &gt; 0 THEN AggregateData.TotalWeight/AggregateData.Head
       ELSE 0
   END AS AverageWeight
FROM
( 
    SELECT Pen.Id,
       Pen.PropertyId
    FROM Pen
    LEFT JOIN [Security] O ON Pen.SecurityId = O.Id
    WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
    AND (@Deleted IS NULL
      OR Pen.Deleted = @Deleted) 

   -- Add null row per property, so animals in transit are included further down
   UNION ALL 
   SELECT 0 AS Id,
      Property.Id AS PropertyId
   FROM Property
   LEFT JOIN [Security] O ON Property.SecurityId = O.Id
   WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
   AND Property.[External] = 0
   AND Property.Deleted = 0
) AS PenIds
LEFT JOIN
( 
   SELECT L.PropertyId,
       COALESCE(L.PenId, 0) AS PenId,
       COUNT(M.BeastId) AS Head,
       SUM(WeightLog.[Weight]) AS TotalWeight
   FROM BeastMaster M
   LEFT JOIN BeastMovement L ON M.BeastMovementId = L.Id
   LEFT JOIN WeightLog ON M.WeightLogId = WeightLog.Id
   LEFT JOIN [Security] O ON L.SecurityId = O.Id
   WHERE (@TenantId IS NULL
      OR O.TenantId = @TenantId)
   AND (@Soh IS NULL
      OR M.Soh = @Soh)
   AND (@PropertyId IS NULL
      OR L.[PropertyId] = @PropertyId)
   GROUP BY L.PropertyId,
        COALESCE(L.PenId, 0)
) AS AggregateData ON PenIds.Id = AggregateData.PenId
   AND PenIds.PropertyId = AggregateData.PropertyId
LEFT JOIN [Pen] AS Records ON Records.Id = PenIds.Id
LEFT JOIN Property ON PenIds.PropertyId = Property.Id
LEFT JOIN Unit ON Records.AreaUnitId = Unit.Id
WHERE (@Id IS NULL
   OR PenIds.[Id] = @Id)
AND (@PropertyId IS NULL
   OR PenIds.[PropertyId] = @PropertyId)
AND 
( 
   @ManagementAreaId IS NULL
   OR EXISTS
   ( 
      -- Properties linked to Management Area
      SELECT *
      FROM [Permission] Pm
      WHERE Pm.Deleted = 0
        AND Pm.UserGroupId = @ManagementAreaId
        AND Pm.RequestedSecurityId = Property.SecurityId )
)
ORDER BY Records.Name
If you were on 2016 or higher, you could have used Query Store to track your plans and force the good one through Query Store functionality. You can still force the plan,though, easiest is probably sp_create_plan_guide_from_handle: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-create-plan-guide-from-handle-transact-sql?view=sql-server-2017 .

Other options could be to use OPTIMIZE FOR or even RECOMPILE query hints, depending on the circumstances and what suits you best.