Choosing Monitoring Tools
Microsoft SQL Server provides a comprehensive set of tools for monitoring. Your choice of tool depends on the type of monitoring you need and the events to be monitored.
Event Viewer
The Application log, accessible in Windows Event Viewer, contains error, warning, and informational messages about Notification Services. You can use these messages to troubleshoot instances and applications.
For more information about Notification Services messages, see Notification Services Errors and Events.
System Monitor
System Monitor, in the Microsoft Windows Performance tool, enables you to perform real-time monitoring of application performance using predefined performance objects that contain performance-related counters. System Monitor collects counts, rates, and averages about resources and processing, such as notification processing, system CPU activity, and database sizes. For more information, see Notification Services Performance Objects.
Notification Services Stored Procedures
Notification Services provides stored procedures for reporting, debugging, and manually running processes separate from those available from SQL Server. For more information, see Notification Services Performance Reports and the Notification Services Stored Procedures (Transact-SQL).
SQL Server Profiler
SQL Server Profiler enables you to monitor server and database activity, such as deadlocks, fatal errors, and query activity. You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis. SQL Server Profiler also has a trace feature that you can use for planning indexes.
Note
Monitoring too many events at one time increases the processing load on the server. Monitor only the events that are necessary for analyzing current performance issues.
For more information, see SQL Server Profiler Reference.
SQL Server System Stored Procedures and Functions
The following SQL Server system stored procedures and functions are useful for monitoring database activity.
- sp_who
Provides snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.
- sp_spaceused
Displays an estimate of the current amount of disk space used by a table or database.
- sp_monitor
Displays statistics, including CPU usage, I/O usage, and the amount of idle time since sp_monitor was last executed.
- built-in functions
Displays 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 System Stored Procedures (Transact-SQL).
See Also
Concepts
Tips for Evaluating Performance
Notification Services Performance Objects
Using Performance Logs and Alerts
Using Reports to Analyze Performance
Other Resources
Notification Services Errors and Events
Notification Services Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)
SQL Server Profiler Reference