We may get the situation where we need to run SQL Profiler Trace to capture information when Query is running on an Instance. We all know that running Profiler on a Production will impact the performance but still sometimes its necessary . Instead of selecting default or all options to run profiler, Please use the following selected options which are sufficient to troubleshoot the Issue which will have little performance effect :
- Run the SQL Server Profiler
- Click File -> New Trace
- Input the server name and click Connect
- Check “Save to file” and specify the file to store the SQL profiler log
- In the Events Selection tab, click “Show all columns”
- Drag the scroll bar and select the items under the column DatabaseName. Make all the items under DatabaseName Checked.
- Click Show all Events,
–> Expand Errors and Warnings, Select the following items: Attention, Exception, Errorlog, Eventlog, User Error Message
–> Expand Stored Procedures, select the following items: RPC: Completed, RPC: Starting, SP: Completed, SP:Starting, SP: StmtStarting
–> Expand TSQL, select the following item: SQL: BatchCompleted, SQL: BatchStarting; SQL: StmtStarting
- Click Run
- Wait for 5 minutes.
- In the SQL Profile, click File -> Stop Trace