September 3, 2009 at 3:52 am
I need to provide a report on how often a Development team Stored Procedures are run ( or not ).
The reason being, is the classic of old / redundant stored procs that they want to remove to tidy up their databases.
Now before you provide some code saying this will check the cache etc...my fellow DBA in my team has provided some code, but unfortunately didnt work in some scenarios.
Anyway if anyone has some concrete / iron proof code that I can run to collect stats on Stored Procs being used per day / week / month etc I would be grateful.
Alternatively if there is any third-part product?...its their budget so that not an issue 😉
Regards,
Paul Jones
Principle Database Administrator
Great Ormond Street NHS Trust,
London, WC1N 3JH
Tel: 020 7405 9200 (extension 5629)
E-mail: JonesP2@gosh.nhs.uk
September 3, 2009 at 12:16 pm
theres always the painfully obvious, put an insert into a table at the start of each sproc..
not the most efficient way to do it though 🙂
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
September 3, 2009 at 12:55 pm
Best thing would be SQLTrace. Set up a server-side trace and run it for as long as necessary.
p.s. Sure you want your email address and phone no visible on a public forum. It's a spammer's dream come true.
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
September 3, 2009 at 12:57 pm
a sql trace over x days/weeks/years could be pretty painful though i'd have thought?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
September 3, 2009 at 1:04 pm
It's not pretty, but it's not too bad. After all, SQL 2005 has a permanently running trace of its own. Trick is to keep the events and the columns to the absolute minimum. If all that's wanted is what procs run, then SP:Started is sufficient and just the SPID, starttime, db_id and object_id are sufficient for columns.
It's trace or edit all procs and put a logging entry in. There's no other real way to record absolutely every execution of a stored procedure.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply