Almost empty plan cache

Question

Almost empty plan cache

gkochkin

SSC Veteran

Points: 237
More actions
May 2, 2017 at 7:32 am

#323267

I am experiencing a strange situation - my plan cache is almost empty. I use the following query to see what's inside:
SELECT dec.plan_handle,qs.sql_handle, dec.usecounts, dec.refcounts, dec.objtype , dec.cacheobjtype, des.dbid, des.text,deq.query_planFROM sys.dm_exec_cached_plans AS decjoin sys.dm_exec_query_stats AS qs on dec.plan_handle=qs.plan_handleCROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS desCROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deqWHERE cacheobjtype = N'Compiled Plan'AND objtype IN (N'Adhoc', N'Prepared')
One moment it shows me 82 rows, the next one 50, then 40 then 55 and so on while an hour before I couldn't reach the end of the plan cache issuing the same command. The point is that SQL Server keeps the plan cache very-very small. The main reason of my investigation is high CPU compared to our baselines without any high loads, under normal during-the day workload - constantly 65-80%
Perfmon counters show low values for Plan Cache Hit Ratio - around 30-50%, high compilations - 400 out of 2000 batch requests per second and high CPU - 73 avg. What could cause this behaviour?
The main purpose of the question is to learn the possible reasons for an empty plan cache.
Memory is OK - min: 0 max: 245000.
I also didn't notice any signs of memory pressure - PLE, lazy writes, free list stalls disk activity were just ok, logs did not tell me a thing. I came here for possible causes of this so I could proceed with investigation.
EDIT: I have also considered this thread:
SQL Server 2008 plan cache is almost always empty
But none of the recommendations/possible reasons are relevant.

Viewing 8 posts - 1 through 8 (of 8 total)

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

Ant-Green SSC Guru Points: 113566 More actions · Answer 1

How much RAM does the box have?

I had the same issue on a server with 64GB RAM, and 60GB allocated to SQL, after reducing this down to 57GB (leaving approx. 10% RAM for the OS) the cache started to fill up as I would expect it. Everything else as you say was not causing any concerns, just the state of the cache.

gkochkin SSC Veteran Points: 237 More actions · Answer 2

anthony.green - Tuesday, May 2, 2017 8:19 AM
How much RAM does the box have?
I had the same issue on a server with 64GB RAM, and 60GB allocated to SQL, after reducing this down to 57GB (leaving approx. 10% RAM for the OS) the cache started to fill up as I would expect it. Everything else as you say was not causing any concerns, just the state of the cache.

And 245000 dedicated to SQL.
But I have not changed anything. Everything was normal..

Ant-Green SSC Guru Points: 113566 More actions · Answer 3

gkochkin - Tuesday, May 2, 2017 8:49 AM
anthony.green - Tuesday, May 2, 2017 8:19 AM
How much RAM does the box have?
I had the same issue on a server with 64GB RAM, and 60GB allocated to SQL, after reducing this down to 57GB (leaving approx. 10% RAM for the OS) the cache started to fill up as I would expect it. Everything else as you say was not causing any concerns, just the state of the cache.

And 245000 dedicated to SQL.
But I have not changed anything. Everything was normal..

Reduce the max server memory to around 235520, that will leave just over 10% free to the OS, see if that helps, if it does, you can slowly increase this up till you find the sweet spot.

gkochkin SSC Veteran Points: 237 More actions · Answer 4

anthony.green - Wednesday, May 3, 2017 1:48 AM
gkochkin - Tuesday, May 2, 2017 8:49 AM
anthony.green - Tuesday, May 2, 2017 8:19 AM
How much RAM does the box have?
I had the same issue on a server with 64GB RAM, and 60GB allocated to SQL, after reducing this down to 57GB (leaving approx. 10% RAM for the OS) the cache started to fill up as I would expect it. Everything else as you say was not causing any concerns, just the state of the cache.

And 245000 dedicated to SQL.
But I have not changed anything. Everything was normal..
Reduce the max server memory to around 235520, that will leave just over 10% free to the OS, see if that helps, if it does, you can slowly increase this up till you find the sweet spot.

what's the point? You think it might be not enough memory for the OS?

Ant-Green SSC Guru Points: 113566 More actions · Answer 5

gkochkin - Wednesday, May 3, 2017 2:25 AM
what's the point? You think it might be not enough memory for the OS?

Yeah, going off past experience with this issue we hadn't left enough RAM for the OS.

The more RAM you have in the box the more RAM you need for Windows to manage itself, a bit of a catch22 situation.

General rule of thumb is to leave around 10% to the OS, then tweak as needed.
https://www.brentozar.com/blitz/max-memory/

Also do you have Analysis or Reporting services running on the same server?

gkochkin SSC Veteran Points: 237 More actions · Answer 6

anthony.green - Wednesday, May 3, 2017 2:38 AM
gkochkin - Wednesday, May 3, 2017 2:25 AM
what's the point? You think it might be not enough memory for the OS?
Yeah, going off past experience with this issue we hadn't left enough RAM for the OS.
The more RAM you have in the box the more RAM you need for Windows to manage itself, a bit of a catch22 situation.
General rule of thumb is to leave around 10% to the OS, then tweak as needed.
https://www.brentozar.com/blitz/max-memory/
Also do you have Analysis or Reporting services running on the same server?

Ok. But I cannot understand how to link small proc cache to low OS RAM...?
No, there is only database engine

Ant-Green SSC Guru Points: 113566 More actions · Answer 7

No I didn't notice anything strange in RAM utilisation, but something was causing the proc cache to not store any records. After reducing SQL's RAM on the server I was working on at the time from 60GB to 58GB the proc cache was kept full.

My guess was that the OS was asking for RAM from SQL do perform some tasks and thus the proc cache was the lesser of the impact to empty to give back to the OS to do what it needed to do.