Share via


NSQuantumDetails (Transact-SQL)

Returns detailed information about a quantum. Use this stored procedure to troubleshoot long-running quanta or to analyze quantum processing.

Syntax

[ schema_name . ] NSQuantumDetails 
    [ @QuantumId = ] quantum_id

Arguments

  • [ @QuantumId = ] quantum_id
    Is the ID of a quantum for the report. quantum_id is int and has no default value. Use the NSQuantumList, NSQuantumExecutionTime, and NSQuantumFailures stored procedures to obtain quantum IDs.

Return Code Values

None

Result Sets

NSQuantumDetails produces five result sets. The first result set contains general information about the quantum.

Column Name Data Type Description

QuantumStartTime

datetime

Start time, in UTC, of the time interval that the quantum represents.

QuantumEndTime

datetime

End time, in UTC, of the time interval that the quantum represents; the end time is the start time plus the quantum duration.

QuantumStatusDescription

nvarchar(255)

Text that describes the current status of the quantum.

ExecutionTimeInMS

int

Time, in milliseconds, to complete quantum processing.

ExecutionStartTime

datetime

The real date and time, in UTC, the generator started processing the quantum.

ExecutionEndTime

datetime

The real date and time, in UTC, the generator finished processing the quantum.

EventNotificationsGenerated

bigint

During quantum execution, the number of notifications generated from event-triggered rules.

ScheduledNotificationsGenerated

bigint

During quantum execution, the number of notifications generated from scheduled subscription rules.

ChronicleRuleFiringsSkipped

nvarchar(255)

Indicates whether event chronicle rule firings were skipped during this quantum

SubscriptionRuleFiringsSkipped

nvarchar(255)

Indicates whether subscription rule firings were skipped during this quantum

The second result set contains information about quantum rule firings.

Column Name Data Type Description

RuleFiringId

int

Unique ID of a rule that fired during the quantum.

RuleName

nvarchar(255)

Name of the rule, as defined in the application definition file (ADF).

ExecutionTimeInMS

int

Time, in milliseconds, to complete the rule firing.

PercentageQuantumTime

numeric(7, 4)

Percentage of overall quantum execution time used to fire the rule.

ExecutionStartTime

datetime

Start date and time, in UTC, for the rule firing.

ExecutionEndTime

datetime

End date and time, in UTC, for the rule firing.

RuleTypeDescription

nvarchar(250)

Describes the type of rule that was executed; the type is event chronicle, event subscription, or scheduled subscription.

EventClassName

nvarchar(255)

Name of the event class of the events processed during the rule firing. If the rule firing type is scheduled subscription, this column is NULL, because these rules are not event triggered.

EventCount

bigint

Number of events processed by the rule.

EventBatchId

bigint

ID of the event batch that contains the events processed during the rule firing. If the rule firing type is scheduled subscription, or if the ProcessEventsInOrder value in the ADF is false, this column is NULL because the rule firing might have processed multiple event batches.

SubscriptionClassName

nvarchar(255)

Name of the subscription class of the subscriptions processed during the rule firing. If the rule firing type is event chronicle, this column is NULL.

SubscriptionCount

bigint

Estimated number of subscriptions processed by the rule. The value is estimated based on the current state of the subscriptions in the application database. At the actual time of the rule firing, the database may have contained more or less subscriptions.

StartScanTime

datetime

Start date and time, in UTC, for the scheduled subscriptions used by the rule. This column contains a value only when the rule type is scheduled subscription and the application processes events in order.

EndScanTime

datetime

End date and time, in UTC, for the scheduled subscriptions used by the rule. This column contains a value only when the rule type is scheduled subscription and the application processes events in order.

NotificationsGenerated

int

Number of notifications generated by the rule firing.

The third result set contains information about event batches collected during the quantum.

Column Name Data Type Description

EventClassName

nvarchar(255)

Name of an event class that submitted an event batch.

EventBatchId

bigint

ID of the event batch.

EventCount

bigint

Number of events in the event batch.

StartCollectionTime

datetime

Start date and time, in UTC, for the event batch collection.

EndCollectionTime

datetime

End date and time, in UTC, for the event batch collection.

CollectionTimeInMS

int

Total time, in milliseconds, to collect the batch of events.

ProviderName

nvarchar(255)

Name of the event provider that submitted the batch of events. This value is NULL if the events were directly inserted into the <EventClassName> view.

SystemName

nvarchar(255)

Name of the system where the event provider is running. This value is NULL for non-hosted event providers or if the events were directly inserted into the <EventClassName> view.

The fourth result set contains information about subscriptions processed during the quantum.

Column Name Data Type Description

SubscriptionClassName

nvarchar(255)

Name of a subscription class for which subscriptions were processed during the quantum.

EventSubscriptionCount

bigint

Estimated number of active event-triggered subscriptions for the subscription class during the quantum period.

ScheduledSubscriptionCount

bigint

Estimated number of active scheduled subscriptions for the subscription class during the quantum period.

The fifth report contains information about notifications generated during the quantum.

Column Name

Data Type

Description

NotificationClassName

nvarchar(255)

Name of the notification class to which the notifications in the batch belong.

NotificationBatchId

bigint

Unique ID of a notification batch generated by a rule firing within the quantum.

RuleFiringId

int

Unique ID of the rule firing that produced the notification batch.

NotificationCount

int

Number of notifications in the notification batch.

NotificationDeliveryAttempts

int

Number of attempts made to deliver notifications from the notification batch.

ms186227.note(en-US,SQL.90).gifNote:

If a notification has been handed to the delivery protocol but its status has not been reported back by the delivery protocol, the notification is not counted as attempted.

NotificationSuccessfulDeliveries

int

Number of successful notification deliveries from the notification batch.

NotificationFailedAttempts

int

Number of failed notification deliveries from the notification batch.

NotificationsDeliveryNotAttempted

int

Number of notifications the distributor has not yet attempted to deliver.

NotificationsNotYetDelivered

int

Number of notifications the distributor has not yet successfully delivered.

NotificationToMessageRatio

float

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

Average time, in seconds, to send the notifications through the distribution process successfully.

Remarks

Notification Services creates the NSQuantumDetails stored procedure in the application database when you create the instance. When you update the application, Notification Services recompiles the stored procedure.

This stored procedure is in the application's schema, which is specified by the SchemaName element of the application definition file (ADF). If no schema name is provided, the default schema is dbo.

After obtaining the high-level details of a quantum, you might want to further research the quantum using the following stored procedures:

Permissions

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

Examples

The following example produces the five result sets listed earlier in this section for the first generator quantum of an application:

The application uses the default SchemaName settings, which places all application objects in the dbo schema.

EXEC dbo.NSQuantumDetails 
    @QuantumId = 1;

See Also

Reference

Notification Services Stored Procedures (Transact-SQL)
NSEventBatchDetails (Transact-SQL)
NSNotificationBatchDetails (Transact-SQL)
NSScheduledSubscriptionDetails (Transact-SQL)

Other Resources

Notification Services Performance Reports
SchemaName Element (ADF)

Help and Information

Getting SQL Server 2005 Assistance