Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
Thanks - much easier than creating the script myself. –  Justin Oct 8 '09 at 10:12
add comment

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?

share|improve this answer
    
Thats not the one, but thats also a brilliant article. –  Justin Oct 8 '09 at 10:13
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.