Procedure Plan Cache is cleared automatically

Question

Procedure Plan Cache is cleared automatically

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

Lazywriter kicks in when SQL detects memory pressure , I was trying to find out if there is memory pressure which causes the Cache to flush or is it something being called regardless of any actual memory pressure. Checkpoint flushes pages to disk as part of your recovery interval, for a highly transactional database you might have a lot of checkpoints happening, I was wondering if this overhead was causing any memory issues.

In memory tables do work differently and procedures that are natively compiled behave very differently from normal procs.

I must admit I am stumped at the momebt, but I am very interested to find out what going on here. Will ping you back with some counters that we can monitor .

Jayanth Kurup[/url]

Wilfred van Dijk SSCrazy Eights Points: 8980 More actions · Answer 2

is "Optimize for AdHoc workloads" enabled? If so, try disable that option (server option, no restart necessary)

Wilfred
The best things in life are the simple things

marksanders1976 Mr or Mrs. 500 Points: 583 More actions · Answer 3

Hi Wilfred, This server option is already disabled.

Wilfred van Dijk (9/21/2015)
is "Optimize for AdHoc workloads" enabled? If so, try disable that option (server option, no restart necessary)

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

If you query the plan cache, what's in there? Lots of single-use ad-hoc queries? Lots of object plans?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

marksanders1976 Mr or Mrs. 500 Points: 583 More actions · Answer 5

Jayanth, many thanks for your efford so far. Please tell me what information I have to collect, and how to do it.

What I think is so strange is that this behaviour is also happening when the server gets hardly any incoming requests. Maybe I should Profile during these periods, but what events to filter on?

Bill Talada SSChampion Points: 11956 More actions · Answer 6

So far I believe the 58 GB max size comment has been the best empirical statement of how to fix the problem. I'm wondering why that hypothesis hasn't been tested. I've seen some arbitrary head scratcher limits in microsoft products so I wouldn't doubt a 60 GB bug. Just look at the goofy time estimates one gets when downloading files (2 hours remaining at 1 MB/sec with only 5 MB remaining) in windows 7.

nicholas.winstanley Grasshopper Points: 18 More actions · Answer 7

I would fire up profiler and filter for commands like: dbcc freeproccache and see if it is being called from inside a Proc.

marksanders1976 Mr or Mrs. 500 Points: 583 More actions · Answer 8

Already tried that, know for sure that no dbcc commands are executed.

nicholas.winstanley (9/21/2015)
I would fire up profiler and filter for commands like: dbcc freeproccache and see if it is being called from inside a Proc.

marksanders1976 Mr or Mrs. 500 Points: 583 More actions · Answer 9

Hi Bill, tried this just to rule it out. Set memory back to 56Gb. Intervals are getting larger now, but still at 2-4 minutes before cache gets cleared.

Bill Talada (9/21/2015)
So far I believe the 58 GB max size comment has been the best empirical statement of how to fix the problem. I'm wondering why that hypothesis hasn't been tested. I've seen some arbitrary head scratcher limits in microsoft products so I wouldn't doubt a 60 GB bug. Just look at the goofy time estimates one gets when downloading files (2 hours remaining at 1 MB/sec with only 5 MB remaining) in windows 7.

marksanders1976 Mr or Mrs. 500 Points: 583 More actions · Answer 10

Hi Gail, I included some images of the plan cache just before the flush, and directly after the flush. Results are grouped on first three columns. As you can see not a lot of single-use queries.

What I noticed is the high number of usecounts on the classifier function for the resource govenor. This is also the case when I disable the Resource Govenor. How is that possible?

What can you tell based, on these figures? Need more info?

GilaMonster (9/21/2015)
If you query the plan cache, what's in there? Lots of single-use ad-hoc queries? Lots of object plans?

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 11

How far apart were these screenshots taken , seeing massive usecounts after the cache has been flushed. conisdering that it flushes every 30 sec , the number doesnt make much sense.

PS:-I'll send you a trace template and counters to monitor on PM in a day or two got a lot on my plate at the moment. Besides you have Gail looking into it and it doesnt get better than that.

Jayanth Kurup[/url]

marksanders1976 Mr or Mrs. 500 Points: 583 More actions · Answer 12

The screenshots are taken 1 second apart. The high number of usecounts are on a plan for the classifier function of RG.

This would imply that cache isn't completely cleared, but only the majority of the plans get removed from cache.

Otherwise the size of the cache wouldn't drop to a couple of MBytes every 30-60 seconds.

I also don't see any messages in errorlog that would point to a complete flush.

The question still stands; why do 99% of the plans get cleared?

marksanders1976 Mr or Mrs. 500 Points: 583 More actions · Answer 13

Update: Reboot took care of the memory problem. Not the fix I was hoping for offcourse.

I will get back on this topic when plans start to get flushed again.

@jayanth; I'm still curious about the counters that I should monitor. Please post them if you will.

Thanks for the help everyone!