Monitoring with SQL Server Profiler

SQL Server Profiler is a graphical tool that allows system administrators to monitor engine events in computers running Microsoft® SQL Server™. Examples of engine events include:

• The login connects, fails, and disconnects.
• The Transact-SQL SELECT, INSERT, UPDATE, and DELETE statements.
• The remote procedure call (RPC) batch status.
• The start or end of a stored procedure.
• The start or end of statements within stored procedures.
• The start or end of an SQL batch.
• An error written to the SQL Server error log.
• A lock acquired or released on a database object.
• An opened cursor.

Data about each event can be captured and saved to a file or SQL Server table for later analysis. Data about the engine events is collected by creating traces. Examples of the event data captured within a trace include:

• The type (class) of event, such as SQL:BatchCompleted, which indicates the completion of an SQL batch.
• The name of the computer on which the client is running.
• The ID of the object affected by the event, such as a table name.
• The SQL Server name of the user issuing the statement.
• The text of the Transact-SQL statement or stored procedure being executed.
• The time the event started and ended.

Event data can be filtered so that only a subset of the event data is collected. This allows you to collect only the event data in which you are interested. For example, only the events that affect a specific database, or those for a particular user, can be collected, and all others ignored. Alternatively, data could be collected about only those queries that take longer than five seconds to execute.

Additionally, SQL Server Profiler allows captured event data to be replayed against SQL Server, thereby effectively reexecuting the saved events as they originally occurred.

SQL Server Profiler can be used to:

• Monitor the performance of SQL Server.
• Debug Transact-SQL statements and stored procedures.
• Identify slow-executing queries.
• Test SQL statements and stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected.
• Troubleshoot problems in SQL Server by capturing events on a production system, and replay those captured events on a test system, thereby re-creating what happened in the production environment for testing or debugging purposes. Replaying captured events on a separate system allows the users to continue using the production system without interference.

SQL Server Profiler provides a graphical user interface to a set of extended stored procedures. You can also use these extended stored procedures directly. For example, it is possible to create your own application that uses SQL Server Profiler extended stored procedures to monitor SQL Server.

See Also

System Stored Procedures (SQL Server Profiler Extended Procedures)