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.
sp_add_resource_limit name, appname, rangename, limittype, limitvalue [, enforced [, action [, scope ]]]
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.
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.
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.
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 |
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. |
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.
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).
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.
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
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
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
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 Adaptive 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, Adaptive 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, Adaptive 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, Adaptive 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.
Although 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.
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. |
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 |
|
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
