How do I properly set up SQL Server Profiler in a production environment?

Solution 1:

Instead of attaching the SQL PRofiler, use the sp_trace_xxx procedures. The reason why the SQL Profiler is not recommended in production is that it can cause the server to block on waiting for network communication with the Profiler to flush ('network' can mean local shared memory, the location is not relevant). The idea is that a single threaded GUI managed process won't be able to keep up with a full speed stream of events from a multi-threaded native highly optimized server. By using the Trace procedures instead you are taking the GUI/Profiler out of the equation and you let the server flush the events as fast as they can be written to disk on the server, which is always much faster than how the Profiler would be able to process them. If you add a decent filter on the events (eg. only trace RPC:Completed and Batch:Completed events with a duration higher than > 1 second or something similar) then you won't have any serious impact on the server.

The GUI Profiler is capable of saving a trace as a series of sp_trace calls for you, so you don't have to manually create the script.