I have a SQL Server 2016 SP1 production server. It's been running without a restart for 330 days. Everything's been running fine. Until last week, one day, all of the sudden, every query on the server has been generating a compilation.
Usually when I check the Perfmon, I see a lot of Batch Requests/Sec but this day, I saw as much Requests/Sec as Compilations/Sec
When I tried to look at what's using my cache, I had nothing! I've never seen anything like that. It went on and on all day, the CPU was at 100% since it was compiling all the time.
Finally, the IT decided to restart the machine. Everything got fixed after a reboot. But I was wondering, have you ever seen something like that? Can the cache plan be corrupted or something? (Like maybe if I had ran a DBCC FREEPROCCACHE it would have fixed itself without a restart?)
Does that mean that I should reboot my server at least once every six months or so?