Getting stored procedure usage data on SQL Server 2000

Question

What is the best way to get stored procedure useage data on a specific database out of SQL Server 2000?

The data I need is:

1. Total of all stored procedure calls over X time
2. Total of each specific stored procedure call over X time.
3. Total time spent processing all stored procedures over X time.
4. Total time spent processing specific stored procedures over X time.

My first hunch was to setup SQL Profiler wiht a bunch of filters to gather this data. What I don't like about this solution is that the data will have to be written to a file or table somewhere and I will have to do the number crunching to figure out the results I need. I would also like get these results ober the course of many days as I apply changes to see how the changes are impacting the database.

I do not have direct access to the server to run SQL Profiler so I would need to create the trace template file and submit it to my DBA and have them run it over X time and get back to me with the results.

Are there any better solutions to get the data I need? I would like to get even more data if possible but the above data is sufficient for my current needs and I don't have a lot of time to spend on this.

Edit: Maybe there are some recommended tools out there that can work on the trace file that profile creates to give me the stats I want?

Answer 1

Two options I see:

1. Re-script and recompile your sprocs to call a logging sproc. That sproc would be called by all your sprocs that want to have perf tracking. Write it to a table with the sproc name, current datetime, and anything else you'd like. Pro: easily reversible, as you'd have a copy of your sprocs in a script that you could easily back out. Easily queryable! Con: performance hit on each run of the sprocs that you are trying to gauge.

2. Recompile your data access layer with code that will write to a log text file at the start and end of each sproc call. Are you inheriting your DAL from a single class where you can insert this logging code in one place? Pro: No DB messiness, and you can switch in and out over an assembly when you want to stop this perf measurement. Could even be tweaked with on/off in app.config. Con: disk I/O.

Answer 2

Perhaps creating a SQL Server Trace outside of SQL Profiler might help.

http://support.microsoft.com/kb/283790

This solution involves creating a text file with all your tracing options. The output is put into a text file. Perhaps it could be modified to dump into a log table.

Monitoring the traces: http://support.microsoft.com/kb/283786/EN-US/