929
Chapter 38: Using Profi ler and SQL Trace
38
Another preconfigured trace is the blackbox trace, which is used to diagnose server crashes.
Starting a trace with option = 8 starts this trace. Typically, this trace is not run unless
there’s a specific problem and Microsoft PSS has asked for data from the trace.Common Criteria and the older C2 Audit security levels also involve running a specific
trace that gathers login and other security data. Executing sp_trace_create with
option = 4 configures these traces.Summary
SQL Server Profiler and SQL Trace are two technologies you need if you’re interested in
what’s happening with your server. Profiler and SQL Trace may be venerable technologies
compared to Change Tracking or Extended Events, but they’re still two of the more useful
tools in the DBA toolbox. Whereas some SQL Server technologies are optional — you can
survive as a DBA without learning much about XML or SMO — PowerShell and Extended
Events are mandatory.Following are key points about SQL Trace and Profiler:■ Trace is a server-side technology that collects data that may be consumed by
Profiler or written to a file.
■ Profiler is a workable UI for Trace, but it may impact performance, so for heavy
traces on a production server, it’s best to use Profiler to configure the trace, gener-
ate a script, and then run the trace on the server.■ (^) There are 179 SQL Trace events, and it’s worth it to become familiar with them.
■ Events can be filtered; typically, Reporting Services and SQL Agent are filtered out.
■ (^) SQL Trace can be completely configured and controlled by T-SQL code alone.
■ SQL Trace events and Performance Monitor data can be integrated after the fact to
produce a complete picture of what was happening on the server.
The next chapter stays in the mode of monitoring and auditing SQL Server. Similar to SQL
Trace, but at a finer granularity, wait states track every process and every time it pauses
for any reason.
c38.indd 929c38.indd 929 7/31/2012 10:04:20 AM7/31/2012 10:04:20 AM
http://www.it-ebooks.info