I'm executing
EXEC sp_recompile <tablename>
but I still see query plan cached for this table:
SELECT
CP.[objtype]
, CP.[cacheobjtype]
, CP.[size_in_bytes]
, CP.[refcounts]
, CP.[usecounts]
, ST.[text]
FROM
sys.dm_exec_cached_plans as CP
CROSS APPLY sys.dm_exec_sql_text (CP.[plan_handle]) as ST
WHERE
1 = 1
AND ST.[text] NOT LIKE '%dm_exec_cached_plans%'
AND ST.[text] LIKE '%<tablename>%'
ORDER BY
CP.[objtype]
Why this happens?
I'm thinking the plan is just invalidated and it will update whilst next execution. Does exist a way to find (maybe in DMVs) if this plan is invalidated (or marked for recompilation).
Any helps is appreciated.