Q
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.

This was last published in March 2008

Dig Deeper on SQL Server Stored Procedures

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close