The New MaxBPs=768: Set MAX_QUERY_TASKS = '1'

Once upon a time, Windows developers encountered strange runtime errors that were solved by this variation of "waving a dead chicken over the keyboard":

---

January 24, 1994

INFO WORLD Volume 16, Issue 4

Windows Manager/Brian Livingston

CORRECT MOST WINDOWS INSTABILITY WITH JUST A SINGLE COMMAND

For years, Windows users have been mystified by strange error messages.
For no apparent reason, you may be confronted with a dialog box such
as, "Out of memory," "This application has violated system integrity and
will be closed," or "Application error." A quick check of the Help About
box in any Windows applet reveals that you have plenty of memory and Free
System Resources. Yet these nonsensical messages appear, after which
Windows behaves erratically and must be restarted.
I've finally found the cause of most of these messages. Best of all, you can
correct the problem by inserting a single command into the [386Enh] section
of your SYSTEM.INI file:

MaxBPs=768

---

Now, for SQL Anywhere, there's a new magic command that can sometimes help when nothing else seems to work:

SET TEMPORARY OPTION MAX_QUERY_TASKS = '1';

Let's say you have a query that takes way too long; for example,

Execution time: 645.493 seconds

instead of

Execution time: 0.422 seconds

Maybe the query uses too much (or too many) CPUs, too much disk I/O, too much RAM cache...



Or, maybe, your server goes completely unresponsive after executing the query for a long time.

Before investigating, before studying plans or applying EBFs or upgrading to a new version, try something simple...

Try turning off intra-query parallelism

If you know which SQL statement is causing the problem, put this SET command ahead of it to turn off intra-query parallelism for the current connection:

SET TEMPORARY OPTION MAX_QUERY_TASKS = '1';

If it doesn't help, take it out.

If you don't know exactly where the problem is, try the nuclear option (turn off intra-query parallelism for all connections, for all time):

SET OPTION PUBLIC.MAX_QUERY_TASKS = '1';

If that doesn't help, turn intra-query parallelism back on (the default):

SET OPTION PUBLIC.MAX_QUERY_TASKS = '0';

Either way, it didn't hurt anything, maybe it helped, and it didn't take much of your time.

And now, something for the unbelievers...

If you don't believe that SQL Anywhere ever goes runaway, or worse, unresponsive, and even if it did, you don't believe in The Power Of The Dead Chicken to fix the problem, there is another reason to mess with MAX_QUERY_TASKS:

To prevent any single query from unfairly monopolizing too many CPUs, set MAX_QUERY_TASKS to some number smaller than the number of CPUs that SQL Anywhere is using... say 1/2, or 1/4, or maybe even just 2...

SET OPTION PUBLIC.MAX_QUERY_TASKS = '2';

Here's the thinking behind the number '2': it lets a single query use twice as many CPUs as it would have if intra-query parallelism didn't exist, but it also lets other queries have a piece of the pie.

For the Boss, of course, there's always this:

SET TEMPORARY OPTION MAX_QUERY_TASKS = '0';

---