Export (0) Print
Expand All
Expand Minimize
This topic has not yet been rated - Rate this topic

NSDiagnosticSubscriptionClass (Transact-SQL)

SQL Server 2005

Produces the subscription class diagnostic report for a Microsoft SQL Server Notification Services application. The report provides information, such as rule firing times and the delivery of notifications based on the subscription class, that you can use to troubleshoot subscription class processing.


[ schema_name . ] NSDiagnosticSubscriptionClass 
    [ @ApplicationName = ] 'app_name' ,
    [ @SubscriptionClassName = ] 'subscription_class_name' 
    [, [ @ReportingInterval = ] interval ] 
    [, [ @StartDateTime = ] 'start_date_time' ] 
    [, [ @EndDateTime = ] 'end_date_time' ] 
[ @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.

[ @SubscriptionClassName = ] 'subscription_class_name'

Is the name of the subscription class, as defined in the application definition file (ADF). subscription_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 @EndDateTime - (5 * QuantumDuration * @ReportingInterval). 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 time at which you invoke the stored procedure (in UTC).

0 (success) or 1 (failure)

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.

SubscriptionsScheduledCount

int

Number of scheduled subscriptions processed during the reporting interval.

AvgScheduledSubscriptionWaitTime

float

For the subscriptions scheduled for processing during the interval, average lag time between the scheduled time for the subscription and the actual time the generator processed the subscription.

AvgScheduledNotificationGenerationTime

float

For the subscriptions scheduled for processing during the interval, average time taken for the generator to create raw notification data from scheduled subscriptions.

AvgScheduledSubscriptionRuleFiringTime

float

For the subscriptions scheduled for processing during the interval, average time taken to run the scheduled subscription rule.

ScheduledNotificationBatchCount

int

For the subscriptions scheduled for processing during the interval, number of notification batches created.

ScheduledNotificationCount

int

For the subscriptions scheduled for processing during the interval, number of notifications created.

AvgScheduledNotificationBatchWaitTillDistribution

float

Average time a notification batch generated from scheduled subscriptions during the reporting interval waited until the distributor picked it up for distribution.

ScheduledNotificationDeliveryAttempts

int

For the subscriptions scheduled for processing during the interval, number of attempts to deliver notifications.

ScheduledNotificationSuccessfulDeliveries

int

For the subscriptions scheduled for processing during the interval, number of successful notification deliveries.

ScheduledNotificationFailedAttempts

int

For the subscriptions scheduled for processing during the interval, number of failed notification delivery attempts.

ScheduledNotificationsDeliveryNotAttempted

int

For the subscriptions scheduled for processing during the interval, number of notifications not yet attempted to be delivered.

ScheduledNotificationsNotYetDelivered

int

For the subscriptions scheduled for processing during the interval, number of notifications not yet delivered either due to no attempt to deliver or due to failure when attempted.

ScheduledNotificationToMessageRatio

float

For the subscriptions scheduled for processing during the interval, ratio of notifications generated to messages sent.

AvgScheduledNotificationBatchSucceedDeliveryTime

float

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.

Notification Services creates the NSDiagnosticSubscriptionClass 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.

Scheduled subscriptions fall within the reporting interval when the firing time for the scheduled rule 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.

Execute permissions default to members of the NSAnalysis and db_owner database roles and members of the sysadmin fixed server role.

A. Specify Reporting Interval, Start Time, and End Time Values

The following example produces the subscription class diagnostic report for the Flight application and FlightSubscriptions subscription class. The instance uses the default database settings, which places all instance objects in the dbo schema.

The report includes 50 generator quanta in each report interval. The report starts at 5:00 P.M. on May 23, 2004, and ends at 6:00 P.M. the same day:

EXEC dbo.NSDiagnosticSubscriptionClass 
    @ApplicationName = N'Flight', 
    @SubscriptionClassName = N'FlightSubscriptions', 
    @ReportingInterval = 50, 
    @StartDateTime = '2004-05-23 17:00', 
    @EndDateTime = '2004-05-23 18:00';

B. Use Default Values, Named Schema

The following example produces the subscription class diagnostic report for the Flight application and FlightSubscriptions subscription 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 subscription class data for the past five quanta.

EXEC FlightInstance.NSDiagnosticSubscriptionClass 
    @ApplicationName = N'Flight', 
    @SubscriptionClassName = N'FlightSubscriptions';

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.