Monitoring Events
SQL Server provides SQL Trace and event notifications to monitor events that occur in the Database Engine.
By recording specified events, SQL Trace helps you troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, and gather data for performance analysis tools. You access SQL Trace either through SQL Server Profiler, a graphical user interface, or through Transact-SQL system stored procedures entered at the command line.
While running, SQL Trace captures events in the Database Engine as they occur. The captured events are event class instances that you choose to monitor in a process called a trace. Each event class consists of an event name and data columns that describe attributes of a particular occurrence of an event class. When an event occurs that belongs to an event class specified in a trace, SQL Trace records the event name and the associated data in a trace file.
You usually access SQL Trace through SQL Server Profiler. You can also access SQL Trace by using Transact-SQL system stored procedures. SQL Server Profiler exploits all of the event-capturing functionality of SQL Trace, and adds the ability to trace information to or from a table, save the trace definitions as templates, extract query plans and deadlock events as separate XML files, and replay trace results for diagnosis and optimization.
Event notifications send information to a Service Broker service about many of the same events that are captured by SQL Trace. But unlike traces, event notifications can be used to perform an action inside SQL Server in response to events. Because event notifications execute asynchronously, these actions do not consume any resources defined by the immediate transaction.
The following topics describe how to use SQL Server Profiler, SQL Trace, and event notifications to monitor events.
Note
In SQL Server 2008, you can also use Extended Events to troubleshoot performance issues. Extended Events offers a deeper view into the internal workings of SQL Server, and can be used for more advanced troubleshooting scenarios. For more information, see SQL Server Extended Events.
In This Section
Topic |
Description |
|---|---|
Explains how SQL Trace works and how to create traces with stored procedures. |
|
Explains how to trace with SQL Server Profiler. |
|
Contains information about server operations that can be tuned to improve utility and tool performance |
|
Contains information about how you can tune SQL Server to optimize server performance. |
|
Explains how event notifications work and how to use them to monitor and respond to database and server events. |
|
Contains information about how you can use SQL Server Management Studio to monitor SQL Server performance. |
|
Contains information about how you can use the SQL Server error log and the Microsoft Windows application log to monitor SQL Server activity. |
|
Contains information about how you can use Transact-SQL statements to monitor an instance of SQL Server. |
See Also