SQL Server Performance Forum – Threads Archive

Collect data for procedures

I am looking for method as how to collect data in 1-hour window form SQL server.
I need to collect data for procedure’s name and its execution time.
Trying not to use profiler.
Are there any documents, which talk about this?
Thanks for your help

Please explain why you want to avoid using profiler

i know of no good reason not to use profiler, provide that you do not save directly to a table on the production server
single line inserts are very expensive, and the profiler load in that mode can be very expensive
otherwise, profiler is reasonably efficient
i would recommend saving to a file first (or you could leave it in memory),
then save to a table at the end of the run, preferably on some other system if you only use stored procedures, you can find unique procedures by parsing for text between the "exec " and the first "@" that follows the exec as in the example below exec dbo.p_get_N1C_01 @ID = 1

Great idea I will try this, as you mentioned my major concern was performance on production server.

You could use the sp_trace_xx stored procedures to create a trace on your server and collect the specific data that you are interested in. Might be a little more daunting but:
(1) You could run this trace as a scheduled event
(2) You can make the procedure that contains the trace definition a start-up procedure and eventually automate your data collection later( if you need to collect for more than 1 hour !).

You could try creating a stored procedure that defines a trace ( using the sp_trace_xx system stored procedures). This procedure can be scheduled to run at a time of your choosing for 1 hour or more (by creating a SQL job for it) or you could even make it a start-up stored procedure and collect data for the entire period of the server up-time( if you changed your mind about the 1-hour window).

Use Links… How to Create a SQL Server 2000 Trace
http://support.microsoft.com/default.aspx?scid=kb;en-us;283790 How to Programmatically Load Trace Files into Tables
http://support.microsoft.com/default.aspx?scid=kb;en-us;270599 How to Trace in SQL Server by Using Extended Stored Procedures
http://support.microsoft.com/default.aspx?scid=kb;EN-US;258990 How to View SQL Server 2000 Performance Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;283886 How to View SQL Server 2000 Activity Data
http://support.microsoft.com/default.aspx?scid=kb;en-us;283784 Job to Monitor SQL Server 2000 Performance and Activity
http://support.microsoft.com/default.aspx?scid=kb;en-us;283696
HTH. Gaurav

]]>