sp_add_resource

Creates a limit on the number of server resources that can be used by an SAP ASE login, or by an application, or both, 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 SAP ASE 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 SAP ASE 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 the SAP ASE 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:
- 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 the SAP ASE server enforces the limit. This must be a positive, nonzero integer that is less than or equal to 2³¹. The following table indicates what value to specify for each limit type:
- 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

enforced Code

Description

Limit Type

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

io_cost

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

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, the SAP ASE server performs a logical “or” of 1 and 2. For example, assume enforced is set to 3. If you run a query with io_cost that 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, the SAP ASE 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:
- 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, the SAP ASE 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:
- 1 – Query
- 2 – Query batch (one or more SQL statements sent by the client to the server)
- 4 – Transaction
- 6 – Query batch and transaction
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, the SAP ASE 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, the SAP ASE 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, the SAP ASE server aborts the transaction:
```
sp_add_resource_limit joe_user, NULL, midday, io_cost, 650, 1, 3, 1
```

Usage

Additional considerations for using sp_add_resource_limit.

You must enable sp_configure "allow resource limits" for resource limits to take effect.
Multiple resource limits can exist for a given user, application, limit type, scope, and enforcement time, as long as their time ranges do not overlap.
All limits for the currently active named time ranges and the “at all times” range for a login and/or application name are bound to the user’s session at login time. Therefore, if a user logs into the SAP ASE server independently of a given application, resource limits that restrict the user in combination with that application do not apply. To guarantee restrictions on that user, create a resource limit that is specific to the user and independent of any application.

Since either the user login name or application name, or both, are used to identify a resource limit, the SAP ASE server observes a predefined search precedence while scanning the sysresourcelimits table for applicable limits for a login session. The following table describes the precedence of matching ordered pairs of login name and application name:

Level	Login Name	Application Name
1	“joe_user”	payroll
2	NULL	payroll
3	“joe_user”	NULL

If one or more matches are found for a given precedence level, no further levels are searched. This prevents conflicts regarding similar limits for different login/application combinations.

If no match is found at any level, no limit is imposed on the session.

When you add, delete, or modify resource limits, the SAP ASE server rebinds the limits for each session for that login and/or application at the beginning of the next query batch for that session.
When you change the currently active time ranges, the SAP ASE server rebinds limits for the session. This rebinding occurs at the beginning of the next query batch.
You cannot associate the limits for a particular login, application, or login/application combination with named time ranges that overlap (except for limits that share the same time range).

For example, if a user is limited to retrieving 50 rows between 9:00 a.m. and 1:00 p.m., you cannot create a second resource limit for the same user that limits him to retrieving 100 rows between 10:00 a.m. and 12:00 noon. However, you can create a resource hierarchy by assigning the 100-row limit to the user between 10:00 a.m. and 12:00 noon and assigning the 50-row limit to an application, like isql, between 9:00 a.m. and 1:00 p.m.

Note: Although the SAP ASE 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.

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

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage resource limit` privilege.
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:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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