question

caoneill avatar image
caoneill asked

SQL Job running long. Need timing for each stored proc running.

Hi, I have a job that just started running long. There is only 1 step which executes a stored procedure. That stored procedure kicks off multiple stored procedures when processing. I'm trying to pinpoint which stored proc is taking so long. What is the best way to determine length of time for each? It runs off hours so I can't execute manually. Thanks!
sql-agent
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
@caoneill You can start the SQL Server Profiler trace, including appropriate events and filters. For example, select SQL: BatchCompleted and SQL: BatchStarted for events, un-check all other events. Optionally, add appropriate filter(s) so that only the procedures executed by the job are traced. The profile trace window will give you the information about every stored procedure executed from the job. The Duration column of the batch completed events will display the execution value in milliseconds.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.