0

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?

0

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.