November 27, 2007 at 11:22 am
Greetings all and thanks for reading my post. I am trying to find a way to determine when the last date/time a stored procedure has been executed. I am dealing with a VLD and really can't afford to have profiler constantly running.
Is there a table that I can query that would provide me this information?
Thanks in advance.
Kurt W. Zimmerman
DBA
RHWI, Inc.
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 27, 2007 at 2:07 pm
Here is my cent.
As far as I know, the profiler is the only built-in tool in SQL to do this task. You are right. We cannot afford running the profiler against a large and heavily used database, in general. But we can set filter only focus at specified objects, such as stored procedures while running the profiler.
December 27, 2007 at 2:14 pm
Thanks for your reply. Here is my problem. Having over 1200 stored procedures and knowing that the database that I'm now support has been around for quite some time I have no way of knowing what is obsolete.
The thought was to have something that provided me a simple way of obtaining Last Execution time to make this job a bit easier.
Historically the management of the database has been OK however I have no idea what is obsolete.
My work is cut out for me... that's for sure.
Again thanks.
Kurt
DBA
RHWI, Inc
Poughkeepsie, NY
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 30, 2007 at 6:53 am
Hi!
I haven't tried this at all myself, but it might be worth a try: Check out the execution plan maintenance view, sys.dm_exec_cached_plans in BOL.
Also, server side trace (SQL Trace) is much less expensive than client side trace (SQL Profiler trace).
Regards Niklas
December 30, 2007 at 11:16 am
The DMV for plans shows what's in cached, but that doesn't tell you what's been run recently. If you had lots of different procs run, you might see them getting booted out and cache regularly.
You can set a server side trace, low impact, and just get sproc calls. Just get the ending event and then run that into a table, find the last call for each one, discard the rest. Over a few months you'll get an idea of what's being run.
Be very careful about removing procs and be sure you keep the code around. There are lots of yearly processes that might use a sproc once a year, so if you remove it, you might cause issues. Old sprocs might be annoying, but they don't hurt.
February 7, 2008 at 12:34 pm
Or, if you like to be kept insanely busy, remove all the sprocs and then restore them as processes fail. You'll only be manic for the first two weeks or so, until the 'popular' sprocs are restored. As Steve suggested, definitely make backup copies of whatever you pull, so you can restore them easily. Perhaps script each one into a separate file for ready reference?
Is it April 1 yet?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply