The scope parameter (for example, sp_add_resource_limit scope or sp_help_resource_limit scope) specifies the duration of a limit in Transact-SQL statements. The possible limit scopes are query, query batch, and transaction:
Query – Adaptive Server applies resource limits to any single Transact-SQL statement that accesses the server; for example, select, insert, and update. When you issue these statements within a query batch, Adaptive Server evaluates them individually.
Adaptive Server considers a stored procedure to be a series of DML statements. It evaluates the resource limit of each statement within the stored procedure. If a stored procedure executes another stored procedure, Adaptive Server evaluates each DML statement within the nested stored procedure at the inner nesting level.
Adaptive Server checks preexecution time resource limits with a query scope, one nesting level at a time. As Adaptive Server enters each nesting level, it checks the active resource limits against the estimated resource usage of each DML statement prior to executing any of the statements at that nesting level. A resource limit violation occurs if the estimated resource usage of any DML query at that nesting level exceeds the limit value of an active resource limit. Adaptive Server takes the action that is bound to the violated resource limit.
Adaptive Server checks execution-time resource limits with a query scope against the cumulative resource usage of each DML query. A limit violation occurs when the resource usage of a query exceeds the limit value of an active execution-time resource limit. Again, Adaptive Server takes the action that is bound to that resource limit.
Query batch – consists of one or more Transact-SQL statements; for example, in isql, a group of queries becomes a query batch when executed by a single go command terminator.
The query batch begins at nesting level 0; each call to a stored procedure increments the nesting level by 1 (up to the maximum nesting level). Each return from a stored procedure decrements the nesting level by 1.
Only execution-time resource limits can have a query batch scope.
Adaptive Server checks execution-time resource limits with a query batch scope against the cumulative resource usage of the statements in each query batch. A limit violation occurs when the resource usage of the query batch exceeds the limit value of an active execution-time resource limit. Adaptive Server takes the action that is bound to that resource limit.
Transaction – Adaptive Server applies limits with a transaction scope to all nesting levels during the transaction against the cumulative resource usage for the transaction.
A limit violation occurs when the resource usage of the transaction exceeds the limit value of an active execution-time resource limit. Adaptive Server takes the action that is bound to that resource limit.
Only execution-time resource limits can have a transaction scope.
Adaptive Server does not recognize nested transactions when applying resource limits. A resource limit on a transaction begins when @@trancount is set to 1 and ends when @@trancount is set to 0.
Session – idle time limits are applied to the sessions in which the limit is active.
