New Properties in SQL Anywhere 12 and Beyond

SQL Anywhere keeps track of several hundred "properties" at runtime and it makes them all available through various procedure and function calls. For example, here's how you can display all the properties at all three levels (connection, database and server):

SELECT PropName, PropDescription, Value FROM sa_conn_properties() WHERE Number = @@SPID ORDER BY PropName;
SELECT PropName, PropDescription, Value FROM sa_db_properties() ORDER BY PropName; 
SELECT PropName, PropDescription, Value FROM sa_eng_properties() ORDER BY PropName;

With each new version (e.g., 12) and each new point release (12.0.1) comes new properties. What's not expected, however, is the addition of new properties when an EBF is published... but that's exactly what has happened at some point since 12.0.1 was published.

Here are the properties that didn't exist in the GA 12.0.1 release 12.0.1.3152 but do now exist in the latest EBF 12.0.1.3505:

PropName             PropDescription                                       Value

Connection...
LastCommitRedoPos    Redo position at end of last commit                   9409179824

Database...
LastCommitRedoPos    Redo position at end of last commit                   9409179824
LastSyncedRedoPos    Last redo position written and synchronized to disk   9409179824
LastWrittenRedoPos   Last redo position written to disk                    9409179824

Server...
LicenseKey           Number of licensed seats                              *****-*****-*****-*****-*****

The obvious question is, were these properties needed by Fuji? EBFs don't come with "What's New" lists so it's hard to say.

I'll bet the answer is "no" for "LicenseKey" (and we won't mention the hosed up interesting PropDescription).

For all those "RedoPos" properties, if Fuji needs that kind of stuff, it surely must be delving deep into the internals :)

---

For the record, here are all the properties that did not exist in the original GA 12.0.0 release but were included in the GA 12.0.1 release (in theory, some of these could have snuck in with an EBF, but let's pretend not):

Connection...
PropName,PropDescription,Value

ConnectedTime,Total time connections have been connected,86.047
java_class_path,Additional jars and directories added to the class path when launching the external java vm,
st_geometry_interpolation,Specifies options to use when converting from an ST_CircularString to an ST_LineString,
uuid_has_hyphens,Controls format for UUID values,On
UserDefinedCounterRate01,A rate counter that is set by the database application (counter 1),0
UserDefinedCounterRate02,A rate counter that is set by the database application (counter 2),0
UserDefinedCounterRate03,A rate counter that is set by the database application (counter 3),0
UserDefinedCounterRate04,A rate counter that is set by the database application (counter 4),0
UserDefinedCounterRate05,A rate counter that is set by the database application (counter 5),0
UserDefinedCounterRaw01,A counter that is set by the database application (counter 1),0
UserDefinedCounterRaw02,A counter that is set by the database application (counter 2),0
UserDefinedCounterRaw03,A counter that is set by the database application (counter 3),0
UserDefinedCounterRaw04,A counter that is set by the database application (counter 4),0
UserDefinedCounterRaw05,A counter that is set by the database application (counter 5),0

Database...
PropName,PropDescription,Value

ApproximateCPUTime,Approximate CPU time used,.2658304
BytesReceived,Bytes received by server,53138
BytesReceivedUncomp,Bytes received after decompression,53138
BytesSent,Bytes sent to client,60237
BytesSentUncomp,Bytes sent before compression,60237
CarverHeapPages,Cache pages used for carvers,0
ClientStmtCacheHits,Number of prepares not required because of the client statement cache,9
ClientStmtCacheMisses,Number of prepares in the client statement cache which were prepared again,0
Commit,Number of commit requests,50
ConnectedTime,Total time connections have been connected,126.6503625460778
Cursor,Declared cursors,8
CursorOpen,Open cursors,8
HeapsCarver,Number of heaps used for carvers,0
HeapsLocked,Number of relocatable heaps currently locked in cache,8
HeapsQuery,Number of heaps used for query processing (hash and sort operations),0
HeapsRelocatable,Number of relocatable heaps,44
PacketsReceived,Packets received by server,156
PacketsReceivedUncomp,Packets received after decompression,156
PacketsSent,Packets sent to client,156
PacketsSentUncomp,Packets sent before compression,156
PrepStmt,Prepared statements,17
QueryHeapPages,Cache pages used for query processing (hash and sort operations),0
QueryMemActiveCurr,The current number of requests actively using query memory,1
QueryMemGrantFailed,The total number of times any request waited for query memory and failed to get it,0
QueryMemGrantGranted,The number of pages currently granted to requests,65676
QueryMemGrantRequested,The total number of times any request attempted to acquire query memory,3
QueryMemGrantWaited,The total number of times any request waited for query memory,0
QueryMemGrantWaiting,The current number of requests waiting for query memory,0
ReqCountActive,Number of requests processed,0
ReqCountBlockContention,Number of times waited for atomic access,0
ReqCountBlockIO,Number of times waited for I/O to complete,126
ReqCountBlockLock,Number of times waited for a lock,0
ReqCountUnscheduled,Number of times waited for scheduling,0
ReqTimeActive,Time spent processing requests,.7556879435794214
ReqTimeBlockContention,Time spent waiting for atomic access,0
ReqTimeBlockIO,Time spent waiting for I/O to complete,.1078449914723799
ReqTimeBlockLock,Time spent waiting for a lock,0
ReqTimeUnscheduled,Time spent unscheduled,0
RequestsReceived,Requests received by server,154
Rlbk,Rollback requests handled,0
UserDefinedCounterRate01,A rate counter that is set by the database application (counter 1),0
UserDefinedCounterRate02,A rate counter that is set by the database application (counter 2),0
UserDefinedCounterRate03,A rate counter that is set by the database application (counter 3),0
UserDefinedCounterRate04,A rate counter that is set by the database application (counter 4),0
UserDefinedCounterRate05,A rate counter that is set by the database application (counter 5),0
UserDefinedCounterRaw01,A counter that is set by the database application (counter 1),0
UserDefinedCounterRaw02,A counter that is set by the database application (counter 2),0
UserDefinedCounterRaw03,A counter that is set by the database application (counter 3),0
UserDefinedCounterRaw04,A counter that is set by the database application (counter 4),0

Server...
PropName,PropDescription,Value

ApproximateCPUTime,Approximate CPU time used,.2971648
Commit,Number of commit requests,50
ConnectedTime,Total time connections have been connected,126.7005812191214
Cursor,Declared cursors,6
CursorOpen,Open cursors,6
PrepStmt,Prepared statements,17
ReqCountActive,Number of requests processed,0
ReqCountBlockContention,Number of times waited for atomic access,0
ReqCountBlockIO,Number of times waited for I/O to complete,126
ReqCountBlockLock,Number of times waited for a lock,0
ReqCountUnscheduled,Number of times waited for scheduling,0
ReqTimeActive,Time spent processing requests,.7711354376044999
ReqTimeBlockContention,Time spent waiting for atomic access,0
ReqTimeBlockIO,Time spent waiting for I/O to complete,.1078449914723799
ReqTimeBlockLock,Time spent waiting for a lock,0
ReqTimeUnscheduled,Time spent unscheduled,0
Rlbk,Rollback requests handled,0
UserDefinedCounterRate01,A rate counter that is set by the database application (counter 1),0
UserDefinedCounterRate02,A rate counter that is set by the database application (counter 2),0
UserDefinedCounterRate03,A rate counter that is set by the database application (counter 3),0
UserDefinedCounterRate04,A rate counter that is set by the database application (counter 4),0
UserDefinedCounterRate05,A rate counter that is set by the database application (counter 5),0
UserDefinedCounterRaw01,A counter that is set by the database application (counter 1),0
UserDefinedCounterRaw02,A counter that is set by the database application (counter 2),0
UserDefinedCounterRaw03,A counter that is set by the database application (counter 3),0
UserDefinedCounterRaw04,A counter that is set by the database application (counter 4),0
UserDefinedCounterRaw05,A counter that is set by the database application (counter 5),0

---

Latest SQL Anywhere EBFs: 12.0.1.3505 and 11.0.1.2713

The three asterisks "***" show what's new since the previous list. Only EBFs for the three fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3469 EBF       14 Oct 2011
                 11.0.1.2664 EBF       24 Aug 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2637 EBF       26 Jul 2011

Linux x86/x64    12.0.1.3457 EBF       12 Oct 2011
                 11.0.1.2700 EBF       27 Oct 2011
                 10.0.1.4239 EBF       17 Jun 2011

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2632 EBF       07 Jul 2011
                 10.0.1.4239 EBF       17 Jun 2011

Windows x86/x64  12.0.1.3505 EBF ***   24 Nov 2011 ***
                 11.0.1.2713 EBF ***   24 Nov 2011 ***
                 10.0.1.4295 EBF       18 Nov 2011

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Solaris x64      11.0.1.2645 EBF                 05 Aug 2011
                 10.0.1.3870 EBF                 01 Apr 2009

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

---

The fRiDaY File - And now, Intra-Personal Parallelism!

---

Pssst! Wanna see some Intra-Query Parallelism?

If you think about this query for a moment,

SELECT COUNT(*) 
  FROM SYSCOLUMN AS A 
       CROSS JOIN SYSCOLUMN AS B
       CROSS JOIN SYSCOLUMN AS C;

you might not be surprised that it does this to a SQL Anywhere 12 server... even with an empty SQL Anywhere 12 database:



That's because SYSCOLUMN is a view on top of ISYSTABCOL, and there are 2,011 rows in ISYSTABCOL even for an "empty" SQL Anywhere 12 database (there are a lot of columns in all those system catalog tables)...

...and there are 2,011 times 2,011 times 2,011 equals 8,132,727,331 rows in the three-way CROSS JOIN of SYSCOLUMN with itself.

That's a lotta rows to count, and if there are eight CPUs with nothing else to do, SQL Anywhere's gonna give them all a piece of the work to do.

Let's recap: That's one query running all 8 CPUs up to 100%.

Hubba Hubba!

That's nasty!

No, that's cool!

That's intra-query parallelism!

Wanna see another feelthy peecture?

Here you go, here's Foxhound showing 8 separate "INT" connections inside SQL Anywhere running the "Exchange algorithm" to hurry things along:



Those eight "INT: Exchange" connections are separate from the dbisql connection "SQL_DBC_4bdc7f00" which is fired off the SELECT COUNT(*), and they are a special kind of connection:

• they are not counted in the server-level "Conns" column which is based on the ConnCount database-level property, but
  

• they are counted in the "Executing" column which is based on the ReqStatus connection-level property, and of course
  

• all sorts of information is available at the connection level, otherwise Foxhound wouldn't be showing all that detail in the "10 connections" section.
  

The "CPU Time" columns are interesting: At the server level, the total is 4m 8.1s across all 8 CPUs, while at the connection level each "INT: Exchange" shows CPU Time over 4m 5s for its individual CPU.

---

The Thursday Quote - Clive Thompson

---

"... the ability to judge information is almost never taught in school."

Why Kids Can’t Search by Clive Thompson, from Wired magazine November 1, 2011

---

Every once in a while, old truths are rediscovered by new people, and so it is with Clive Thompson: Kids can't search because they can't judge the quality of information.

---

High school and college students may be "digital natives," but they’re wretched at searching. In a recent experiment at Northwestern, when 102 undergraduates were asked to do some research online, none went to the trouble of checking the authors’ credentials. In 1955, we wondered why Johnny can’t read. Today the question is, why can’t Johnny search?

Who’s to blame? Not the students. If they’re naive at Googling, it’s because the ability to judge information is almost never taught in school.
. . .
A good education is the true key to effective search. But until our kids have that, let’s make sure they don’t always take PageRank at its word.

---

Google may be new, but the (in)ability to judge information isn't, nor is the call for "a good education".

Alas, instead of critical thinking, here is what's being taught to millions of kids today:

---

"It’s the government’s job is to watch out for us, to take care of us. That's their job."

The Story of Stuff (see the transcript here)

---

---

Microsoft SQL Server 1992... almost

Sometimes it's fun to browse the promotional material for Other People's Products, just to see what's coming, and sometimes, just to see what they are finally getting around to implementing.

Or, in the case of Denali's "new" Partially Contained Databases, what they are partially implementing:

---

A contained database is a concept in which a database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed. Users connect to the database without authenticating a login at the Database Engine level. Isolating the database from the Database Engine makes it possible to easily move the database to another instance of SQL Server. Including all the database settings in the database enables database owners to manage all the configuration settings for the database.

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) includes a first step toward contained databases, introducing partially contained databases (also known as Partial-CDB). Partially Contained Databases provide some isolation from the instance of SQL Server but do not yet provide full containment.

