Monitoring and Reporting

This section describes ways you can monitor a running SQL-NS instance and obtain reports about its activities. When monitoring, it's important to look out not only for explicit error messages and warnings from SQL-NS components, but also for deviations from expected levels of performance. For example, if the rate of event submission in an application suddenly drops, it might be an indication of a problem that requires your attention, even though you may not see an error message.

SQL-NS provides three main sources of information about the behavior of a running instance:

• Error messages, warnings, and informational messages in the Application Event Log

• A set of performance counters that provide specific performance data about individual SQL-NS components

• A set of reports that provide overview information about the health of a whole instance, as well as detailed information about specific components and functional units

These information sources are described in detail in this section. You'll probably use all of them in practice.

Using the Application Event Log

Windows provides aset of standard event logs that services and applications (including SQL-NS) use to record events. The events in the event logs provide diagnostic information, error messages, and warnings. The Event Viewer tool (part of the Administrative tools on Windows operating systems) allows you to examine the event logs on your system.

Open the Event Viewer on a machine on which the SQL-NS engine runs. The left pane shows a list of event logs you can examine. You will see at least the three standard event logs listed: Application, Security, and System. Other event logs, created by applications or services on the machine, may also appear in the list.

The SQL-NS engine writes events to the Application Event Log. This is usually the first place you should look to assess the condition of a SQL-NS instance. Whenever a SQL-NS application in the instance exhibits unexpected or incorrect behavior, you should check the Application Event Log to see whether an error message has been logged.

When you click the Application log in the Event Viewer's left pane, the right pane shows a list of events. Double-click any event listed to see its properties. The Source column in the Event Viewer's right pane indicates which service or application logged each event. Events generated by the SQL-NS engine have a source value of NotificationServices. On your machine, there many not be any NotificationServices events if the SQL-NS engine did not encounter any errors.

Note

Entries in the event logs are generally called events but may also be referred to as messages. Be careful not to confuse the term events, when used in the context of the event log, with events in the SQL-NS sense (the input data submitted by event providers).

Entries in the event log are categorized as informational messages, warnings, or errors. These types are distinguished by the icon and label in the Type column. By default, the SQL-NS engine writes errors and warnings when it encounters problems in its execution. As described in the "Setting the Logging Level" section (p. 486), you can adjust the engine's configuration to make it log informational messages as well. These messages can provide detailed information about the behavior of the components in the engine, at various stages of processing. Although these messages can be useful, writing them involves a lot of overhead, so they are turned off by default.

SQL-NS Event Log Entries

When the SQL-NS engine encounters an error, or a situation that warrants a warning, the entry it writes to the event log contains a detailed description of the issue. Figure 14.1 shows an example of an error event generated by the SQL-NS engine.

Figure 14.1. The Event Viewer showing the properties of a SQL-NS event.

Every event has an Event ID, an identifier that indicates the type of the event. For example, in NotificationServices events, an Event ID of 4045 indicates that the event represents a rule firing failure.

In NotificationServices events, the Description section of the Event Properties window always contains text that provides additional information about the occurrence that resulted in the event being logged. Usually, the description contains a specific error or warning message, along with the names of the instance, the application, and the component that generated the event. The descriptions in most NotificationServices events also contain a link to a web page that provides even more detailed information and troubleshooting assistance. Whenever you see an event log entry from NotificationServices for which you need more information, click the supplied link.

Note

The link pointing to additional information is usually at the end of the event description, so you may have to scroll down to see it.

This book does not provide a complete reference to all the SQL-NS events that you might see in the Application Event Log. Instead, Chapter 15, "Troubleshooting SQL-NS Applications," describes the common problem situations and how to diagnose them based on events in the Application Event Log.

Setting the Logging Level

You can adjust the SQL-NS engine's logging level to change the amount of information it writes to the event log. If you're having trouble diagnosing a problem, turning up the engine's logging level can provide additional information that might be helpful.

If the engine runs as a Windows service (as it usually does), you can adjust its logging level by editing the NSService.exe.config file that provides the service with its startup parameters. When the engine is hosted in a custom application process (instead of the service), you can control the logging level with settings in the hosting application's configuration file, as explained in Chapter 17, "Hosting the SQL-NS Execution Engine."

The NSService.exe.config file is always located in the same directory as the service executable, NSService.exe. You'll find this in the SQL-NS binaries directory, beneath the SQL Server install location. If SQL Server is installed in the C:\Program Files\Microsoft SQL Server directory, you'll find the SQL-NS binaries and associated files, including NSService.exe and NSService.exe.config, in C:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\bin.

