Choosing a Monitoring Tool

Microsoft® SQL Server™ provides a comprehensive set of tools for monitoring events in SQL Server. Your choice of tool will depend on the type of monitoring and the events to be monitored. For example, ad hoc monitoring to determine the number of users currently connected to an instance of SQL Server can be accomplished by using the sp_who system stored procedure, rather than creating a trace and using SQL Profiler.

SQL Profiler

Enables you 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 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 Profiler tracks engine process events, such as the start of a batch or a transaction.

System Monitor

Enables you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor (Performance Monitor in Microsoft Windows NT® 4.0) collects counts rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). You can set thresholds on specific counters to generate alerts that notify operators. System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use.

System Monitor works only on Microsoft Windows® 2000 and can monitor (remotely or locally) an instance of SQL Server on Windows NT 4.0 or Windows 2000 only.

Current activity window (SQL Server Enterprise Manager)

Graphically displays information about processes running currently on an instance of SQL Server, blocked processes, locks, and user activity. This is useful for ad hoc views of current activity.

Error Logs

Contain additional information about events in SQL Server than is available elsewhere. You can use the information in the error log to troubleshoot SQL Server-related problems. The Windows application event log provides an overall picture of events occurring on the Windows NT 4.0 and Windows 2000 system as a whole, as well as events in SQL Server, SQL Server Agent, and full-text search.

sp_who

Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether 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 statistics, including CPU usage, I/O usage, and the amount of time idle since sp_monitor was last executed.

DBCC statements

Enables you to check performance statistics and the logical and physical consistency of a database. For more information, see DBCC.

Built-in functions

Display snapshot statistics about SQL Server activity since the server was started; these statistics 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; and @@PACKET_ERRORS contains the number of network packets occurring on SQL Server connections. For more information, see Functions.

SQL Profiler stored procedures and functions

Use Transact-SQL stored procedures to gather SQL Profiler statistics. For more information, see System Stored Procedures.

Trace flags

Display information about a specific activity within the server and are used to diagnose problems or performance issues (for example, deadlock chains). For more information, see Trace Flags.

Simple Network Management Protocol (SNMP)

Simple Network Management Protocol (SNMP) is an application protocol that offers network management services. Using SNMP, you can monitor an instance of SQL Server across different platforms (for example, Windows NT 4.0, Windows 98, and UNIX). With SQL Server and the Microsoft SQL Server Management Information Base (MSSQL-MIB), you can use SNMP applications to monitor the status of SQL Server installations. You can monitor performance information, access databases, and view server and database configuration parameters.

The choice of a monitoring tool depends on the type of events and activity to be monitored.

Event or activity	SQL Profiler	System Monitor	Current activity window	Transact-SQL	Error logs
Trend analysis	Yes	Yes
Replaying captured events	Yes
Ad hoc monitoring	Yes		Yes	Yes	Yes
Generating alerts		Yes
Graphical interface	Yes	Yes	Yes		Yes
Using within custom application	Yes 1			Yes

1 Using SQL Profiler system stored procedures.

The key difference between the two main monitoring tools, SQL Profiler and System Monitor, is that SQL Profiler monitors engine events while System Monitor monitors resource usage associated with server processes. For example, SQL Profiler can be used to monitor deadlocks events, including the users and objects involved in the deadlock. System Monitor can be used to monitor the total number of deadlocks occurring in a database or on a specific object.

Windows NT 4.0 and Windows 2000 also provides these monitoring tools:

• Task Manager

  Shows a synopsis of the processes and applications running on the system.

• Network Monitor Agent

  Assists in monitoring network traffic.

For more information about Windows NT 4.0 or Windows 2000 tools, see the Windows NT 4.0 or Windows 2000 documentation.