SQL Profiler makes debugging easy...
... if you make it easy on yourself.
I was searching for a particular statement hidden in a Stored Procedure, but all I got was tons of statements that were irrelevant to my problem.
The problem with SQL Profiler is that it can give you lots of information, but it is not easy getting the right information. The Standard (default) template for a SQL Profiler trace doesn't capture the statements inside a stored procedure. To do this, you will have to choose a different template: the TSQL_SPs template.
But even with the right template it could be hard to find a specific statement caused by a specific application.
By default .NET applications show up in the Profiler with the ApplicationName '.Net SqlClient Data Provider'. It would be so much easier if you could just filter on 'My Application'. Well, you can.
Change the connectionstring for your application. Add 'Application Name=My Application;' and the ApplicationName will now say: My Application.
You can now filter Sql Profiler to show stored procedure statements of your application only and for your database.
For an explanation of all the different Sql Server Profiler templates you can go here: http://www.extremeexperts.com/SQL/Articles/TraceTemplate.aspx