In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get the number of times each procedure has been called during a certain timespan?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
You can get this (and more) from Dynamic Management Views (DMVs). To get statistics for a particular stored procedure, try the following query.
To look at the most frequently executed procedures:
The values reported are cumulative since the last restart. If you want to measure over a fixed period, use the command below to reset wait stats.
If you wanted to measure fixed timespans across the day, you could feed the query output to a table via an agent job and either a) calculate the values between two runs or b) issue the wait stats reset as the last step in the agent job.
Alternatively, capture a profiler trace and run it through Clear Trace.