Microsoft SQL Server Web Edition (64-bit)
SQL Server 2008 R2
I have noticed that we are getting a lot of execution plan re-complilcations - first executions quite slow, subsequent calls very fast as you would expect.
However, periodically (after around 10 minutes or so), performances returns to that of the first query i.e. execution is slow again, then fast.
I have read many articles on what could cause this; I have changed auto update statistics to off as suggested.
What I have noticed is that syscacheobjects will build up nicely, with usecounts into the 1,000's - then suddenly the table will be empty, causing all the queries to recompile.
Does anyone know why the cache may be getting flushed like this?