Xrhrft

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)

Partial query plan - after flush (good performance)

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) > 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