Stored procedure has different plans in different databases, can't replicate better plan in mai-CodePudding

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

CodePudding user response：

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;