Can you point me in the right direction on where to look to figure out why one plan cache is different from the other one in another database. One database is older and has less data but the schema should be intact, along with the compatibility mode.
I have done things like freeproc, reset statistics, full scan and more. One seems to do an Index Scan while the other an Index Seek. They have the same indexes, stats look similar but not exact. The query is listed in blitzCache but no warnings.
Production https://www.brentozar.com/pastetheplan/?id=rkU8tqhmY
Development https://www.brentozar.com/pastetheplan/?id=S1-XYc2mY
ALTER PROCEDURE [GetTeamPlayerCount]
@EventId INT,
@Active INT = 1
AS
BEGIN
SET NOCOUNT ON;
SELECT
tp.TeamId,
COUNT(*) AS [Count]
FROM
Division d
INNER JOIN
DivisionTeam dt ON dt.DivisionId = d.Id
INNER JOIN
TeamPlayer tp ON dt.Id = tp.TeamId
WHERE
d.EventId = @EventId AND tp.Active = @Active
GROUP BY
tp.TeamId
END
The last stats update as shown in the execution plans is significantly different between the two databases. This may result in different plans even with similar data due to different row count estimates.
Update statistics for all tables referenced in the query. The FULLSCAN
option might be overkill but won't do harm.
UPDATE STATISTICS dbo.Division WITH FULLSCAN;
UPDATE STATISTICS dbo.DivisionTeam WITH FULLSCAN;
UPDATE STATISTICS dbo.TeamPlayer WITH FULLSCAN;