From the Archives: Controlling service levels using the PRIORITY option

In this post, originally written by Glenn Paulley and posted to sybase.com in September of 2009, Glenn discusses using the PRIORITY option in SQL Anywhere.

SQL Anywhere version 11 deprecated the BACKGROUND_PRIORITY connection option in favour of a new connection option, PRIORITY.

The PRIORITY connection option establishes the processing priority of any SQL request for this connection. The default is “Normal”; other potential values are Critical, High, Above Normal, Below Normal, Low, and Background. When SQL requests are queued for service, the server will process the queue in priority order. Setting the priority option to different values for different connections (or users) permits the categorization of service levels across the entire server’s workload.

While individual users can set their own PRIORITY setting, they cannot set their connection’s PRIORITY to be greater than the value of the MAX_PRIORITY option. The default setting for the MAX_PRIORITY option is also “Normal”; it’s value can be altered only by a user with DBA privileges. Altering the setting is straightforward via the SET OPTION statement:

SET EXISTING OPTION PRIORITY = 'Low'

As with other server, database, and connection-level options, the values of PRIORITY and MAX_PRIORITY can be queried through various means, including the sa_conn_properties() procedure and the CONNECTION_PROPERTY() function, as follows: