SQL Server Profiler Pointers

SQL Server Profiler Pointers

SQL Server Profiler is a gui tool which you can use for monitoring a SQL Server instance, and databases on that instance.  The Profiler captures many different types of data events such as batch/procedure/statement starts, logins, logouts, batch/procedure/statement completing, etc.  The Profiler allows you to capture data specific to many, many events like this... all very useful for transaction analysis and performance tuning, among other things.  In short, it is very useful for debugging stored procedures, tSQL statements and other activity at the server.

I've been in the SQL seat for many years, and when there's some latency, or otherwise unexplained activity that I just can't explain..... I use the Profiler.

But... pretty much right out of the box, there is a tremendous amount of overhead!  There are definitely DOs and DON'Ts with the Profiler.  I have tried to put together a few pointers for the use of SQL Server Profiler, based upon my own use.  Please don't forget about MSDN and BOL.. this is not the only list, by any means.

Do NOT select more events and data columns than you actually need.  
Just like the 'SELECT *' ---  If you don't need it, leave it be.  Very simply, the more events you collect, the more resources used AND overhead expended by the Profiler.  Your applications will feel it, I assure you.

DON'T run the Profiler directly on the database server that you are targeting.  Use it on a client, connected to the server in question.  With this method, you will collect your events from the production server, but you the overhead of the event display, and calculation, will be vested upon the client workstation.

DON'T output your trace directly to a table.  Far too much overhead here.  A much better and faster alternative is to write the trace to a file.

FILTERS!  When you create your trace, you should use filters to reduce the events you are collecting.  Remember, these things are expensive.  You should collect only the events that you need, or even further filter them, for example, by duration, like this:  "Duration > 1000"

Trace STOP times are important.  You can start the SQL Server Profiler trace at the appropriate time using the stored procedures.
Don't just turn it on and run it... Take a look at sp_trace_create procedure in BOL.  Note the @stoptime parameter, and use this to control the runtime of the trace.

Lastly, run it during 'down time', or periods of lower activity.     
Remember, the overhead is there.  If you need it, please be sure you are running it during idle CPU time, or slower production hours.