By: Ben Snaidero
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
- Read more details on Showplan results
