How can I determine the current value of server option max_query_tasks?

[Former Member]

How can I confirm that server option max_query_tasks is set to the desired value? I am using SA 11.0.1.2472 and I don't see that setting when I run sa_eng_properties, or SELECT PROPERTY('max_query_tasks'). It does appear if I just do SET in DBISQL, but it seems that the value retrieved by that method may be the connection value rather than the global one -- the value that comes up with SET doesn't always match what I just entered with SET OPTION PUBLIC.max_query_tasks, so I would like another way to confirm the setting, or to be able to query it from within my applications.

[Chris_Kleisath · Accepted solution]

Try this:

select "setting" 
from sysoptions 
where "option" = 'max_query_tasks' and "user_name" = 'public';

See more detailed info on this at this page on DCX: http://dcx.sybase.com/index.html#1101en/dbadmin_en11/finding-introduction-dboptions.html

[Former Member · Accepted solution]

Perfect. Also thanks for the DCX link. I did not know it existed!

[Breck_Carter]

Although there is much overlap between options and properties, they are two different things.

The max_query_tasks property is a connection property, not a server or database property.

The max_query_tasks option may be defined at the PUBLIC, user id and current connection levels (this contradicts the 11.0.1 Help which says only PUBLIC and current connection).

Here is some SQL code which may answer your question, as well as shedding light on the difference between option and property...

SET OPTION PUBLIC.max_query_tasks = '11';
SET OPTION DBA.max_query_tasks = '22';

-- Disconnect / reconnect as DBA.

SELECT CONNECTION_PROPERTY ( 'max_query_tasks' );

/*

CONNECTION_PROPERTY('max_query_tasks')
'22'

*/

SET TEMPORARY OPTION max_query_tasks = '33';

SELECT USER_NAME ( SYSOPTION.user_id ) AS user_id,
       SYSOPTION."option",
       SYSOPTION.setting
  FROM SYS.SYSOPTION
 WHERE SYSOPTION."option" = 'max_query_tasks';

SELECT CONNECTION_PROPERTY ( 'max_query_tasks' );

/*

user_id,option,setting
'PUBLIC','max_query_tasks','11'
'DBA','max_query_tasks','22'

CONNECTION_PROPERTY('max_query_tasks')
'33'

*/