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

Immy
No. SQL Server Profiler is your friend



Show quoteHide quote

I would rather have a log table and have an insert (getdate()) to the log
table as the first line of the proc.

Hi
Could be , however it's overhead, especially in OLTP applications


Show quoteHide quote

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?

Yes, you are



Show quoteHide quote

Immy (therealasianb***@hotmail.com) writes:
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.

Help with varchar to date
schedule dts at a remote server
can a SELECT return this result ?!
can a SELECT return this result ?!
Issue with T-SQL and SQL 7 to SQL2000 conversion