Last Execution Time of Stored Procedure

Hi all,
since seeing the post from Satya with regards to Alter time of proc, there's
been a question bugging me for quite a long time now.
Is there a way within SQLServer (2000) or 2005 where i can tell the last
time a proc was executed?
ImmyImmy
> Is there a way within SQLServer (2000) or 2005 where i can tell the last
> time a proc was executed?
No. SQL Server Profiler is your friend
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uj5H6udlGHA.4540@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> since seeing the post from Satya with regards to Alter time of proc,
> there's been a question bugging me for quite a long time now.
> Is there a way within SQLServer (2000) or 2005 where i can tell the last
> time a proc was executed?
> Immy
>|||I would rather have a log table and have an insert (getdate()) to the log
table as the first line of the proc.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi
Could be , however it's overhead, especially in OLTP applications
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:32205CF4-F393-4535-B88D-45B836FA2230@.microsoft.com...
>I would rather have a log table and have an insert (getdate()) to the log
> table as the first line of the proc.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Thats right. you would not be wanting to know the last execution time of a
Stored proc that gets executed frequently. My suggestion would be more
appropriate for a batch process.
Actually, i was thinking of this scenario where you have hundreds of procs
and you want to monitor the execution of a few of them. In that case you can
let the profiler to be running and keeping track of all the procs. Am I righ
t?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Yes, you are
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:DF96BA8C-73A7-42D7-80DE-E37B797B5B16@.microsoft.com...
> Thats right. you would not be wanting to know the last execution time of a
> Stored proc that gets executed frequently. My suggestion would be more
> appropriate for a batch process.
> Actually, i was thinking of this scenario where you have hundreds of procs
> and you want to monitor the execution of a few of them. In that case you
> can
> let the profiler to be running and keeping track of all the procs. Am I
> right?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Immy (therealasianbabe@.hotmail.com) writes:
> since seeing the post from Satya with regards to Alter time of proc,
> there's been a question bugging me for quite a long time now.
> Is there a way within SQLServer (2000) or 2005 where i can tell the last
> time a proc was executed?
In SQL 2005 there is, sort of. This is query lists the last execution
time for all SQL modules in a database:
SELECT object_name(m.object_id), MAX(qs.last_execution_time)
FROM sys.sql_modules m
LEFT JOIN (sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st)
ON m.object_id = st.objectid
AND st.dbid = db_id()
GROUP BY object_name(m.object_id)
But there are tons of caveats. The starting point of this query is
the dynamic management view dm_exec_query_stats, and the contents is
per *query plan*. If a stored procedure contains several queries,
there are more than one entry for the procedure in dm_exec_query_stats.
More importantly, if the procedure has no query plans at all, it will
not appear in dm_exec_query_stats. This could happen if you have a
procedures that just assign variables, or only calls a couple of other
stored procedure.
Furthermore, dm_exec_query_stats reflects what's in the *cache*. That is,
if the plans for a stored procedure falls out of the cache, so does the
information in sys.dm_exec_query_plans. How long a plan stays in the
cache depends on how lively the activity is on the server, and how
often the procedure is executed. Note that certain activities will
flush all plans for a table, for instance adding an index. Or restarting
SQL Server.
So whlle the query above can give you some interesting revelations,
you cannot reliably use it to determine that there are procedures
are not in use and that can be dropped.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx