Problem solve Get help with specific problems with your technologies, process and projects.

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

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.


One of my Stored Procedures takes more than 9 hours and still not executed. Could you please help to provide me the troubleshooting steps? SP consists of NOLOCK Cmd, which will prevent frm any deadlock situations, but still this sp is taking a long time. Your valuable suggestions pls?