I want to keep track of which connection use most of resources.
Within Sybase Central (SQL Anywhere 9, 12), for each server and database, we have pane to check in real time All Connected Users and Connections.
How to check for each connect it's cpu and ram consumption (in real time)?
asked 20 Nov '14, 14:54
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.
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.
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.