Why Did a Plan Get Removed from Cache?

I was recently asked if we could tell why a plan was removed from cache. If you read this blog, you know what I'm going to say next. I checked the extended events and there are actually two different events that will tell us information about a plan removed from cache; sp_cache_remove and query_cache_removal_statistics. Let's talk about how these work.

Removed From Cache

Just so we can see ALL the activity, I'm creating an Extended Events session that captures a little more than just the two events:

CREATE EVENT SESSION PlanCacheRemoval
ON SERVER
ADD EVENT sqlserver.query_cache_removal_statistics
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.rpc_completed
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.rpc_starting
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sp_cache_hit
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sp_cache_insert
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sp_cache_miss
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sp_cache_remove
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sp_statement_completed
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sp_statement_starting
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sql_batch_completed
(WHERE (sqlserver.database_name = N'AdventureWorks2017')),
ADD EVENT sqlserver.sql_batch_starting
(WHERE (sqlserver.database_name = N'AdventureWorks2017'))
ADD TARGET package0.event_file
(SET filename = N'C:\PerfData\PlanCacheRemoval.xel')
WITH (TRACK_CAUSALITY = ON);

I'm capturing batch start and complete, rpc start and complete, and finally all the cache statements, hit, miss, insert and remove. The first time I run a procedure, the results could look like this:

Since this is the first time running the procedure, we get a sp_cache_miss event since the query isn't there. We then see the sql_batch_starting (I'm running the procedure from SSMS). Following is the sp_cache_insert event as the plan gets added to the cache. We then see all the statements run, plus recompiles because I tossed in a temporary table for fun (so you can see what happens).

At the end of this, the plan is in cache. Now, let's do something to cause the plan to be removed from cache. I'll modify the procedure. This results in the following events:

You'll note that the second statement in the sequence is "CREATE OR AL..." in the batch_text. That's me modifying the procedure. The very next event is sp_cache_remove. It shows the remove_method as "Compplan Remove". This the plan being removed in an automated way from cache. The next three events are all for query_cache_removal_statistics.

What are they?

These are the statement level statistical information being removed from the DMVs. That's right, we can observe that information getting removed from the system along with the plan from cache.

Manually Removed From Cache

We can easily see the automated processes removing plans from cache. What happens if we were to use the database scoped configuration to clear out the procedure cache for this database (NOTE: you'll want to run your procedure at least once so that it's in there to be removed):

You can again see the "Compplan Remove" method being used. What happens when we nuke the cache using DBCC FREEPROCCACHE:

There are hundreds of query_cache_removal_statistics events and then multiple different "... Flush" events to remove everything.

Conclusion

While I'm not getting into all the details of how objects are removed from cache, you can see that it is possible to get some understanding of how and why objects were moved out of cache using extended events. Combining all the events through causality tracking makes it much easier to see the order in which these events occurred.