Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Profiler Best Practices



By:
Overview

Just as when installing SQL Server or creating a database schema there are best practices that should be followed.  There are also some best practices that should be taken into consideration when we are going to setup a trace using SQL Profiler. The following these best practices will allow you to monitor your SQL Server instance without causing too much overhead or increasing the impact to a performance problem you may already be experiencing.

Explanation

The following is a list (in no particular order) of best practices that should be considered when you are using SQL Profiler. If you have never used SQL Profiler before you may not understand some of the terminology below, but after you have gone through the other sections in this tutorial they should be clear.

Don't capture every event available

There is so much going on behind the scenes in a database instance that capturing every event in a trace will first make your trace file/table really large and more importantly make the display in SQL Profiler almost unreadable. Unless you have narrowed down your issue to a particular event or process id you should also try to avoid tracing events that occur frequently, i.e. Lock:Acquired, as tracing these types of events will also make the display difficult to read.

Use filters

Filters provide a good way to limit the amount of data in your trace especially if you are tracing a frequent event as mentioned above.  Possible filters can be based on the user name, server name, application name, etc.

Avoid adding redundant events

Unless you've pinpointed an issue and require it you should try to avoid adding redundant events because much of the same information is collected.  One example is SP:StmtStarting and SP:StmtCompleted.

Run SQL Profiler on a remote system

Regardless of how you configure your trace, running SQL Profiler can use a lot of memory. It's always best to run it on a remote system so that it is not competing for memory or disk resources with the SQL Server instance you are trying to trace.

Save trace events to a rollover file

Saving trace data to a file is a little bit faster and easier to manage than writing it to a database table, especially if the trace is collecting a large amount of data. If required you can always add all or a subset of the data to a table so it's easier to query after your trace session is complete.  With the rollover file setting, a file will grow to a predefined size, then initiate a new file to save the results and repeat the process each time the file grows to the predefined size.

Don't capture showplan event for long periods of time

Be careful when capturing Showplan events as these can create some very large files (see additional items).

Create templates for quick access

Microsoft provides some templates, but it's always a good idea to have a few of your own defined as well. You don't want to be in the position of having to scramble to define a trace when there is an issue that requires immediate attention.

Additional Information

Last Update: 3/3/2016




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools