SQL - Event Notifications vs. SQL Trace

Event Notifications in SQL

This is also a very beautiful feature of SQL Server which was introduced with the release of SQL Server 2005. Event Notifications mechanism is used for tracking all events or changes happening at the database or instance level. Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled as SQL Trace.

SQL Tracing
Microsoft SQL Server provides Transact-SQL system stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within our own applications to create traces manually, instead of using SQL Server Profiler. This allows us to write custom applications specific to the needs of our enterprise.

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

Both SQL tracing and event notifications have their own functional area. They can be access through some Server Management Objects (SMO) TraceReader Class and Server Management Objects (SMO) Event classes.