SQL Profiler Trace Steps to Capture Query Info

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.