On many forums I have found a very basic yet important query - “How can I know when was my Stored Procedure last executed?” And today through this blog I will try to answer this question.
Actually speaking, without explicit logging or tracing, it is not possible to get this information for each and every Stored Procedure of our Database. However, we can get this detail along with many other relevant information for the stored procedure having it’s execution plan currently cached on the server by using - sys.dm_exec_procedure_stats It’s a system dynamic view that returns aggregate performance statistics for cached stored procedures. Please note that this view has been introduced from SQL Server 2008.
The important thing to note is that this view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.
So, let’s see a way, using which at least we can find out the some important facts for the cached stored procedures -
INNER JOIN sys.objects O
ON O.[object_id] = PS.[object_id]
P.S. Please replace the DBName with the actual name of the Database.
The above script will return the name of all the cached stored procedure of the current database with their last execution time.
For more details on this dynamics view, please refer - sys.dm_exec_procedure_stats (Transact-SQL)
can u help me to calculate the CPU & Memory usage base on the working set size of the each process running....i want the formula i dnt want to use java API or .net APIReplyDelete
Can you please let me know your exact requirement. I did not get you properly on - "CPU & Memory usage base on the working set size of the each process running" Here, what do you mean by working set size and what processes you are talking about.