June 17, 2009 at 2:50 pm
We are starting to have quite a few stored procedures in our DB. A lot are no longer needed, for various reasons. We are in the process of moving our DB into source control, and would like to 'clean up' as much of the unneeded stuff as possible.
I've read a few articles that claim there is not really a good way to find the time a stored procedure was last run. Is this true?
I've also read a few articles that say using SQL Profiler is probably the best bet. Is this true? I have only used profiler a few times, and am anxious to become more familiar with it. Any links to good articles about it?
Thanks all!
June 17, 2009 at 4:09 pm
I went through a similar exercise a long time ago - using SQL 2000. We listed all stored procedures, found all references to them in the application code (so those were excluded - or at least someone elses responsibility), found any in the procedure cache - There is a way of checking this in SQL 2005 - do a google on sys.dm_exec_cached_plans - So those must have been run recently. I ended up identifying several procedures where we didn't know if they were required or not. So added a line to each to insert/update a row into a table to track their usage.
After a couple of months look at the ones with no hits. That involved altering all the stored procedures I was unsure of. I'd do it differently today, I'd add a scheduled job to search the cached plans periodically for matches in a table of procedures - as each one was found the job would remove that procedure from the table - eventually you'd end up with only the ones that had never been run.
June 17, 2009 at 10:43 pm
If you want a great learning resource for Profiler, I would recommend Brad McGehee's free e-book "Mastering SQL Server Profiler."
http://www.red-gate.com/products/SQL_Response/offers/mastering_sql_profiler_ebook.htm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply