1

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.

user3104183
  • 408
  • 1
  • 9
  • 26
  • 1
    @Martin Smith: you're right, i've updated the question.. – user3104183 Dec 17 '13 at 15:28
  • 2
    `EXEC sp_recompile 'YourTable'` bumps up the `modify_date` on `YourTable`. I assume this is stored in the plan but don't think it is exposed to us in any way. [Trace Flag 8666](http://blogfabiano.com/2012/07/03/statistics-used-in-a-cached-query-plan/) doesn't show it. – Martin Smith Dec 17 '13 at 16:02
  • 2
    Actually it wouldn't need to store that. It already knows the plan `creation_time` (shown in `sys.dm_exec_query_stats`) so if the modify_date is later when it comes to execute it then it knows it needs to recompile. – Martin Smith Dec 17 '13 at 17:29

0 Answers0