How do I know a query plan is invalidated?

329 views Asked by At

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.

0

There are 0 answers