3

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

  • That much uptime indicates you haven't patched in almost a year, so yes you should be rebooting via regular patching activities more often. Are you only checking these stats where there is a problem, or do you have a real baseline? What type of workload is this? Are you sure the CPU was caused by these compilations? – LowlyDBA Mar 19 '18 at 20:48
  • Check out some of the suggestions from this similar question as far as data points to look further into. – LowlyDBA Mar 19 '18 at 20:53
  • Yes I have a baseline. I monitor these regularly. This is the workload of one of our client. It's all mobile devices connecting through a website to synchronize data with the database. Usually I don't get any Compilations. – Danielle Paquette-Harvey Mar 19 '18 at 20:57
  • Have you recently started using query store ?. And does SQL Server service account has locked pages in memory privilege ? – Shanky Mar 20 '18 at 10:09
  • 2
    @DaniellePaquette-Harvey OK can you please disable query store and see if this helps. There is known issue with Query store and plan cache clearing. – Shanky Mar 21 '18 at 7:36
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.