title: What causes DMV to show a NULL query plan?
body: <p>When I execute this simple SP and then check for the query plan from the DMV query that follows, the query plan comes back as NULL.  But if I comment out the else block in the SP (or change it to not reference temp table), the DMV query does show a query plan.  Any idea what is causing this difference?  And does a NULL query plan mean that nothing was cached?  I'm using SQL Server 2008 R2.</p>
  
  <pre><code>CREATE PROCEDURE dbo.SampleSp
  (
      @Option TINYINT
  )
  AS
      DECLARE @RowCount INT;
  
      -- Get ID into local temp table
  
      CREATE TABLE #P (ID INT PRIMARY KEY);
      INSERT INTO #P
      VALUES (1), (2)
  
      IF @Option = 1
      BEGIN
          SELECT ID FROM #P
      END
      ELSE
      BEGIN
          SELECT ID FROM #P
      END
  GO
  
      --Call SP
      EXEC dbo.SampleSp 1
  GO
      --DMV Query to find query_plan
      SELECT  
              CP.objtype, 
              CP.usecounts AS Ct, 
              ST.last_execution_time,
              SUBSTRING(Text, (statement_start_offset / 2) + 1, (CASE statement_end_offset WHEN -1 THEN DATALENGTH(Text) ELSE statement_end_offset END  - ((statement_start_offset / 2) + 1))) AS query,
              T.text,
              PL.query_plan
      FROM sys.dm_exec_cached_plans AS CP WITH(NOLOCK)
      CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS T
      OUTER APPLY sys.dm_exec_query_plan(CP.plan_handle) AS PL
      LEFT OUTER JOIN sys.dm_exec_query_stats AS ST WITH(NOLOCK) ON ST.plan_handle = CP.plan_handle
      WHERE 1=1
      AND ST.last_execution_time &gt; DATEADD(MINUTE, -5, GETDATE())
      AND T.text LIKE '%SampleSp%'
      AND T.text NOT LIKE '%dm_exec_cached_plans%'
      ORDER BY ST.last_execution_time DESC
  </code></pre>