I have a stored proc that I was trying to find out how much it's being called for performance reasons. I looked in sys.dm_exec_procedure_stats, at the execution_count column, but this number seems extremely high for what I expected. So, I opened up Profiler and did a trace on the same proc, but the count was significantly less than the DMV. I see the execution_count increment without an event in Profiler.
Does anyone know why this is? In Books Online, the description for the the execution_count column is: "Number of times that the stored procedure has been executed since it was last compiled." Why am I not seeing the count grow at the same rate as the events being captured by Profiler?
Thanks in advance.
The sys.dm_exec_procedure_stats returns aggregate performance statistics for cached stored procedures which will return number of times since the last compiled. But the execution times in the SQL profiler is the number of times after you opened SQL profiler.
More information about sys.dm_exec_procedure_stats: http://msdn.microsoft.com/en-us/library/cc280701.aspx.
Hope this helps.
Microsoft Online Community Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
I appreciate your reply, but I understand that the count of Profiler trace events would be less than the execution_count column from the DMV.
What I am confused about is this... I see the execution_count increment without an event in Profiler. Why am I not seeing the count grow at the same rate as the events being captured by Profiler?
For my stored proc in question, I was seeing the DMV count increment much faster than the events show up in Profiler.
Is the proc you are trying to watching is exec in a very high frequency? Is the sever a very busy server? I mean, besides the sp events, there are lots of other events generated at the same time, right? If this is the situation, I think the scenario you describe might be cause by profiler dropping some of the events.
Profiler will start to dropping event when it can no "catch up" the event generating frequency on server side, it doing this for limit the performance impact to the server. While DMV do not have this problem. I am not sure, but I think you can try server side trace instead of profiler to see if the number of sp events are closer to that in DMV.
Hope this will help.
- Proposed as answer by Alex Feng (SQL)Moderator Tuesday, December 22, 2009 5:17 AM