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.
Best Answer
Ref: Server Memory Server Configuration Options
max server memorycontrols the SQL Server memory allocation, including (essentially any memory clerk found in sys.dm_os_memory_clerks):So all of these components will be effected by
max server memorysetting 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.