Sql-server – Instant plan cache cleanup

I'm experiencing almost instant plan cache clean up. No plan resides in the plan cache for more than about 30 seconds (if it's not use meanwhile).

Please see my testing environment (MS SQL 2017 – completely controled by me, no other user or activity):

EXEC dbo.ProcInTheCache

SELECT 
    cp.cacheobjtype
    ,cp.objtype
    ,st.text
    ,qp.query_plan
    ,cp.plan_handle 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st

-- OK - proc dbo.ProcInTheCache - is IN the cache - IMG #1

-- **wait for 20s** or so, NO other activity on the server

SELECT 
    cp.cacheobjtype
    ,cp.objtype
    ,st.text
    ,qp.query_plan
    ,cp.plan_handle 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st

-- NO - proc dbo.ProcInTheCache - is NOT IN the cache - IMG #2

Any ideas why?

No, nobody runs anything to clean the cache (https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/). There is absolutely no activity on the server, no CPU/RAM stress, nothing. Optimize for AdHoc workload is FALSE (but it's no AdHoc statement anyway).

Of course I'm asking not because it happens just in my testing environment, but it is a problem in production.

---

Additional info:

@@VERSION is Microsoft SQL Server 2017 (RTM-CU4) (KB4056498) – 14.0.3022.28 (X64)

I can see a lot of these lines in MS SQL Server ErrorLog:

Source spid10s Message A significant part of sql server process
memory has been paged out. This may result in a performance
degradation. Duration: 329 seconds. Working set (KB): 223964,
committed (KB): 481136, memory utilization: 46%.

But the times do not correspond exactly with plan cache flushes.

---

Here is the output of EXEC dbo.sp_Blitz @OutputType = 'MARKDOWN'

Priority 1: Backup:

• Backing Up to Same Drive Where Databases Reside – 1 backups done on drive C:\ in the last two weeks, where database files also live. This represents a serious risk if that array fails.

• Backups Not Performed Recently

  • H08DJ – Last backed up: Dec 15 2017 2:24PM

  • master

  • model

  • msdb

Priority 1: Reliability:

• Last good DBCC CHECKDB over 2 weeks old

  • H08DJ – Last successful CHECKDB: 2015-03-06 13:00:38.567

  • master

  • model

  • msdb

Priority 10: Performance:

• Auto-Shrink Enabled

  • H08DJ – Database [H08DJ] has auto-shrink enabled. This setting can dramatically decrease performance.

• Plan Cache Erased Recently – The oldest query in the plan cache was created at Jun 29 2018 4:56PM. Someone ran DBCC FREEPROCCACHE, restarted SQL Server, or it is under horrific memory pressure.

Priority 20: File Configuration:

• TempDB on C Drive tempdb – The tempdb database has files on the C drive. TempDB frequently grows unpredictably, putting your server at risk of running out of C drive space and crashing hard. C is also often much slower than other drives, so performance may be suffering.

Priority 20: Reliability:

• Memory Dumps Have Occurred – That ain't good. I've had 10 memory dumps between Dec 2 2017 6:48PM and May 3 2018 12:44PM!

• User Databases on C Drive

  • H08DJ – The H08DJ database has a file on the C drive. Putting databases on the C drive runs the risk of crashing the server when it runs out of space.

Priority 50: DBCC Events:

• Overall Events – 1 DBCC events have taken place between Jun 4 2018 10:38PM and Jun 4 2018 10:38PM. This does not include CHECKDB and other usually benign DBCC events.

Priority 50: Reliability:

• Page Verification Not Optimal

  • H08DJ – Database [H08DJ] has TORN_PAGE_DETECTION for page verification. SQL Server may have a harder time recognizing and recovering from storage corruption. Consider using CHECKSUM instead.

• Remote DAC Disabled – Remote access to the Dedicated Admin Connection (DAC) is not enabled. The DAC can make remote troubleshooting much easier when SQL Server is unresponsive.

Priority 50: Server Info:

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

Priority 100: Performance:

• Max Memory Set Too High – SQL Server max memory is set to 2147483647 megabytes, but the server only has 8069 megabytes. SQL Server may drain the system dry of memory, and under certain conditions, this can cause Windows to swap to disk.

Priority 110: Performance:

• Active Tables Without Clustered Indexes
  • H08DJ – The [H08DJ] database has heaps – tables without a clustered index – that are being actively queried.

Priority 150: Performance:

• Inactive Tables Without Clustered Indexes

  • H08DJ – The [H08DJ] database has heaps – tables without a clustered index – that have not been queried since the last restart. These may be backup tables carelessly left behind.

• Triggers on Tables

  • H08DJ – The [H08DJ] database has 703 triggers.

Priority 170: File Configuration:

• File growth set to 1MB

  • H08DJ – The [H08DJ] database file C:\SQL\SQL2017\Data\H08DJ.mdf is using 1MB filegrowth settings, but it has grown to 1 GB. Time to up the growth amount.

• 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 16 2015 12:31PM

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: Licensing:

• Non-Production License – We're not the licensing police, but if this is supposed to be a production server, and you're running Developer Edition (64-bit) the good folks at Microsoft might get upset with you. Better start counting those cores.

Priority 200: Monitoring:

• Agent Jobs Without Failure Emails

  • The job mdw_purge_data_[H08DJ] has not been set up to notify an operator if it fails.

  • The job syspolicy_purge_history has not been set up to notify an operator if it fails.

  • The job sysutility_get_cache_tables_data_into_aggregate_tables_daily has not been set up to notify an operator if it fails.

  • The job sysutility_get_cache_tables_data_into_aggregate_tables_hourly has not been set up to notify an operator if it fails.

  • The job sysutility_get_views_data_into_cache_tables has not been set up to notify an operator if it fails.

• No Alerts for Corruption – SQL Server Agent alerts do not exist for errors 823, 824, and 825. These three errors can give you notification about early hardware failure. Enabling them can prevent you a lot of heartbreak.

• No Alerts for Sev 19-25 – SQL Server Agent alerts do not exist for severity levels 19 through 25. These are some very severe SQL Server errors. Knowing that these are happening may let you recover from errors faster.

• No failsafe operator configured – No failsafe operator is configured on this server. This is a good idea just in-case there are issues with the [msdb] database that prevents alerting.

• Not All Alerts Configured – Not all SQL Server Agent alerts have been configured. This is a free, easy way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.

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.

• Database Mail XPs – This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

• filestream access level – This sp_configure option has been changed. Its default value is 0 and it has been set to 1.

• Ole Automation Procedures – 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:

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

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

  • H08DJ

• User-Created Statistics In Place

  • H08DJ – [H08DJ] has 4 user-created statistics. This indicates that someone is being a rocket scientist with the stats, and might actually be slowing things down, especially during stats updates.

Priority 210: Non-Default Database Config:

• Read Committed Snapshot Isolation Enabled H08DJ – This database setting is not the default.

• Snapshot Isolation Enabled H08DJ – This database setting is not the default.

Priority 210: Non-Default Database Scoped Config:

• LEGACY_CARDINALITY_ESTIMATION – Set value: 1 Default: 0 Set value for secondary: Empty Default value for secondary: Empty

  • H08DJ

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\SQLAgent$SQL2017. 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\MSSQL$SQL2017. I wish I had an Active Directory service account instead.

Priority 250: Server Info:

• Agent is Currently Offline – Oops! It looks like the SQL Server Agent (SQL2017) service is Stopped. The startup type is Manual.

• Default Trace Contents – The default trace holds 1484 hours of data between Apr 28 2018 8:14PM and Jun 29 2018 4:58PM. The default trace files are located in: C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Log

• Drive C Space – 8992.00MB free on C drive

• Hardware – Logical processors: 4. Physical memory: 7GB.

• Hardware – NUMA Config – Node: 0 State: ONLINE Online schedulers: 4 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 20

• Instance Stacking – Your Server has 6 Instances of SQL Server running. More than one is usually a bad idea. Read the URL for more info

• Operating System Version – You're running Windows 10 Pro, version 10.0

• Power Plan – Your server has 2.30GHz CPUs, and is in balanced power mode — Uh… you want your CPUs to run at full speed, right?

• Server Last Restart – Jun 26 2018 9:19PM

• Server Name – DOLEZALJI-NB\SQL2017

• Services

  • Service: SQL Server (SQL2017) runs under service account NT Service\MSSQL$SQL2017. Last startup time: Jun 26 2018 9:20PM. Startup type: Automatic, currently Running.

  • Service: SQL Server Agent (SQL2017) runs under service account NT Service\SQLAgent$SQL2017. Last startup time: not shown.. Startup type: Manual, currently Stopped.

• SQL Server Last Restart – Jun 26 2018 9:20PM

• SQL Server Service – Version: 14.0.3022.28. Patch Level: RTM. Cumulative Update: CU4. Edition: Developer Edition (64-bit). Availability Groups Enabled: 0. Availability Groups Manager Status: 2

Priority 254: Rundate:

• Captain's log: stardate something and something…

EXEC sp_Blitz @CheckServerInfo = 1

EXEC sp_Blitz @OutputType = 'markdown'

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'access check cache bucket count',512;
exec sp_configure 'access check cache quota',2048;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO