Once upon a time, Windows developers encountered strange runtime errors that were solved by this variation of "waving a dead chicken over the keyboard":
Now, for SQL Anywhere, there's a new magic command that can sometimes help when nothing else seems to work: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
Let's say you have a query that takes way too long; for example,SET TEMPORARY OPTION MAX_QUERY_TASKS = '1';
instead ofExecution time: 645.493 seconds
Maybe the query uses too much (or too many) CPUs, too much disk I/O, too much RAM cache...Execution time: 0.422 seconds
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 parallelismIf 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:
If it doesn't help, take it out.SET TEMPORARY OPTION MAX_QUERY_TASKS = '1';
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):
If that doesn't help, turn intra-query parallelism back on (the default):SET OPTION PUBLIC.MAX_QUERY_TASKS = '1';
Either way, it didn't hurt anything, maybe it helped, and it didn't take much of your time.SET OPTION PUBLIC.MAX_QUERY_TASKS = '0';
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...
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.SET OPTION PUBLIC.MAX_QUERY_TASKS = '2';
For the Boss, of course, there's always this:
SET TEMPORARY OPTION MAX_QUERY_TASKS = '0';