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.