Create an SQL trace without using SQL Profiler

Question

I've read before that its possible to create a SQL profiler trace that records directly to a table without neeing to use the SQL profiler - apparently this is better than having the SQL profiler itself running as the SQL profiler captures all events, and then filters entries in the profiler itself to display only the ones specified in the current filter. The direct trace only collects events specified in the trace itself, resulting in less of an impact on the server itself.

I want to set up a trace that records all queries taking longer than 1 second to execute, but now I cant seem to find that article again.

Answer 1

I was shown a trick a while back to do just that. You can easily get the trace definition by creating your trace with SQL Profiler, starting it, then immediately stopping it. At that point, Profiler will allow you to export the SQL statements to create the same thing server-side. You'll still need to modify a few data points like mrdenny noted, but it takes most of the work out of creating the server-side trace. The location of that command on SQL2005+ is File > Export > Script Trace Definition; on SQL2000 it is at File > Script Trace.

Answer 2

Yes, its recommended that 'server side' traces are used in a production environment instead of using SQL Profiler to do 'client side' traces, not because profiler captures all event but because of the memory and network overhead required in running Profiler.

As already suggested, the best way to create a server side trace is to setup your filtering in SQL Server Profiler, then export the trace to a script.

There's a great step-by-step article on sqlserverpedia that explains how to do it, is this the article you were after?

Answer 3

Running SQL Profiler (with profiler running on a client) and running a trace from T/SQL will do the exact same thing. The filtering is all done on the server.

This can be seen by setting up a SQL Profiler trace to monitor SQL PRofiler, then start that trace. Then setup a second trace to handle what you want, then start the second trace. In the first trace you'll see all the T/SQL commands to create the trace and setup the filtering.

You'll need to tweak the code to use T/SQL variables to handle the fact that the TraceId could be different from the one used when you setup the sample trace.