sp_add_resource_limit

Description

Creates a limit on the number of server resources that can be used by an Adaptive Server login and/or an application to execute a query, query batch, or transaction.

Syntax

sp_add_resource_limit name, appname, rangename, limittype, limitvalue 
	[, enforced [, action [, scope ]]]

Parameters

name

is the Adaptive Server login to which the limit applies. You must specify either a name or an appname or both. To create a limit that applies to all users of a particular application, specify a name of NULL.

appname

is the name of the application to which the limit applies. You must specify either a name or an appname or both. To create a limit that applies to all applications used by an Adaptive Server login, specify an appname of null. To create a limit that applies to a particular application, specify the application name that the client program passes to the Adaptive Server in the login packet.

rangename

is the time range during which the limit is enforced. The time range must exist in the systimeranges system table of the master database at the time you create the limit.

limittype

is the type of resource to limit. This must be one of the following:

Limit type

Description

row_count

Limits the number of rows a query can return

elapsed_time

Limits the number of seconds, in wall-clock time, that a query batch or transaction can run

io_cost

Limits either the actual cost or the optimizer’s cost estimate for processing a query

tempdb_space

Limits the number of pages a tempdb database can have during a single session

limitvalue

is the maximum amount of the server resource (I/O cost, elapsed time in seconds, row count, or tempdb space) that can be used by the login or application before Adaptive Server enforces the limit. This must be a positive, nonzero integer that is less than or equal to 231. The following table indicates what value to specify for each limit type:

Limit type

Limit value

row_count

The maximum number of rows that can be returned by a query before the limit is enforced.

elapsed_time

The number of seconds, in wall-clock time, that a query batch or transaction can run before the limit is enforced.

io_cost

A unitless measure derived from the optimizer’s costing formula.

tempdb_space

The number of pages used in tempdb per session.

enforced

determines whether the limit is enforced prior to or during query execution. The following table lists the valid values for each limit type:

enforced code

Description

Limit type

1

Action is taken when the estimated I/O cost of execution exceeds the specified limit.

io_cost

2

Action is taken when the actual row count, elapsed time, or I/O cost of execution exceeds the specified limit.

row_count

elapsed_time

io_cost

3

Action is taken when either the estimated cost or the actual cost exceeds the specified limit.

io_cost

If you specify an enforced value of 3, Adaptive Server performs a logical “or” of 1 and 2. For example, assume enforced is set to 3. If you run a query whose io_cost exceeds the estimated cost, the specified action is executed. If the query is within the limits specified for estimated cost but exceeds the actual cost, the specified action is also executed.

If you do not specify an enforced value, Adaptive Server enforces limit 2 for row_count and elapsed_time and limit 3 for io_cost. In other words, if the limit type is io_cost, the specified action is executed if the query exceeds either the estimated or actual cost.

action

is the action to take when the limit is exceeded. The following action codes are valid for all limit types:

action code

Description

1

Issues a warning

2

Aborts the query batch

3

Aborts the transaction

4

Kills the session

If you do not specify an action value, Adaptive Server uses a default value of 2 (abort the query batch).

scope

is the scope of the limit. Specify one of the following codes appropriate to the type of limit:

scope code

Description

Limit type

1

Query

io_cost

row_count

2

Query batch (one or more SQL statements sent by the client to the server)

elapsed_time

4

Transaction

elapsed_time

6

Query batch and transaction

elapsed_time

If you do not specify a scope value, the limit applies to all possible scopes for the limit type.

Examples

Example 1

Creates a resource limit that applies to all users of the payroll application during the early_morning time range. If the query batch takes more than 120 seconds to execute, Adaptive Server issues a warning:

sp_add_resource_limit NULL, payroll, early_morning, elapsed_time, 120, 2, 1, 2

Example 2

Creates a resource limit that applies to all ad hoc queries and applications run by “joe_user” during the midday time range. When a query returns more than 5000 rows, Adaptive Server aborts the transaction:

sp_add_resource_limit joe_user, NULL, midday, row_count, 5000, 2, 3, 1

Example 3

Creates a resource limit that applies to all ad hoc queries and applications run by “joe_user” during the midday time range. When the optimizer estimates that the I/O cost would exceed 650, Adaptive Server aborts the transaction:

sp_add_resource_limit joe_user, NULL, midday, io_cost, 650, 1, 3, 1

Usage

NoteAlthough Adaptive Server terminates the current transaction when it reaches its time limit, you receive no 1105 error message until you issue another SQL command or batch; in other words, the message appears only when you attempt to use the connection again.

Permissions

The permission checks for sp_add_resource_limit differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be a user with manage resource limit privilege.

Granular permissions disabled

With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Documents For more information on resource limits, see the System Administration Guide.

System procedures sp_configure, sp_drop_resource_limit, sp_help_resource_limit, sp_modify_resource_limit

Utility isql