Sql-server – SQL Server 2016 All Queries recompile all the time (plan cache corruption?)

plan-cachesql serversql-server-2016

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

Here is a few jours of monitoring

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?

Here is a regular baseline for the same server.
Here is a regular baseline

Best Answer

You should enable Lock Pages in Memory (LPIM).

On SQL Server 2016+, I've encountered a similar scenario that I believe to be a bug. When the server experiences memory pressure, and LPIM is not be enabled, SQL Server will clear the plan cache as part of it's process to release memory back to the OS. This much in normal. SQL Server should only do this when a low memory condition exists.

In some cases, SQL Server will continue to clear the plan cache, even after the low memory condition is resolved. This results in the behavior you are seeing. On a busy server, you will see some plans entering the plan cache, only to get purged seconds later. This is not the proper behavior.

Enabling LPIM will resolve the issue. Enabling LPIM is a best practice for all SQL Server installations, so I would recommend enabling this for every SQL Server instance, regardless of version or whether you've experienced this problem.

To enable the lock pages in memory option

  1. On the Start menu, click Run. In the Open box, type gpedit.msc.
  2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand Local Policies.
  4. Select the User Rights Assignment folder.
  5. The policies will be displayed in the details pane.
  6. In the pane, double-click Lock pages in memory.
  7. In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
  8. In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.
  9. Restart the SQL Server Service for this setting to take effect.
Related Question