---

Did you notice that? "Isolating the database from the Database Engine makes it possible to easily move the database to another instance of SQL Server."

Just like SQL Anywhere, which has isolated the database from the server since... well, forever... at least as far back as SQL Anywhere 5.5 in 1992...

dbeng50w [engine-switches][database-file [database-switches], ...]

The separation of database from server is such a fundamental characteristic of SQL Anywhere that the docs don't talk about it, not even in the Hallmarks of SQL Anywhere 12.

Gosh, what's next for SQL Server?

Self-management?

Binary portability of the database file?

Linux?

---

Latest SQL Anywhere EBF: 10.0.1.4295

The three asterisks "***" show what's new since the previous list. Only EBFs for the three fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3469 EBF       14 Oct 2011
                 11.0.1.2664 EBF       24 Aug 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2637 EBF       26 Jul 2011

Linux x86/x64    12.0.1.3457 EBF       12 Oct 2011
                 11.0.1.2700 EBF       27 Oct 2011
                 10.0.1.4239 EBF       17 Jun 2011

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2632 EBF       07 Jul 2011
                 10.0.1.4239 EBF       17 Jun 2011

Windows x86/x64  12.0.1.3484 EBF       31 Oct 2011
                 11.0.1.2701 EBF       07 Nov 2011
                 10.0.1.4295 EBF ***   18 Nov 2011 ***

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Solaris x64      11.0.1.2645 EBF                 05 Aug 2011
                 10.0.1.3870 EBF                 01 Apr 2009

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

---

The fRiDaY File - Hurry! Before It's Gone!

Bottleneck: Latency Versus Throughput

Get three computer experts in a room and ask them for a definition of "latency" and they'll give you six different answers, same thing for "throughput"... the only thing they'll agree on is this: Are latency and throughput important?

Answer: Yes!

OK, let's try that again: What kind of latency is good latency?

Answer: Low latency!

What kind of throughput is good throughput?

Answer: High throughput!

Next question: How do I measure SQL Anywhere latency and throughput?

Answer: You can measure latency and throughput at two levels: at the network level, and closer in at the database server level.

So, for SQL Anywhere, the six different answers can be boiled down to two sets of definitions; first...

Network Latency and Throughput

Here's what Glenn Paulley has to say on the subject in the "Measuring network performance" section of Optimizing Adaptive Server Anywhere Performance Over a WAN:

Latency and throughput can be used together to describe the performance of a network. Latency refers to the time delay between when one machine sends a packet of data and the second machine receives the data (for example, if the second machine receives the data 10 ms later than the first machine sent it, the latency is 10 ms). Throughput refers to the amount of data that can be transferred in a given time (for example, if a one machine sends 1000 KB of data, and it takes 5 seconds for all of it to be received by the second machine, the throughput is 200 KB/s). On a LAN, latency is typically less than 1 ms, and throughput is typically more than 1 MB/s. On a WAN, the latency is typically significantly higher (perhaps 5 ms to 500 ms), and the throughput is typically significantly lower (perhaps 4 KB/s to 200 KB/s).

You can measure network latency between two machines by the round trip time reported by the system’s ping utility. The round trip time is the latency to transfer data from one machine to a second machine plus the latency to transfer data from the second machine back to the first machine. You can measure network throughput by copying a file of a known size of at least 200 KB from one machine to a second machine and timing the copy. This copy could be performed as a regular file copy, using FTP, or by downloading a file using an Internet browser.

To get reasonable Adaptive Server Anywhere performance on a network that has high latency, but reasonable throughput, the number of requests made by the client must be minimized. If a network has reasonable latency, but low throughput, the amount of data transferred between the client and server must be minimized.

Database Server Latency and Throughput

Here's an excerpt from the Foxhound FAQ which talks about latency and throughput from the server's point of view rather than the client or network side:

Latency, also known as response time or access time, is a measure of how long it takes the the database to respond to a single request.

The "Heartbeat / Sample Times" columns are the primary measurements of latency displayed by Foxhound. The Heartbeat is the round-trip elapsed time for a single SELECT dummy_col FROM DUMMY statement issued by Foxhound to the target database; the time is rounded upwards to the nearest tenth of a second so the minimum displayed value is 0.1s.

The Sample time is the round-trip time it takes Foxhound to request and receive three sets of performance statistics from the target database. The workload varies with the number of connections on the target database so the sample time is an indication of how long a non-trivial transaction takes rather than a benchmark measurement.

The Heartbeat time is usually smaller than the Sample time, but it is possible for the Hearbeat time to be larger; here is an example:

Heartbeat,
                                Sample
           Samples  Interval     Times
May 16 11:00:29 PM   50.1s    39.7s / .9s

The heartbeat query and the sample gathering process are performed separately, one after another, and their elapsed times are calculated separately: the Sample time does not include the Heartbeat time. It is possible that the target database did not respond to the heartbeat query for a long time, but then did respond to the subsequent sample queries on a timely basis.

Throughput, also known as bandwidth, is a measure of how many requests the database can respond to per unit of time.

The following Foxhound Monitor columns provide an indication of throughput:

The "Req" column shows the rate at which the server started processing a new request or resumed processing an existing request during the preceding interval. In this context, a request is defined as an atomic unit of work performed for a connection.
The "Commits" column shows the approximate rate at which COMMIT operations were performed in the previous interval. This number is approximate because a connection may issue a commit and disconnect between two Foxhound samples, and that commit won't be counted in this rate. Depending on how the database workload is structured, the commit count may or may not be the same as the transaction count.

The "Bytes In / Out" columns show the rates at which data was received by and sent back by the server from and to client connections in the previous interval.

Low latency is a generally a good thing, but so is high throughput, and the trick is to achieve a balance between the two.

---

The Thursday Quote - Ed Yourdon

---

"...approximately half of the small businesses in the U.S., England, and various other countries (a) have not yet begun any efforts to achieve Y2K compliance, and (b) don't plan to do so until they see what "breaks" after January 1, 2000."

The Y2K End Game by Ed Yourdon, September 7, 1999

---

If that number's right, half the small businesses got it right, and the other half wasted time and money on Y2K compliance.

But Yourdon's estimate was probably low... Pointy-Haired Bosses tend to work for large corporations, and only Pointy-Haired Bosses listen to fear-mongers like Ed Yourdon.



Yes, billions were spent. And yes, there are those who claim Y2K preparations are the reason there weren't any problems... as if spending by some eliminated problems for all (Remember January 1, 2000? nothing happened, anywhere, to anyone, prepared or otherwise.)

It's been 12 years since Ed Yourdon shouted his final warnings and fled into the desert, never to be heard from again.

But it wasn't just Ed Yourdon who shut up, everyone stopped talking about Y2K, especially those who spent the most money. And when no one talks about a mistake, no one learns from it.

And when no one learns from a mistake...

...it will happen again.

...if it isn't happening already.

---

Next week: Clive Thompson

---

Bottleneck: Blocked Connections

Question: Do blocked connections affect performance?

Short Answer: For the user waiting for a response, you betcha!

Long Answer: It's not just those waiting users who are affected, everyone's performance can suffer.

Here's an example of a simple but time-consuming Query From Hell run simultaneously on three SQL Anywhere 12 connections:

SELECT COUNT(*)
  FROM SYSCOLUMN AS A 
       CROSS JOIN SYSCOLUMN AS B
       CROSS JOIN SYSDOMAIN AS C;

Execution time: 36.187 seconds

Execution time: 38.356 seconds

Execution time: 40.052 seconds

Here's what happened when there are 79 other connections running at the same time, each one blocked by a row lock:

SELECT COUNT(*)
  FROM SYSCOLUMN AS A 
       CROSS JOIN SYSCOLUMN AS B
       CROSS JOIN SYSDOMAIN AS C;

Execution time: 110.565 seconds

Execution time: 112.186 seconds

Execution time: 113.905 seconds

Those other 79 connections weren't doing anything at all, but still the Queries From Hell took over three times longer.

But wait, it gets worse...

...a simple SELECT FROM DUMMY on yet another connection took over 2 minutes to execute while all this was going on.

Plus, the 79 blocked connections were only part of the story: the test of blocked connections involved 200 connections in total. Only 79 got as far as being blocked, the remaining 121 never made it to the row lock, they just sat there waiting to run.

Here's what Foxhound showed, followed by an explanation of some of the numbers:

(click image to see full-size)

The sample at 11:00:11 AM shows a "Heartbeat" time of 2m 14.7s: that's how long it took for Foxhound to receive a response to a trivial SELECT FROM DUMMY while all of this was going on. This isn't just a Foxhound effect, separate tests using dbisql confirm that SELECT FROM DUMMY could take a long time.

The subsequent "Sample" time was only 5.1s even though that accounts for much more work than SELECT FROM DUMMY. In effect, Foxhound was completely blocked from proceeding, and it issued an "Alert #1" (database unresponsive) because of it.

The "Max Req" column shows that the AutoMultiProgrammingLevel feature had allowed the multiprogramming level to climb to 80... but no further. 79 of those requests show up in the "Waiting Conns" and "Conns Blocked" columns. The other 121 connections that should have been blocked show up instead in the "Waiting Conns" column... not idle, but waiting.

79 of the 80 requests shown in the "Active Req" column are not, in fact, active but are sitting there doing nothing... not active, not idle, but waiting.

...and that's where the problem arises: it looks like there's only one thread left to do any actual work. Short of STOP ENGINE it's hard to imagine a better example of "bottleneck".

The bottom line is this: You should pay attention to blocked connections, and not just because of the screaming users waiting for their responses.

---

Bottleneck: MultiProgramming Level

Question: Should the multiprogramming level be increased?

With SQL Anywhere 12, the answer is... Ask a different question! The new AutoMultiProgrammingLevel feature lets the database server choose what the multiprogramming level should be.

So, the new question is two-fold:

---

Question: Will a higher multiprogramming level improve performance, and if so, how do I get the server to pick a higher value?"

---

Here's a Foxhound snapshot of a server in difficulty: 35 connections (Conns) are pounding the server with around 3,000 transactions per second (Commits), the CPU Time is hitting 95%, and two-thirds of the connections are waiting for work to be done (Waiting Conns)... yet the multiprogramming level (Max Req) has only grown to 11!

(click image to see full-size)

So the answer is "Yes" to the original question, "Should the multiprogramming level be increased?"

But the answer is "No" to the implied question, "Should I increase the dbsrv12 -gn value?" with the reason being that forcing SQL Anywhere to use a larger value can have unintended consequences... it can lead to worse performance, in this or other scenarios.

Yes, the multiprogramming level should be increased... but SQL Anywhere should do it itself.

In this case, the problem is "not enough CPUs"... if you really want to push thousands of transactions through a database server, you really need more than one CPU.

Here are the same 35 connections pushing the same workload through a server with 8 CPUs:

(click image to see full-size)

With 8 CPUs, the multiprogramming level (Max Req) has only grown by 3 to 14 but the effect on performance is huge: Now the workload pushes the CPU Time from 95% of one CPU to 60% of eight, and the overall throughput as measured from the client side has grown from 2,759 transactions per second to 11,722... which is a 300% improvement.

---

The fRiDaY File - Business Intelligence Explained

This is how BI works, right? Data goes in...

---

Latest SQL Anywhere EBF: 11.0.1.2701

The three asterisks "***" show what's new since the previous list. Only EBFs for the three fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.

Current builds for the most active platforms...

HP-UX Itanium    12.0.1.3469 EBF       14 Oct 2011
                 11.0.1.2664 EBF       24 Aug 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2637 EBF       26 Jul 2011

Linux x86/x64    12.0.1.3457 EBF       12 Oct 2011
                 11.0.1.2700 EBF       27 Oct 2011
                 10.0.1.4239 EBF       17 Jun 2011

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2632 EBF       07 Jul 2011
                 10.0.1.4239 EBF       17 Jun 2011

Windows x86/x64  12.0.1.3484 EBF       31 Oct 2011
                 11.0.1.2701 EBF ***   07 Nov 2011 ***
                 10.0.1.4239 EBF       17 Jun 2011

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Solaris x64      11.0.1.2645 EBF                 05 Aug 2011
                 10.0.1.3870 EBF                 01 Apr 2009

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

---

The Thursday Quote - Moshe Y. Vardi

---

"Looking behind me to see how other attendees were reacting to the highly dense presentation, I was greeted by a wall of laptop screens; people were busily reading their email."

Are You Talking to Me? by Moshe Y. Vardi, from Communications of the ACM September 2011

---

It is a brave audience member who can write "I was hoplessly lost" when describing a conference presentation in his own field.

Or maybe not... maybe it's just business as usual:

---

I recently attended a rather theoretical computer-science conference, and sat, as is my habit, in the front row. The speaker was trying to convey the fine details of a rather intricate mathematical construction. I was hopelessly lost. At that point I found the talk indistinguishable from Doug Zongker's celebrated "Chicken Chicken Chicken" talk presented at the 2007 AAAS Humor Session (http://www.youtube.com/watch?v=yL_-1d9OSdk). Looking behind me to see how other attendees were reacting to the highly dense presentation, I was greeted by a wall of laptop screens; people were busily reading their email.

At the business meeting that evening, I asked "How many people could follow 100% of 100% of the talks?" Silence. "80% of 80%?" One brave soul responded positively. It was only when I got to "50% of 50%" that about 50% of the participants raised their hands. Of course, this statistic should not be taken too seriously, but, nevertheless, I found it shocking! About 100 people are spending four days attending talks, and only 50% understand 50% of 50% the talks? What is the point of this futile exercise?

I am reminded of Lance Fortnow's pithy description of a computer-science conference as "a journal that meets at a hotel." Indeed, if the point of the conference is simply to score a prestigious publication, then attending the conference and giving a talk is just a hurdle that one must overcome as a condition of publication. As I pointed out in my May 2011 editorial, "Technology Has Social Consequences," many conferences eliminated face-to-face program-committee meetings in the late 1990s to save travel expenses and hassle. Why don't we take the next logical step and virtualize our conferences in the name of efficiency?

