sqlsql-serverdatabasesql-execution-planparameter-sniffing

Stored procedure has different plans in different databases, can't replicate better plan in main database


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

enter image description here


Solution

  • 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;