Visualizing Connection-Level Resource Consumption

Question: How do I determine which connections use the most resources? Sybase Central 12 doesn't show CPU and RAM consumption.

Answer: Sybase Central and other tools all get their connection-level performance statistics from the builtin SQL Anywhere connection properties, and closest thing to a connection property for "RAM consumption" are TempFilePages and TempTablePages, with TempFilePages being the more useful of the two.

TempFilePages Returns the number of temporary file pages used by the connection.

TempTablePages Returns the number of pages in the temporary file used for temporary tables.

The closest connection property to "CPU consumption" is ApproximateCPUTime, and there are many other properties like RollbackLogPages that may also be of interest to you:

ApproximateCPUTime Returns an estimate of the amount of CPU time accumulated by a given connection, in seconds. The value returned may differ from the actual value by as much as 50%, although typical variations are in the 5-10% range. On multi-processor computers, each CPU (or hyperthread or core) accumulates time, so the sum of accumulated times for all connections may be greater than the elapsed time. This property is supported on Windows and Linux.

RollbackLogPages Returns the number of pages in the rollback log.

Unfortunately, Connections pane in Sybase Central 12 doesn't show any of those connection properties and there's no way to tell it to.



You can, however, tell the SQL Anywhere 12 DBConsole utility to display all those connection properties:



Here's how to launch DBConsole and connect to a running SQL Anywhere 12 database:

"%SQLANY12%\bin32\dbconsole.exe"^
  -c "ENG=inventory12_envy;DBN=inventory12;UID=dba;PWD=sql;"

Unfortunately, unlike Sybase Central, DBConsole doesn't show the 8 internal connections that were spawned when the "k.delacruz" connection used intra-query parallelism to execute a runaway query. It also doesn't show any CPU time at all being consumed by the "k.delacruz" connection, which is true but profoundly misleading: the "k.delacruz" connection may not be using any CPU time but its eight minions are using up everything that eight CPUs have to offer.

The Foxhound 3 Database Monitor shows it all: all the internal connections, all the CPU time they are using, plus the total CPU time is also shown against "k.delacruz":



Foxhound also saves all the data it captures so you can look back in time to see what an individual connection was doing...



You can read more about Foxhound in the white paper here.

---