Post reply

DMVs - What causes NULL Query_Plan values in sys.dm_exec_cached_plans

  • June 8, 2012 at 8:46 am

    #275643

    Hello,

    1. I am trying to understand when\why SQL will sometimes create the XML of the query plan and other times show NULL Query_PLans in sys.dm_exec_cached_plans.

    2. When the SQL services are stopped and restarted it clears out from memory all the data for sys.dm_exec_query_stats, sys.dm_exec_cached_plans and other DMVs.

    SQL Server dynamically manages when it purges records out and when it retains data. Does anyone know the algorithm for determining it? For some data, it will keep from when the services was restarted, for other executed query, it will remove immediately.

    Thanks

    Daniel

  • June 8, 2012 at 1:04 pm

    #1498894

    While I am not 100% sure, I've always under the impression that procedures that had WITH RECOMPILE on them didn't retain the execution plans...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • June 8, 2012 at 1:34 pm

    #1498914

    Thanks. That may be one of the cases but it doesn't count for the high % of NULL Query_Plans.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply