NULL value under Object_name

  • 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

  • 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.

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

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