Reducing the overhead of a SQL Trace with filters

Question

We have a SQL 2000 server that has widely varied jobs that run at different times of day, or even different days of the month. Normally, we only use the SQL profiler to run traces for very short periods of time for performance troubleshooting, but in this case, that really wouldn't give me a good overall picture of the kinds of queries that are run against the database over the course of a day or week or month.

How can I minimize the performance overhead of a long-running SQL trace? I already know to:

• Execute the trace server-side (sp_ create_trace), instead of using the SQL Profiler UI.
• Trace to a file, and not to a database table (which would add extra overhead to the DB server).

My question really is about filters. If I add a filter to only log queries that run more than a certain duration or reads, it still has to examine all activity on the server to decide if it needs to log it, right? So even with that filter, is the trace going to create an unacceptable level of overhead for a server that is already on the edge of unacceptable performance?

Answer 1

Adding Filters does minimize the overhead of event collection and also prevents the server from logging transaction entries you don't need.

As for whether the trace is going to create an unacceptable level of overhead, you'll just have to test it out and stop it if there are additional complaints. Taking the hints of the DB Tuning Advisor with that production trace file could improve performance for everyone tomorrow though.

Answer 2

You actually should not have the server process the trace as that can cause problems: "When the server processes the trace, no event are dropped - even if it means sacrificing server performace to capture all the events. Whereas if Profiler is processing the trace, it will skip events if the server gets too busy." (From SQL 70-431 exam book best practices.)

Answer 3

I found an article that actually measures the performance impact of a SQL profiler session vs a server-side trace:

http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx

This really was my underlying question, how to make sure that I don't bog down my production server during a trace. It appears that if you do it correctly, there is minimal overhead.