Our CPU on one of our six production servers has been going to 100% some mornings. We noticed that at 4am something is causing the size of the plan cache to plummet from 7GB to about 50MB. Since the plan cache is being starved for memory, the SQL Compilations / Sec is significantly increasing causing CPU pressure.
The server has 128GB of memory. The databases on it are quite large so perhaps we just need more memory (which unfortunately means adding another server because we are on standard edition which caps the memory).
I have a couple of questions...
- Is there anyway to set a minimum size for the plan cache (don't let it go below 2GB)?
- We currently set the maximum amount of memory that SQL can use to 80% of the server total. Some sites suggest 90%. What do people here use?