Microsoft SQL Server 2012 Bible

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, generate 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.