September 17, 2015 at 3:33 am
The plan cache is cleared automatically (every 30-60 seconds) on our high load OLPT server.
Can't find a reason for it. I have been logging procedure cache thru dm_exec_cached_plans, every second for a couple of minutes.
It shows that it doesn't grow beyond 200Mb. SQLserver has 64Gb available.
DBCC FREEPROCCACHE is not executed.
dm_os_memory_clerks shows the same 200Mb max (approximately).
I disabled the SQL Agent, still happens, so it's not a job causing the problem.
I disabled the Resource Govenor, still the same problem.
Could use some pointers 😀
September 17, 2015 at 3:43 am
Whats the amount of RAM in the actual server
September 17, 2015 at 3:47 am
Total RAM is 72Gb, 64Gb is max memory for SQL.
September 17, 2015 at 3:49 am
Cleared automatically meaning you're getting the cache flush messages in the error log? Or you're seeing the size of cached plans going to zero?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2015 at 3:53 am
I don't get Cache flush messages in error log, I'm seeing the size of cached plans going to zero.
September 17, 2015 at 4:26 am
dm_os_memory_clerks shows that CACHESTORE_OBJCP is way to small.
On simular servers it's at least 2Gb.
What can I do to make it grow larger? Or how to stop getting cleared?
September 17, 2015 at 4:34 am
Does it flush regularly at 30 sec or is it an approximation? all day or only during peak hours? It doesnt have to be a job it could even be someone calling DBCC FREEPROCACHE within the procedures instead of say recompile.
I know it sounds very stupid , but I have seen it executed this way. Frankly I dont know of any process that causes proc cache to flush on a time interval , only memory pressure and DBA can make this happen.
September 17, 2015 at 4:38 am
I had the same problem on a server with 64GB of RAM and SQL set to 60GB, reducing SQL's memory to 58GB ensured the plans where staying in cache.
Try dropping the max memory setting to see if your having the same issue I noticed.
September 17, 2015 at 4:45 am
Hi Jayanth,
It's not exactly 30 seconds, can also be 35 or 40 seconds. But it's going on all day long.
I find it very hard to believe that it could be a DBCC command causing this behaviour all day long.
What can I do to investigate memory pressure?
Thx!
Jayanth_Kurup (9/17/2015)
Does it flush regularly at 30 sec or is it an approximation? all day or only during peak hours? It doesnt have to be a job it could even be someone calling DBCC FREEPROCACHE within the procedures instead of say recompile.I know it sounds very stupid , but I have seen it executed this way. Frankly I dont know of any process that causes proc cache to flush on a time interval , only memory pressure and DBA can make this happen.
September 17, 2015 at 4:54 am
Hi Anthony,
Just dropped it to 60Gb (with 72Gb available), but no improvement. Still getting cleared within 30-60 seconds.
anthony.green (9/17/2015)
I had the same problem on a server with 64GB of RAM and SQL set to 60GB, reducing SQL's memory to 58GB ensured the plans where staying in cache.Try dropping the max memory setting to see if your having the same issue I noticed.
September 17, 2015 at 5:03 am
Set it back to 64Gb, now it's taking 60-90 seconds before cache gets cleared again.
Any other hints?
marksanders1976 (9/17/2015)
Hi Anthony,Just dropped it to 60Gb (with 72Gb available), but no improvement. Still getting cleared within 30-60 seconds.
anthony.green (9/17/2015)
I had the same problem on a server with 64GB of RAM and SQL set to 60GB, reducing SQL's memory to 58GB ensured the plans where staying in cache.Try dropping the max memory setting to see if your having the same issue I noticed.
September 17, 2015 at 5:15 am
Is it just the DB engine on the server or do you have IS/AS/RS installed also?
Any use of extended stored procs, sp_oa.... procedure calls or linked servers?
Whats the Memory: Available MB counter value in PerfMon?
September 17, 2015 at 5:31 am
IS service is running but not actively used as far as I know. AS is not running. RS isn't even installled.
Not using extended stored procedures or sp_oa... calls.
No Linked servers configured on this server. But there is an other server that querys information from this specific server, but not 24/7.
Available MB Counter is 3000Mb continuously.
anthony.green (9/17/2015)
Is it just the DB engine on the server or do you have IS/AS/RS installed also?Any use of extended stored procs, sp_oa.... procedure calls or linked servers?
Whats the Memory: Available MB counter value in PerfMon?
September 18, 2015 at 3:42 am
The irregular timing suggests that it might be a frequently executed procedure. Is the database highly transactional , how frequently does lazywriter and checkpoint run ?
Do you know since how long this has been happening and if there were any deployments made to the server ?
Using any In memory tables?
September 18, 2015 at 8:03 am
Database is highly transactional, but it also happens late in the evening when database is hardly used. Interval is steady on 30 seconds when database is hardly used, during peaktimes the interval is larger between 30-60 seconds. No idea if we should draw conclusion on this.
I think it has been going on for a couple of months, only noticed it recently because we investigated some baselines.
There were no deployments to the server, no changes in stored procedures that could cause this.
We are using memory tables quite often, and I know there are a lot of recompiles happening in stored procedures.
Could this be the cause? But why does this get to be a problem all of a sudden?
What do you mean by "how frequently does lazywriter and checkpoint run"?
Jayanth_Kurup (9/18/2015)
The irregular timing suggests that it might be a frequently executed procedure. Is the database highly transactional , how frequently does lazywriter and checkpoint run ?Do you know since how long this has been happening and if there were any deployments made to the server ?
Using any In memory tables?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply