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

NSDiagnosticEventClass (Transact-SQL)

SQL Server 2005

Produces the event class report for a Microsoft SQL Server Notification Services application. This report can help you troubleshoot event collection and can provide information about the processing of events by the application.


[ schema_name . ] NSDiagnosticEventClass 
    [ @ApplicationName = ] 'app_name' ,
    [ @EventClassName = ] 'event_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.

[ @EventClassName = ] 'event_class_name'

Is the name of an event class within the application. event_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.

EventBatchesCollectedCount

int

Number of event batches collected within the reporting interval. Only batches that completed the collection process during the interval are counted.

UnfinishedEventBatches

int

Number of event batches started, but not completed, during the reporting interval.

AvgEventBatchEventCount

float

For event batches collected during the reporting interval, average number of events per batch.

AvgEventsCollectedPerSecond

float

For event batches collected during the reporting interval, average number of events collected per second.

AvgGeneratorPickupWaitPerBatch

float

For event batches collected during the reporting interval, average amount of time, in seconds, it took the generator to process the event batch (by executing an event chronicle or event subscription rule against it).

AvgEventChronicleRuleFiringTimePerBatch

float

For event batches collected during the reporting interval, average time, in seconds, it took for event chronicle rules to run.

AvgEventSubscriptionRuleFiringTimePerBatch

float

For event batches collected during the reporting interval, average time, in seconds, it took for the event subscription rules to run.

EventNotificationBatchCount

int

From the event batches collected during the reporting interval, the number of notification batches generated.

EventNotificationsGeneratedCount

int

From the event batches collected during the reporting interval, the number of notifications generated.

AvgEventNotificationBatchGenerationTime

float

For the event batches collected during the reporting interval, average time, in seconds, for the generator to create notification batches.

AvgEventNotificationBatchWaitTillDistribution

float

For the event batches collected during the reporting interval, average time, in seconds, that notification batches waited for the distributor to process them.

EventNotificationDeliveryAttempts

int

Number of attempts to deliver notifications created from events collected during the reporting interval.

EventNotificationSuccessfulDeliveries

int

For the event batches collected during the reporting interval, number of successful notification deliveries.

EventNotificationFailedAttempts

int

For the event batches collected during the reporting interval, number of failed notification delivery attempts.

EventNotificationsDeliveryNotAttempted

int

For the event batches collected during the reporting interval, number of notifications the distributor has not yet attempted to deliver.

EventNotificationsNotYetDelivered

int

For the event batches collected during the reporting interval, number of notifications that have not yet been delivered either due to no attempt to deliver or due to failure when attempted.

EventNotificationToMessageRatio

float

For the event batches collected during the reporting interval, the 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.

AvgEventNotificationBatchSucceedDeliveryTime

float

For the notifications generated from the interval's event batches, average time to successfully send the notifications through the distribution process.

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

Event batches fall within the reporting interval when the EndCollectionTime value is greater than the IntervalStartDateTime value and less than or equal to the IntervalEndDateTime value.

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 database role, the db_owner fixed database role, and the sysadmin fixed server role.

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

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

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

EXEC dbo.NSDiagnosticEventClass 
    @ApplicationName = N'Flight', 
    @EventClassName = N'FlightEvents', 
    @ReportingInterval = 40, 
    @StartDateTime = '2004-05-23 17:00', 
    @EndDateTime = '2004-05-23 18:00';

B. Use Default Values, Named Schema

The following example produces the event class diagnostic report for the Flight application and FlightEvents event 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.NSDiagnosticEventClass
    @ApplicationName = N'Flight',
    @EventClassName = N'FlightEvents';

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

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.