Plan Handle exists but NO QUERY PLAN?

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  February 9, 2023 at 8:55 pm

  #4147769

  Is this a fully legit situation in the result of the below query to have Plan Handle but NO QUERY PLAN?

  SELECT * FROM

  ( SELECT

  DBName = DB_NAME(ps.database_id)

  , ObjectName = OBJECT_NAME(ps.object_id, ps.database_id)

  , cached_time = ps.cached_time

  , last_execution_time = ps.last_execution_time

  , last_elapsed_time_secs = ps.last_elapsed_time / 1000000.0

  , execution_count = ps.execution_count

  , PlanHandle = ps.plan_handle

  , QueryPlan = qp.query_plan

  FROM sys.dm_exec_procedure_stats AS ps

  CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp

  CROSS APPLY (SELECT CONVERT(int, pa.value) AS value

  FROM sys.dm_exec_plan_attributes(ps.plan_handle) AS pa

  WHERE pa.attribute IN ('set_options')) AS so

  ) x

  WHERE ObjectName IN ('my object name')

   

  

  Likes to play Chess

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  February 9, 2023 at 8:58 pm

  #4147770

  Never mind :).

  My boss just pointed me to the answer here:

  Look here for details of that DMV

  https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-transact-sql?f1url=%3FappId%3DDev15IDEF1%26l%3DEN-US%26k%3Dk(dm_exec_query_plan_TSQL)%3Bk(sql13.swb.tsqlresults.f1)%3Bk(sql13.swb.tsqlquery.f1)%3Bk(MiscellaneousFilesProject)%3Bk(DevLang-TSQL)%26rd%3Dtrue&view=azuresqldb-current

    under the Remarks section.

  Likes to play Chess