syscacheobjects is cleared randomly Microsoft SQL Server Web Edition (64-bit)

Version 10.50.1600.1

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?

Many thanks

Paul
A couple of things to check into. These two you could capture with a server-side trace or extended events (though I haven't used the latter for this specific task):


explicit calls to DBCC FREEPROCCACHE or DBCC FREESYSTEMCACHE
certain explicit calls to RECONFIGURE (or RECONFIGURE WITH OVERRIDE)


If you see these calls happening often, you'll want to figure out where they're coming from and why, because they absolutely flush the procedure cache.

Also check the error log for events containing the text "cachestore flush":

EXEC xp_readerrorlog 0, 1, N'cachestore flush';


If you see these events make sure the database in question does not have the option AUTO_CLOSE on. You may want to make sure this isn't the case anyway, and it is often turned on with Web Edition since this edition is typically used to hold a lot of infrequently-used databases at a web host.