2 vs 4 Physical CPUs

[RiclWash]

We have been running SQL Anywhere on Windows servers with 2 CPUs for years. We are upgrading to new hardware and have decided to go to our maximum licensing allowance of 4 CPUs. We just did some testing and see no difference in SQL Query/Update/Edit completion from the old hardware.

What am I missing?

[chris_keating]

Please check the following query:

select  property('MinMultiprogrammingLevel'),
           property('CurrentMultiprogrammingLevel'),
           property('MaxMultiprogrammingLevel')

You will likely need to up the maximum multiprogramming level (-gnh).  Unless you have set this already, it is likely too small  given the number of logical processors.  I suspect that the max is currently 80, in the absence of that switch? Sounds like you have beefy hardware so I would consider upping the max to 500 perhaps? Running with -gns  to monitor the engines adjustments to the MPL  is also suggested.    It will output lines like:

          MPL[curr=20 prev=20] concurrency[0] throughput[curr=74 prev=79] algorithm=2 

If the maximum MPL curr reported  in these statistics is near the MaxMultiprogrammingLevel, it may suggest that the MaxMultiprogrammingLevel should be increased.  Note that there is a balancing act here as the MPL configuration impacts memory usage. 

[RiclWash]

I'm going to discuss the maxquerytask setting with my internal team. I have no idea why it is set to '1', nor do I know why the other settings are not just the default.

[RiclWash]

I will also discuss the multiprogramming level with the team. Breck Carter helped us with those settings in 2022, so I will need to pull up those notes.

[VolkerBarth]

IMHO, there are actual reasons not to use intra-query parallelism for particular queries, and therefore it's not uncommon to specify maxquerytask = 1 for those. This has been discussed several times in this forum (more correctly: in the waaayyy better former SQL Anywhere Forum!) and in Breck's blog. But of course one should document such cases and restrict this setting to those particular cases in contrast to a global default setting.

[RiclWash]

2025-04-07 15:31:31.473846 MPL[curr=48 prev=48] concurrency[7] throughput[curr=84590 prev=94936] algorithm=0

[RiclWash]

"You should considering experimenting with a larger -gnh as I suspect that maybe 80 too limiting."

I would assume that increasing this will affect the amount of RAM that will be needed, correct?

[Officegroup]

First of all look at server properties:

NumLogicalProcessors, NumLogicalProcessorsUsed, NumPhisicalProcessors, NumPhisicalProcessorsUsed

So you are sure how many phisical cpu SQL Anywhere use

Then it depends on query complexity; if you use database parameter maxquerytask = 0 you can benefit of intraquery parallelism 

[RiclWash]

The log shows: I. 03/27 07:21:44. This server is licensed to use: all logical processors on all cores on up to 4 physical processors I. 03/27 07:21:44. Processors in use by server: 192 logical processor(s) on 96 core(s) on 4 physical processor(s)

[RiclWash]

I will look into the maxquerytask parameter

[RiclWash]

So we have maxquerytask set to 1:

[RiclWash]

SET OPTION PUBLIC.ansi_update_constraints = 'off'; -- different from default 'Cursors' SET OPTION PUBLIC.auditing_options = '2'; -- different from default '4294967295' SET OPTION PUBLIC.continue_after_raiserror = 'OFF'; -- different from default 'On' SET OPTION PUBLIC.login_procedure = 'NFR.login_check'; -- different from default 'sp_login_environment' SET OPTION PUBLIC.max_query_tasks = '1'; -- different from default '0' SET OPTION PUBLIC.max_temp_space = '32G'; -- different from default '0' SET OPTION PUBLIC.min_password_length = '0'; -- different from default '3' SET OPTION PUBLIC.sr_timestamp_format = 'hh:nn:ss.Ssssss yyyy/mm/dd'; -- different from default 'yyyy/mm/dd hh:nn:ss.Ssssss' SET OPTION PUBLIC.string_rtruncation = 'OFF'; -- different from default 'On'

[chris_keating]

Setting the max_query_tasks option to 1 disables intra-query parallelism. That may degrade performance if queries could be parallelized but prevented from doing so. It is generally best to run with this options default.

Please see my comment to your original question related to MaxMultiprogrammingLevel.

[RiclWash]

Version: 17.0.11.7908, -gna=1, -gn=48, -gnl=48, -gnh=80, current=48

[chris_keating]

You should considering experimenting with a larger -gnh as I suspect that maybe 80 too limiting.