Use SQL Profiler to find long running stored procedures and commands

Troubleshoot long running stored procedures with SQL Server Profiler by adding events to find the command with high run time.

I am a performance testing engineer and new to performance tuning. In SQL Server Profiler, I have noticed one of our stored procedures is running many times and taking long durations of time. I want to know why this stored procedure run time is so long. Can you please provide the steps I should follow to fix this issue?
To see why a stored procedure is running too many times you will need to review the application code that calls the stored procedure.

As to the long running procedure, you can bring up SQL Server Profiler again and add in the event "SP:StatementStarting" and "SP:StatementCompleted." This will show you each command within the stored procedure and how long each command takes. You can then find the command with the high run time and tune that specific query. Normally, this will be caused by incorrect indexing on one or more tables used by the query.

Dig Deeper on SQL Server Stored Procedures