---

To be fair, the next paragraph starts with "I am not serious, of course" and it goes on to mention ways to improve the situation... like considering the quality of presentations when making program decisions.

---

Next week: Ed Yourdon

---

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?

---

OLAP Window for Running Balance

Quick! What does this do? ...

How about this one?

... I thought not, neither do I.

But if you do know what they mean, this might not be the right blog for you :)

What's the point?

The point is, not only is the saying true "You can't judge a book by its cover", sometimes you can't judge an article by its first page.

Or, in the case of this Sybase iAnywhere White Paper, the first 20 pages...

Analytic Functions in SQL Anywhere by G. N. Paulley and B. Lucier

...not if you're looking for information about the new OLAP WINDOW, PARTITION and RANGE keywords and the related aggregate functions like RANK(), ROW_NUMBER() and FIRST_VALUE().

In other words, if you're not looking for a detailed discussion of statistical minutae formulae involving VAR_POP(), STDDEV_SAMP() and the like, don't give up, just skip forward to page 21: that's where the fun begins.

Question: How do I compute a running balance?

It's curious that among all the excellent examples in Analytic Functions in SQL Anywhere so few (i.e., none) have exactly matched my requirements over the years... but that's OK; sometimes students must do their own homework :)

In Example 11 on page 22, however, the article does come close; "cumulative shipped quantity" is pretty much the same as "running balance", isn't it? We shall see...

---

Example 11 (Queries with window functions)

Consider the following query which lists all products shipped in July and August 2001 and the cumulative shipped quantity by shipping date:

Select p.id, p.description, s.quantity, s.ship date,
       Sum(s.quantity) Over (Partition by s.prod id
                             Order by s.ship date
                             Rows Between Unbounded Preceding
                                          and Current Row) as cumulative qty
From sales order items s Join product p On (s.prod id = p.id)
Where s.ship date Between ‘2001-07-01’ and ‘2001-08-31’
Order by p.id

---

Here are the tables for the running balance computation:

CREATE TABLE opening_balance (
   account_number      VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
   amount              DECIMAL ( 11, 2 ) NOT NULL );

CREATE TABLE transaction (
   account_number      VARCHAR ( 10 ) NOT NULL REFERENCES opening_balance,
   transaction_number  BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   transaction_date    DATE NOT NULL DEFAULT CURRENT DATE,
   transaction_type    VARCHAR ( 10 ) NOT NULL CHECK ( transaction_type IN ( 'Debit', 'Credit' ) ),
   amount              DECIMAL ( 11, 2 ) NOT NULL );

INSERT opening_balance VALUES 
   ( '200', 100.00 ),
   ( '300', 658.39 );

INSERT transaction VALUES 
   ( '200', DEFAULT, '2011-10-20', 'Debit',    20.00 ),
   ( '300', DEFAULT, '2011-10-21', 'Debit',   927.47 ),
   ( '200', DEFAULT, '2011-10-23', 'Credit',  200.00 ),
   ( '300', DEFAULT, '2011-10-25', 'Credit', 1242.67 ),
   ( '200', DEFAULT, '2011-10-29', 'Credit',  400.00 ),
   ( '300', DEFAULT, '2011-10-30', 'Credit',   78.05 ),
   ( '200', DEFAULT, '2011-11-01', 'Debit',   100.00 ),
   ( '300', DEFAULT, '2011-11-02', 'Debit',    63.89 );

COMMIT;

Here's a solution that uses SUM() OVER an OLAP WINDOW:

SELECT transaction.account_number,
       transaction.transaction_number,
       transaction.transaction_date,
       transaction.transaction_type,
       transaction.amount,
       opening_balance.amount
          + SUM ( CASE transaction_type 
                     WHEN 'Credit' THEN transaction.amount
                     WHEN 'Debit'  THEN -transaction.amount
                     ELSE transaction.amount
                  END CASE
           ) OVER running AS running_balance
  FROM transaction
          INNER JOIN opening_balance
          ON opening_balance.account_number = transaction.account_number
WINDOW running AS ( 
          PARTITION BY transaction.account_number
          ORDER BY transaction.transaction_number
          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
 ORDER BY transaction.account_number,
       transaction.transaction_number;

The WINDOW clause on lines 16 through 19 divides all the rows into partitions by account_number, and then defines the partition in terms of the current row: for each row in the result set, the partition is defined as that row plus all the rows that precede it and have the same account number. In other words, the window is a "running partition" that grows bigger with each row.

The SUM() OVER expression on lines 7 through 12 adds up (and in the case of debits, subtracts) all the transaction.amount values in the partition. In other words, it computes a "running total" of all the transactions.

The reference to opening_balance.amount on line 6 takes care of the fact that a "running balance" is required, which is subtly different from a "running total"... sales quotas might reset at the beginning of each reporting cycle, but banks have longer memories :)

Here's the output:

account_number transaction_number transaction_date transaction_type amount running_balance

'200'          1                  '2011-10-20'     'Debit'           20.00    80.00
'200'          3                  '2011-10-23'     'Credit'         200.00   280.00
'200'          5                  '2011-10-29'     'Credit'         400.00   680.00
'200'          7                  '2011-11-01'     'Debit'          100.00   580.00

