NSQuantumExecutionTime (Transact-SQL)

Returns information to help you identify long-running generator quanta, which you can then use to analyze the quanta in more detail.

Syntax

[ schema_name . ] NSQuantumExecutionTime 
    [ @MinExecutionTime = ] min_time 
    [, [ @MaxExecutionTime = ] max_time ]
    [, [ @SinceQuantumInitialized = ] since_init ]

Arguments

  • [ @MinExecutionTime = ] min_time
    Specifies the minimum execution time for a quantum to be included in the report. min_time is int, is specified in seconds, and has no default value.
  • [@MaxExecutionTime = ] max_time
    Specifies the maximum execution time for a quantum to be included in the report. max_time is int, is specified in seconds, and has a default value of min_time plus 5.
  • [ @SinceQuantumInitialized = ] since_init
    Specifies whether the report includes quanta prior to the last time the quantum clock was reset using the NSSetQuantumClock or NSSetQuantumClockDate stored procedures. since_init is tinyint and can be either 0 or 1. The default is 1, which includes only quantum periods after the last reset. 0 includes quantum periods prior to the last reset.

Return Code Values

None

Result Sets

Column Name Data Type Description

QuantumId

int

Unique ID of a quantum. You can supply this ID to the NSQuantumDetails stored procedure to obtain additional information about the quantum.

QuantumStatusDescription

nvarchar(255)

Text that describes the current status of the quantum.

ExecutionTimeInMS

bigint

Number of milliseconds for the generator to process the quantum.

QuantumStartTime

datetime

Start time, in UTC, of the time period the quantum represents.

QuantumEndTime

datetime

End time, in UTC, of the time period the quantum represents. The end time is the start time plus the quantum duration.

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.

QuantumDurationLimit

nvarchar(20)

Indicates whether the quantum execution time was under or over the quantum duration defined in the application definition file (ADF).

ChronicleQuantumLimit

nvarchar(20)

Indicates whether the quantum execution time was greater than ChronicleQuantumLimit * QuantumDuration (defined in the ADF). This indicates whether the time taken for the quantum to execute caused the application to exceed its chronicle quantum limit.

SubscriptionQuantumLimit

nvarchar(20)

Indicates whether the quantum execution time was greater than SubscriptionQuantumLimit * QuantumDuration (defined in the ADF). This indicates whether the time taken for the quantum to execute caused the application to exceed its subscription quantum limit.

ChronicleRuleFiringCount

int

Number of event chronicle rule firings in the quantum.

EventSubscriptionRuleFiringCount

int

Number of event subscription rule firings in the quantum.

ScheduledSubscriptionRuleFiringCount

int

Number of scheduled subscription rule firings in the quantum.

EventNotificationsGenerated

int

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

ScheduledNotificationsGenerated

int

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

Remarks

Notification Services creates the NSQuantumExecutionTime 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 you have identified a quantum of interest, you might need to do further analysis. To get details about the quantum, use the NSQuantumDetails stored procedure.

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. Use Default Values

The following example shows how to produce a quantum execution time report for all quanta that took between 0 and 5 seconds to execute since the last initialization.

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

EXEC dbo.NSQuantumExecutionTime 
    @MinExecutionTime = 0;

B. Return all Quanta Within the Minimum and Maximum Values

The following example shows how to produce a quantum execution time report for all generator quanta that ran for longer than 30 seconds but less than 35 seconds (the value NULL is the same as min_time + 5).

In this example, the stored procedure (like all other application objects) is in the Stock schema, as specified in the SchemaName element of the ADF.

EXEC Stock.NSQuantumExecutionTime 
    @MinExecutionTime = 30, 
    @MaxExecutionTime = NULL, 
    @SinceQuantumInitialized = 0;

See Also

Reference

Notification Services Stored Procedures (Transact-SQL)
NSQuantumDetails (Transact-SQL)

Other Resources

Notification Services Performance Reports
SchemaName Element (ADF)

Help and Information

Getting SQL Server 2005 Assistance