Tips for Evaluating Performance

You evaluate the status of Microsoft SQL Server Notification Services by monitoring the flow of events and notifications through the system. When monitoring the system, you want to ensure the following:

• Events are arriving and being collected by the event providers. Monitoring tools you can use include:
  • Windows Event Viewer
  • Performance counters in the following performance objects: NS$instance_name: Event Providers Object and NS$instance_name: Events Object
  • NSEventBatchList (Transact-SQL), NSSnapshotEvents (Transact-SQL), and NSSnapshotProviders (Transact-SQL) stored procedures
• The generator is processing the event batches and generating notifications in a timely manner and is not skipping quanta. Monitoring tools you can use include:
  • Event Viewer
  • Performance counters in the following performance objects: NS$instance_name: Events Object and NS$instance_name: Generator Object
  • NSNotificationBatchList (Transact-SQL), NSSnapshotApplications (Transact-SQL), and NSQuantumsSkipped (Transact-SQL) stored procedures
• Notifications are accumulating in the notification tables and the distributor is formatting notifications and delivering them to the delivery channels. Monitoring tools you can use include:
  • Event Viewer
  • Performance counters in the NS$instance_name: Notifications Object
  • NSNotificationBatchList (Transact-SQL) and NSSnapshotDeliveryChannels (Transact-SQL) stored procedures
• The external delivery system is accepting and delivering the formatted notifications. Monitoring tools you can use include:
  • Event Viewer
  • Performance counters in the NS$instance_name: Notifications Object
  • NSDiagnosticFailedNotifications (Transact-SQL) stored procedure
• The vacuuming process is occurring as scheduled in the application definition file (ADF). Monitoring tools you can use include:
  • Event Viewer
  • Performance counters in the NS$instance_name: Vacuumer Object
  • NSSnapshotApplications (Transact-SQL) stored procedure
• The CPU and disks are operating well within their capacity. For more information, see Monitoring CPU Usage and Monitoring Disk Usage.
• Database and log files are not filling up and either auto-growing too frequently or running out of space. For more information, see SQL Server, Databases Object.

If your application is not operating as expected, see Troubleshooting Notification Services.

Monitoring Production Applications

Notification Services applications typically have peak periods and quiet periods because subscribers tend to want information delivered at similar times and because large batches of events often arrive at the same times each day. For this reason, it is very important to monitor applications during peak periods, establish performance baselines, and then compare performance over time.

For applications in production, you should always monitor critical performance counters and set alerts to notify operators of poor performance. You can set alerts using Performance Logs and Alerts, a feature of the Microsoft Windows Performance tool.

Responding to Performance Problems

When you encounter performance problems, use all the tools available to determine what is causing the problem. Here are some possible remedies for performance problems:

• Configure indexes for rules (event chronicle rules, subscription event rules, and subscription scheduled rules) to tune the application.
• Do not run ad-hoc queries against the instance and application databases during peak processing periods because you might be creating temporary locks on resources needed by Notification Services.
• Ensure that the CPU resources are adequate for distribution, which is typically CPU-bound.
• Ensure that your server has adequate bandwidth to servers or services that deliver notifications.
• Ensure that applications are tuned for the best performance. To tune an application, you might need to adjust several elements in the application definition file, especially elements in the ApplicationExecutionSettings element.
• Place database files, log files, and the tempdb database on three separate physical disks. The performance for generating notifications on the database system is often limited by disk resources.

For any performance problems, it is important to isolate the problem by looking at performance counters, using reports, and examining the application log to see what errors are occurring.

See Also

Concepts

Choosing Monitoring Tools
Notification Services Performance Objects
Using Performance Logs and Alerts
Using Reports to Analyze Performance
Specifying Application Execution Settings

Other Resources

Notification Services Errors and Events

Help and Information

Getting SQL Server 2005 Assistance