Export (0) Print
Expand All

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.

Topic Description

Introducing SQL Trace

Explains how SQL Trace works and how to create traces with stored procedures.

Introducing SQL Server Profiler

Explains how to trace with SQL Server Profiler.

Optimizing Utility and Tool Performance

Contains information about server operations that can be tuned to improve utility and tool performance

Optimizing Server Performance

Contains information about how you can tune SQL Server to optimize server performance.

Introducing Event Notifications

Explains how event notifications work and how to use them to monitor and respond to database and server events.

Monitoring with SQL Server Management Studio

Contains information about how you can use SQL Server Management Studio to monitor SQL Server performance.

Monitoring the Error Logs

Contains information about how you can use the SQL Server error log and the Microsoft Windows application log to monitor SQL Server activity.

Monitoring with Transact-SQL Statements

Contains information about how you can use Transact-SQL statements to monitor an instance of SQL Server.

SQL Server Event Class Reference

Describes each event class that you can include in a trace, organized by event category.

Community Additions

ADD
Show:
© 2014 Microsoft