What does changing Max Server Memory do besides clear the plan cache and (obviously) alter the memory setting)?

Question

Running SQL Server 2012 SP3 on 32 GB ram and 4 cores, 60-80 concurrent connections, with a largely ad-hoc workload, we are seeing the SQL Server process (CPU) spike and remain spiked once or twice per day at unpredictable times. We are working on identifying the root cause of the spike. In the meantime, we've found that altering the Max Memory setting (either up or down) seems to be the only thing that will return the CPU load to normal.

Checking logs, and searching StackExchange (https://dba.stackexchange.com/a/183276) we see that the plan cache is being flushed by changing the Max Memory setting. However, if we flush the plan cache via DBCC FREESYSTEMCACHE('SQL Plans'), the CPU load doesn't return to normal.

Since changing the Max Memory setting resolves the issue no matter weather we increase or decrease it, the issue doesn't seem to be related directly to the Max Server Memory setting. As such, we are trying to understand what else changing the memory setting does, and then use that information to help identify the root cause of our CPU spike.

Answer 1

Ref: Server Memory Server Configuration Options

max server memory controls the SQL Server memory allocation, including (essentially any memory clerk found in sys.dm_os_memory_clerks):

• the buffer pool
• compile memory
• all caches
• qe memory grants
• lock manager memory
• clr memory

So all of these components will be effected by max server memory setting change.

On a side note this will be a very difficult path to find what is causing your CPU spike. I suggest you use following articles for your troubleshooting.

1. Troubleshooting SQL Server CPU Performance Issues by Joe Sack
2. How do I find out what is hammering my SQL Server?