February 13, 2008 at 3:55 am
I'm trying to use the sys.dm_exec_query_plan dmv to figure out which procedures are being called the most in our database. I got some sample code off the web somewhere that I used some days ago, and it was working fine:
SELECT TOP 10
total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan,
object_name(objectid) as procname,
objectid,
qs.execution_count,qs.creation_time,qs.last_execution_time,
(total_worker_time/(execution_count * 1000.0)) as avg_worker_time_in_millisecs
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY execution_count DESC
This no longer works as the procname column is always NULL. A left join against sys.all_objects also returns NULL for all names.
So it seems that the objectid value from the dmv is simply wrong. How can this be?
February 13, 2008 at 9:15 am
Make sure you're running this in the database that the procs are in. Object_name, when passed one param, looks in the current DB.
If you're running SP2, you can use the expanded object name function
object_name(objectID, DBID)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2008 at 1:15 am
Ah. Of course! I was probably running the query from the master database which is picked by default by sql managment studio. Now I feel a bit silly. Thanks for the help 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply