We have a SQL Server 2016 SP1 with max memory set to 24GB.
This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored in the plan cache but the size of the plan cache is not increasing (approx 3.72GB).

I suspect that there is local memory pressure that lead to removal of plans from the cache. The plan cache pressure limit is 5GB. (75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory>64GB). When a cachestore reaches 75% of the pressure limit, plans should be removed from the cache. In my case 75% of 5 GB is 3.75GB. So it is plausible this is the cause of the high compiles.

Is there a way to measure (perfmon, extended events, ...) the removal from plans out of the cache? So I can be certain local memory pressure is really the cause of the high compiles?

up vote 6 down vote accepted

There's an XEvent for that:

query_cache_removal_statistics

Occurs when a query plan is removed from the plan cache and the historical statistics for the object are about to be destroyed

So something like:

CREATE EVENT SESSION [PlanCacheEvictions] ON SERVER 
ADD EVENT sqlserver.query_cache_removal_statistics(
    ACTION(sqlserver.sql_text))

Also if your plan cache has a large number of single-use plans consider setting optimize for ad hoc workloads.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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