Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Sybase

Posted Mar 2, 2010

Using the Sybase ASE Resource Governor

By DatabaseJournal.com Staff

by Jeff Garbus

The Sybase ASE Resource Governor provides you with a mechanism to limit the resources a given process may request or acquire. If you have very heavy resource drains, it may be that adding this tiny bit of overhead can reduce whatever is currently stressing your system.

If you’ve ever been responsible for database or server performance, you’ve probably heard complaints along these lines:

“The system was slow last night because a super-user was downloading 30 million rows of data to his Access database and it cause a bit of blocking & network bottlenecking.” (For some reason this one seems to happen disproportionately often at health insurance companies.)

“We have a mixed-use environment with no restrictions on when various departments can run jobs. Some of these jobs can grab significant resources over the course of a few minutes and completely halt the rest of the processing”

“The marketing guys will frequently send coupons to a couple of thousand folks, but every once in a while they will accidentally try to send a few million, which hoses the CPU, IO subsystem, and network, all at the same time.”

“The batch process keeps taking up the entire tempdb database, creating a lot of calls to the help desk.”

The Resource Governor provides the ASE administrator with a mechanism to limit the resources a given process may request or acquire. It can limit the runaway queries at the application or login level. It can limit:

  • i/o cost, estimated and/or actual
  • elapsed time, estimated and/or actual
  • number of rows to be returned, estimated and/or actual
  • The number of pages of tempdb space a process may acquire

Stated differently, you can choose to enforce your resource limits:

  • Prior to execution
  • During execution
  • Or both

You can also choose a scope for the action. If you have a complicated batch, you may want the scope to be the batch rather than an individual SQL Statement. Your choices there are:

  • Query
  • Batch
  • Transaction
  • Batch and transaction

The Systems Administrator can instruct the server to perform any of the following actions when a resource limitation is exceeded:

  • A warning can be issued
  • The batch can be aborted
  • The transaction can be aborted
  • The login can be terminated (session ended)

You can also set different resource limits for different times of day. For example, you may choose to let the “batch” login have all the resources it wants from midnight to 6am, but limit the resources the rest of the time (of course, you want to make sure your developers know that they need to put restart logic in their scripts in case the processes are terminated!)

Step 1: Enabling resource limits

Resource limits are enabled at the server level. This allows the server to allocate memory for time ranges, internal alarms, and to set up storage structures to apply applicable ranges & limits to login sessions.

Note that this is where we get into a point of contention with some DBAs, who claim that enabling resource limits slows the server down. In a sense, they are right; it does take some server resources to maintain the resource governor; but it’s not that much. If you’re running at 90% of capacity, you don’t want to add ANY overhead to your box; if you’re running at 30% of capacity, it doesn’t matter a lot. In between, there’s a gray line some place... the Resource Governor should take up less than 5% of resources, though these numbers can be difficult to impossible to measure.

Syntax:

sp_configure ‘allow resource limits’,1

Note that this is not a dynamic parameter, and as such, the server needs to be restarted to take effect.

Step 2: Setting up time ranges

A time range is a contiguous block of time elapsed over a contiguous block of days over which a resource limit is intended to be enforced.

There is a default time range set up, “at all times,” which as you may surmise means all of the time.

For example, you may set up a time period of 8am to 5pm, Monday through Friday, and call that “Prime Time” – you may have another called “Weekend” which includes Saturday and Sunday; and perhaps another called “Batch Time” which is midnight to 6am, all 7 days.

These time ranges may overlap, which is good since otherwise the “at all times” time range (which cannot be removed) would conflict with everything else.

You get to choose and set them up, like this:

Syntax:

sp_add_time_range name, startday, endday, starttime, endtime

Examples:

sp_add_time_range 'Prime Time',  Monday, Friday, '08:00','17:00'

-- This creates the time range we discussed above, weekdays from 8am to 5pm.

sp_add_time_range 'Weekend',  Saturday, Sunday, '00:00','00:00'

--  ... and here's the other one. Note that we used zero o'clock for both times to include the whole days.

You can change a time range if your business needs change, somebody is abusing their privileges (always starting something on a boundary & getting aborted), etc. Note that null values here mean, “Don’t change anything – we like you just the way you are.”

Syntax:

sp_modify_time_range name, startday, endday, starttime, endtime

Example:

sp_modify_time_range 'Prime Time', NULL, NULL, NULL,'17:30'

-- changes the time range to extent until 5:30 p.m.

Finally, you can get rid of a time range if you no longer have use for it, like this:

Syntax:

sp_drop_time_range timerangename 

Example:

sp_drop_time_range ‘Prime Time’

A couple of notes: The default time range may not be modified or dropped. Also, changes have no effect on active processes, only on subsequent connections. Finally, you can’t drop a time range that is in use by a resource limit.

Step 3: Setting up resource limits

Finally, it’s time to add the resource limit.

A resource limit is a way of limiting the effect an individual process or group of processes may have on a specific resource. For example, you might limit the marketing application to a specific number of rows of demographic data returned to their client application, by sending a warning message to the user. Or, you might limit an access program from retrieving more than 10,000 rows of data, by aborting the application when the server estimates (or exceeds) the 10,000 rows.

Syntax:

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

This one bears explanation first, examples second:

Name

Name of the resource limit (null applies to all users of the application)

Appname

