Tuesday, December 24, 2013

How to Debug an SQL Stored Procedure

Have you come across this situation where you need to know in which statement the stored procedure executed in SQL gives you the slow performance?

If you have and you still can't find the answer, then you come to the right page.

This is how to debug SQL stored procedure:
1. Open SQL Server Profiler.
2. Click New Trace and enter the connection you want.
3. You will get "Trace Properties" window. In the second tab, "Event Selection", click "Show all events".
4. You will get the whole events. But no, you don't want all actually. So scroll down to the 'Stored Procedures" row. Tick "SP:StmtStarting" and "SP:StmtCompleted".
5. Click Run.

After activating the profiler, go back to the SQL Management Studio and run the SP you want to debug. The profiler will show you statement by statement executed by the SP.

No comments: