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.
Query – SAP ASE 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, SAP ASE evaluates them individually.
SAP ASE 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, SAP ASE evaluates each DML statement within the nested stored procedure at the inner nesting level.
SAP ASE checks preexecution time resource limits with a query scope, one nesting level at a time. As SAP ASE 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. SAP ASE takes the action that is bound to the violated resource limit.
SAP ASE 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, SAP ASE 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.
SAP ASE 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. SAP ASE takes the action that is bound to that resource limit.
Transaction – SAP ASE 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. SAP ASE takes the action that is bound to that resource limit.
Only execution-time resource limits can have a transaction scope.
SAP ASE 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.