Question Re: Visualize in real time CPU and RAM consumption of sql connections in Technology Q&A

Visualize in real time CPU and RAM consumption of sql connections

Former Member — Thu, 20 Nov 2014 14:54:11 GMT

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)?

Re: Visualize in real time CPU and RAM consumption of sql connections

MCMartin — Fri, 21 Nov 2014 08:34:09 GMT

I don't think it is possible, but you could use the request level logging to identify any long running statement instead.

Re: Visualize in real time CPU and RAM consumption of sql connections

Breck_Carter — Fri, 21 Nov 2014 09:46:51 GMT

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.

Re: Visualize in real time CPU and RAM consumption of sql connections

Former Member — Sat, 22 Nov 2014 11:34:15 GMT

This solutions apply also to SQL Anywhere 9?

Re: Visualize in real time CPU and RAM consumption of sql connections

Former Member — Tue, 25 Nov 2014 09:28:34 GMT

The connection_property('QueryHeapPages') can be used to find out how much memory a connection is using for query processing (sorts, hash join, hash group by, hash distinct). This is available in 9.0 as well.

Connections can use memory for other purposes as well (for example, to store the data structures to execute query plans or store the current values during execution) but usually these are relatively small unless the query is very complex.