June 24, 2011 at 9:11 am
I have been tasked with identifying stored procedures to be deprecated in an older application, running on SQL2K5 SP2. There are several thousand stored procedures that were put into the database over many years of development. I have been capturing the contents of dm_exec_query_stats weekly for 6 months and want to use this data to help identify the unused/inactive SPs. My thinking is that any object that hasn't had a cached query plan for 6 months hasn't been used and is a candidate for deprecation. My concern is that a SP which was executed in the last 6 months did not show up in the weekly query_stats data and may be marked for deprecation incorrectly.
Does this seem like the correct process? Are there any problems/faults/"gotchas" that I should be concerned with?
Thanks.
June 24, 2011 at 2:59 pm
A couple
http://sqlinthewild.co.za/index.php/2009/07/10/why-the-dmvs-are-not-a-replacement-for-sql-trace/
Weekly is no where near enough. Hourly maybe, with the exception of plans that are never cached. If you need a correct record of procedures executed, modify each to log to a table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2011 at 5:13 pm
I'd completely agree with Gail. Weekly isn't granular enough. I'd go with a better trace or logging.
June 25, 2011 at 9:35 am
Thank you for the response. The issue for us with capturing data more frequently is storage: I'm pulling the stats from about 55 servers (it's a saas-app). I will explore using traces and a more frequent data capture.
Thanks again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply