Clearing plan cache improves performance - bad plans in cache?

Question

I have run into this situation a number of times on a VM with SQL Server Express 12.0.6205.1 (SQL Server 2014 SP3 CU1)

Using SSMS, I'll run a stored procedure a few times. It consistently takes, say 4 seconds to run. ("Wait time on server replies" in "Client Statistics"). Then I clear the plan cache:

DBCC FREEPROCCACHE

Now the same stored procedure will consistently take 400 ms to run.

This VM is under VERY light load. It's just me poking at it every few days.

This VM is a copy of a production server that is having performance problems. So it seems like maybe bad plans are getting stuck in the plan cache? I would like the calls to be consistently fast, obviously. What is the problem and how to I fix it?

Answer 1

Plans stay in cache until forced out by memory pressure, statistics updates at a certain level, or you recompile them. If a plan gets compiled for a value that returns say 1 record, then, later, gets run for a value that returns 1,000, performance can stink. Vice-versa too.

Compare the slow and fast plans.

Also, you can pass the plan handle to FREEPROCCACHE to remove just one plan at a time. Much safer. - https://dba.stackexchange.com/users/6597