Microsoft® SQL Server™ provides a comprehensive set of tools for monitoring events in SQL Server. The choice of tool depends on the events to be monitored and the type of monitoring. For example, ad hoc monitoring to determine the number of users who are currently connected to a computer running SQL Server can be accomplished by using the sp_who system stored procedure, rather than creating a trace and using SQL Server Profiler.
| Tool | Description |
|---|---|
| SQL Server Profiler | Provides the ability to monitor server and database activity (for example, number of deadlocks, fatal errors, tracing stored procedures and Transact-SQL statements, or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and also replay the events captured on SQL Server, step by step, to see exactly what happened. SQL Server Profiler tracks engine process events, such as the start of a batch or a transaction. |
| Windows NT Performance Monitor | Provides the ability to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. Windows NT Performance Monitor collects counts of the events rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). Thresholds can be set on specific counters to generate alerts that notify operators. Windows NT Performance Monitor mainly tracks resource usage, such as the number of buffer manager page requests that are in use.
Windows NT Performance Monitor only works on Microsoft Windows NT® and can monitor (remotely or locally) SQL Server running only on Windows NT. |
| Current activity in (SQL Server Enterprise Manager) | Provides the ability to graphically display information about currently running processes in SQL Server, blocked processes, locks, and user activity. This is useful for ad hoc views of current activity. |
| Error logs | Contains more complete information about events in SQL Server. You can use the information in the error log to troubleshoot problems that are known to be SQL Server-related. The Microsoft Windows NT® application log provides an overall picture of events occurring on the Windows NT system as a whole, as well as events in SQL Server and SQL Server Agent. |
| sp_who | Reports snapshot information about current SQL Server users and processes, including the currently executing statement and if the statement is blocked. This is a Transact-SQL alternative to viewing user activity in the current activity window in SQL Server Enterprise Manager. |
| sp_lock | Reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies. This is a Transact-SQL alternative to viewing lock activity in the current activity window in SQL Server Enterprise Manager. |
| sp_spaceused | Displays an estimate of the current amount of disk space used by a table (or a whole database). This is a Transact-SQL alternative to viewing database usage in SQL Server Enterprise Manager. |
| sp_monitor | Displays snapshot statistics, including CPU usage, I/O usage, and amount of time idle, which indicate how busy SQL Server has been since sp_monitor was last executed. |
| DBCC statements | Checks performance statistics and the logical and physical consistency of a database. For more information, see DBCC. |
| Built-in functions | Displays snapshot statistics about SQL Server activity since the server was started that are stored in predefined SQL Server counters. For example, @@CPU_BUSY contains the amount of time the CPU has been executing SQL Server code; @@CONNECTIONS contains the number of SQL Server connections or attempted connections made; and @@PACKET_ERRORS contains the number of network packets occurring on SQL Server connections. For more information, see Functions. |
| SQL Server Profiler extended stored procedures | Gathers SQL Server Profiler statistics by executing Transact-SQL extended stored procedures. For more information, see System Stored Procedures. |
| Trace flags | Displays information about a specific activity within the server that is used to diagnose detailed problems or performance issues (for example, deadlock chains). For more information, see Trace Flags. |
| Simple Network Management Protocol (SNMP) | Monitors SQL Server from applications that run on operating systems that support SNMP (for example, UNIX) by generating alerts when specific events occur within the server. For more information, see SNMP. |