Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 100

FROM SYS.TRACES
WHERE ID <> 1 -- Id 1 is default SQL black box trace.

Figure 5-3 shows that there were two traces created on the server instance. From the query results, the
IS_Rowset column shows indicator flags, the number 0 indicates a server-server trace, and the number 1
indicates a Profiler trace. BUFFER_COUNT is the number of in-memory buffers used by a trace. Since these
two traces were not started at the exact same time, the Event_COUNT had different numbers.

Figure 5-3

The following query displays all current running traces with similar information.

select * from fn_trace_getinfo(NULL)

TracinginaProductionEnvironment
In a production environment, if critical events must be monitored or confirmed in real time for a short
period, SQL Profiler is one of the best tools to use.

SQL Profiler is a client application; its graphical interface utilizes server resources. Since dedicated SQL
Servers in a production environment are adjusted to achieve greater performance of background services,
it is not good practice to run the Profiler directly on a production SQL Server.

Running Profiler from another machine is the best practice; however, you need to consider the following
factors. Because SQL Profiler is a process that is separate from the SQL Server process, transferring each
event of a monitored SQL instance to a SQL Profiler will take some time. If you run SQL Profiler on a
different computer, this process may take longer due to network latency. Therefore, performance of the
SQL Server instance can be affected.

The disk space should also be monitored. Profiler trace displays trace results; in the mean time, it
saves the results in a temporary file until the Profiler is closed. The file location depends on how the