SQL Profiling

I was investigating a bug on UAT environment which was one-off and apparently not reproduce able in  other environments. After spending many hours I didn't know what was causing the issue due to lack of logging and error message in event viewer. I was left with 2 options first to enable tracing on CRM server and second was to go to SQL profiling which is not a preferred option generally. However due to number of front end servers running, turning on tracing on each of them was not my preferred option in this case and I opted for SQL profiling.  I was generating SQL tracing information for the action which was giving error, and I made sure we do SQL profiling after business hours.

Before starting the profiling stop any service which can trigger lots of SQL tracing. In my case i was investigating some ui issue so I disabled Microsoft Async Service and Reporting Service for some time.
Following are the steps to do sql profiling.

Open SQL server Management studio, then go to "Tools" -> "SQL Server Profiler" option.

On Trace properties prompt shown select "Event Selection" tab.
Select "Show all events" check-box.

Expand security option and select
- Audit Login
-Audit Logout

Expand Stored Procedures option and select
-RPC output parameter
-RPC:Completed
-RPC:Starting
-SP:Completed
-SP:Starting
-SP:StmtCompleted
-SP:StmtStarting

Expand TSQL
-SQL:BatchCompleted
-SQL:BatchStarting