Export (0) Print
Expand All
Expand Minimize

NSQuantumPerformance (Transact-SQL)

SQL Server 2005

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.


[ application_schema_name . ] NSQuantumPerformance 
    [ [ @ReportingInterval = ] interval ]
    [, [ @SinceQuantumInitialized = ] since_init ]

[ @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.

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 Maybe over.

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.

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

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;

Community Additions

ADD
Show:
© 2014 Microsoft