SQL Server job (stored proc) trace

Question

Hi Friends,

I need your suggestion on tracing the issue.

We are running data load jobs at early morning and loading the data from Excel file into SQL Server 2005 db. When job runs on production server, many times it takes 2 to 3 hours to complete the tasks. We could drill down to one job step which is taking 99% of the total time to finish.

While running the job step (stored procs) on staging environment (with the same production database restored) takes 9 to 10 minutes, the same takes hours on production server when it run at early morning as part of job. The production server always stuck up at the very job step.

I would like to run trace on the very job step (around 10 stored procs run for each user in while loop within the job step) and collect the info to figure out the issue.

What are the ways available in SQL Server 2005 to achieve the same? I want to run the trace only for these SPs and not for certain period time period on production server, as trace give lots of information and it becomes very difficult for me (as not being DBA) to analyze that much of trace information and figure out the issue. So I want to collect info about specific SPs only.

Let me know what you suggest.

Appreciate your time and help.

Thanks.

Answer 1

What else is happening on the server at that time is important when it is faster on other servers but not prod. Maybe you are running into the daily backup or maintenance of statistics or indexes jobs?

Answer 2

Use SQL Profiler. It allows you to trace plenty of events, including stored procedures, and even apply filters to the trace.

• Create a new trace
• Select just stored procedures (RPC:Completed)
• Check "TextData" for columns to read
• Click the "Column Filters" button
• Select "TextData" from the left hand nav
• Expand the "Like" tree view, and type in your procedure name
• Check "Exclude Rows that Do Not Contain Values"
• Click "OK", then "Run"