-2

I am experiencing a strange situation right now - my plan cache is almost empty. I use the following query to see what's inside:

SELECT dec.plan_handle,qs.sql_handle, dec.usecounts, dec.refcounts, dec.objtype
  , dec.cacheobjtype, des.dbid, des.text,deq.query_plan
FROM sys.dm_exec_cached_plans AS dec
join sys.dm_exec_query_stats AS qs on dec.plan_handle=qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE cacheobjtype = N'Compiled Plan'
AND objtype IN (N'Adhoc', N'Prepared')

It shows me 82 rows, while an hour before I couldn't reach the end of the plan cache issuing the same command.

Perfmon counters show low values for Plan Cache Hit Ratio - around 30%, high compilations - 400 out of 2000 batch requests per second and high CPU - 73 avg. What could cause this behaviour?

The main purpose of the question is to learn the possible reasons for an empty plan cache.

sp_Blitz results

Priority    FindingsGroup   Finding
0   Outdated sp_Blitz   sp_Blitz is Over 6 Months Old
0   sp_Blitz (TM) v39 as of Feb 16 2015 12:00AM From Brent Ozar Unlimited
1   Backup  Backups Not Performed Recently
1   Backup  Backups Not Performed Recently
1   Backup  Backups Not Performed Recently
1   Backup  Backups Not Performed Recently
1   Backup  Backups Not Performed Recently
1   Backup  Full Recovery Mode w/o Log Backups
5   Reliability Priority Boost Enabled
10  Performance Plan Cache Erased Recently
10  Security    SQL Agent Job Runs at Startup
10  Security    SQL Agent Job Runs at Startup
20  Reliability System Database on C Drive
20  Reliability System Database on C Drive
20  Reliability System Database on C Drive
20  Reliability Unsupported Build of SQL Server
20  Security    Elevated Permissions on a Database
50  Reliability Agent Jobs Without Failure Emails
50  Reliability Agent Jobs Without Failure Emails
50  Reliability Agent Jobs Without Failure Emails
50  Reliability Agent Jobs Without Failure Emails
50  Reliability Agent Jobs Without Failure Emails
50  Reliability Alerts Configured without Follow Up
50  Reliability Alerts Disabled
50  Reliability Last good DBCC CHECKDB over 2 weeks old
50  Reliability Last good DBCC CHECKDB over 2 weeks old
50  Reliability Last good DBCC CHECKDB over 2 weeks old
50  Reliability Last good DBCC CHECKDB over 2 weeks old
50  Reliability Max File Size Set
50  Reliability Max File Size Set
50  Reliability No failsafe operator configured
100 Performance Deadlocks Happening Daily
100 Performance File growth set to percent
100 Performance File growth set to percent
100 Performance File growth set to percent
100 Performance File growth set to percent
100 Performance File growth set to percent
100 Performance Memory Pressure Affecting Queries
100 Performance Poison Wait Detected: RESOURCE_SEMAPHORE
100 Performance Slow Storage Reads on Drive C
100 Performance Slow Storage Writes on Drive G
100 Performance Stored Procedure WITH RECOMPILE
100 Performance Stored Procedure WITH RECOMPILE
100 Performance Stored Procedure WITH RECOMPILE
100 Performance Uneven File Growth Settings in One Filegroup
110 Performance Active Tables Without Clustered Indexes
110 Performance Active Tables Without Clustered Indexes
110 Performance Inactive Tables Without Clustered Indexes
110 Performance Inactive Tables Without Clustered Indexes
110 Performance Inactive Tables Without Clustered Indexes
110 Performance Plan Guides Enabled
110 Performance Plan Guides Enabled
110 Performance Queries Forcing Join Hints
110 Performance Queries Forcing Order Hints
110 Performance Stats Updated Asynchronously
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
110 Performance Triggers on Tables
150 Security    Server Audits Running
200 Backup  MSDB Backup History Not Purged
200 Informational   Backup Compression Default Off
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Linked Server Configured
200 Informational   Tables in the Master Database
200 Informational   Tables in the Master Database
200 Informational   Tables in the Master Database
200 Informational   Tables in the Master Database
200 Informational   Tables in the Master Database
200 Informational   Tables in the Master Database
200 Informational   Tables in the Master Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Informational   Tables in the MSDB Database
200 Licensing   Enterprise Edition Features In Use
200 Licensing   Enterprise Edition Features In Use
200 Non-Default Server Config   Ad Hoc Distributed Queries
200 Non-Default Server Config   Agent XPs
200 Non-Default Server Config   Database Mail XPs
200 Non-Default Server Config   max degree of parallelism
200 Non-Default Server Config   max server memory (MB)
200 Non-Default Server Config   Ole Automation Procedures
200 Non-Default Server Config   optimize for ad hoc workloads
200 Non-Default Server Config   priority boost
200 Non-Default Server Config   remote admin connections
200 Non-Default Server Config   remote query timeout (s)
200 Non-Default Server Config   show advanced options
200 Performance Agent Jobs Starting Simultaneously
200 Performance User-Created Statistics In Place
200 Security    Database Owner <> SA
200 Security    Database Owner <> SA
200 Security    Database Owner <> SA
200 Surface Area    Endpoints Configured
210 Non-Default Database Config Read Committed Snapshot Isolation Enabled
210 Non-Default Database Config Snapshot Isolation Enabled
240 Wait Stats  1 - PAGEIOLATCH_SH
250 Server Info Default Trace Contents
250 Server Info Drive C Space
250 Server Info Drive G Space
250 Server Info Drive L Space
250 Server Info Drive T Space
250 Server Info Hardware
250 Server Info Hardware - NUMA Config
250 Server Info Server Name
250 Server Info Services
250 Server Info Services
250 Server Info SQL Server Last Restart
250 Server Info SQL Server Service
254 Rundate Apr 25 2017  3:11PM
255 Thanks! From Brent Ozar Unlimited

Memory is OK - min: 0 max: 245000.

I also didn't notice any signs of memory pressure - PLE, lazy writes, disk activity were just ok, logs did not tell me a thing. I came here for possible causes of this so I could proceed with investigation.

Performance Plan Cache Erased Recently is in the output because I cleared it manually, but the problem is not connected with that. It existed before.

Regarding "Backups Not Performed Recently": There are temporary archive databases which do not need backup.

closed as off-topic by Erik Darling, Philᵀᴹ, Marco, McNets, Colin 't Hart May 4 '17 at 8:48

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – Erik Darling, Philᵀᴹ, Marco, McNets, Colin 't Hart
If this question can be reworded to fit the rules in the help center, please edit the question.

1

One way to remove plans is DBCC FREEPROCCACHE. An extended events session would catch this.

Another would be a restart of the SQL Server instance. This would show in the server logs, however.

I believe, but can't check right now, that dropping an SP removes the plan from cache. A large release could have done this. Check sys.procedures. I seem to recall that sp_refreshsqlmodule does this too. SQL Server Data Tools seems very fond of invoking this.

A change to a widely-used object, such as a logging table, may also have invalidated the plans. Similarly if an index that was widely used changed significantly the corresponding plan would be affected.

Memory pressure would empty the cache. You've eliminated this possibility.

Do you employ any of the less frequent tools such as Service Broker or Stream Insight? Changes there may well reflect in the plans.

If this DB is a replication target changes at the source may perhaps, possibly affect it.

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