Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

'SQL Server Citation is a SQL Server blog by Hemantgiri S. Goswami, Surat,India. He is a SQL Server Specialist and SQL Server MVP,Author, President and Leader of Surat User Group
Browse by Tags · View All
Sql server 2008 14
sql 2005 10
sql 2008 10
sql server administration 10
sql server 2005 10
sql 2012 9
sql 2008 r2 9
sql server 2012 8
sql 2012 r2 6
sql 2000 6

Archive · View All
September 2011 9
March 2012 5
December 2011 4
January 2012 4
November 2011 3
April 2013 2
March 2013 2
October 2012 2
October 2011 2
December 2012 1

SQL Server Citation

Last Execution Date Time of a Stored Procedure

Mar 11 2012 12:00AM by Hemantgiri S. Goswami   

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 -
USE DBName
GO
SELECT 
 O.name,
 PS.last_execution_time
FROM 
 sys.dm_exec_procedure_stats PS 
INNER JOIN sys.objects O 
 ON O.[object_id] = PS.[object_id] 
GO
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)


Republished from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP [15 clicks].  Read the original version here [2 clicks].

Hemantgiri S. Goswami
109 · 1% · 467
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Last Execution Date Time of a Stored Procedure" rated 5 out of 5 by 1 readers
Last Execution Date Time of a Stored Procedure , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]