The Connection History page shows the connection-level performance
statistics for a single connection, as well the server and
database-level statistics corresponding to the top-most row
in the connection section.
The Connection History page is similar to the Connection section of
the Database Monitor and Sample History pages, except that multiple
samples for a single connection are shown rather than multiple
connections for a single sample.
In other words, you can use the Connection History page to see the history
of a single connection at a glance, rather than having to jump from page
to page (sample to sample) on the History page and then scroll to the
connection you're interested in.
The Connection History Page is displayed for target databases running on
SQL Anywhere 9 and later.
« Back to Menu
xxx (DSN)
New MenuSchemaMonitorHistoryFoxhound OptionsMonitor OptionsAbout -
Mmm Dd yyyy...
The « Back to Menu link
displays the main Foxhound menu
page in the current browser window or tab.
The DSN: / Connection String: title
shows the name of the DSN or Connection
String that you used to connect to the target database.
This identifies the "current target database", a term used in this Help.
Multiple Connection History pages that display the same DSN or Connection String show the same
data; they do not represent separate sampling sessions.
The New Menu link
opens the main Foxhound menu page in a new browser window or tab.
The Schema link
opens the Display Schema page for this target database in a new browser window or tab.
The Monitor link
opens the Monitor Database page for this sampling session in a new browser window or tab.
The History link
opens the Sample History page for this sampling session in a new browser window or tab.
The Foxhound Options link
opens the Foxhound Options page in a new browser window or tab.
The Monitor Options link
opens the Monitor Options page in a new browser window or tab.
The
button is a context-sensitive link to this Help topic.
This button appears in many locations, each of them a link to a different Help topic in this frame.
Tip:
To hide the Help for every new page, see the Show Help section on the Foxhound Options page.
The About link
opens the About Foxhound page in a new browser window or tab.
The Mmm Dd yyyy... timestamp shows when this Connection History page was displayed.
Newest - Scroll to the most recent samples for this connection
Tip: Click on Newest to scroll to the "new top". If sampling is running,
new samples will continue to be gathered, and the Connection History page won't automatically
show them unless you scroll up.
500 - Scroll up by 500 to more recent samples for this connection
100 - Scroll up by 100 to more recent samples for this connection
20 - Scroll up by 20 to more recent samples for this connection
1 sample - Scroll up by 1 to more recent samples for this connection
Message - Scroll up to the next most recent sample that has an Alert, AutoDrop or other message
Month - Scroll up by one month to more recent samples for this connection
Week - Scroll up by one week to more recent samples for this connection
Day - Scroll up by one day to more recent samples for this connection
3 Hours - Scroll up by three hours to more recent samples for this connection
1 Hour - Scroll up by one hour to more recent samples for this connection
Message 1 sample 20 100 500 Oldest
1 Hour 3 Hours Day Week Month Go to:
Message - Scroll down to the next older sample that has an Alert, AutoDrop or other message
1 sample - Scroll down by 1 to older samples for this connection
20 - Scroll down by 20 to older samples for this connection
100 - Scroll down by 100 to older samples for this connection
500 - Scroll down by 500 to older samples for this connection
Oldest - Scroll down to the oldest sample for this connection
1 Hour - Scroll down by one hour to older samples for this connection
3 Hours - Scroll down by three hours to older samples for this connection
Day - Scroll down by one day to older samples for this connection
Week - Scroll down by one week to older samples for this connection
Month - Scroll down by one month to older samples for this connection
The Go to: field lets you scroll to a specific sample date/time or sample number.
You don't have to be precise, the Connection History page will scroll to the point nearest the value you typed.
The Go to: value is checked to see if it's a valid number first, then a date/time,
so "Go to: 20120601" interpreted as a sample number rather than a sample date/time.
Go to: 1 works like Oldest.
Go to: 0 works like Oldest.
Go to: -1 is ignored, as are all values other than non-negative integers and valid date/times.
The Top Sample section shows the database sample data
corresponding to the top-most Connection History sample.
The Top Sample section consists of 4 or 5 lines:
The...
first three lines...
...are column titles.
The 4th line shows the sample data, and
a 5th line appears to show the age of the Top Sample if it is 15 seconds old or more.
Performance Tip:
If the sample age grows very long without a new sample being displayed,
and you are running an application that
is sending a heavy workload to a target server on the network,
and both Foxhound and the application are running on the
same workstation,
try running Foxhound and the test application
on different workstations. In this scenario, Foxhound's
connection to the target server may not be able to gather
connection-level information on a timely basis. In some cases,
it may not display the next sample until after the heavy
workload is completely finished, and some of the rate
calculations may be incorrect; e.g., commits per second, etc.
The other columns are highlighted according to the values in the Peaks section
(shown on the Sample History page), as follows:
How Highlighting Works
A sample value is highlighted with this color when it is
80% as large as the corresponding peak value or larger, and
this color is used for sample values that are
50% as large as the corresponding peak value or larger,
but haven't reached 80% .
For Cache Satisfaction and Index Satisfaction the thresholds are inverted:
20% or smaller and 50% or smaller .
Some values are not highlighted at all, even if they exceed the 80%/50% thresholds:
Parent Conns = 1
Child Conns = 1
Executing Conns = 1
Idle Conns = 1
Active Req = 1
Max Req = 21 or smaller
Number of processors
DB File Fragments = 1
Other values are always highlighted if they are not zero, regardless of the peak value:
Conns Blocked >= 1
Dashes "-" are displayed for omitted column values instead of empty spaces. This makes it
clear which values are not available or not applicable, and it also makes the resulting text somewhat easier
to read when you use copy-and-paste. For example, the "CPU" percentage appears as a dash "-" for the first
sample because it can only be calculated for second and later samples.
Note: This use of dashes "-" does not apply to columns which have been entirely omitted because
the data is not available for the version of SQL Anywhere being used for the target database;
e.g., the "CPU" column does not appear at all for version 5 and 6 target databases.
The Interval column
shows the actual time interval between the previous sample and this
one.
Foxhound tries to record a new sample every 10 seconds
but the actual interval can vary.
Foxhound uses the interval time to convert performance statistics from
cumulative counts to rates; e.g., to convert the total number of disk writes
into the number of disk writes per second.
A very long interval like 1h 19.8m may indicate
the computer was in sleep, standby or hibernate mode.
Response time, also known as latency or access time, is
a measure of how long it takes the database to respond
to a single request:
The Response... Heartbeat time column shows how long it took for
Foxhound to execute the SELECT * FROM DUMMY heartbeat
or "canarian" query.
Performance Tip:
Heartbeat time should be very small. Repeated values larger than 1s may indicate a performance bottleneck,
and sudden increases in value may indicate a period of non-responsiveness.
Note that there's a difference between precision and accuracy; for example, two successive Heartbeat measurements
like 25ms and 50ms may be very precise but they may not accurately represent an immediate 100% slowdown in performance.
On the other hand, a long period of Heartbeat values averaging 25ms, followed by a long period of 50ms numbers,
may indeed accurately reflect a significant change in performance.
Heartbeat time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
The Response... Sample time column shows how long it took for
Foxhound to gather all the performance data for this sample.
Sample time should always be longer than Heartbeat
time, and it will grow longer with an increasing number of
connections because of the extra work involved to analyze
each sample.
The Sample time is calculated after Heartbeat time and the two values do not overlap. It is possible
for the Heartbeat time to be much longer than the Sample Time (e.g., 10m versus 100ms) when the target
database becomes unresponsive but eventually responds; the long Heartbeat time reflects the unresponsiveness
whereas the subsequent short Sample Time measures the return to normal.
Performance Tip:
A long Sample Time may indicate a performance problem with Foxhound rather than the target database; e.g.:
Sample Time is over 1 second but everything else is running OK on the target database.
Sample Time is large but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time increases rapidly with the number of connections to the target database.
Sample Time increases with the number of connections but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time is over 10 seconds causing the Interval time to also exceed 10 seconds.
To see where the performance problem lies, repeatedly run the following ISQL script against
your target database; if this script runs quickly but Foxhound reports a large Sample Time, the problem lies with Foxhound:
SELECT * INTO #temp1 FROM rroad_engine_properties();
SELECT * INTO #temp2 FROM rroad_database_properties(1);
SELECT * INTO #temp3 FROM rroad_connection_properties();
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;
Sample time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
The Response... Ping time column shows how long it took for Foxhound to connect and
disconnect from the target database.
Performance Tip:
Long Ping times may indicate that a performance bottleneck is preventing
the target database from accepting new connections on a timely basis.
Ping times for local "shared memory" connections may be similar to or even smaller than Sample times
for the same database, but they will usually be larger than Heartbeat times.
Ping times for network connections may be much longer than Ping times for local connections.
Foxhound uses a custom "ping" process to test separate connections to the target database.
Each time the ping process runs it opens a new connection to the target database via the embedded SQL interface,
issues a SELECT @@SPID command and then immediately disconnects.
This is different from the Foxhound Monitor process which connects to the target database via ODBC
and keeps that connection open while it collects multiple samples.
Note that the Foxhound ping process does not use the dbping.exe utility that ships with SQL Anywhere,
nor does it use the ODBC interface that is used to gather sample data.
Also note that the separate ping process "uses up" another SQL Anywhere connection number each time it runs,
which is once approximately every 10 seconds for each target database.
The new ping process can be used in three ways:
As an addition to the Foxhound Monitor sampling process, the ping process tests the target database's
ability to accept new connections as well as providing data for a third measure of response time: ping response time.
As an alternative to Foxhound's sampling process, ping-only sampling may be used to check for
Alert #1 Database unavailable without storing a lot of data in the Foxhound database.
Throughput, also known as bandwidth, is a measure of how much work the database has performed:
Throughput... Req is the rate at which the server has started processing a new request
or resumed processing an existing request during the preceding interval.
Performance Tip:
Large Throughput... Req values may indicate that heavy client
load on the database is a performance bottleneck.
Small values may indicate that some other performance
bottleneck is preventing the database from processing
client requests.
Note that "request" is an atomic internal unit of work processed by the server for a connection,
not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived
column for adhoc queries of connection-level data.
Throughput... Req is based on the server-level Req property
(note that this is different from the connection-level Throughput... Req column which
is based on the connection-level ReqCountActive property)
Throughput... Commits is the approximate rate at which commit requests have been
executed by all connections in the previous interval.
Performance Tip:
Large Throughput... Commits values may indicate that heavy client
load on the database is a performance bottleneck.
Small values may indicate that some other performance
bottleneck is preventing the database from processing
client requests.
This number is approximate because a connection may issue a commit and disconnect between two Foxhound samples,
and that commit won't be included in this total.
The total since the server started is reset to zero and accumulation starts over again when sampling is stopped and restarted.
"Commits" is roughly the same as "transaction count" unless client connections issue a lot of redundant commits.
Throughput... Commits is based on the sum of the connection-level Commit property.
Foxhound does gather the database-level Commit property for target databases running on SQL Anywhere 12 and later,
and the value is stored in the sample_detail."Commit" column for adhoc queries,
but it is not the value displayed by Foxhound on the Monitor and other pages.
Historically speaking, the value that Foxhound does display (the sum of the connection-level Commit property)
is known to be more reliable than the database-level Commit property. However, it is possible that Foxhound
may not count commits performed by short-lived connections that aren't captured at all.
This and other values displayed by Foxhound are intended for performance analysis purposes, not accounting or
other purposes that require absolute accuracy.
Throughput... Bytes is the rate at which data has been received by and sent by the server
across the client server interface in the previous interval.
Performance Tip:
Large Throughput... Bytes values may indicate that heavy client
load on the database is a performance bottleneck.
Small values may indicate that some other performance
bottleneck is preventing the database from processing
client requests.
Throughput... Bytes is displayed for target databases running on SQL Anywhere versions 7 and later.
It is based on the server-level BytesReceived and BytesSent properties for target
databases running on SQL Anywhere versions 7 through 12,
and on the database-level BytesReceived and BytesSent properties for versions 16 and later.
The Conns column shows how many connections existed
on a target database running on SQL Anywhere 11 or earlier,
with Foxhound itself counting as 1 connection.
- or -
The Parent, Child Conns columns show how many primary
(parent) and internal (child) connections existed on a target
databases running on SQL Anywhere 12 or later, with Foxhound
itself counting as 1 parent connection.
Performance Tip:
A large Conns or Parent Conns value may indicate that heavy client
load on the database is a performance bottleneck.
Performance Tip:
A large Child Conns value (up to the number of processors available)
may indicate that heavy use of intra-query parallelism is a performance bottleneck.
With intra-query parallelism, multiple processors (child connections) can
do work for one client connection (the parent).
A small number of individual client connections (e.g., one) may be responsible for
heavy CPU usage on multiple child connections, thus preventing other client connections
from getting their work done. If you suspect this is happening, try turning down
the level of intra-query parallelism (for example, SET OPTION PUBLIC.MAX_QUERY_TASKS = '4'
on an eight-processor computer) or turning it off altogether
(SET OPTION PUBLIC.MAX_QUERY_TASKS = '1').
The parent connection count is based on the database-level ConnCount property,
and the child connection count is calculated by Foxhound as the
number of connections with non-zero connection-level ParentConnection
property values.
Performance Tip:
The sum of the Parent and Child Conns columns should match the
total number of connections shown in the connections detail
section at the bottom of the page, but it may differ for the following reasons:
The database-level ConnCount property and connection details are recorded at
slightly different points in time.
SQL Anywhere 17 and later does not count the short-lived connections created by the SQL Anywhere Cockpit in the database-level ConnCount property
but Foxhound does show those connections in the connections detail section.
You can suppress those connections by not running the Cockpit at the same time as Foxhound.
SQL Anywhere 17.0.4 does not count the INT: StmtPerfMngrConn internal connection in the database-level ConnCount property
but Foxhound does show that connection in the connections detail section.
What is INT: StmtPerfMngrConn?
INT: StmtPerfMngrConn is a persistent (permanent) internal connection that may appear in
databases created with SQL Anywhere 17.0.4.2053 or later.
If it shows up in your target database, Foxhound's "Parent, Child Conns" columns may
not add up to the same total as the "Executing, Idle, Waiting Conns" columns.
That's because (as of build 2053) it isn't counted in the SQL Anywhere database-level
ConnCount property but it is counted in the connection-level ReqStatus property.
INT: StmtPerfMngrConn is related to the Statement Performance Summary Feature (Profiler)
enhancements made in SQL Anywhere 17 build 2000.
You can eliminate the INT: StmtPerfMngrConn connection by using the dbsrv17 -k option, but that's a
bad idea if you use Foxhound because it disables many important performance properties...
...so just ignore INT: StmtPerfMngrConn because it doesn't seem to use any
resources during normal (non-profiling) operations.
The Executing, Idle, Waiting Conns columns
show how many connections were executing, idle or waiting,
where "waiting" includes blocked connections.
Performance Tip:
A large Waiting Conns value may indicate a performance bottleneck; have a look at the
connection-level Current Req Status column to find the affected connections.
The database-level Executing, Idle, Waiting Conns columns are closely related to the
connection-level Current Req Status value:
Foxhound Foxhound
Database-Level Connection-Level SQL Anywhere
Executing Idle Waiting Current Req Status SQL Anywhere Description ReqStatus
---------------------- --------------------------- --------------------------------------- -----------------
Yes - - Executing The connection is executing a request. Executing
- Yes Idle The connection is not currently Idle
processing a request.
- - Yes Waiting for thread The connection has work to do and Unscheduled
is waiting for a worker thread.
- - Yes Waiting for I/O The connection is waiting for an I/O. BlockedIO
- - Yes Waiting for shared resource The connection is waiting for access to BlockedContention
shared database server data structures.
- - Yes Blocked by lock The connection is blocked waiting BlockedLock
for a locked row.
- - Yes Blocked by mutex The connection is blocked waiting BlockedLock (1)
for a mutex.
- - Yes Blocked by semaphore The connection is blocked waiting Executing (2)
for a semaphore.
Note 1: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'BlockedLock' for a connection
blocked by a mutex on a target database running on SQL Anywhere 17.
Note 2: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'Executing' for a connection
blocked by a semaphore on a target database running on SQL Anywhere 17.
Here's an analogy that doesn't prove anything but might help understanding:
If Executing is like a car moving down the road, then
Waiting is like a car stuck in traffic,
Blocked is like a car waiting at a red light, and
Idle is like a car parked at the side of the road.
Performance Tip:
The sum of the Executing, Idle, Waiting Conns columns should match the
sum of the Parent and Child Conns columns, but it may differ for the following reasons:
Different properties are recorded at slightly different points in time.
SQL Anywhere 17.0.4 does not count the INT: StmtPerfMngrConn internal connection in the database-level ConnCount property
but it is counted in the connection-level ReqStatus property.
What is INT: StmtPerfMngrConn?
INT: StmtPerfMngrConn is a persistent (permanent) internal connection that may appear in
databases created with SQL Anywhere 17.0.4.2053 or later.
If it shows up in your target database, Foxhound's "Parent, Child Conns" columns may
not add up to the same total as the "Executing, Idle, Waiting Conns" columns.
That's because (as of build 2053) it isn't counted in the SQL Anywhere database-level
ConnCount property but it is counted in the connection-level ReqStatus property.
INT: StmtPerfMngrConn is related to the Statement Performance Summary Feature (Profiler)
enhancements made in SQL Anywhere 17 build 2000.
You can eliminate the INT: StmtPerfMngrConn connection by using the dbsrv17 -k option, but that's a
bad idea if you use Foxhound because it disables many important performance properties...
...so just ignore INT: StmtPerfMngrConn because it doesn't seem to use any
resources during normal (non-profiling) operations.
The Executing, Idle, Waiting Conns columns are displayed for target databases running on
SQL Anywhere 9 and later.
For target databases running on SQL Anywhere versions 9 through 16, the Executing, Idle, Waiting Conns
columns are entirely based on the connection-level ReqStatus property.
For target databases running on SQL Anywhere 17, the Executing, Idle, Waiting Conns columns are based
on the connection-level ReqStatus property for connections that are not blocked by a mutex or semaphore.
For connections blocked by a mutex, the Waiting Conns column is based on the sa_locks() procedure.
For connections blocked by a semaphore, the Waiting Conns column is based on the connection-level
BlockedOn, LockObjectType and LockObjectOID properties and the SYSMUTEXSEMAPHORE table.
Active Req is the number of server workers that are currently
handling client-side requests.
Performance Tip:
Large Active Req values may indicate that heavy client
load on the database is a performance bottleneck.
Performance Tip:
Small Active Req values may indicate that some other performance
bottleneck is preventing the database from processing
client requests.
Note that "request" is an atomic internal unit of work processed by the server for a connection,
not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived
column for adhoc queries of connection-level data.
Active Req is based on the server-level ActiveReq property.
Max Req is the server multiprogramming level, or the maximum number
of requests that can be processed at one time (the -gn option).
Performance Tip:
A Max Req value that is too small may cause the Unsch Req value to increase, in turn
causing a bottleneck that increases response time and/or decreases throughput.
Performance Tip:
A Max Req value that varies up and down may indicate the server is unable to
correctly determine the best value.
If the computer is dedicated to running this SQL Anywhere server and no other process of importance,
consider disabling the dynamic tuning of the multiprogramming level by specifying the -gna 0 and
-gn options to set a fixed value. With a dedicated computer there may be no need for SQL Anywhere
to perform extra work changing the multiprogramming level up and down.
Max Req is displayed for target databases running on SQL Anywhere 9 and later,
and is based on the server-level Threads property for SQL Anywhere 9
and on the server-level MultiProgrammingLevel property for SQL Anywhere 10 and later.
Unsch Req (formerly called Waiting Req) shows the number
of requests that were waiting to be processed.
Performance Tip:
Large Unsch Req values may indicate that some performance bottleneck is
preventing the database from processing its workload.
The bottleneck may simply be a Max Req value that is too low (solution:
increase the server -gn option), but the problem may be harder than that;
for example, if the application design results in excessive blocking and/or
too many long-running requests.
Locks Held is the total number of locks held by all connections.
Performance Tip:
Large numbers of Locks Held are not a problem in themselves; SQL Anywhere is
perfectly capable of handling millions of locks being held at one time.
When locks become a problem is when they are held too long and cause other
connections to be blocked (prevented) from doing work.
For example, when Locks Held grows large during a period of high activity (high
CPU, disk), and then *remains* high for a period of low activity,
that may indicate an application flaw where a COMMIT is not issued
as soon as it should be.
The Locks Held value does not include schema locks. For example, if a SELECT statement
holds a schema lock that is blocking another connection from executing an ALTER TABLE
statement, Locks Held may be empty even though Conns Blocked is 1.
Locks Held is displayed for target databases running on SQL Anywhere 10 and later,
and is based on the database-level LockCount property.
Conns Blocked is the number of connections that are blocked.
Performance Tip:
Large numbers of blocked connections may indicate a serious performance bottleneck
caused by locks being held too long.
The cause may be an application that breaks this fundamental rule of transaction
design:
"Never yield control of the mouse or keyboard to the end user while
database locks are being held; always do a COMMIT first."
When that rule is broken a long-running transaction is often the result, with those
locks preventing other users from getting their work done while the first user
decides what to do (or takes a lunch break).
Conns Blocked is based on the connection-level BlockedOn property for
target databases running on SQL Anywhere versions 5.5 through 16,
and on the connection-level BlockedOn, LockObjectType and LockObjectOID properties for version 17.
Note that Foxhound calculates the database-level Conns Blocked value by summarizing
data provided by SQL Anywhere at the connection level.
Waiting Time is the total time all current connections
were blocked or forced to wait during the previous interval.
Performance Tip:
Long waiting times may indicate a serious performance bottleneck caused
a lack of resources.
The Waiting Time value is not cumulative. A obscenely large value (such as a Waiting Time of 4h in an Interval of only 13s)
is possible when, for example, 1000 connections are all trying to get work done but SQL Anywhere is only able to execute a few requests at a time.
During a period of long Waiting Time values a sudden spike (for example, 3h to 6h) may occur when SQL Anywhere performs a checkpoint.
Waiting Time is displayed for target databases running on SQL Anywhere 9 and later,
and is based on the sum of the connection-level
ReqTimeBlockContention,
ReqTimeBlockIO,
ReqTimeBlockLock and
ReqTimeUnscheduled properties.
How To Enable Timing Information
Here's how to turn on the capturing of timing information for each connection to
target databases running on SQL Anywhere 9 and later:
Click on the Favorable? link near the top of the Foxhound Monitor or History page.
That will open section 14. Change Target Settings on the Monitor Options page.
- or -
Specify the -zt server command line option when starting the target database.
- or -
CALL sa_server_option ( 'RequestTiming', 'ON' ) on the target database if it is running on SQL Anywhere 9.
- or -
CALL sa_server_option ( 'RequestTiming', 'YES' ) on the target database if it is running on SQL Anywhere 10 or later.
The RequestTiming setting enables the following SQL Anywhere connection-level properties:
SQL Anywhere Property Foxhound Usage
----------------------- --------------------------------------------------------------
ReqCountActive connection-level Throughput... Req value
ReqCountBlockContention [not recorded by Foxhound]
ReqCountBlockIO [not recorded by Foxhound]
ReqCountBlockLock [not recorded by Foxhound]
ReqCountUnscheduled [not recorded by Foxhound]
ReqStatus connection-level Executing, Idle, Waiting, Current Req Status
ReqTimeActive connection-level Busy, Wait, Idle
ReqTimeBlockIO connection-level Waiting Time, Busy, Wait, Idle
ReqTimeBlockContention connection-level Waiting Time, Busy, Wait, Idle
ReqTimeBlockLock connection-level Waiting Time, Busy, Wait, Idle
ReqTimeUnscheduled connection-level Waiting Time
Changes to RequestTiming affect new connections, and they may or may not immediately affect existing connections as follows:
Changing RequestTiming from NO to YES (or OFF to ON) will not immediately affect existing connections.
Changing RequestTiming from YES to NO (or ON to OFF) may or may not immediately affect existing connections,
depending on the version of SQL Anywhere used for the target database.
The CPU column shows two values:
the percent used during the preceding interval (nn%) and
the number of processors used (of n).
The CPU time values are for the target SQL Anywhere server as a
whole, not an individual target database when the server is
running more than one database.
The percentages are adjusted for the number of CPUs being used by SQL Anywhere.
For example, if four processors are used, and SQL Anywhere is using 100% of one processor and 0%
of the other three, Foxhound will show the CPU time as 25% of 4.
Performance Tip:
CPU time is most important when the value is unexpected.
A high value like 80% may indicate the server is spending too much time executing inefficient
SQL statements, or it may simply indicate the server is busy getting a lot of work done.
A low value like 10% may indicate everything's OK with the SQL code, or it may indicate
some other bottleneck is preventing the CPU from getting its work done.
An extremely high value like 100% usually means trouble, especially when other
numbers are cause for concern; e.g., low throughput, high response time. The solution
may be "get a more powerful CPU", but it also may be "find and fix the problem".
A zero value usually means "idle server", but it may also be a symptom of "unresponsive server".
Performance Tip:
If you suspect that too much CPU time is being used by SQL code inside stored procedures, triggers, events and/or web services,
SQL Anywhere's own "procedure profiler" is the very best way to find those bottlenecks.
The CPU time and CPU count values are displayed for target databases running on SQL Anywhere version 7 and later,
and are based on the server-level NumLogicalProcessorsUsed, NumProcessorsAvail,
NumProcessorsMax and ProcessCPU properties.
The Temp Space column shows the total amount of
temporary file space used by all the connections.
Temporary pages are used for many purposes, most often
to hold intermediate results during query processing.
Even if there is no temporary file, as with an in-memory
no write database, temporary space is allocated and used
by the engine.
Performance Tip:
Large amounts of Temp Space may indicate that runaway queries are a performance bottleneck.
To find which connections are using the most temporary file space, click on the Temp Space
column heading in the connection section of Foxhound Monitor or History page. That will sort
the connections in decreasing order by Temp Space.
To limit the amount of temporary file space any individual connection can use on a target
database running on SQL Anywhere version 10 or later, use the MAX_TEMP_SPACE option; e.g.,
SET OPTION PUBLIC.MAX_TEMP_SPACE = '1G';
Performance Tip:
The term "temporary file" is misleading and it should be renamed "temporary space" because the data
may or may not reside in the actual temporary file (which may not exist at all).
Temp Space is displayed for target databases running on SQL Anywhere version 8 and later
and is based on the database-level PageSize property and the sum of the connection-level
TempFilePages property.
Rollback Log shows how much space in
the rollback log is currently used by all the connections.
Performance Tip:
A large Rollback Log value at the server level may indicate that
long-running transactions are a performance bottleneck.
Look for confirmation of this in the server-level Conns Blocked column and
the connection-level Transaction Time column.
Performance Tip:
The final checkpoint stage of a normal server shutdown may also take a long time
while the changes recorded in a large rollback log are being reversed.
RollBack Log is based on the database-level PageSize property and the sum of the
connection-level RollbackLogPages property.
Uncommitted shows how many operations have been performed by all the
connections but not yet committed.
Performance Tip:
A large number of Uncommitted operations at the server level may indicate that
long-running transactions are a performance bottleneck.
Look for confirmation of this in the server-level Conns Blocked column and
the connection-level Transaction Time column.
Performance Tip:
The count of Uncommitted operations includes (and may be exactly equal to) the number of uncommitted
row-level inserts, updates and deletes.
The count of Uncommitted operations may be larger than the number of INSERT, UPDATE and DELETE
statements because one statement can affect many rows.
If a single row is inserted, then updated twice and finally deleted, for example, that counts as
four Uncommitted operations.
If a single row matches the WHERE clause of an UPDATE statement but all of the new column values in the
SET clause are the same as the current values in the row, that row is not updated and the operation
is not counted as an Uncommitted operation.
A table-level schema lock obtained by a SELECT statement is not counted as an Uncommitted operation
even though the lock is not cleared until a subsequent commit or rollback (and may thus be regarded
as "uncommitted").
Uncommitted is based on the sum of the connection-level UncommitOp property.
Cache Panics is displayed for target databases running on SQL Anywhere version 9 and later,
and is based on the server-level CachePanics property.
Low Memory is the number of times the target server
had to change a query execution plan because cache memory
ran low in the previous interval.
Low Memory is displayed for target databases running on SQL Anywhere version 8 or later
and is based on the database-level QueryLowMemoryStrategy property.
Cache Satisfaction is the percentage of times a
database page lookup was satisfied by the cache
in the previous interval.
Performance Tip:
These conditions may indicate that insufficient memory in the database cache
is a performance bottleneck:
large Cache Panics values (greater than zero),
large Low Memory values (greater than zero) and/or
small Cache Satisfaction values (less than 100%).
Historical rules-of-thumb no longer apply to modern
databases; for example, "The cache should be at least 10% as large as the database file" has
been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache
that's even larger than the database file may be justified by queries that use enormous quantities
of temporary space.
Note: Cache Satisfaction is different from Index Satisfaction;
one compares memory and disk usage, the other compares index and table usage.
Performance Tip:
If the computer is dedicated to running this SQL Anywhere server and no other process of importance,
consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options
to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform
extra work changing the cache size up and down.
Performance Tip:
A long-running UNLOAD TABLE statement may result in extreme Cache Satisfaction values like 37.74%.
Cache Satisfaction is based on the server-level CacheHits and CacheRead properties
for target databases running on SQL Anywhere versions 5 and 6, and on the
database-level CacheHits and CacheRead properties for versions 7 and later.
Checkpoints is the number of CHECKPOINT operations
that have been executed by the server in the previous interval.
Performance Tip:
Checkpoint operations can be expensive. More than one checkpoint every once in a while
may indicate that undesirable behavior is causing a performance bottleneck.
Generally speaking, explicit CHECKPOINT statements are not required in application programs
because the server does a good job of scheduling checkpoints to minimize their
impact on performance. An explicit CHECKPOINT should never be used without
careful consideration, especially in a busy multi-user environment.
Checkpoints are different from commits; a commit ensures that the transaction
log file is up to date whereas a checkpoint ensures that the database file is up to date.
As long as commits are performed frequently, performance can be greatly improved by
delaying checkpoint operations... which is what SQL Anywhere does by default.
Performance Tip:
Think twice before running a database without a transaction log. Even if you don't need
the log for recovery purposes, SQL Anywhere will perform a checkpoint after every commit
if there's no transaction log... and that can be very bad for performance.
Checkpoints is based on the server-level Chkpt property
for target databases running on SQL Anywhere versions 5 and 6, and on the
database-level Chkpt property for versions 7 and later.
Checkpoint Urgency is the percentage of the SET OPTION
PUBLIC.CHECKPOINT_TIME value that has elapsed since the previous
checkpoint.
Checkpoint Urgency is displayed for target databases running on SQL Anywhere version 7 and later,
and is based on the database-level CheckpointUrgency property.
Recovery Urgency is the estimated time required to
recover the database when restarting after an
abnormal stoppage, expressed as a percentage of the SET
OPTION PUBLIC.RECOVERY_TIME value.
Checkpoint Urgency and Recovery Urgency are
used by the target server to help decide when to take
a checkpoint. They both increase monotonically
until a checkpoint is taken, then drop to zero.
The Recovery Urgency may exceed 100% because SQL Anywhere
enforces a lower boundary on the interval between successive
checkpoints. This lower bound is calculated using the
checkpoint_time and recovery_time options, and the default
is 2 minutes. If Recovery Urgency increases rapidly immediately
after one checkpoint is taken, it may continue far beyond
100% before the next checkpoint is allowed.
Recovery Urgency is displayed for target databases running on SQL Anywhere version 7 or later,
and is based on the database-level RecoveryUrgency property.
Performance Tip:
Before changing how often SQL Anywhere takes checkpoints, think twice!
SQL Anywhere almost always does an excellent job of picking the right time to
perform a checkpoint without your help.
Disk/Cache: Internal Index is the percentage ratio between the number of
index internal-node pages that have been read from disk and from the cache in the previous interval.
Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere version 7 and later,
and is based on the database-level DiskReadIndInt and CacheReadIndInt properties.
Disk/Cache: Leaf is the percentage ratio between the number of
index leaf pages that have been read from disk and from the cache in the previous interval.
Disk/Cache: Leaf is displayed for target databases running on SQL Anywhere version 7 and later,
and is based on the database-level DiskReadIndLeaf and CacheReadIndLeaf properties.
Disk/Cache: Table is the percentage ratio between the number of
table pages that have been read from disk and from the cache in the previous interval.
Disk/Cache: Table is displayed for target databases running on SQL Anywhere version 7 and later,
and is based on the database-level DiskReadTable and CacheReadTable properties.
The CacheReadTable counter is incremented every time SQL Anywhere tries to read a table page.
The DiskReadTable counter is incremented every time SQL Anywhere can't find the table in the cache
and has to read it from disk.
Performance Tip:
These conditions may indicate that insufficient memory in the database cache
is a performance bottleneck.
large Disk/Cache: Internal Index steady-state values (greater than 1%, with zero expected),
large Disk/Cache: Leaf steady-state values (greater than 1%) and/or
large Disk/Cache: Table steady-state values (greater than 1%).
The term "steady-state" means the database has serving the same application for a few minutes.
Historical rules-of-thumb no longer apply to modern
databases; for example, "The cache should be at least 10% as large as the database file" has
been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache
that's even larger than the database file may be justified by queries that use enormous quantities
of temporary space.
Performance Tip:
If the computer is dedicated to running this SQL Anywhere server and no other process of importance,
consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options
to set a fixed cache size.
With a dedicated computer there may be no need for SQL Anywhere to perform
extra work changing the cache size up and down.
Performance Tip:
Disk/Cache ratios that are consistently large may indicate that cache thrashing caused by a
large database page size is a performance bottleneck.
A smaller database page size may allow important pages to remain in the cache longer
rather than being swapped out to disk to make room for different (giant) pages.
Performance Tip:
A long-running UNLOAD TABLE statement may result in extreme Disk/Cache: Internal Index, Leaf, Table values like "- / - / 99%".
Incomplete Reads is the current number of file reads that have been started but not yet completed.
Performance Tip:
Consistently large Incomplete Reads values (greater than zero) may indicate that there is a performance
bottleneck caused by the inability of the disk system to keep up with database activity.
Incomplete Reads is displayed for target databases running on SQL Anywhere version 17,
and is based on the database-level CurrRead property.
Incomplete Writes is the current number of file writes that have been started but not yet completed.
Performance Tip:
Consistently large Incomplete Writes values (greater than zero) may indicate that there is a performance
bottleneck caused by the inability of the disk system to keep up with database activity.
Disk Reads is the number of pages that have been
read from disk
in the previous interval.
Disk Reads is based on the server-level DiskRead property for
target databases running on SQL Anywhere versions 5 and 6,
and on the database-level DiskRead property for versions 7 and later.
Disk Writes is the number of modified pages
that have been written to disk
in the previous interval.
Disk Writes is based on the server-level DiskWrite property for
target databases running on SQL Anywhere versions 5 and 6,
and on the database-level DiskWrite property for versions 7 and later.
Log Writes column shows the number of pages that
have been written to the transaction log
in the previous interval.
Log Writes is based on the server-level LogWrite property for
target databases running on SQL Anywhere versions 5 and 6,
and on the database-level LogWrite property for versions 7 and later.
Performance Tip:
Large Disk Reads, Disk Writes, Log Writes values may indicate
that heavy disk I/O is a performance bottleneck.
Performance Tip:
Disk Reads and Disk Writes count page-level input and output operations on the SYSTEM,
temporary and secondary dbspace files, and Log Writes counts pages written to the transaction log file.
Other files are excluded; e.g., non-dbspace files used by LOAD and UNLOAD statements and by
xp_read_file() and xp_write_file() procedure calls.
Index Adds is the number of times an entry has been
added to an index
in the previous interval.
Performance Tip:
A large Index Adds value may indicate that a large number of row inserts is a performance bottleneck
...or a large number of row updates that are changing index columns.
Index Adds is based on the server-level IndAdd property for
target databases running on SQL Anywhere versions 5.5 and 6,
and on the database-level IndAdd property for target databases
running on SQL Anywhere versions 7 and later.
Index Lookups is the number of times an entry
has been looked up in an index
in the previous interval.
Performance Tip:
A large Index Lookups value may indicate that heavy index usage is a performance bottleneck
...or just the opposite; heavy index usage is often better than the alternative (frequent
retrieval of data pages).
Index Lookups is based on the server-level IndLookup property for
target databases running on SQL Anywhere versions 5.5 and 6,
and on the database-level IndLookup property for target databases
running on SQL Anywhere versions 7 and later.
Index Satisfaction is the percentage of times that
an index lookup was satisfied by the index without having
to retrieve more information from the table data
in the previous interval.
Performance Tip:
Small Index Satisfaction values (less than 100%) may indicate that low index selectivity
is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without
having to read table pages.
When index selectivity is low it often means
SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
the index is fragmented (also possible; see the sa_index_levels() procedure).
To increase Index Satisfaction at the database level it may be necessary to
find the individual queries that are responsible; try clicking on the connection section column
titles Full Index Comps or Index Satisfaction to sort the connections in descending order.
Note: Index Satisfaction is different from Cache Satisfaction;
one compares index and table usage, the other compares memory and disk.
Index Satisfaction is based on the server-level IndLookup and FullCompare
properties for target databases running on SQL Anywhere versions 5.5 and 6,
and on the database-level IndLookup and FullCompare properties for target
databases running on SQL Anywhere versions 7 and later.
Full Index Comps
shows how many times additional information had
to be obtained from the table data
in order to satisfy an index lookup
in the previous interval.
Performance Tip:
Large values of Full Index Comps may indicate that low index selectivity is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without having to read table pages.
When index selectivity is low it often means
SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
the index is fragmented (also possible; see the sa_index_levels() procedure).
To reduce the number of Full Index Comps at the database level it may be necessary to
find the individual queries that are responsible; try clicking on the connection section column
titles Full Index Comps or Index Satisfaction to sort the connections in descending order.
Full Index Comps is based on the server-level FullCompare property for
target databases running on SQL Anywhere versions 5.5 and 6,
and on the database-level FullCompare property for target databases
running on SQL Anywhere versions 7 and later.
The Connection Identification section displays the values that uniquely
identify this connection apart from all other connections that exist now,
or have ever existed, or will ever exist on any target database recorded
by this copy of Foxhound.
The Conn #, User, OS User, IP, Name columns identify this connection.
Conn # is the connection number of the connection.
SQL Anywhere assigns a unique connection number
to each connection started since the database started.
Recent versions of SQL Anywhere assign small numbers (1, 2, 3, ...) to external
client connections, and large numbers to events, web
services and internal ("temporary") connections (1000000065, 1000000066, ...).
The range for external client connection numbers is 1 through 999,999,999.
If that range is exceeded the numbering starts at 1 again, but numbers
already in use are skipped.
There will never be two connections with the same number until the
SQL Anywhere server is stopped and restarted.
Two numbers may be shown in the Conn # column for
temporary internal or "child" connections that
have been started by some other "parent" connection.
In this case, the first number will be the parent connection number and the second number
will be the actual connection number for the internal connection; e.g., 1 1000000090
The Conn # column contains a single number for target databases running on SQL Anywhere 5.5 through 11,
and one or two numbers for target databases running on SQL Anywhere 12 and later.
When the Conn # contains a single number, it is based on the connection-level Number property.
When the Conn # contains a two numbers, the first number is based on the connection-level
ParentConnection property and the second number is based on the connection-level Number property.
User is the SQL Anywhere database user id that was
used to make the connection; e.g., DBA.
User is based on the connection-level Userid property.
OS User is the operating system user id associated
with the client process.
When the OS User is available, it may be easier to use for administrative purposes than
the SQL Anywhere user id or the IP address; e.g., when identifying individual users and/or
contacting them when connections are dropped.
OS User is displayed for target databases running on SQL Anywhere version 11 and later
and is based on the connection-level OSUser property.
IP is the network IP address of the client side of
the connection; e.g., 192.168.1.104.
IP is based on the connection-level NodeAddress property.
Name is the connection name of the connection.
Performance Tip: Try using the ConnectionName (CON=) parameter to uniquely identify
the actual end user responsible for each client connection.
This is sometimes helpful when it's too hard to figure out who's doing what by looking at the
User, OS User or IP values in Foxhound output.
Connection names may be automatically assigned as follows:
Internal connections started by SQL Anywhere EVENT handlers have the event name assigned
as connection name.
Internal connections started by SQL Anywhere web SERVICE handlers have the service name
assigned as connection name.
Internal "temporary" or "child" connections created by recent versions of SQL Anywhere
have standard connection names assigned in the format "INT:xxx".
For example, INT:Cleaner is the connection name for the background database cleaner
process, and INT:Exchange is assigned to child connections used for intra-query parallelism.
When no explicit connection name is specified for a client connection, SQL Anywhere
may assign a name like SQL_DBC_c657ef0.
By default the Foxhound Monitor process uses connection names like "Foxhound-Monitor-nnnn"
when connecting to target databases, where "nnnn" is Windows Process Identifier (PID)
of the SQL Anywhere dbsrv16.exe or dbsrv17.exe process that is running the Foxhound database.
INT: StmtPerfMngrConn is a persistent (permanent) internal connection that may appear in
databases created with SQL Anywhere 17.0.4.2053 or later.
If it shows up in your target database, Foxhound's "Parent, Child Conns" columns may
not add up to the same total as the "Executing, Idle, Waiting Conns" columns.
That's because (as of build 2053) it isn't counted in the SQL Anywhere database-level
ConnCount property but it is counted in the connection-level ReqStatus property.
INT: StmtPerfMngrConn is related to the Statement Performance Summary Feature (Profiler)
enhancements made in SQL Anywhere 17 build 2000.
You can eliminate the INT: StmtPerfMngrConn connection by using the dbsrv17 -k option, but that's a
bad idea if you use Foxhound because it disables many important performance properties...
...so just ignore INT: StmtPerfMngrConn because it doesn't seem to use any
resources during normal (non-profiling) operations.
Name is based on the connection-level Name property.
The Login Time column helps to uniquely identify this
connection apart from earlier or later connections which
may have the same connection number because the target server was restarted.
Login Time is displayed for target databases running on SQL Anywhere 9 and later,
and is based on the connection-level LoginTime property.
The Connection Id String column is an artificial identifier
constructed by Foxhound to uniquely identify each target connection within
the entire Foxhound database.
Foxhound uses this string to create URLs for opening new Connection History pages.
You may find it useful when creating adhoc queries to select all the history for one connection.
It is stored in the sample_connection.connection_id_string column, and there is an index on that column.
Here's the format of the Connection Id String:
ss-cc-yyyymmddhhnnss-sss
where...
ss sample_connection.sampling_id - unique identifier to each target database sampling session
cc sample_connection.connection_number
yyyymmddhhmmss-sss DATEFORMAT ( sample_connection.LoginTime, 'YYYYMMDDHHNNSS-SSS' )
Note: It is possible for two or more different Connection Id String values to represent the same actual connection.
This can happen if you accidentally create two or more separate Foxhound sampling sessions for the same
target database; e.g., by using a DSN and a Connection String that point to the same target database.
The result will be two or more sets of data about the same connection, using different values in the sampling_id column.
This is not a fatal error, simply a waste of Foxhound resources, and it shouldn't affect your adhoc queries; it is still
true that a single Connection Id String value will uniquely identify a single actual connection.
Connection Id String is available for target databases running on SQL Anywhere 9 and later.
The Parent Conn #, User, OS User, IP, Name columns are displayed when the
current connection is a child connection, and they form a link that opens a separate
Connection History page for the parent connection to this child connection.
Conn #, User, OS User, IP, Name ... Parent Conn #, User, OS User, IP, Name
3 1000008782 / - / - / - / INT: Exchange 3 / DBA / Breck / - / ddd16-1
Parent Conn #, User, OS User, IP, Name is displayed for target databases running on SQL Anywhere 12 and later,
and is based on the connection-level ParentConnection property.
The pair of numbers [in square brackets] above the "Connection Samples" column title are Foxhound database primary key values that can be used when coding adhoc queries; e.g., [4,1796].
The first number is the sampling_id which identifies the Foxhound Monitor session for this target database.
The meaning of the second number depends on the type of data displayed in the top row of this section; for example:
SELECT * FROM alert WHERE alert_occurrence = 1796;
SELECT * FROM alert_cancelled WHERE alert_cancelled_occurrence = 1796;
SELECT * FROM all_clear WHERE all_clear_occurrence = 1796;
SELECT * FROM autodropped_connection WHERE autodrop_occurrence = 1796;
SELECT * FROM email_failure WHERE email_failure_occurrence = 1796;
SELECT * FROM sample_connection WHERE sample_set_number = 1796;
The Interval column
shows the actual time interval between the previous sample and this
one.
Foxhound tries to record a new sample every 10 seconds
but the actual interval can vary.
Foxhound uses the interval time to convert performance statistics from
cumulative counts to rates; e.g., to convert the total number of disk writes
into the number of disk writes per second.
A very long interval like 1h 19.8m may indicate
the computer was in sleep, standby or hibernate mode.
Throughput, also known as bandwidth, is a measure of how much work the database has performed:
Throughput... Req is the rate at which the server has started processing a new request
or resumed processing an existing request for this connection in the previous interval.
Performance Tip:
Large Throughput... Req values may indicate that this connection is placing a heavy load on the database.
Note that "request" is an atomic internal unit of work processed by the server for a connection,
not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived
column for adhoc queries of connection-level data.
Throughput... Req is displayed for target databases running on SQL Anywhere version 9 and later,
and is based on the connection-level ReqCountActive property.
(note that this is different from the server-level Throughput... Req column which
is based on the server-level Req property)
Throughput... Commits is the rate at which commit requests have been
handled by the server for this connection in the previous interval.
Performance Tip:
Large Throughput... Commits values may indicate that this connection is placing a heavy load on the database.
Throughput... Commits is based on the connection-level Commit property.
Throughput... Bytes is the rate at which data has been received by and sent by the server
across the client server interface for this connection in the previous interval.
Performance Tip:
Large Throughput... Bytes values may indicate that this connection is placing a heavy load on the database.
Throughput... Bytes is displayed for target databases running on SQL Anywhere versions 8 and later,
and is based on the connection-level BytesReceived and BytesSent properties.
Locks Held is the total number of locks held by this connection.
Performance Tip:
Large numbers of Locks Held are not a problem in themselves; SQL Anywhere is
perfectly capable of handling millions of locks being held at one time.
When locks become a problem is when they are held too long and cause other
connections to be blocked (prevented) from doing work.
For example, when Locks Held grows large during a period of high activity (high
CPU, disk), and then *remains* high for a period of low activity,
that may indicate an application flaw where a COMMIT is not issued
as soon as it should be.
The Locks Held value does not include schema locks. For example, if a SELECT statement
holds a schema lock that is blocking another connection from executing an ALTER TABLE
statement, Locks Held may be empty even though Conns Blocked is 1.
Locks Held is displayed for target databases running on SQL Anywhere 10 and later,
and is based on the connection-level LockCount property.
Conns Blocked is the number of other connections that
were blocked by this connection.
Performance Tip:
Large numbers of blocked connections may indicate a serious performance bottleneck
caused by locks being held too long.
The cause may be an application that breaks this fundamental rule of transaction
design:
"Never yield control of the mouse or keyboard to the end user while
database locks are being held; always do a COMMIT first."
When that rule is broken a long-running transaction is often the result, with those
locks preventing other users from getting their work done while the first user
decides what to do (or takes a lunch break).
Conns Blocked is based on the connection-level BlockedOn property for
target databases running on SQL Anywhere versions 5.5 through 16,
and on the connection-level BlockedOn, LockObjectType and LockObjectOID properties for
target databases running on SQL Anywhere 17.
Transaction Time is the length of time since the database was first modified
by this connection after a COMMIT or ROLLBACK.
Performance Tip: A long Transaction Time may indicate that a
long-running transaction is a performance bottleneck.
Note that while it is possible for a SELECT to acquire locks and thus block other connections (e.g., a shared
lock obtained by a SELECT will block an ALTER TABLE), a SELECT does not
count as a modification as far as the Transaction Time is concerned.
In other words, a SELECT does not start a transaction.
Transaction Time is displayed for target databases running on SQL Anywhere 8 and later,
and is based on the connection-level TransactionStartTime property.
Waiting Time is the total amount of time this
connection has been blocked or forced to wait.
Performance Tip:
Long waiting times may indicate a serious performance bottleneck caused
a lack of resources.
Waiting Time is displayed for target databases running on SQL Anywhere 9 and later,
and is based on the sum of the connection-level
ReqTimeBlockContention,
ReqTimeBlockIO,
ReqTimeBlockLock and
ReqTimeUnscheduled properties.
The Busy, Wait, Idle columns are intended to give the user a rough idea of what's going
on at the connection level. They are displayed as cumulative percentages since login:
Busy: A worker is dedicated to processing a request for the connection, and that worker is
neither blocked waiting for I/O, waiting for access to a shared data structure nor waiting
for a locked object. The worker may be actively executing the request, but it may also be
honoring a WAITFOR statement or waiting for some external work to be done (remote procedure,
remote table, web service client procedure, external function, and so on).
Wait: The connection is waiting for the server to start processing a request, or the worker
dedicated to processing that request is blocked waiting for I/O, waiting for access to a shared
data structure or waiting for a locked object.
Idle: The connection is neither waiting nor busy.
The Busy, Wait, Idle columns are
displayed for target databases running on SQL Anywhere 9 and later,
and are based on the connection-level LoginTime, ReqTimeActive,
ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock
and ReqTimeUnscheduled properties:
Here's how to turn on the capturing of timing information for each connection to
target databases running on SQL Anywhere 9 and later:
Click on the Favorable? link near the top of the Foxhound Monitor or History page.
That will open section 14. Change Target Settings on the Monitor Options page.
- or -
Specify the -zt server command line option when starting the target database.
- or -
CALL sa_server_option ( 'RequestTiming', 'ON' ) on the target database if it is running on SQL Anywhere 9.
- or -
CALL sa_server_option ( 'RequestTiming', 'YES' ) on the target database if it is running on SQL Anywhere 10 or later.
The RequestTiming setting enables the following SQL Anywhere connection-level properties:
SQL Anywhere Property Foxhound Usage
----------------------- --------------------------------------------------------------
ReqCountActive connection-level Throughput... Req value
ReqCountBlockContention [not recorded by Foxhound]
ReqCountBlockIO [not recorded by Foxhound]
ReqCountBlockLock [not recorded by Foxhound]
ReqCountUnscheduled [not recorded by Foxhound]
ReqStatus connection-level Executing, Idle, Waiting, Current Req Status
ReqTimeActive connection-level Busy, Wait, Idle
ReqTimeBlockIO connection-level Waiting Time, Busy, Wait, Idle
ReqTimeBlockContention connection-level Waiting Time, Busy, Wait, Idle
ReqTimeBlockLock connection-level Waiting Time, Busy, Wait, Idle
ReqTimeUnscheduled connection-level Waiting Time
Changes to RequestTiming affect new connections, and they may or may not immediately affect existing connections as follows:
Changing RequestTiming from NO to YES (or OFF to ON) will not immediately affect existing connections.
Changing RequestTiming from YES to NO (or ON to OFF) may or may not immediately affect existing connections,
depending on the version of SQL Anywhere used for the target database.
The CPU column shows how much of the overall CPU time
available was used by this connection in the previous interval.
The percentage is adjusted for the number of CPUs being used by the server.
The CPU % is displayed for target databases running on SQL Anywhere 10 and later,
and is based on the connection-level ApproximateCPUTime property and
the server-level NumLogicalProcessorsUsed property.
The Child Conns column displays the number of internal
child connections that have been started by each primary parent
connection.
When a connection makes use of the intra-query parallelism feature,
it spawns a number of internal child connections which do most of the work;
e.g., one INT: EXCHANGE child connection for each available processor.
Performance Tip: Some versions of SQL Anywhere report the total CPU time used by all the child
connections as the ApproximateCPUTime value for each child connection in use,
and almost none for the parent connection. This inflates the amount of
CPU time used by each child connection without reporting any CPU usage
by the parent connection.
In an attempt to make sense of this behavior, Foxhound calculates the
average non-zero ApproximateCPUTime for the child connections
and reports it as the CPU time for this parent connection.
The inflated values reported by SQL Anywhere for each child connection
are still shown by Foxhound; only the parent connection CPU time is adjusted.
One consequence of the Foxhound calculations is that the AutoDrop #5 CPU Usage
process does apply to parent connections using intra-query parallelism.
Note that the AutoDrop process is never performed on a child connection or any
other internal connection that isn't directly associated with a client application.
Child Conns is displayed for target databases running on SQL Anywhere 12 and later,
and is based on the connection-level ParentConnection property.
Temp Space shows how much temporary
space is currently used by this connection.
Temporary pages are used for many purposes, most often
to hold intermediate results during query processing.
Even if there is no temporary file, as with an in-memory
no write database, temporary space is allocated and used
by the engine.
Performance Tip:
Large amounts of Temp Space may indicate that runaway queries are a performance bottleneck.
To find which connections are using the most temporary file space, click on the Temp Space
column heading in the connection section of Foxhound Monitor or History page. That will sort
the connections in decreasing order by Temp Space.
To limit the amount of temporary file space any individual connection can use on a target
database running on SQL Anywhere version 10 or later, use the MAX_TEMP_SPACE option; e.g.,
SET OPTION PUBLIC.MAX_TEMP_SPACE = '1G';
Performance Tip:
The term "temporary file" is misleading and it should be renamed "temporary space" because the data
may or may not reside in the actual temporary file (which may not exist at all).
Temp Space is displayed for target databases running on SQL Anywhere version 8 and later,
and is based on the database-level PageSize property and the connection-level
TempFilePages property.
Rollback Log shows how much space in
the rollback log is currently used by this connection.
Performance Tip:
A large Rollback Log value at the connection level may indicate that a
long-running transaction is a performance bottleneck.
Look for confirmation of this in the connection-level Conns Blocked and Transaction Time columns.
Performance Tip:
The final checkpoint stage of a normal server shutdown may also take a long time
while the changes recorded in a large rollback log are being reversed.
RollBack Log is based on the database-level PageSize property and the
connection-level RollbackLogPages property.
Uncommitted shows how many operations have
been performed by this connection but not yet
committed.
Performance Tip:
A large number of Uncommitted operations at the connection level may indicate that a
long-running transaction is a performance bottleneck.
Look for confirmation of this in the connection-level Conns Blocked and Transaction Time columns.
Performance Tip:
The count of Uncommitted operations includes (and may be exactly equal to) the number of uncommitted
row-level inserts, updates and deletes.
The count of Uncommitted operations may be larger than the number of INSERT, UPDATE and DELETE
statements because one statement can affect many rows.
If a single row is inserted, then updated twice and finally deleted, for example, that counts as
four Uncommitted operations.
If a single row matches the WHERE clause of an UPDATE statement but all of the new column values in the
SET clause are the same as the current values in the row, that row is not updated and the operation
is not counted as an Uncommitted operation.
A table-level schema lock obtained by a SELECT statement is not counted as an Uncommitted operation
even though the lock is not cleared until a subsequent commit or rollback (and may thus be regarded
as "uncommitted").
Uncommitted is based on the connection-level UncommitOp property.
Low Memory is the number of times
since this connection started that the target server
had to change a query execution plan for this connection
because cache memory ran low.
Low Memory is displayed for target databases running on SQL Anywhere version 8 or later
and is based on the connection-level QueryLowMemoryStrategy property.
Cache Satisfaction is the percentage of times
since this connection started that a
database page lookup for this connection
was satisfied by the cache.
Performance Tip:
These conditions may indicate that insufficient memory in the database cache
is a performance bottleneck:
large Cache Panics values (greater than zero),
large Low Memory values (greater than zero) and/or
small Cache Satisfaction values (less than 100%).
Historical rules-of-thumb no longer apply to modern
databases; for example, "The cache should be at least 10% as large as the database file" has
been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache
that's even larger than the database file may be justified by queries that use enormous quantities
of temporary space.
Note: Cache Satisfaction is different from Index Satisfaction;
one compares memory and disk usage, the other compares index and table usage.
Performance Tip:
If the computer is dedicated to running this SQL Anywhere server and no other process of importance,
consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options
to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform
extra work changing the cache size up and down.
Performance Tip:
A long-running UNLOAD TABLE statement may result in extreme Cache Satisfaction values like 37.74%.
Cache Satisfaction is displayed for target databases running on SQL Anywhere 8 and later,
and is based on the connection-level CacheHits and CacheRead properties.
Time Since Last Request is the elapsed time since
the last time a request was started for this connection.
Performance Tip: If this connection is blocked, Time Since Last Request tells you
exactly how long it has been waiting.
Performance Tip: A long Time Since Last Request is usually associated with an idle connection,
but if the connection is busy (high CPU, high disk activity, etc.) it could mean that some single operation
is "stuck" using up resources but not getting anywhere.
Time Since Last Request is based on the connection-level LastReqTime property.
Current Req Status shows whether this connection was Idle, Waiting, Blocked or Executing.
Performance Tip:
A blocked or waiting connection is a dramatic form of performance bottleneck when it corresponds
to an end user who is unable get any work done.
Small numbers of short-lived blocks are OK if nobody notices... the blocks are probably
just doing their job by preventing collisions between competing updates.
However, large numbers of blocked connections, and/or long-lasting blocks can bring a multi-user
system to its knees.
Foxhound columns that can help pinpoint the problems include Blocked By, Transaction Time, Locks Held,
Conns Blocked, Rollback Log, Uncommitted, Block Reason and Locked Row Query.
The connection-level Current Req Status is closely related to the database-level Executing,
Idle, Waiting Conns columns:
Foxhound Foxhound
Database-Level Connection-Level SQL Anywhere
Executing Idle Waiting Current Req Status SQL Anywhere Description ReqStatus
---------------------- --------------------------- --------------------------------------- -----------------
Yes - - Executing The connection is executing a request. Executing
- Yes Idle The connection is not currently Idle
processing a request.
- - Yes Waiting for thread The connection has work to do and Unscheduled
is waiting for a worker thread.
- - Yes Waiting for I/O The connection is waiting for an I/O. BlockedIO
- - Yes Waiting for shared resource The connection is waiting for access to BlockedContention
shared database server data structures.
- - Yes Blocked by lock The connection is blocked waiting BlockedLock
for a locked row.
- - Yes Blocked by mutex The connection is blocked waiting BlockedLock (1)
for a mutex.
- - Yes Blocked by semaphore The connection is blocked waiting Executing (2)
for a semaphore.
Note 1: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'BlockedLock' for a connection
blocked by a mutex on a target database running on SQL Anywhere 17.
Note 2: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'Executing' for a connection
blocked by a semaphore on a target database running on SQL Anywhere 17.
Here's an analogy that doesn't prove anything but might help understanding:
If Executing is like a car moving down the road, then
Waiting is like a car stuck in traffic,
Blocked is like a car waiting at a red light, and
Idle is like a car parked at the side of the road.
Current Req Status is displayed for target databases running on SQL Anywhere 9
and later.
For target databases running on SQL Anywhere versions 9 through 16,
Current Req Status is based on the connection-level ReqStatus property.
For target databases running on SQL Anywhere 17, Current Req Status is based
on the connection-level ReqStatus property for connections that are not blocked by a
mutex or semaphore.
For connections blocked by a mutex, Current Req Status is based on the
sa_locks() procedure.
For connections blocked by a semaphore, Current Req Status is based on
the connection-level BlockedOn, LockObjectType and LockObjectOID properties and the
SYSMUTEXSEMAPHORE table.
Disk/Cache: Internal Index is the percentage ratio between the number of
index internal-node pages that have been read from disk and from the cache for this connection in the previous interval.
Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere version 7 and later,
and is based on the connection-level DiskReadIndInt and CacheReadIndInt properties.
Disk/Cache: Leaf is the percentage ratio between the number of
index leaf pages that have been read from disk and from the cache for this connection in the previous interval.
Disk/Cache: Leaf is displayed for target databases running on SQL Anywhere version 7 and later,
and is based on the connection-level DiskReadIndLeaf and CacheReadIndLeaf properties.
Disk/Cache: Table is the percentage ratio between the number of
table pages that have been read from disk and from the cache for this connection in the previous interval.
Disk/Cache: Table is displayed for target databases running on SQL Anywhere version 7 and later,
and is based on the connection-level DiskReadTable and CacheReadTable properties.
The CacheReadTable counter is incremented every time SQL Anywhere tries to read a table page.
The DiskReadTable counter is incremented every time SQL Anywhere can't find the table in the cache
and has to read it from disk.
Performance Tip:
These conditions may indicate that insufficient memory in the database cache
is a performance bottleneck.
large Disk/Cache: Internal Index steady-state values (greater than 1%, with zero expected),
large Disk/Cache: Leaf steady-state values (greater than 1%) and/or
large Disk/Cache: Table steady-state values (greater than 1%).
The term "steady-state" means the database has serving the same application for a few minutes.
Historical rules-of-thumb no longer apply to modern
databases; for example, "The cache should be at least 10% as large as the database file" has
been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache
that's even larger than the database file may be justified by queries that use enormous quantities
of temporary space.
Performance Tip:
If the computer is dedicated to running this SQL Anywhere server and no other process of importance,
consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options
to set a fixed cache size.
With a dedicated computer there may be no need for SQL Anywhere to perform
extra work changing the cache size up and down.
Performance Tip:
Disk/Cache ratios that are consistently large may indicate that cache thrashing caused by a
large database page size is a performance bottleneck.
A smaller database page size may allow important pages to remain in the cache longer
rather than being swapped out to disk to make room for different (giant) pages.
Performance Tip:
A long-running UNLOAD TABLE statement may result in extreme Disk/Cache: Internal Index, Leaf, Table values like "- / - / 99%".
Performance Tip:
Disk Reads and Disk Writes count page-level input and output operations on the SYSTEM,
temporary and secondary dbspace files, and Log Writes counts pages written to the transaction log file.
Other files are excluded; e.g., non-dbspace files used by LOAD and UNLOAD statements and by
xp_read_file() and xp_write_file() procedure calls.
Index Adds is the rate at which entries were added to indexes
for this connection in the previous interval.
Performance Tip:
A large Index Adds value may indicate that a large number of row inserts is a performance bottleneck
...or a large number of row updates that are changing index columns.
Index Adds is based on the connection-level IndAdd property.
Index Lookups is the rate at which entries were
looked up in indexes for this connection in the previous interval.
Performance Tip:
A large Index Lookups value may indicate that heavy index usage is a performance bottleneck
...or just the opposite; heavy index usage is often better than the alternative (frequent
retrieval of data pages).
Index Lookups is based on the connection-level IndLookup property.
Index Satisfaction is the percentage of times that
an index lookup was satisfied by the index without having
to retrieve more information from the table data,
for this connection since it started.
Index Adds is the rate at which entries were added to indexes
for this connection in the previous interval.
Performance Tip:
Small Index Satisfaction values (less than 100%) may indicate that low index selectivity
is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without
having to read table pages.
When index selectivity is low it often means
SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
the index is fragmented (also possible; see the sa_index_levels() procedure).
Full Index Comps is the rate at which additional information had
to be obtained from the table data in order to satisfy an index lookup,
for this connection in the previous interval.
Performance Tip:
Large values of Full Index Comps may indicate that low index selectivity is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without having to read table pages.
When index selectivity is low it often means
SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
the index is fragmented (also possible; see the sa_index_levels() procedure).
If this connection has been has been dropped by Foxhound's
AutoDrop process, an AutoDropped for this reason:
message will appear with an explanation; e.g.:
AutoDropped for this reason: #1: This connection has been blocking 1 or more (currently 1)
other connections for 5 or more (currently 5) samples
Note that a dropped connection may keep running for a while as SQL Anywhere rolls back
its work even though the client application has been disconnected.
If Foxhound's AutoDrop process dropped or attempted to drop
a connection, the Autodrop Result: line will explain
whether the attempt was successful or not; e.g.:
Autodrop Result: OK... issued at 2013-12-26 14:35:28.326, processed at 2013-12-26 14:35:28.345
AutoDrop Result: Failed... issued at 2014-02-07 16:30:06.090, failed at 2014-02-07 16:30:06.114: SQLCODE = -660,
SQLSTATE = WO005, ERRORMSG() = Server 'p001': [Sybase][ODBC Driver][SQL Anywhere]
Permission denied: you do not have permission to disconnect "29"
Note that a dropped connection may keep running for a while as SQL Anywhere rolls back
its work even though the client application has been disconnected.
The Blocked By: Conn #, User, OS User, IP, Name
line identifies the connection that is blocking this one.
Performance Tip:
If the target database is running on SQL Anywhere Version 9 or later,
the Blocked By: Conn #, User, OS User, IP, Name columns form a link that
opens the Connection History page in a separate browser window or tab.
That page will show just this one single connection over time.
Blocked By: Conn # is the connection number of the blocking connection.
Blocked By: Conn # is based on the connection-level BlockedOn property.
Blocked By: User is the SQL Anywhere database user id that was
used to make the connection; e.g., DBA.
Blocked By: User is based on the connection-level Userid property.
Blocked By: OS User is the operating system user id associated
with the blocking client process.
Blocked By: OS User is displayed for target databases running on SQL Anywhere version 11 and later
and is based on the connection-level OSUser property.
Blocked By: IP is the network IP address of the client side of
the blocking connection; e.g., 192.168.1.104.
Blocked By: IP is based on the connection-level NodeAddress property.
Blocked By: Name is the connection name of the blocking connection.
Blocked By: Name is based on the connection-level Name property.
The Block Reason: line describes the lock that's blocking the connection; e.g.:
Block Reason: Row Transaction Write lock on DBA.t
Block Reason: Row Transaction Intent, Row Transaction Write, Row Transaction WriteNoPK lock on dba.t
Block Reason: Mutex Connection Exclusive lock on dba.test_mutex
Block Reason: Semaphore Connection lock on dba.test_semaphore
The format of Block Reason: depends on the level of detail provided to Foxhound by the target database;
for example, only SQL Anywhere 17 supports Mutex and Semaphore locks.
Here are the various layouts used, from high to low levels of detail:
Block Reason: [class] [scope/duration] [type] lock on [owner].[object]
[class] [scope/duration] [type] lock on [object]
[class] [scope/duration] [type]
Lock on [owner].[object]
Lock on [object]
Fields...
[class]: Schema, Row, Table, Position, Mutex, Semaphore
[scope/duration]: Transaction, Position, Connection
[type]: Shared, Exclusive, Write, ...
[owner]: object creator
[object]: table, mutex or semaphore name
Block Reason is based on the sa_locks() procedure, the
SYSMUTEXSEMAPHORE table, and the connection-level
BlockedOn, LockObjectType and LockObjectOID properties.
The Last Statement: line displays the last SQL statement received from the
client application on this connection, as of the point this sample was recorded:
Last Statement: [Show More] select "COUNT_BIG"() -- 14 seconds from "SYSTAB" as "A" cross join "SYSTABCOL" as "B" c...
If the value is too long to show on one line, click on [Show More]:
Last Statement: [Show Less]
select "COUNT_BIG"() -- 14 seconds
from "SYSTAB" as "A"
cross join "SYSTABCOL" as "B"
cross join "SYSUSER" as "C"
Last Statement is displayed for target databases running on SQL Anywhere 8 and later,
and is based on the connection-level LastStatement property.
How To Enable The Last Statement Line
Turn on the capturing of the most recently-prepared SQL statement for each connection:
Click on the Favorable? link near the top of the Foxhound History page.
That will open section 14. Change Target Settings on the Monitor Options page
where you can enable the RememberLastStatement setting on the target server.
- or -
Specify the -zl server command line option when starting the target database.
If that doesn't work, try turning off client statement caching on the target database:
SET TEMPORARY OPTION MAX_CLIENT_STATEMENTS_CACHED = '0';
- or -
SET OPTION PUBLIC.MAX_CLIENT_STATEMENTS_CACHED = '0';
The Last Statement line may not appear for a variety of reasons, and when it does
appear it may not contain the statement that is currently executing.
Here's the full story:
The CONNECTION_PROPERTY ( 'LastStatement' ) function call is used to obtain the value.
The SQL Anywhere documentation says that call "Returns the most recently prepared
SQL statement for the current connection."
The Last Statement line will not appear when CONNECTION_PROPERTY ( 'LastStatement' )
returns an empty value, and that can happen
when the RememberLastStatement server option is not set to 'Yes' (see above),
when a cached statement is reused because the MAX_CLIENT_STATEMENTS_CACHED option
is not set to '0' for the connection (see above), and
when a prepared statement has finished executing and it is dropped.
The Last Statement line may show a statement that is different from the one currently executing; that can happen
when a client application calls a stored procedure that contains a long-running query, thus causing the Last Statement
line to show the CALL even though it's the query that is currently executing, and
when an application prepares multiple statements before executing any of them with the result that "the most
recently prepared SQL statement" may not the one currently executing.
The Last Statement line may or may not apply to the same query as the Last Plan Text line.
The Last Statement line shows what came from the client application, whereas Last Plan Text
applies to the last query run by the server whether it came from the client
or came from within a stored procedure.
The Last Plan Text: line displays the last query execution plan used by this connection,
as of the point this sample was recorded:
Last Plan Text: [Show More] ( Plan ( SingleRowGroupBy ( Exchange [ 8 ] ( SingleRowGroupBy ( Nested...
If the value is too long to show on one line, click on [Show More]:
Last Plan Text: [Show Less]
( Plan
( SingleRowGroupBy
( Exchange [ 8 ]
( SingleRowGroupBy
( NestedLoopsJoin
( NestedLoopsJoin
( ParallelTableScan ( ISYSUSER su ) )
( TableScan ( ISYSTAB tab ) )
)
( TableScan ( ISYSTABCOL col ) )
)
)
)
)
)
Last Plan Text is displayed for target databases running on SQL Anywhere 10 and later,
and is based on the connection-level LastPlanText property.
How To Enable The Last Plan Text Line
Turn on the capturing of the long text plan for the last query executed on each connection:
Click on the Favorable? link near the top of the Foxhound History page.
That will open section 14. Change Target Settings on the Monitor Options page where you can
enable the RememberLastPlan setting on the target server.
- or -
Specify the -zp server command line option when starting the target database.
- or -
CALL sa_server_option ( 'RememberLastPlan', 'YES' ) on the target database.
The Last Plan Text line may or may not apply to the same query as the Last Statement line.
The Last Plan Text line shows the last query run by the server whether it came from the client
or came from within a stored procedure, whereas Last Statement shows what came from the client application.
button is a context-sensitive link to this Help topic.
This button appears in many locations, each of them a link to a different Help topic in this frame.
Newest - Scroll to the most recent samples for this connection
- Scroll down to the next older sample that has an Alert, AutoDrop or other message