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

BlueMark's gravatar image

BlueMark
24671423
accept rate: 50%


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.

alt text

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

alt text

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":

alt text

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

alt text

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

permanent link

answered 21 Nov '14, 08:46

Breck%20Carter's gravatar image

Breck Carter
27.1k424582831
accept rate: 21%

edited 21 Nov '14, 09:00

This solutions apply also to SQL Anywhere 9?

(22 Nov '14, 11:34) BlueMark
Replies hidden
1

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.

(25 Nov '14, 09:28) Ivan T. Bowman

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

permanent link

answered 21 Nov '14, 08:34

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

edited 21 Nov '14, 08:35

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×406
×241
×124
×97
×30

question asked: 20 Nov '14, 14:54

question was seen: 1,513 times

last updated: 25 Nov '14, 09:28