WE’LL NEED TO RUN SP_TRACE_SETSTATUS(1).TABLE 13.2 STATUS PARAMET...
2, we’ll need to run sp_trace_setstatus(2,1).
Table 13.2 Status Parameters to Control Server-Side Traces
Status Description
0 Stop trace
1 Start trace
2 Close trace and remove trace definition
Finally, if you lose track of which traces are running on a server, run Select ∗ from
fn_trace_getinto(null) to return details of all traces. Remember that unless you’ve
disabled it there’s a default trace running at all times to populate the DMVs;
this will be TraceID 1.
Combining System
Monitor and Profiler Traces
First introduced to SQL Server 2005, this excellent feature of Profiler allows
Administrators to combine a System Monitor (Performance Monitor) trace with
a SQL Trace. This presents a single view of system resources (disk, memory, CPU)
hardware utilization. The red vertical bar allows administrators to jump to a particular
point (e.g., a CPU spike), and the profiler trace will identify the T-SQL running at
the moment of the spike.
Figure 13.5 Combining System
Monitor and Profiler Traces for a Single View
Replaying Traces
SQL Server provides the ability to replay traces; typical scenarios involve capturing
a trace from a production server and replaying the trace against a test or preproduc-
tion server. This can help when evaluating changes and also with some trouble-
shooting scenarios. Each statement captured in the trace will be replayed against the
target server; this can be helpful in verifying that a fix implemented on a test server
does resolve a problem.
There are a minimum set of events required for trace playback; alternately use
the Replay trace template. Not all servers can be traced and replayed; for example,
if the server is participating in Transactional Replication trace replay isn’t possible
because of the way transactional replication marks transactions in the log.
Head of the Class…
Working with SQL Trace Data
Capturing trace data on a busy server many generate many gigabytes of
data. Using a server-side trace to a file on a local disk will minimize the
tracing overhead. However, unless you’re looking for a specific event in
the trace, often the best way to manage the data is to load the trace data
from a flat file into a table. The following function will load trace data
from a file into a table:
SELECT * INTO srv1_trace FROM::fn_trace_gettable('c:\temp\trace1.trc', default)Once the data is in a database, analysis with TSQL queries is much easier!
Using SQLDiag to
Collect Performance Data
SQLDiag is a command-line, data-collection utility first supplied with SQL Server
2005 that can useful for collecting performance data. SQLDiag collects the
following:
Windows Event Logs
■