NSQuantumPerformance (Transact-SQL)
Returns information about the execution times of application quanta. The result set groups quanta according to the times they took to execute, showing the number of quanta in various ranges of execution time. This can help you to troubleshoot long-running quanta.
Syntax
[ application_schema_name . ] NSQuantumPerformance
[ [ @ReportingInterval = ] interval ]
[, [ @SinceQuantumInitialized = ] since_init ]
Arguments
[ @ReportingInterval = ] interval
Is the span (in seconds) of each execution time range in the result set. The report contains one row per time range.interval is int and has a default value of 5, which indicates five seconds per time range.
- [ @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 |
|---|---|---|
MinimumExecutionTime |
int |
Quantum execution time, in seconds, for the lower bound of the execution time range. |
MaximumExecutionTime |
int |
Quantum execution time, in seconds, for the upper bound of the execution time range. |
CountOfQuantums |
int |
Count of quanta that executed for a length of time greater than or equal to the row's Minimum Execution Time value, but less than the row's Maximum Execution Time value. |
QuantumDuration |
nvarchar(255) |
Indicates whether the time range for this row of the report includes times longer than the quantum duration defined in the application definition file (ADF). Quantum execution that takes longer than the quantum duration causes the generator to fall behind. Possible values are Under, Over, and Maybe over. |
ChronicleQuantumLimit |
nvarchar(255) |
Indicates whether the time range for this row of the report includes times longer than the result of ChronicleQuantumLimit * QuantumDuration (defined in the ADF). Quantum execution that takes longer than this time can cause chronicle rule firings to be skipped. Possible values are Under, Over, and Maybe over. |
SubscriptionQuantum Limit |
nvarchar(255) |
Indicates whether the time range for this row of the report includes times longer than the result of SubscriptionQuantumLimit * QuantumDuration (defined in the ADF). Quantum execution that takes longer than this time causes subscription rule firings to be skipped. Possible values are Under, Over, and Maybeover. |
Remarks
Microsoft SQL Server Notification Services creates the NSQuantumPerformance 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.
The QuantumDuration, ChronicleQuantumLimit, and SubscriptionQuantumLimit values indicate whether the quantum durations of quanta in the reporting interval took less time or more time than that expected for the application. The values in these columns indicate the following:
- Over indicates that the MinimumExecutionTime value is greater than the quantum limit; all quanta reported in the row took longer than the quantum limit.
- Under indicates that the MaximumExecutionTime value is less than the quantum limit; all quanta reported in the row took less time than the quantum limit.
- Maybe over indicates that the MinimumExecutionTime value is less than or equal to the quantum limit and the MaximumExecutionTime value is greater than or equal to the quantum limit; some quanta might have run longer than the MaximumExecutionTime value.
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 the quantum performance report using a reporting interval of 5 seconds, and using the default value for @SinceQuantumInitialized. This includes only the quanta processed after the last quantum clock initialization.
The application uses the default SchemaName settings, which places all application objects in the dbo schema.
EXEC dbo.NSQuantumPerformance;
B. Use All Quanta and Specify the Reporting Interval
The following example shows how to produce the quantum performance report using a reporting interval of 60 seconds and including quanta process before the last quantum clock initialization.
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.NSQuantumPerformance
@ReportingInterval = 60,
@SinceQuantumInitialized = 1;
See Also
Reference
Notification Services Stored Procedures (Transact-SQL)
Other Resources
Notification Services Performance Reports
SchemaName Element (ADF)