NSDiagnosticNotificationClass (Transact-SQL)
Produces the notification class report for a Microsoft SQL Server Notification Services application. The report provides information about the number of notifications and notification batches processed by the notification class, and whether the notifications are being successfully delivered.
Syntax
[ schema_name . ] NSDiagnosticNotificationClass
[ @ApplicationName = ] 'app_name' ,
[ @NotificationClassName = ] 'notification_class_name'
[, [ @ReportingInterval = ] interval ]
[, [ @StartDateTime = ] 'start_date_time' ]
[, [ @EndDateTime = ] 'end_date_time' ]
Arguments
- [ @ApplicationName=] 'app_name'
Is the name of an application, as defined in the configuration file. app_name is nvarchar(255) and has no default value.
- [ @NotificationClassName=] 'notification_class_name'
Is the name of the notification class, as defined in the application definition file (ADF). notification_class_name is nvarchar(255) and has no default value.
[ @ReportingInterval=] interval
Is the number of generator quanta in each reporting interval. The report contains one row per interval. interval is int and has a default value of 1, which indicates one generator quantum per interval.The quantum duration is defined in the application definition file (ADF).
- [ @StartDateTime=] 'start_date_time'
Is the report start date and time in UTC (Coordinated Universal Time or Greenwich Mean Time). start_date_time is datetime. The default value is (5 * QuantumDuration * @ReportingInterval) before the @EndDateTime value. Using the default value, the result set contains at most five rows, each row representing one reporting interval.
- [ @EndDateTime=] 'end_date_time'
Is the report end date and time in UTC. end_date_time is datetime. The default value is the result of the GETUTCDATE function, which returns the time when the stored procedure is invoked.
Return Code Values
0 (success) or 1 (failure)
Result Sets
| Column Name | Data Type | Description |
|---|---|---|
IntervalStartDateTime |
datetime |
Start date and time, in UTC, for the reporting interval. |
IntervalEndDateTime |
datetime |
End date and time, in UTC, for the reporting interval. |
NotificationBatchesGenerated |
int |
For the notification class, the number of notification batches generated during the reporting interval. |
NotificationsGenerated |
int |
For the notification class, the number of notifications generated during the reporting interval. |
NotificationsGeneratedPerSecond |
float |
For the notification class, the number of notifications generated per second during the reporting interval. |
AvgNotificationBatchWaitTillDistribution |
float |
For the notification class, average wait time before the distributor picked up a notification batch during the reporting interval. |
NotificationDeliveryAttempts |
int |
For notification batches of the given notification class generated during the reporting interval, number of notification delivery attempts. |
NotificationSuccessfulDeliveries |
int |
For notification batches of the given notification class generated during the reporting interval, number of notifications successfully delivered. |
NotificationFailedAttempts |
int |
For notification batches of the given notification class generated during the reporting interval, number of failed notification delivery attempts. |
NotificationsDeliveryNotAttempted |
int |
For notification batches of the given notification class generated during the reporting interval, number of notifications the distributor has not yet attempted to deliver. |
NotificationsNotYetDelivered |
int |
For notification batches of the given notification class generated during the reporting interval, number of notifications not yet delivered either due to no attempt to deliver or due to failure when attempted. |
NotificationToMessageRatio |
float |
For notification batches generated during the reporting interval, ratio of notifications generated to messages sent. For digest or multicast delivery, one message can include multiple notifications. This column indicates how many notifications, on average, are included in digest or multicast messages. |
AvgNotificationBatchSucceedDeliveryTime |
float |
For notification batches generated during the reporting interval, average time to successfully send the notifications through the distribution process. |
Remarks
Notification Services creates the NSDiagnosticNotificationClass stored procedure in the instance database when you create the instance. When you update the instance, Notification Services recompiles the stored procedure.
This stored procedure is in the instance schema, which is specified by the SchemaName element of the instance configuration file (ICF). If no schema name is provided, the default schema is dbo.
Notification batches fall within a reporting interval when the EndGenerationTime value is greater than IntervalStartDateTime and less than or equal to IntervalEndDateTime.
To determine the current UTC date and time, run SELECT GETUTCDATE() in SQL Server Management Studio. The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.
Permissions
Execute permissions default to members of the NSAnalysis database role, the db_owner fixed database role, and the sysadmin fixed server role.
Examples
A. Specify Reporting Interval, Start Time, and End Time Values
The following example produces the notification class diagnostic report for the Flight application and FlightNotifications notification class. The instance uses the default database settings, which places all instance objects in the dbo schema.
The report includes 60 generator quanta in each report interval. The report starts at 5:00 P.M. on 23 May, 2002, and ends at 6:00 P.M. the same day:
EXEC dbo.NSDiagnosticNotificationClass
@ApplicationName = N'Flight',
@NotificationClassName = N'FlightNotifications',
@ReportingInterval = 60,
@StartDateTime = '2004-05-23 17:00',
@EndDateTime = '2004-05-23 18:00';
B. Use Default Values, Named Schema
The following example produces the notification class diagnostic report for the Flight application and FlightNotifications notification class. In this example, the stored procedure (like all other instance objects) is in the FlightInstance schema, as specified in the SchemaName element of the ICF.
The report uses default values, which specifies to show data for the past five generator quanta.
EXEC FlightInstance.NSDiagnosticNotificationClass
@ApplicationName = N'Flight',
@NotificationClassName = N'FlightNotifications';
See Also
Reference
Notification Services Stored Procedures (Transact-SQL)
Other Resources
Notification Services Performance Reports
SchemaName Element (ICF)