Professional Microsoft® SQL Server® 2008 Administration

13.5. Monitoring Events

Performance Monitor enables you to sample performance counters. Events are fired at the time of some significant occurrence within SQL Server. Using events enables you to react to the behavior at the time it occurs, and not have to wait until some later time. SQL Server generates many different events and has several tools available to monitor some of these events.

The following list describes the different features you can use to monitor events that happened in the Database Engine:

• Default Trace: Initially added in SQL Server 2005, this is perhaps one of the best kept secrets in SQL Server. It's virtually impossible to find any documentation on this feature. The default trace is basically a flight data recorder for SQL Server. It records the last 5MB of key events. The events it records were selected to be very lightweight, yet valuable when troubleshooting a critical SQL event.

• SQL Trace: This records specified events and stores them in a file (or files) that you can use later to analyze the data. You have to specify which Database Engine events you want to trace when you define the trace. There are two ways to access the trace data:

  • Using SQL Server Profiler, a graphical user interface

  • Through T-SQL system stored procedures

• SQL Server Profiler: This exploits all of the event-capturing functionality of SQL Trace, and adds the capability to trace information to or from a table, save the trace definitions as templates, extract query plans and deadlock events ...