SQL Server clears plan cache and execution stats periodically

Question

After upgrading SQL Server 2014 to 2016, the server keeps resetting cached execution plans and dm* views (like dm_exec_query_stats) etc. every couple of hours

As if someone executes DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS manually (except for no one does, it happens automatically).

The same very database worked fine on SQL Server 2014 and Windows Server 2012, things went south after moving to SQL Server 2016 (and Windows server 2016)

Things I checked: the database does not have "auto close" flag. The SQL server is ad hoc optimized set to true (I thought it would help, it didn't). The "query store" is "off". Server has 16 GB memory.

Nothing helpful in the "SQL Server log" either. Just a weekly backup message...

I also checked this article https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options (scroll down to "Examples" section, and right above it) there is a list of situations when the plan is cleared automatically. None of those apply.

UPDATE:

Unfortunately, none of the suggestions helped. Granting LPIM permissions, detecting and fixing non-parameterized queries that generated tons of plans for the same query, lowering "max server memory"... Plans keep resetting randomly, from every couple of hours to every 5-10 minutes. If the server was "under memory pressure" how come the 2014 version was working fine on the same machine.

Here's the sp_Blitz output as requested

**Priority 10: Performance**:

- Query Store Disabled - The new SQL Server 2016 Query Store feature has not been enabled on this database.

    * xxx


**Priority 50: Server Info**:

- Instant File Initialization Not Enabled  - Consider enabling IFI for faster restores and data file growths.


**Priority 100: Performance**:

- Resource Governor Enabled  - Resource Governor is enabled.  Queries may be throttled.  Make sure you understand how the Classifier Function is configured.


**Priority 120: Query Plans**:

- Implicit Conversion Affecting Cardinality - One of the top resource-intensive queries has an implicit conversion that is affecting cardinality estimation.

    * 

- Missing Index - One of the top resource-intensive queries may be dramatically improved by adding an index.

    * 

- RID or Key Lookups - One of the top resource-intensive queries contains RID or Key Lookups. Try to avoid them by creating covering indexes.

    * 

**Priority 170: File Configuration**:

- System Database on C Drive
    * master - The master database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

    * model - The model database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.

    * msdb - The msdb database has a file on the C drive.  Putting system databases on the C drive runs the risk of crashing the server when it runs out of space.


**Priority 200: Backup**:

- MSDB Backup History Not Purged msdb - Database backup history retained back to Jun 10 2017  9:47PM


**Priority 200: Informational**:

- Backup Compression Default Off  - Uncompressed full backups have happened recently, and backup compression is not turned on at the server level. Backup compression is included with SQL Server 2008R2 & newer, even in Standard Edition. We recommend turning backup compression on by default so that ad-hoc backups will get compressed.


**Priority 200: Non-Default Server Config**:

- Agent XPs  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- max server memory (MB)  - This sp_configure option has been changed.  Its default value is 2147483647 and it has been set to 15000.

- optimize for ad hoc workloads  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- show advanced options  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.

- xp_cmdshell  - This sp_configure option has been changed.  Its default value is 0 and it has been set to 1.


**Priority 200: Performance**:

- Buffer Pool Extensions Enabled  - You have Buffer Pool Extensions enabled, and one lives here: Z:\sql_buffer_pool.BPE. It's currently 60.00000000000 GB. Did you know that BPEs only provide single threaded access 8KB (one page) at a time?

- cost threshold for parallelism  - Set to 5, its default value. Changing this sp_configure setting may reduce CXPACKET waits.

**Priority 240: Wait Stats**:

- No Significant Waits Detected  - This server might be just sitting around idle, or someone may have cleared wait stats recently.

**Priority 250: Informational**:

- SQL Server Agent is running under an NT Service account  - I'm running as NT Service\SQLSERVERAGENT. I wish I had an Active Directory service account instead.

- SQL Server is running under an NT Service account  - I'm running as NT Service\MSSQLSERVER. I wish I had an Active Directory service account instead.

**Priority 250: Server Info**:

- Default Trace Contents  - The default trace holds 125 hours of data between Aug 19 2017 11:55AM and Aug 24 2017  4:59PM. The default trace files are located in: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log

- Hardware  - Logical processors: 2. Physical memory: 15GB.

- Hardware - NUMA Config  - Node: 0 State: ONLINE Online schedulers: 2 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 29

- Locked Pages In Memory Enabled  - You currently have 12.02534484863 GB of pages locked in memory.

- Memory Model Unconventional  - Memory Model: LOCK_PAGES

- Server Last Restart  - Aug 20 2017 12:32PM

- Server Name  - xx

- Services
 - Service: SQL Full-text Filter Daemon Launcher (MSSQLSERVER) runs under service account NT Service\MSSQLFDLauncher. Last startup time: not shown.. Startup type: Manual, currently Running.

 - Service: SQL Server (MSSQLSERVER) runs under service account NT Service\MSSQLSERVER. Last startup time: Aug 20 2017 12:32PM. Startup type: Automatic, currently Running.

 - Service: SQL Server Agent (MSSQLSERVER) runs under service account NT Service\SQLSERVERAGENT. Last startup time: not shown.. Startup type: Automatic, currently Running.

- SQL Server Last Restart  - Aug 20 2017 12:33PM

- SQL Server Service  - Version: 13.0.4446.0. Patch Level: SP1. Edition: Enterprise Edition (64-bit). AlwaysOn Enabled: 0. AlwaysOn Mgr Status: 2

- Virtual Server  - Type: (HYPERVISOR)

- Windows Version  - You're running a pretty modern version of Windows: Server 2012R2 era, version 6.3


**Priority 254: Rundate**:

 - Captain's log: stardate something and something...

Answer 1

First, get the exact times when the plan cache is being cleared. Here's the easiest way to do it - it should run nearly instantly, and won't block anyone:

SELECT TOP 1 creation_time
FROM sys.dm_exec_query_stats WITH (NOLOCK)
ORDER BY creation_time;

If that date/time seems older to you than you'd expected, then only part of the plan cache is being cleared. For example, maybe someone is doing an index rebuild or update stats job, which would flush the plan cache for the specific objects affected - but other objects will still stick around. I see this a lot when system queries (like DMV queries) stick around, but user database plans clear out.

If that date/time updates at specific intervals, like if it seems to update exactly every 2 hours to say 6:00, 8:00, 10:00, etc, then someone's probably running a job or query that causes the plan cache to clear out. Once you know the exact frequency, then you can:

• Look at your job schedules to see what runs at that interval
• Run a Profiler trace or Extended Events trace during that timespan to figure out the mystery (I'm not usually a fan of tracing in production, but if you know exactly when the killer is going to strike, it's easy enough to fire up a low-overhead sample of what's running)
• Log sp_WhoIsActive to a table during that time (the easiest method, but the least likely to narrow it down to the exact query causing it)

If that date/time keeps changing each time you run the query, then your server is probably under memory pressure. Run this to generate basic health check info, and then you can copy/paste it into your Stack question so we can diagnose it:

sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1, @CheckUserDatabaseObjects = 1

(Disclosure: I'm one of the authors of sp_Blitz.)

Updated 2017/08/25 with your sp_Blitz data - thanks for running sp_Blitz and adding it to your question, and it really helps show a few things. You're running SQL Server 2016 Enterprise Edition on a VM with 2 cores and 16GB of RAM. First, a quick note on licensing: if you're licensing by the guest, the minimum purchase requirement is 4 cores, not 2. (See the SQL Server Licensing Guide for more details.) 4 cores of Enterprise Edition is about $28K USD, and it's fairly unusual to see that much licensing money spent on just 16GB RAM. If you're licensing SQL Server Enterprise Edition at the host level, you can ignore that and run smaller VMs, though.

It looks like your SQL Server is coming under external memory pressure. You've got 16GB RAM, and you've set max server memory at 15GB. Unfortunately, 1GB isn't enough left over for the operating system (plus whatever else you're going to run on there, like backup software and SSMS.) In our SQL Server Setup Guide, we suggest leaving 4GB or 10% free, whichever is greater - in your case, that would be 4GB, so your max server memory setting should be 12GB rather than 15GB.

More evidence shows up in your current memory allocations: you have locked pages in memory (LPIM) turned on, but you've only got 12.02GB of pages locked in memory. That likely (but not guaranteed) means that some other application needed memory, so Windows sent out a memory pressure notification, and SQL Server gave up the other 3GB of memory to let the other app do its thing. That's more proof that you can't really go with a 15GB max - you need memory for other stuff.

When your SQL Server comes under that external memory pressure and needs to free up memory for other apps, your plan cache will suffer.

So you've got a few options:

• Set max memory appropriately - say, 12GB (or even lower if you're going to run other apps on the server.) That way, SQL Server won't have to have a fire sale on memory and flush stuff out just because some other app needs 2-3GB of RAM - it'll already be available
• Stop running other apps on the server - this can be tough if it's other sysadmins remote desktopping and running stuff like SSMS, though. I've set up Perfmon counter alarms for the number of RDP sessions open, and alerted when it's anything other than 0 - that can help catch the culprit in action.
• Add more memory to the VM - but I don't think you really need it. Some evidence is shown by the sp_Blitz report of "no significant waits detected." I don't think you're under frequent memory pressure, especially since you report that it only happens every now and then. This is the least cost-effective option.

Answer 2

OK, OP here, I finally fixed this problem by updating SQL Server 2016 to the latest version. I had SP1 and yesterday I installed Cumulative Update 6.

I also set "max memory" appropriately like Brent's answer suggests. Great answer by the way, I urge everyone to upvote it.

It's been 36 hours and counting, plans not being reset.

Brent Ozar also has a very nice website here: https://sqlserverupdates.com/ to help determine which updates you need.

One other thing that helped was detecting and resolving "untrusted foreign keys" issue. Brent has a very nice article (hahah, yeah, Brent again, I know right) on how to resolve it, just google, he's the #1 result

Answer 3

I have had this issue in my home testing box and I found out that by adding 'Lock Pages in memory' permission to the SQL Server service account solved the problem, but I'm not sure this is the best advice.

See Enable the Lock Pages in Memory Option (Windows)