I’m going to have to blog very quietly tonight since I don’t want the plan cache to hear me. This is a summary of a very silent plan cache purge on a production SQL Server that was whispering to us that it needed a new RAM configuration.
This purge was happening on an old SQL Server 2012 instance that I inherited from a contractor. It didn’t have all the settings that I put in my builds, especially for memory. On my new builds I follow Glenn Berry’s formulas for setting ‘max server memory (MB)’. see here (for general formula) or also here (see step 26.g)
Silent Rumors!
A colleague and I first discovered the silent purge when talking very quietly about a stored procedure that was just run three weeks prior and only runs once a year. We were talking very quietly since there were rumors floating around. Other coworkers were whispering that someone may have run the annual procedure more than once … scandalous! The first place I went to look for proof of the rumor was at the last_execution_time in sys.dm_exec_procedure_stats. You can imagine my horror when I found out that the plan cache had silently been purged the day before. I wanted to scream, but the plan cache may have heard me if I had done that.