SQL Stored procedure is continually reparsed and recompiled, causing significant delay in response

Question

Our organization has a stored procedure, that seems to always be recompiled and reparsed nearly every execution, adding upwards of 14 seconds to overall execution time. The procedure is frequently but irregularly run, so it becomes common for users to experience this compile/parse delay on every execution, although subsequent executions say within SSMS will avoid this. Ex. A loop will only compile on initial run, but subsequent calls are fine. After a wait of 20-30 seconds SQL will recompile again.

This is a highly populated pool around 240 DBs across 40-vCores, although typically underutilized and stays around 60-70% or lower. Migrating an individual DB to a less populated and bigger pool seems to help, lower compile time & stays in cache longer, but is there some limit we're coming up against that we're not aware of? proc cache memory issue?

Answer 1

Let's make some hypothesis :

• If the stored procedure uses parameters, SQL Server might generate different execution plans based on the initial parameter values. If subsequent executions use different parameter values, SQL Server might decide to recompile the plan.
• If the statistics on the underlying tables change frequently, SQL Server might decide to recompile the stored procedure to generate a new execution plan.
• If the stored procedure uses temporary tables, SQL Server might recompile the procedure if it detects changes in the temp table schema or data.
• If the SET options (for example ANSI_NULLS, QUOTED_IDENTIFIER) change between executions, SQL Server might recompile the stored procedure.
• If there is memory pressure on the server, SQL Server might evict execution plans from the cache more frequently, leading to recompilations.
• In a multi-tenant environment like Azure SQL Database, the plan cache might be evicted more frequently due to resource governance policies.

What I recommend ?

Try to optimize your stored procedure.

You can usequery hints like OPTION (RECOMPILE) or OPTION (KEEPFIXED PLAN) to control recompilation behavior but don't forget that they can have side effects.

Try se plan guides to force a specific execution plan for the stored procedure.

If you want you can use DMVs to monitor recompilation events and analyze the causes. For example:

SELECT 

sql_text.text, 

exec_stats.execution_count, 

exec_stats.total_worker_time, 

exec_stats.total_elapsed_time, 

exec_stats.total_logical_reads, 

exec_stats.total_logical_writes, 

exec_stats.total_physical_reads, 

exec_stats.creation_time, 

exec_stats.last_execution_time, 

exec_stats.plan_handle

FROM 

sys.dm_exec_query_stats AS exec_stats

CROSS APPLY 

sys.dm_exec_sql_text(exec_stats.sql_handle) AS sql_text

WHERE 

sql_text.text LIKE '%YourStoredProcedureName%'

ORDER BY 

exec_stats.last_execution_time DESC;