Understanding Event Notifications vs. SQL Trace
The following table compares and contrasts using event notifications and SQL Trace for monitoring server events.
SQL Trace |
Event Notifications |
|---|---|
SQL Trace generates no performance overhead associated with transactions. Packaging of data is efficient. |
There is performance overhead associated with creating the XML-formatted event data and sending the event notification. |
SQL Trace can monitor any trace event class. |
Event notifications can monitor a subset of trace event classes and also all data definition language (DDL) events. |
You can customize which data columns to generate in a trace event. |
The schema of the XML-formatted event data returned by event notifications is fixed. |
Trace events generated by DDL are always generated, regardless of whether the DDL statement is rolled back. |
Event notifications do not fire if the event in the corresponding DDL statement is rolled back. |
Managing the intermediate flow of trace event data involves populating and managing trace files or trace tables. |
Intermediate management of event notification data is accomplished automatically through Service Broker queues. |
Traces must be restarted every time the server restarts. |
After being registered, event notifications persist across server cycles and are transacted. |
After being initiated, the firing of traces cannot be controlled. Stop times and filter times can be used to specify when they initiate. Traces are accessed by polling the corresponding trace file. |
Event notifications can be controlled by using the WAITFOR statement against the queue that receives the message generated by the event notification. They can be accessed by polling the queue. |
ALTER TRACE is the least permission that is required to create a trace. Permission is also required to create a trace file on the corresponding computer. |
Least permission depends on the type of event notification being created. For more information, see CREATE EVENT NOTIFICATION (Transact-SQL). RECEIVE permission is also needed on the corresponding queue. |
Traces can be received remotely. |
Event notifications can be received remotely. |
Trace events are implemented by using system stored procedures. |
Event notifications are implemented by using a combination of Database Engine and Service Broker Transact-SQL statements. |
Trace event data can be accessed programmatically by querying the corresponding trace table, parsing the trace file, or using the SQL Server Management Objects (SMO) TraceReader Class. |
Event data is accessed programmatically by issuing XQuery against the XML-formatted event data, or by using the SMO Event classes. |
See Also