'300'          2                  '2011-10-21'     'Debit'          927.47  -269.08
'300'          4                  '2011-10-25'     'Credit'        1242.67   973.59
'300'          6                  '2011-10-30'     'Credit'          78.05  1051.64
'300'          8                  '2011-11-02'     'Debit'           63.89   987.75

---

Did you notice?

• The multi-row VALUES lists?

  INSERT opening_balance VALUES 
     ( '200', 100.00 ),
     ( '300', 658.39 );
  

• The END CASE closing the CASE expression?

  + SUM ( CASE transaction_type 
             WHEN 'Credit' THEN transaction.amount
             WHEN 'Debit'  THEN -transaction.amount
             ELSE transaction.amount
          END CASE
  

Both are relatively new to SQL Anywhere, both are small improvements that nevertheless make life easier.

---

---

The fRiDaY File - Chicken Chicken Chicken: Chicken Chicken

Tell me you haven't sat through presentations just like this one... I know you have... so has the audience in this video, and so have the over one million viewers on Youtube:



The full text is available here.

---

OLAP Again

The following question

"How do I select the first row in each partition of a table?"

was discussed in these two articles

Unrequited OLAP
OLAP Counseling

and the conclusion seemed to be

"The new FIRST_VALUE() aggregate function together with the OLAP WINDOW clause might seem to be ideal, but it's not. The RANK() function can be made to work with the WINDOW clause, but the Old-School solution using MIN() and GROUP BY is actually simpler... and easier to understand for some (ok, me)."

You can see a side-by-side comparison here. Scroll down to the bottom of that article and you will see this parting shot

"...just because the Old School solution worked it doesn't mean we can give up on learning about OLAP and retreat to the familiar."

Onwards and upwards...

Here's a newer, slightly harder question:

"How do I select the top 10 rows in each partition of a table?"

Let's start with a sample table containing 300 rows in three partitions:

CREATE TABLE t ( 
   partition_id     INTEGER NOT NULL,
   entry_id         INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   data             VARCHAR ( 10 ) NOT NULL,
   PRIMARY KEY ( entry_id ) );

BEGIN
   DECLARE @loop_counter INTEGER;
   SET @loop_counter = 1;
   WHILE @loop_counter <= 100 LOOP
      INSERT t ( partition_id, data ) VALUES ( 10, 'aaa' );
      INSERT t ( partition_id, data ) VALUES ( 20, 'bbb' );
      INSERT t ( partition_id, data ) VALUES ( 30, 'ccc' );
      SET @loop_counter = @loop_counter + 1;
   END LOOP;
   COMMIT;
END;

SELECT * FROM t ORDER BY entry_id;

partition_id,entry_id,data
10,1,'aaa'
20,2,'bbb'
30,3,'ccc'
10,4,'aaa'
20,5,'bbb'
30,6,'ccc'
10,7,'aaa'
20,8,'bbb'
30,9,'ccc'
10,10,'aaa'
20,11,'bbb'
30,12,'ccc'
10,13,'aaa'
20,14,'bbb'
30,15,'ccc'
10,16,'aaa'
20,17,'bbb'
30,18,'ccc'
10,19,'aaa'
20,20,'bbb'

...

20,290,'bbb'
30,291,'ccc'
10,292,'aaa'
20,293,'bbb'
30,294,'ccc'
10,295,'aaa'
20,296,'bbb'
30,297,'ccc'
10,298,'aaa'
20,299,'bbb'
30,300,'ccc'

Here's what the output is supposed to look like:

SELECT [the top 10 rows in each partition of t];

partition_id,entry_id,data
10,1,'aaa'
10,4,'aaa'
10,7,'aaa'
10,10,'aaa'
10,13,'aaa'
10,16,'aaa'
10,19,'aaa'
10,22,'aaa'
10,25,'aaa'
10,28,'aaa'
20,2,'bbb'
20,5,'bbb'
20,8,'bbb'
20,11,'bbb'
20,14,'bbb'
20,17,'bbb'
20,20,'bbb'
20,23,'bbb'
20,26,'bbb'
20,29,'bbb'
30,3,'ccc'
30,6,'ccc'
30,9,'ccc'
30,12,'ccc'
30,15,'ccc'
30,18,'ccc'
30,21,'ccc'
30,24,'ccc'
30,27,'ccc'
30,30,'ccc'

Presumably, the Old-School solution would use TOP 10, but how? It turns out that the OLAP RANK() solution used previously to solve the "first" problem works just as well on the "top 10":

SELECT ranked.partition_id,
       ranked.entry_id,
       ranked.data
  FROM ( SELECT *,
                RANK() OVER partition_window AS entry_rank
           FROM t
         WINDOW partition_window AS (
                   PARTITION BY t.partition_id
                   ORDER BY t.entry_id ) 
       ) AS ranked
 WHERE ranked.entry_rank <= 10
 ORDER BY ranked.partition_id,
       ranked.entry_id;

Logically speaking, the WINDOW clause on lines 7 through 9 subdivides all the rows of t into three partitions (partition_id = 10, 20, 30) then sorts those rows on entry_id within each partition: entry_id = 1, 4, 7... for partition_id = 10, entry_id = 2, 5, 8... for partition_id = 20, and so on.

So far, so good... but we still can't use TOP 10 because that doesn't work on windows or partitions, just the whole result set, and the result set returned by the SELECT on lines 4 through 10 contains all 300 rows...

...and besides, it doesn't have an ORDER BY, and you have to have an ORDER BY to use TOP 10. Sure, the WINDOW clause has an ORDER BY, but TOP doesn't do windows.

The solution lies with the RANK() function call on line 5: it adds a new column called "entry_rank" to the result set, and that column contains the value 1, 2, 3... for the rows in each partition; unlike TOP, RANK() does work with windows and partitions.

The WHERE clause on line 11 does what TOP couldn't do: selects only the top 10 rows in each partition, ordered by entry_id.

The ORDER BY clause on lines 12 and 13 re-sorts the whole thing in the final order for presentation... you pretty much always need an outer ORDER BY because there is no guarantee that the ordering imposed by inner ORDER BY clauses will be preserved, especially not an ORDER BY buried two levels down in a WINDOW clause.

---

For a real-world example, have a look at this Foxhound FAQ: How do I run adhoc queries on the Foxhound database?

---

The Thursday Quote - Phillip G. Armour

---

"While the business of software has evolved from actually punishing people for finding defects, we can still be pretty ambivalent about them."

Testing: Failing to Succeed by Phillip G. Armour, from Communications of the ACM October 2011

---

So, explain it to me again: Why can't we punish people for finding bugs?

...oh, yeah, ethics, morality, legality, economics, common sense, all that stuff. But you have to admit, it's a common desire to hear only what we want to hear.

Anyway, here's the wider context for that quote, an excerpt from Philip Armour's recent article on software testing:

---

There are two situations in software testing that scare testers: when they see "too many" defects and when they do not see "enough." Too many defects may indicate the system contains a lot of problems even though it has moved into the testing phase. Too few discovered defects might imply the product is of high quality but it usually means the testing process itself is not working.

This is symptomatic of the paradox of testing: We want to find defects in the system under test, but we do not really want to find them. While the business of software has evolved from actually punishing people for finding defects, we can still be pretty ambivalent about them. Experienced testers have a good feel for the balance between finding too many defects and not finding enough2 but most organizations and most customers would still prefer to hear "we haven't found many defects" than "we've found huge numbers of defects."

If we view testing as a knowledge acquisition activity rather than simply a post hoc quality assurance process we get a different view. We also get a different view when we line testing up against the Five Orders of Ignorance. Zero Order Ignorance (0OI) is lack of ignorance; it is proven knowledge, knowledge that we have acquired and against which some trial has made that certifies it as "correct." Second Order Ignorance (2OI) is lack of awareness of ignorance; it occurs when we don't know something and we are unaware that we don't know it. That is, we have ignorance about our lack of knowledge. We test systems primarily for these two of the Five Orders of Ignorance and their focus is quite different.

---

He wrote about the The Five Orders of Ignorance back in October 2000; here's a summary:

• 0th Order Ignorance (0OI) — Lack of Ignorance: "I know this."
  

• 1st Order Ignorance (1OI) — Lack of Knowledge: "I know that I don't know something."
  

• 2nd Order Ignorance (2OI) — Lack of Awareness: "I don't know what it is that I don't know.
  

• 3rd Order Ignorance (3OI) — Lack of Process: "I don't know how to figure out what it is that I don't know."
  

• 4th Order Ignorance (4OI) — Meta Ignorance: "I don't know about the five orders of ignorance."
  

At first reading the five orders seem deep and significant... but are they useful? After all, it's impossible to hang on to 4th Order Ignorance, it's gone by the time you read about it!

---

Next week: Moshe Y. Vardi

---

Latest SQL Anywhere EBFs, Halloween Edition

The three asterisks "***" show what's new since the previous list. Only EBFs for the three fully-supported versions of SQL Anywhere are shown here: 10.0.1, 11.0.1 and 12.0.1.

Current builds for the active platforms...

HP-UX Itanium    12.0.1.3469 EBF       14 Oct 2011
                 11.0.1.2664 EBF       24 Aug 2011
                 10.0.1.3777 EBF       14 Oct 2008

IBM AIX          12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2637 EBF       26 Jul 2011

Linux x86/x64    12.0.1.3457 EBF       12 Oct 2011
                 11.0.1.2700 EBF ***   27 Oct 2011 ***
                 10.0.1.4239 EBF       17 Jun 2011

Mac OS           12.0.1.3352 EBF       26 May 2011
                 11.0.1.2449 EBF       29 Jun 2010
                 10.0.1.4042 EBF       01 Oct 2010

Solaris SPARC    12.0.1.3469 EBF       20 Oct 2011
                 11.0.1.2632 EBF       07 Jul 2011
                 10.0.1.4239 EBF       17 Jun 2011

Windows x86/x64  12.0.1.3484 EBF ***   31 Oct 2011 ***
                 11.0.1.2686 EBF       13 Oct 2011
                 10.0.1.4239 EBF       17 Jun 2011

[Download EBFs here] [Register for the Fuji Beta here]

...and for the other platforms:

HP-UX PA-RISC    10.0.1.3778 EBF                 16 Oct 2008

Linux Itanium    10.0.1.3777 EBF                 14 Oct 2008

NetWare          10.0.1.4036 EBF                 10 Mar 2010

Solaris x64      11.0.1.2645 EBF                 05 Aug 2011
                 10.0.1.3870 EBF                 01 Apr 2009

Windows CE       10.0.1.4036 EBF                 09 Mar 2010

Windows Itanium  10.0.1 GA Upgrade from 10.0.0   26 Apr 2007

[Download EBFs here]

---