Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question
Anything logged to the error log when this occurs? – Martin Smith Jun 28 '12 at 10:46
Why are you looking at syscacheobjects? Are you observing the same thing in the DMV you should be looking at, sys.dm_exec_cached_plans? – Aaron Bertrand Jun 28 '12 at 11:53
@MartinSmith - I can see some DCOM errors which I think relate to Data collection which I configured to see if that would help. I Was still having this issue before this was installed – user1296176 Jun 28 '12 at 17:47
@AaronBertrand - I was looking in syscacheobjects so I can see the SQL to try and relate them to what was running on the server. I see that dm_exec_cached_plans behaves the same in that it gets cleared randomly at the same time. – user1296176 Jun 28 '12 at 17:50
FYI you can see the SQL from sys.dm_exec_cached_plans as well, you just need to cross apply against sys.dm_exec_sql_text(). It can be useful to also cross apply against sys.dm_exec_query _plan(). A recent example I posted here (just ignore the filtering): dba.stackexchange.com/questions/19527/… – Aaron Bertrand Jun 29 '12 at 4:10

migrated from stackoverflow.com Jun 28 '12 at 20:02

1 Answer

up vote 1 down vote

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):

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.

share|improve this answer
Many thanks for the advice. To answer a couple of specific points: AUTO_CLOSE is off. I have 3 entries in the log for 'cachestore flush' from yesterday when I explicitly called DBCC FREPROCCACHE as part of my investigations. I'll now read up on the first options you suggest. Thanks – user1296176 Jun 28 '12 at 18:16
You'll also want to check for DBCC FLUSHPROCINDB - is this your database or a vendor database? – Aaron Bertrand Jun 28 '12 at 18:21
Ok will do - it's my database – user1296176 Jun 28 '12 at 18:27
I have used profiler and watched the queries come through, I cannot see any calls to the commands you mention, I just suddenly see the execution time increase (as it compliles the execution plan) the cache is cleared and the queries continue. Is there anything SQL Server does to manage the size of the cache? Like clear it out when it reaches a certain size? – user1296176 Jun 28 '12 at 18:42
It should purge older, un-used plans from the cache, but not en masse unless one of these other events has occurred. What are your settings for 'optimize for ad hoc workloads' at the server level (check sp_configure), parameterization for this database, and what is the nature of the plans filling up the cache before this happens - are they all similar? Are they all single-use plans? Are your queries ad hoc, procedures, what are the max and total size of plans in the cache relevant to this database, and are just the plans for this db disappearing or is the entire plan cache empty? – Aaron Bertrand Jun 28 '12 at 18:44
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.