It is quite a common requirement to track the time taken by the Stored Procedures in SQL Server. Sometimes, we use custom logic to track the requests, like creating a variable with start time when the procedure execution starts, another variable with end time when the execution ends, and calculating the difference between the two variables to get the execution time.
However, we have another built-in option to get the complete list of statistics of the executed procedures. This list contains only for the cached procedures. If the procedure is altered, then the data is removed for the procedure and is only available when it is executed again. This is through the use of the sys.dm_exec_procedure_stats view in SQL Server. Just run the statement.
- SELECT * FROM sys.dm_exec_procedure_stats
and see the results. Some of the main factors it provides, include the following:
- objectid which can be joined with sys.objects table or use the Object_Name function, to retrieve the procedure name.
- cached_time time when the procedure was added to the cache.
- type type of stored procedure i.e. normal procedure or clr procedure or extended stored procedure
- last_execution_time last time the procedure was executed.
- last_elapsed_time total time taken in milliseconds, last time the procedure was executed.
Apart from these, it also provides information about last logical/physical reads and writes performed by the procedure. A complete list of the information provided is available on
MSDN.
Hope you enjoyed reading it. Happy querying!!!