Clearing plan cache improves performance - bad plans in cache?

0 votes

1 answer

716 views

sql-server sql-server-2014 execution-plan plan-cache

                          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?

Asked by mhenry1384 (156 rep)

Jun 23, 2020, 08:43 PM
Last activity: Apr 27, 2023, 11:03 AM

Clearing plan cache improves performance - bad plans in cache?

Related Questions