Post migration from SQL Server 2008 to SQL Server 2016, one of our stored procedure is showing performance degradation. It works fine few days, and then gets slow taking up to 5 minutes. Interestingly, at times SP gets back to working normal again.
On clearing the query cache of stored procedure (
DBCC FREEPROCCACHE), it starts to work fine (but only for few days).
We have tried to look into execution plan, and it looks like when the performance is fine – the smaller table join happens first to filter the records, whereas while the stored procedure is slow, the smaller table join happens in the end resulting into slow performance. The clustered index seek shows up "Expected Number of Rows"= 4 and "Actual Number of Rows" ~ 60000000.
Appreciate any inputs.