Name of the application to limit as passed in by the client program (null applies to all applications for a login)

Rangename

This is the time range you specified in the earlier step

Limit type

  • Row_count – the number of rows an application can return
  • Elapsed_time – the number of elapsed seconds that an application can run
  • Io_ cost – the cost in io of the query (based upon the optimizers unitless measure
  • Tempdb_space – the number of pages in tempdb that the query can acquire

Limitvalue

A positive integer <= 231 specifying a value as in limit types above

Enforced

When is the action to be taken?

1 – Action is taken when the estimated I/O cost of execution exceeds the specified limit.
2 – Action is taken when the actual row count, elapsed time, or I/O cost of execution exceeds the specified limit.
3 – Action is taken when either the estimated cost or the actual cost exceeds the specified limit.
So... 1 is enforced for estimates, 2 is enforced for actual, 3 is enforced if either is exceeded

Action

What do we do when the limit is exceeded?

1 – issue a warning
2 – abort the batch
3 – abort the transaction
4 – kill the session
Note that the default behavior is to kill the batch

Scope

What are we limiting?
1 – query
2 – query batch
4 – transaction
6 – query batch and transaction
If you don’t specify a scope, this will refer to all scopes

Examples

1)      Create a limit for all logins that are running isql during prime time (defined above). Abort the batch if the actual or number of tempdb pages is greater than 100.

sp_add_resource_limit NULL, isql,’Prime Time’, tempdb_space, 100,3,2

2)      Create a limit for web_login. For all applications, prime time, issue a warning if the actual or number of tempdb pages is greater than 100000

sp_add_resource_limit web_login, NULL, ‘Prime Time’, tempdb_space, 100000,3,1 

Rehash

So, the steps were:

1)      Enable resource limits at the server level

2)      Create the named time ranges

3)      Define the limits on the specific resources to take effect during your time range or the default (“at all times”)

Limit hierarchies

As you’ve seen, you have the ability to create resource limits at either the application or the login level.

What happens when both apply? What if they conflict?

Adaptive Server checks limits in this order:

1.       check limits for a login/application combo

2.       check limits for a application

3.       check limits for a login

Once it finds a limit, it stops looking. So, no conflicts are possible.

Possibilities

The obvious possibilities are listed above; you can stop specific users and/or applications from monopolizing or abusing your system.

There are some other possibilities too. For example, in any client / server application you run the risk that folks will, for example, log in through their excel spreadsheet and make changes directly to your system. You probably don’t want users to do this.

You can force users to use only known applications like this:

1)      Using the “at all times” range, set each limit type to abort when the limit hits zero. This stops the users from running anything.

2)      Turn on the acceptable applications for all users to the settings you’re willing to allow them to use.

Viewing resource limits

What limits have already been set? As with many other ASE tools, it’s an sp_help variant.

Syntax:

sp_help_resource_limit [name [,appname[, limittime [, limitday [, scope [,action [,verbose] ]]]]]]

-or-

sp_help_resource_limit @verbose = 1

Once again, we’ll define first, and offer examples second:

Name

Name of the resource limit. Default is the current login; only a login with the sa_role can look at limits for other logins

appname

Name of the application for which we want information; if null, this refers to all applications

Limittime

The time the limit is enforces; if null, all times)

Limitday

Day of the week for enforcement; default is all days

Scope

Limit of the scope (see scope numbering, above); null means all scopes

Action

Action for the limit (see action numbering, above); null means all actions

Verbose

Translates numbers to verbiage

Modifying resource limits

You can modify the action or the value upon which the action acts.

Syntax

sp_modify_resource_limit name, appname, time range,limittype, limitvalue, enforced, action, scope

As these are all defined above, we won’t repeat them here. Should you choose not to modify the limit of either the action or the value, use either the existing value or null for the parameter. All other values must match the initially created values. If this is insufficient to your needs, drop & recreate the resource limit.

Removing resource limits

Limits can also be removed, as follows.

Syntax

sp_drop_resource_limit name, appname, rangename, limittype, action, scope

Again, the parameters are the same, so we won’t rehash.

Summary

Resource limits give you a great degree of control over the resources in your environment. Impact is relatively low. On the flip side, if you have very heavy resource drains, it may be that adding this tiny bit of overhead can reduce whatever is currently stressing your system, or at least cut it down to something manageable.

Limits can change based upon who is running, what they are running, and what time the application is running, or any combination thereof.

Limits can be enforced based upon estimated, actual, or either utilization. When the limit is exceeded, you have your choice as to whether to send a warning, or abort the query at several different levels.

Additional Resources

Sybase Adaptive Server Enterprise 15 Underused features for the DBA

A 20-year veteran of Sybase ASE database administration, design, performance, and scaling, Jeff Garbus has written over a dozen books, many dozens of magazine articles, and has spoken at dozens of user’s groups on the subject over the years. He is CEO of Soaring Eagle Consulting, and can be reached at Jeff Garbus.



Sybase Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Sybase Forum
Topic By Replies Updated
DB Error Help Prepared Statements sjulian 2 July 5th, 04:58 AM
DB Error Help Prepared Statements sjulian 0 May 31st, 07:19 AM
Test, just a test XRumerTest 0 May 5th, 04:57 AM
Execute Procedure in SQL statement vcs1161 0 August 24th, 07:38 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date