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.
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, 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 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 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.
|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.
The following SQL Server system stored procedures and functions are useful for monitoring database activity.
Provides snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.
Displays an estimate of the current amount of disk space used by a table or database.
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).
ConceptsTips for Evaluating Performance
Notification Services Performance Objects
Using Performance Logs and Alerts
Using Reports to Analyze Performance
Other ResourcesNotification Services Errors and Events
Notification Services Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)
SQL Server Profiler Reference