NSService.exe.config is an XML document, and you can edit it with any XML or text editor. Although this file can be used to pass a number of configuration options to the SQL-NS service, it is most often used to specify logging levels by means of a set of log switches. Listing 14.1 shows the contents of the NSService.exe.config file as installed, before any changes are made.

Listing 14.1. Log Switch Settings in the NSService.exe.config File

 <configuration>     <system.diagnostics>         <switches>             <add name="LogAdministrative" value="2" />             <add name="LogService" value="2" />             <add name="LogEventProvider" value="2" />             <add name="LogEventCollector" value="2" />             <add name="LogGenerator" value="2" />             <add name="LogDistributor" value="2" />             <add name="LogVacuumer" value="2" />             <add name="LogOther" value="2" />             <add name="LogPerformanceMonitor" value="2" />         </switches>     </system.diagnostics> </configuration> 

Each log switch controls the logging for a particular component in the SQL-NS engine. For example, the LogGenerator switch controls generator logging. The value set for each switch determines the amount of information the corresponding component writes to the log. All switches are initially set to the value 2, which results in errors and warnings being logged. The log switches can be set to any value between 0 and 4, as follows:

• 0No logging

• 1Log only errors

• 2Log errors and warnings

• 3Log errors, warnings, and informational messages

• 4Log errors, warnings, informational messages, and additional verbose informational messages

To obtain more or less information in the event log, you can adjust the log switch values for the relevant components. After changing the settings in the NSService.exe.config file, you must restart the service for the changes to take effect.

Caution

The log switch values that you set apply to the services for all SQL-NS instances on the machine. There is no way to control the logging levels independently for just one particular instance.

Note

Do not confuse the logging described in this section with the distributor logging described in the "Distributor Logging Options" section (p. 445) in Chapter 12, "Performance Tuning." This section describes log messages written to the Application Event Log by the SQL-NS engine (and the components in it). The distributor logging described in Chapter 12 refers to information the distributor writes to the database as it processes notifications.

SQL-NS Performance Counters

Registering a SQL-NS instance results in the installation of a set of performance counters you can use to obtain quantified data about the activities of the instance. A dedicated set of counters is installed for each instance registered. Monitoring the counters is an easy way to determine whether an instance is operating as it should: Sudden increases or decreases in counter values are easy to spot and can alert you to potential problems early.

SQL-NS performance counters are grouped into performance objects. Each performance object contains a set of related counters that measure various performance indicators related to a particular component or functional area. SQL-NS performance objects are divided into three categories:

• Component-level performance objects for delivery channels, distributors, event providers, and the generator

• Application-level performance objects for events, subscriptions, notifications, and the vacuum

• An instance-level performance object for subscribers

For example, the events performance object (one of the application-level objects) contains counters that measure the following:

• The number of event batches waiting to be processed by the generator

• The number of event batches currently being submitted by event providers

• The total number of event batches submitted since the application started

• The total number of events received since the application started

Monitoring these counters gives you a picture of the event activity in the system. You can monitor performance counters using the built-in perfmon tool (part of Windows), a third-party monitoring tool, or programmatically, in monitoring applications of your own.

Note

More than one instance of some counters may be installed, based on the entities in the applications. For example, one instance of each event counter is installed for each event class.

The SQL-NS Books Online provides complete documentation for all the SQL-NS performance objects and the individual counters they contain. Consult the "Notification Services Performance Objects" topic in SQL-NS Books Online for a complete reference. The information presented in this book is intended to be a guide on how to use the performance counters and does not duplicate the detailed counter reference.

To see the SQL-NS performance counters in use, make sure you have the music store instance created, registered, and enabled, and then carry out the following instructions:

1.	If it is not already running, start the SQL-NS service for the music store instance. This is required because some of the performance counters are updated by code that runs in the service.
2.	On a machine on which you have registered the instance, start the perfmon tool: Open Administrative Tools and select Performance, or run perfmon.exe from a command prompt.
3.	The perfmon window contains two panes. The one on the left contains a tree view, and the one on the right contains a graph. Below the graph is a list of the counters currently being monitored. When you start perfmon, some system counters will be in the list, and their values will be reflected in the graph. Remove these counters from the list by selecting them and pressing the Delete key.
4.	Click the Add button above the graph (the Add button has an icon that looks like a plus sign). The Add Counters dialog box opens.
5.	The Add Counters dialog box is used to add to the list of counters being monitored. It allows you first to choose the computer from which to select counters. Select the local computer.
6.	In the Performance object drop-down list, you can choose the performance object from which to add counters. Expand the drop-down list to see the list of performance objects. The objects associated with a SQL-NS instance all have names that begin with NS$, followed by the instance name (for example, objects associated with the MusicStore instance have names that begin with NS$MusicStore). Select the events object for the music store instance, called NS$MusicStore: Events.
7.	The dialog box's list of counters will be populated with the counters in the selected performance object. Selecting a particular counter and then pressing the Explain button provides descriptive text for the counter. Select all the counters shown for the previously selected NS$MusicStore: Events object and click the Add button.
8.	Click the Close button in the Add Counters dialog box. Back in the main window, the events counters now appear in the list below the graph. Their values are represented by the lines on the graph.
9.	Execute the SubmitEvents.sql script (from the C:\SQL-NS\Samples\MusicStore directory) in Management Studio to add some events to the application.
10.	You should see some of the counter values change after you submit the events. Look at the Total Events counter, for example. There may be a significant delay (up to 1 minute) between the event submission and the counter updates, as explained later in this section.

Tip

Selecting an individual counter in the list causes its last, average, minimum, and maximum values to be displayed in the text boxes below the graph.

Figure 14.2 shows a screenshot of perfmon monitoring the events counters. If you carried out the preceding instructions on your system, you should see a similar display.

Figure 14.2. perfmon is used to monitor the event performance counters.

The values of some performance counters are obtained by doing calculations over the data in the database. These calculations are performed by a performance-monitoring thread in the SQL-NS engine, on a periodic interval. By default, the performance monitor thread calculates and updates the counter values every 1 minute. Between these periodic updates, the counter values do not change. This is the reason for the delay mentioned in step 10 of the previous instructions.

You can adjust the interval on which the performance monitoring thread recalculates the counter values. To do this, use the <PerformanceQueryInterval> element in the ADF. (This element is not used in any of this book's examplessee the SQL-NS Books Online for information on this element.) Be aware that increasing the frequency of performance counter updates increases the load on the SQL Server and may therefore reduce the performance of your applications.

SQL-NS Reporting Stored Procedures

SQL-NS provides stored procedures that generate reports about instances, applications, and their subcomponents. The SQL-NS compiler creates these stored procedures in the instance and application schemas when an instance is compiled. You can invoke these stored procedures directly or from reporting tools to get information about the current state and history of your SQL-NS instance.

The reports produced by the SQL-NS reporting stored procedures fall into three categories:

• Snapshot reports that provide information about the current state of the system

• Diagnostic reports that provide troubleshooting information and details of failures

• Analysis reports that provide historical and summary information about particular activities in the system

This section provides some examples of the various reports available. You should browse the "Notification Services Stored Procedures" topic in the SQL-NS Books Online to learn about all the other reports you can use. The SQL-NS Books Online provides detailed reference information for each report.

A Snapshot Report Example

To see an example of a snapshot report, you can invoke the NSSnapshotEvents stored procedure. This produces a report of the current event activity in an instance. For the music store instance, you can invoke it by running the following commands in Management Studio:

 USE [MusicStore] GO EXEC [NSInstance].[NSSnapshotEvents] 

Note

Like NSSnapshotEvents, many of the other reporting stored procedures are defined in the instance schema, even though they provide information about applications.

The resultset contains information about the enabled state of event submission and the time the last event batch was submitted for each event class in each application. This information allows you to quickly assess the state of the event submission activities. For example, an unusual increase in the reported elapsed time since the last event batch submission could be an indication of a problem with the event providers.

A Diagnostic Report Example

The diagnostic reports can help you troubleshoot problems by providing detailed information about the operation of individual components. For example, the NSDiagnosticEventClass stored procedure produces a report of event-processing activities for a particular event class. In the music store instance, it can be invoked as follows:

 USE [MusicStore] GO EXEC [NSInstance].[NSDiagnosticEventClass] N'SongAlerts', N'SongAdded' 

The arguments to NSDiagnosticEventClass specify an application name and an event class name. The stored procedure also takes other optional arguments that can be used to specify a time interval for which the report should be generated. The resultset returned summarizes the event batches submitted for the given event class and provides information on how they were processed.

Tip

In a running SQL-NS instance, it's better to use the reporting stored procedures to obtain information from the database rather than run queries directly against the tables. The reporting stored procedures are designed and implemented so as to avoid contention and locking problems while accessing tables used in the SQL-NS processing pipeline. Running your own queries directly against these tables can sometimes cause operations in the SQL-NS engine to time out or deadlock.

An Analysis Report Example

An example of a reporting stored procedure that produces an analysis report is NSQuantumPerformance. The data in this report summarizes execution times and other data related to processing of quantums in an application. NSQuantumPerformance is invoked from the application schema. To run it against the SongAlerts application, execute the following statements:

 USE [MusicStore] GO EXEC [SongAlerts].[NSQuantumPerformance] 

The resultset shows data about the quantums processed by the application. This gives you an indication of how well the generator is keeping up with its workload.