Using Reports to Analyze Performance
When analyzing Microsoft SQL Server Notification Services performance, start by determining how an instance and its applications are performing. To obtain this information, use the application snapshot and administrative reports:
- The application snapshot report is produced by the NSSnapshotApplications (Transact-SQL) stored procedure. For example, you can use this report to see whether the generator is running behind schedule or whether data is being removed by the vacuum process.
- The administrative report is produced by the NSAdministrationHistory (Transact-SQL) stored procedure. For example, you can use this report to look for failed notification batches.
These reports help you determine whether your applications are performing as expected. If not, use the methodology below to analyze poor performance.
Analyzing Poor Performance
Analyzing Notification Services applications begins with the generator quantum. The generator is the core of Notification Services, and when the instance is running, it should fire on a regular time interval called a quantum. You specify the duration of a quantum within the application definition file (ADF). The generator uses the quantum duration to determine how often to fire rules defined in the ADF.
When analyzing performance problems, you typically look for quantum periods that did not complete as expected, and then determine what happened during those quanta. Use the following methodology to locate quanta to analyze, get details about the quanta, and then analyze instance and application details.
For more information about quantum intervals, see Subscription Processing Architecture and Specifying the Generator Quantum Duration.
Step 1: Determine a quantum of interest
The first step in analyzing a Notification Services application is to identify a set of relevant quantum periods. A quantum period that is relevant to poor performance typically has one of the following characteristics:
- The quantum is running longer than expected. This can be caused by several factors, and you must further analyze the application to determine the cause.
- The quantum is marked as failed. A quantum period can have failures for several reasons, such as that a rule did not run as planned.
- The quantum was skipped. In an effort to keep the generator processing current data, you can configure the generator to skip quantum periods when necessary. Skipped quanta are usually a side effect of long-running quantum periods.
To help identify long-running, failed, or skipped quantum periods, Notification Services provides the quantum performance, quantum execution time, quantum failure, and quanta skipped reports.
- The quantum performance report categorizes quanta according to quantum execution times. This can help you determine how long quanta are running in general. The NSQuantumPerformance (Transact-SQL) stored procedure produces this report.
- The quantum execution time report contains quanta that are running longer than a specified time. Using the quantum IDs, you can then analyze the quanta in more detail. The NSQuantumExecutionTime (Transact-SQL) stored procedure produces this report.
- The quantum failure report provides information about failed generator quanta. A quantum is failed if the quantum could not complete required processing, such as rule firings. The NSQuantumFailures (Transact-SQL) stored procedure produces this report.
- The quanta skipped report provides information about skipped generator quanta. Quanta can be skipped if the generator falls behind and if quantum limits are set in the ADF. The NSQuantumsSkipped (Transact-SQL) stored procedure produces this report.
Scenario: Using the NSQuantumPerformance stored procedure, you might determine that quantum 188 ran twice as long as all other quanta. The next step is to discover what happened during that quantum.
Step 2: Analyze detailed quantum information
After you have identified a quantum of interest, determine what happened during the quantum. To obtain details about the quantum, use the quantum detail and quantum list reports.
- The quantum detail report provides detailed information about a specified quantum. Use this report to troubleshoot long-running quanta or to analyze quantum processing. The NSQuantumDetails (Transact-SQL) stored procedure produces this report.
- The quantum list report provides information about quanta that were processed during a specified time period, and displays the quanta in the order in which they ran. The NSQuantumList (Transact-SQL) stored procedure produces this report.
Scenario: Continuing the scenario introduced in step 1 above, you run the NSQuantumDetails stored procedure for quantum 188. According to the report, you discover that one of the rule firings consumed 90 percent of the quantum time. The next step is to look at the event and notification batches for this and other quanta. Using this report, you note that event batch 60 and notification batch 40 were processed during this quantum.
Step 3: Analyze detailed application data
After analyzing a quantum period, you might want to focus on the specific events, subscriptions, or notifications within the quantum. The event batch details report, the scheduled subscription details report, and the notification batch detail report provide detailed information about application data.
- The event batch detailsreport provides information about a specific event batch for an event class. The report shows summary information about the event batch, and then shows information about each event in the batch. The NSEventBatchDetails (Transact-SQL) stored procedure produces this report. Use the NSEventBatchList (Transact-SQL) stored procedure to obtain event batch IDs.
- The scheduled subscription detailsreport provides information about all of the subscriptions in a subscription class. The NSScheduledSubscriptionDetails (Transact-SQL) stored procedure produces this report.
- If using condition actions, the subscription condition information report returns the query used to evaluate a condition-based subscription. The NSSubscriptionConditionInformation (Transact-SQL) stored procedure produces this report.
- The notification batch detail report provides information about a specific notification batch for a notification class. The report shows summary information about the notification batch, and then shows information about each notification in the batch. The NSNotificationBatchDetails (Transact-SQL) stored procedure produces this report. Use the NSNotificationBatchList (Transact-SQL) stored procedure to obtain notification batch IDs.
- You also can use any of the diagnostic or snapshotreports to look at applications in varying levels of detail.
Scenario: To conclude your analysis of quantum 188, you first run the NSEventBatchDetails stored procedure for event batch 60 and notice that a large number of events were collected during this quantum. Using NSDiagnosticEventClass, you determine that this event batch had substantially more events than most event classes, which shows you that the longer-than-normal quantum execution time does not indicate an application problem; however, optimization of the application, such as optimizing queries and adding indexes, might be required to improve performance.
See Also
Concepts
Notification Services Performance Reports
Monitoring Notification Services Performance and Activity