October 13, 2021 at 5:45 am
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
October 13, 2021 at 7:15 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy