March 3, 2014 at 7:47 am
How do you find stale stored procedures ?
In a scenario where a developer created a slight modification of a stored procedure because he was afraid of breaking something else and took the easy way out, and a few more later down the line, multiple versions of a stored proc. doing slightly different things are just laying around.
"Last used" would be useful piece of information to determine the most recent date a stored procedure was called, either by the application itself or by another stored procedure itself called by the application.
Any stored proc not used for more than say 6 months would then be identified as a candidate for clean-up.
So - short or creating - after the fact - a trigger to update the usage date upon each call - which means a lot of work and no result for the next six months, how can one go about this ?
And could this be done in SS2K8 ? (No, I'm not going to cross-post in the SS2K8 forum).
March 3, 2014 at 7:52 am
j-1064772 (3/3/2014)
So - short or creating - after the fact - a trigger to update the usage date upon each call
Yup, that's how you do it. Not a trigger, a logging statement in every proc, logging the fact that it ran.
You can do it with extended events too, create an event session which records procedure execution probably bucketiser on the object id and count the executions, you can do it with trace, though I wouldn't recommend it. Core of the matter is SQL doesn't track executions by default.
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
March 3, 2014 at 7:54 am
Not what I wanted to hear.
But thank you for your assistance.
Regards
March 3, 2014 at 9:49 pm
Will it help
SELECT
[procedure] = QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
+ '.' + QUOTENAME(OBJECT_NAME([object_id])),
last_execution_time,
avg_execution_time = CONVERT(DECIMAL(30,2), total_worker_time * 1.0 / execution_count),
max_worker_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
ORDER BY avg_execution_time DESC
March 3, 2014 at 11:34 pm
Sushil Dwivedi (3/3/2014)
Will it help
Slightly. It'll tell you what procedures have been run since the last server restart and still have their plans in cache. It can't tell you that a procedure is unused, it can only tell you if the procedure has been run and is still cached.
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
March 4, 2014 at 7:24 am
Thank you both.
Regards
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply