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 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply