Wednesday, November 9, 2011

Jousting For Knobs

Folks who live outside the castle keep, and by that I mean folks who toil with SQL Anywhere databases rather than creating the software itself, sometimes react negatively when told the software drives itself and doesn't need any more knobs.

What's a "knob"? It is a term of scorn applied to a SQL Anywhere parameter or option that is important to the correct and efficient operation of SQL Anywhere but is difficult for mere mortals to set correctly; for example, the multiprogramming level server option -gn prior to Version 12.
The truth is, SQL Anywhere does drive itself to a large extent, Engineering has done a wonderful job in making the product self-tuning.

It is also true that SQL Anywhere doesn't, in general, need more knobs.

And it will be even better when some of the knobs it does have are replaced by automatic processes... like the AutoMultiProgrammingLevel feature which has more-or-less replaced the -gn knob in SQL Anywhere 12.

But...


SQL Anywhere is not perfect, and cannot ever be. In some cases it does need the knobs it has, and in other cases it just might need a new knob or two.

Case in point: When the server crashes, say, on an assertion error, it doesn't automatically come back up. Sometimes it doesn't actually stop but gets "stuck" at the point of shutdown, so it is impossible to code a restart loop in a batch file. Some shops like this behavior, they call in the experts when a server crashes or they install High Availability with every database. But other folks don't want that aggravation... what they want is for the server to come back up automatically. If it crashes again and again they don't care, because automatic recovery works, because crashes are rare and repeated crashes even more so, and because availability is more important to them than any other factor.

They want the choice. They want a knob with two settings: Stay-Down-After-A-Crash versus Come-Back-Up-Right-Away.

Extrapolating from past answers given to other knob-related requests, the response might be thus: "You don't need a knob for that. Besides, it's not safe. We will create sophisticated algorithms that will let the engine decide when it is safe to restart and when it isn't. If you're still not happy, let us know and we will fix it."

That's the message sent, but not the message received.

The message received is, "We know what you need, and it's not what you're asking for."

Which is another way of saying, "You're an idiot."

Even when that is true, it is never the right message.

If silence ensues it is a good indicator, not of consent, but of the fact no other polite form of response is possible.

Especially since...


...knobs are appearing in SQL Anywhere far faster than they are being removed; consider the FROM clause:
FROM table-expression, ...

table-expression :
   table-name
   | ...

table-name :
   [ userid.]table-name ]
   [ [ AS ] correlation-name ]
   [ WITH ( hint [...] ) ]
   [ FORCE INDEX ( index-name ) ]

table-hint :
   READPAST
   | UPDLOCK 
   | XLOCK
   | FASTFIRSTROW
   | HOLDLOCK 
   | NOLOCK 
   | READCOMMITTED
   | READUNCOMMITTED
   | REPEATABLEREAD
   | SERIALIZABLE

index-hint : 
   NO INDEX 
   | INDEX ( [ PRIMARY KEY | FOREIGN KEY ] index-name [, ...] ) [ INDEX ONLY { ON | OFF } ]
Which begs the question, how exactly do knobs get picked for addition to SQL Anywhere?




1 comment:

Anonymous said...

If that would be a forum question, I surely would add a "great-rant" tag - both for contents and wording:)

Well done, again. Of course. I second that proposal.

Volker