Regarding Query Plan Caching on Azure SQL Elastic Database

The plan cache getting cleared frequently is not exactly normal, but it suggests that something is going on.

The only situation the service will cause the plan cache to be cleared is in case of a failover. That should not happen that frequently.

The other possibility is that you or the application are running commands that clears the cache, for instance DBCC FREEPROCCACHE, but it can also be configuration changes like changing the compatibility level.

I am also curious how you conclude that the cache has been cleared entirely. On what do you base this observation?

Please make sure stored procedures/queries do not have the "WITH RECOMPILE" clause because as explained here, query plans created with that option won't be cached.

Azure planned maintenance, some transient errors and scale up/down or changing settings on the elastic pools could end clearing the query cache also. Try to keep pool size and settings steady.

Try enabling "Force Last Good Plan" on the automatic tuning options and use the history of query plans stored on Query Store.

Hello Vandana Kulkarni,

Thank you for posting your question on the Microsoft Q&A portal and for the community members who have already provided helpful responses which covered common causes like failovers and the WITH RECOMPILE hint.

I understand you're facing frequent query plan cache clearing, which is leading to query timeouts and impacting your end users.

In addition to the suggestions provided by the community members, some other common reasons for frequent plan cache clearing and additional pointers you can investigate:

• When the SQL Server engine is under memory pressure, it may evict less frequently used plans from the cache to free up space. This can be caused by a sudden spike in resource usage in one or more databases within the elastic pool. You can monitor memory pressure using the sys.dm_os_performance_counters DMV (Dynamic Management Views).
• When statistics on a table are updated, either automatically or as part of a maintenance plan, it can invalidate query plans that were using the old statistics. This forces the queries to recompile and can lead to a plan being cleared from the cache. You can look for recent statistics updates in the sys.dm_db_stats_properties DMV.
• Any DDL (Data Definition Language) operations, such as creating, altering, or rebuilding indexes, or adding/removing columns from a table, will cause query plans that reference those objects to be marked as invalid and cleared from the cache.
• While not a cause for a full cache clear, a query with different parameter values might get a suboptimal plan. Over time, this can cause the old plan to be replaced by a new one for a different parameter value, giving the appearance of frequent clearing for a single query.
• While already mentioned, it's worth re-emphasizing. Azure SQL Database uses high availability replicas. A failover will typically clear the plan cache on the new primary replica. You can check for recent failovers using the sys.dm_os_sys_info DMV, specifically the sqlserver_start_time column.

To better understand your specific situation and pinpoint the cause, could you please share the following details:

How are you currently observing and measuring that the plan cache is being cleared frequently? Are you using specific DMVs like sys.dm_exec_cached_plans?

1. Are there any DDL operations (like ALTER TABLE or CREATE INDEX) being performed on the database during these periods?
2. Can you confirm if you are explicitly using the WITH RECOMPILE hint or any similar recompile options in your queries or stored procedures?

References

1. SQL Server Plan Cache: https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-plan-caching-and-recompilation?view=sql-server-ver16
2. sys.dm_exec_cached_plans (Transact-SQL): A DMV that can be used to monitor query plans in the cache. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-cached-plans-transact-sql?view=sql-server-ver16
3. Memory Grants and Query Performance in Azure SQL Database: https://techcommunity.microsoft.com/blog/azuredbsupport/memory-grant-feedback---sql-2019-new-feature-applied-to-azure-sql-db/1020997
4. https://learn.microsoft.com/en-us/azure/azure-sql/database/query-performance-insight-use?view=azuresql

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Thanks and Happy to help

Pratyush