NULL value under Object_name

• Arsh

  SSCertifiable

  Points: 6354

  More actions

  October 13, 2021 at 5:45 am

  #3940029

  Dear Friends,

  Please advise why there appears NULL value under Object_name in sys.dm_exec_sql_text(sql_handle). Below is my complete piece of code to get the TOp 100 Code running on the server.

   

  "SELECT top 100

  OBJECT_NAME(qt.objectid),

  qs.total_worker_time CPU,

  qs.last_worker_time Last_CPU,

  qs.last_execution_time,

  qs.execution_count,

  qs.total_logical_reads,

  qs.total_physical_reads,

  qs.total_logical_writes,

  qp.query_plan

  FROM sys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

  CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

  where query_plan is not NULL

  ORDER BY qs.total_logical_reads DESC -- logical reads"

  Thanks and Regards

  Arshad

• Ant-Green

  SSC Guru

  Points: 113509

  More actions

  October 13, 2021 at 7:15 am

  #3940049

  Adhoc statements will be null

  Object_Name function only works in the correct database context if you supply object_id only.

  You need to pass it the database id also to get the right object from the right database.