Limiting Elapsed Time

Elapsed time is the number of seconds required to execute a query batch or transaction, determined by such factors as query complexity, server load, and waiting for locks.

To develop appropriate limits for elapsed time, use information you have gathered with set statistics time. You can limit the elapsed-time resource only at execution.

With set statistics time set on, run some typical queries to determine processing time in milliseconds. Convert milliseconds to seconds when you create the resource limit.

Elapsed-time resource limits are applied to all SQL statements in the limit’s scope (query batch or transaction), not only to the DML statements. A resource limit violation occurs when the elapsed time for the appropriate scope exceeds the limit value.

Separate elapsed time limits are not applied to nested stored procedures or transactions. In other words, if one transaction is nested within another, the elapsed time limit applies to the outer transaction, which encompasses the elapsed time of the inner transaction. Therefore, if you are counting the running time of a transaction, it includes the running time for all nested transactions.