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) > 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
Best Answer
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.