Stub plan

 My boss has started SQL performance-related sessions, and I have benefited a lot. I'd like to share what I have learned from these sessions.

Q: How many entries does the CACHESTORE_SQLCP have if I execute the following two T-SQL statements sequentially and auto-parameterization is used.

select *From t20240712 where c1=1

go

select *From t20240712 where c1=2

A: It has three entries. One real query plan and two stub plans pointing to the real query plans.

SELECT t.text, p.query_plan,  cp.plan_handle, cp.objtype ,cp.size_in_bytes,cp.usecounts  

FROM sys.dm_exec_cached_plans   cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)    p

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)  t

WHERE text like '%t20240712%' and text not like '%dm_exec_cached_plans%'

Here are the screenshots of 'stub plan' and 'real plan', which shows that 'stub plan' is just a shell.

Too many stub plans may reach the soft quota limit that cause plan CACHESTORE_SQLCP run into memory pressure.

When CACHESTORE_SQLCP has memory pressure, it will purge query plans. Becuse the cost of Stub plans is 0, it will be purged first when memory pressure happens.

select original_cost,current_cost,text From sys.dm_os_memory_cache_entries ce 

inner join sys.dm_exec_cached_plans  cp on cp.memory_object_address=ce.memory_object_address

CROSS APPLY sys.dm_exec_sql_text(plan_handle)  

where text like '%t20240712%'

and text not like '%dm_exec_cached_plans%'

----------------T-SQL Table creation ------------

drop table if exists t20240712

create table t20240712(c1 int)

----------------T-SQL Table creation ------------