Help for Foxhound 5.0.5516a

Table of Contents     [RisingRoad]
search engine by freefind advanced


Foxhound 5 

3. The Monitor Database Page

The Monitor Database page shows current server and database-level performance statistics for a target database, as well as information about current connections. This information is collectively referred to as a "sampling session" because it is gathered (sampled) at regular intervals rather than recorded on a continuous basis.

Note: The Monitor page is not available for offline databases; see the Offline Foxhound Sampling Service (OFSS).

3.1 The Monitor Database Menu

3.2 The Monitor Control Panel

3.3 The Monitor Status Area

3.4 Top Sample

3.5 Peaks

3.6 Samples and Messages

3.7 Connections


Foxhound 5 » 3. The Monitor Database Page 
3.1 The Monitor Database Menu

The Help 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.

The « Back to Menu link displays the main Foxhound menu page in the current browser window or tab.

The drop-down lets you choose which target database to display.

You can switch between different target databases in the same Monitor page,

You can also open multiple Monitor pages to show the same or different target databases.

Note that when you open multiple Monitor pages for the same target database, they show the same data; they do not represent different sampling sessions.

Also note that offline databases don't appear in this drop-down list because the Monitor page isn't available for them; see the Offline Foxhound Sampling Service (OFSS).

The New Menu link opens the 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 History link opens the 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 to show the options for this sampling session in a new browser window or tab.

The About link opens the About Foxhound page in a new browser window or tab.

The Help link opens the Table of Contents.

The "Mmm Dd yyyy Hh:nn:ss AA Foxhound5" field tells you the current time, and that you're looking at Version 5 of Foxhound... which might be helpful if you print or capture the screen.

the Login / Logout link appears if the Administrator Authentication feature has been enabled. Login means you're in Read-Only mode and you can switch to Administrator mode, and Logout means the opposite. If you don't see either link, you're (effectively) in Administrator mode.


Foxhound 5 » 3. The Monitor Database Page 
3.2 The Monitor Control Panel
The Monitor Control Panel lets you view and control the Foxhound Monitor sampling and display processes. These processes are completely separate:

Sampling Running               

The status field displays

The button lets you stop the sampling session. This doesn't delete the session, it just suspends the sampling process.

When sampling is stopped, all active alerts are cancelled because Foxhound is no longer gathering the information required the check the alert criteria.

The "...Sample Schedule in effect" link may appear instead of the Stop Sampling and Start Sampling buttons. If sampling is under the control of a Sample Schedule, you can't stop and start sampling manually, you have to adjust (or turn off) the schedule on the Monitor Options page.

The button may appear instead of Stop Sampling. The Cancel Request button lets you stop further attempts to start or re-start the sampling session.

If Foxhound can't connect to the target database, it will keep trying forever, unless you press Cancel Request or the connection timeout period is exceeded. See the Foxhound Options page for more information on connection timeouts.

The button tells Foxhound to try to start the sampling session.

The button immediately updates the information displayed on this page without waiting for the regular refresh.

If the display is paused but the sampling session is running, you can press Refresh Display to show the most recent sample without restarting the automatic refresh process.

The (number) in parentheses in the Refresh Display button tells you how many times this page has been refreshed.

The button stops the Monitor page from being refreshed every 10 seconds, and the button starts it again.

Neither button affects the background sampling process one way or the other. For example, if the sampling session is running and you press Disable Refresh and later press Enable Refresh, the displayed data "catches up" with the most recent sample.

The button immediately and permanently deletes Foxhound's current record of peak sample values for this target database, causing the Peaks since section to disappear.

The recording of peak values will automatically restart with the next successful sample and the Peaks section will reappear.

The Reset Peaks button has no effect on the sample data itself, just the record of peak values.

Administrative Tip: Think twice before pressing the button. That's because Foxhound's ability to save or restore old peak values is limited; see Recalculate Peaks.

One reason to reset the peaks is when most of the peaks are so old they're no longer relevant; for example, the peaks predate major improvements made to database performance or they point to samples that have been purged,

Administrative Tip: You can force the Peaks row to be recalculated using all the samples recorded for this target database; see Recalculate Peaks on the Adhoc Queries Help page.


Foxhound 5 » 3. The Monitor Database Page 
3.3 The Monitor Status Area
Status

Machine: Name

Server: Name

Database: Name

Started At:

Running Time:

Cache:

DB DBSpace - Size, Used, Frags, Avail, File

Log DBSpace - Size, Used, Frags, Avail, File

Temp DBSpace - Size, Used, Frags, Avail, File

n Active Alerts [scroll to Active Alert...]

SPs:

Purge:

Favorable?

Software: Version

DB File: Version

GlobalDBID:

Database Disposition

Duplicate Foxhound sampling sessions.


Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Status
The status field displays

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Machine: Name
The Machine: name is the computer or host name of the computer running SQL Anywhere.
The Machine: name is displayed for target databases running on SQL Anywhere 8 and later and is based on the server-level MachineName property.

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Server: Name
The Server: name is the name dynamically assigned at runtime by SQL Anywhere to the executable instance of SQL Anywhere running this target database.
It defaults to be the same as the Database: name, or it can be explicitly set by the -n server name command line parameter:
Command Line                      Server Name  Database Name
-----------------------------     -----------  -------------
dbsrv17 demo1.db                     demo1         demo1
dbsrv17 demo2.db -n ddd2             ddd2          ddd2
dbsrv17 -n sss3 demo3.db             sss3          demo3
dbsrv17 -n sss4 demo4.db -n ddd4     sss4          ddd4

The Server: name is displayed for target databases running on SQL Anywhere 10 and later and is based on the server-level ServerName property.

See also:

Monitor - Database Disposition
Alert #9 Arbiter unreachable
Alert #10 Partner unreachable
Alert #11 ServerName change
Alert #30 Database read-only
Alert #31 Database updatable

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Database: Name
The Database: name is the name dynamically assigned at runtime by SQL Anywhere to this target database.
It defaults to be the same as the file name portion of the database file specification, or it can be set by the -n database name command line parameter for this target database:
Command Line                      Server Name  Database Name
-----------------------------     -----------  -------------
dbsrv17 demo1.db                     demo1         demo1
dbsrv17 demo2.db -n ddd2             ddd2          ddd2
dbsrv17 -n sss3 demo3.db             sss3          demo3
dbsrv17 -n sss4 demo4.db -n ddd4     sss4          ddd4
The Database: name is based on the database-level Name property.

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Started At:
The Started At: field tells you when the target server was started, relative to the computer on which Foxhound is running.
It is possible for individual databases to be started via the START DATABASE statement long after the server was started (and stopped and re-started via STOP and START DATABASE statements). Generally, however, databases are started when the server starts, and the Started At field applies to the database as well as the server.

Started At is displayed for target databases running on SQL Anywhere 8 and later, and is based on the server-level StartTime property.


Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Running Time:
The Running Time: field tells you how long the target server has been running.
It is possible for individual databases to be started via the START DATABASE statement long after the server was started (and stopped and re-started via STOP and START DATABASE statements). Generally, however, databases are started when the server starts, and the Running Time field applies to the database as well as the server.

Running Time is displayed for target databases running on SQL Anywhere 8 and later, and is based on the server-level StartTime property.


Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Cache:
The Cache: and % of Max fields tells you how much RAM the server is using for the database cache, and how current cache size compares with the maximum.
Performance Tip: A small Cache value together with 100% of Max may indicate that insufficient memory in the database cache is a performance bottleneck. 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.

The Cache and % of Max fields are displayed for target databases running on SQL Anywhere 7 and later, and are based on the server-level CurrentCacheSize and MaxCacheSize properties.

See also:

Monitor - Cache Panics, Low Memory, Satisfaction.
Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Low Memory, Cache Satisfaction
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
DB DBSpace - Size, Used, Frags, Avail, File

See also:

Alert #5 Database disk space
Alert #13 File fragmentation

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Log DBSpace - Size, Used, Frags, Avail, File

See also:

Alert #7 Log disk space

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Temp DBSpace - Size, Used, Frags, Avail, File

See also:

Monitor - Temp Space, Rollback Log, Uncommitted
Monitor - Connections - Temp Space, Rollback Log, Uncommitted
Alert #6 Temp disk space
Alert #21 Temp space usage
Alert #22 Conn temp space usage
Alert #32 Rollback log usage
Alert #33 Uncommitted operations
Alert #34 Long uncommitted
Connection Flag #4 Temp space usage
Connection Flag #7 Rollback log usage
Connection Flag #8 Uncommitted operations
Connection Flag #9 Long uncommitted
Connection Flag #23 Total transaction time
AutoDrop #4 Temp space usage

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
n Active Alerts [scroll to Active Alert...]
The n Active Alerts field shows how many Alerts are still active.

You can click on one of the entries in the drop-down list to open a new Sample History page with the Samples and Messages section scrolled down to that Alert.

This is useful when one or more Alerts are no longer visible on the Monitor page even though they are still active.

The text [no Active Alerts] is displayed if no Alerts are still active.


Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
SPs:
The SPs: field displays YYY, NNN or some combination of Ys and Ns to indicate whether or not Foxhound has deployed, and is using, these three stored procedure on the non-OFSS target database: rroad_connection_properties, rroad_database_properties and rroad_engine_properties.
Performance Tip: Those three procedures optimize the Foxhound sampling process for non-OFSS target databases. Without them, Foxhound can still gather samples but it has to work harder to do it; i.e., if SPs isn't all Ys, or at least YNN.

Administrative Tip: Foxhound can't directly install these procedures on readonly target databases. This includes High Availability secondary (mirror) databases and Read-Only Scale Out copy databases.

One workaround is to first start a sampling session on the corresponding primary database so SQL Anywhere will then automatically send the Foxhound SPs to the secondary and copy databases before Foxhound connects to them.

Another workaround is to manually install the Foxhound SPs on the primary database before starting sampling sessions on the readonly databases; see the section below How To Install The Foxhound SPs On Non-OFSS Target Databases.

If you still don't see SPs: YYY for your readonly database, try stopping and restarting the sampling session for that database.

Administrative Tip: For offline (OFSS) subject databases, the SPs: field is shown as three dashes because the code for the three procedures is built in to the OFSS scripts.

How To Install The Foxhound SPs On Non-OFSS Target Databases

Three stored procedures are delivered with Foxhound for installation on your target databases.

These procedures are optional but highly recommended:

  • rroad_connection_properties greatly improves Foxhound performance when the target database has a large number of client connections.

  • rroad_database_properties improves Foxhound performance when gathering database-level properties, as well as enabling these Foxhound display fields:
    • Log Size - based on DB_EXTENDED_PROPERTY ( 'FileSize', 'translog' )
    • Log Used - based on DB_EXTENDED_PROPERTY ( 'FreePages', 'translog' )
    • Temp Size - based on DB_EXTENDED_PROPERTY ( 'FileSize', 'temp' )
    • Temp Used - based on DB_EXTENDED_PROPERTY ( 'FreePages', 'temp' )
    • Temp Frags - based on DB_EXTENDED_PROPERTY ( 'DBFileFragments', 'temp' )

  • rroad_engine_properties improves Foxhound performance when gathering server-level properties.

Foxhound will automatically install and call these stored procedures if it can.

The phrase "if it can" means "the user id with which Foxhound connects to the target database has the RESOURCE authority" as in:

GRANT RESOURCE TO FOXHOUND;
Foxhound will keep trying to install those procedures each time it connects to the target database; e.g., each time sampling is stopped and started, or each time Foxhound is started.
Performance Tip: Here's how to force Foxhound to re-install those procedures on your target database:
  1. Stop Foxhound sampling on your database.

  2. Run these commands via ISQL on your database:

    DROP PROCEDURE rroad_connection_properties;
    DROP PROCEDURE rroad_database_properties;
    DROP PROCEDURE rroad_engine_properties;
    

    You might have to specify the user id that owns the procedures; i.e., the user id that Foxhound uses to connect to your database:

    DROP PROCEDURE FOXHOUND.rroad_connection_properties;
    DROP PROCEDURE FOXHOUND.rroad_database_properties;
    DROP PROCEDURE FOXHOUND.rroad_engine_properties;
    

  3. Start sampling on your target database; Foxhound should re-send those procedures when it discovers they don't exist.

As mentioned above, Monitor performance will suffer without those procedures, especially if the target database has a lot of connections. The Foxhound Monitor page displays SPs YYY if finds the three procedures, and SPs NNN if it doesn't.

. . . but Wait, There Is Another Way!

If you don't want to let the Foxhound user id to have either RESOURCE or DBA privileges on the target database, but you are willing to install those three procedures on the target database yourself, here's how:

(Note: If you choose to use this method, you will have to repeat these steps for each target database you wish to monitor, and again each time you upgrade to a new version or build of Foxhound.)
  1. Create a user id to be used by Foxhound on the target database; e.g.:
    GRANT CONNECT TO FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';
    

  2. Find the following three text files in the Foxhound folder (which defaults to C:\ProgramData\RisingRoad\Foxhound5 on Windows 7):
    rroad_connection_properties.sql
    rroad_database_properties.sql
    rroad_engine_properties.sql
    

  3. Edit each file to specify the user id you chose in step 1; e.g., change {OWNER} to FOXHOUND:
    CREATE PROCEDURE FOXHOUND.rroad_connection_properties() 
    CREATE PROCEDURE FOXHOUND.rroad_engine_properties() 
    CREATE PROCEDURE FOXHOUND.rroad_database_properties(d INTEGER) 
    

  4. Use dbisql to run those files against the target database.

  5. If the Foxhound Monitor is already connected to the target database, click on Stop Sampling, then Start Sampling.

  6. Check to make sure "SPs YYY" appears on the Foxhound Monitor page.

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Purge:
The Purge field summarizes what the Foxhound purge process is set up to do.
It displays "Off" if no purging is enabled, and "After xx day(s)" if one or the other or these options are enabled:
                 Purge all sample data enabled: [x] After [xx] day(s).
   Purge uninteresting connection data enabled: [x] After [xx] day(s).	

You can click on the Purge link to open Monitor Options - 6.6 Purge.

See also:

Monitor Options - 6.6 Purge

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Favorable?
The Favorable? field displays a combination of three Y, N and - characters to indicate whether or not three settings on the target database are (or were) favorable to the recording of data for Foxhound to display on the Monitor, History and Connection History pages:

You can click on the Favorable? link to open Monitor Options - 14. Change Target Settings to change these three settings:

  1. The RememberLastPlan server option controls whether or not anything is displayed in this connection-level field:

    • Last Plan Text

  2. The RememberLastStatement server option controls whether or not anything is displayed in these connection-level fields:

    • Blocked Statement
    • Last Statement

  3. The RequestTiming server option controls whether or not anything is displayed in these connection-level columns:

    • Throughput... Req
    • Waiting Time
    • Busy, Wait, Idle

Administrative Tip: The Favorable? link to the Monitor Options page is not available for offline (OFSS) subject databases because Foxhound is not connected to those databases. Instead, you can directly set the server options yourself on your subject databases.

Performance Tip: Number 2 (RememberLastStatement) is by far the most useful of the three.

Performance Tip: Changes to these server options only affect future samples gathered by Foxhound; there is no way to go back and "fix" historical data.

Performance Tip: Changes to these server options on a High Availability primary database or a Read-Only Scale-Out root database will not be automatically transmitted to a secondary or copy database.

However, since these are server options rather than database options, you can use the Favorable? field on the secondary or copy database to make the same changes even though the Foxhound connections to those databases are read-only.

Performance Tip: After a High Availability failover, it is possible for the Favorable? field on the Monitor page for the secondary database to show different values from the Change Target Settings section on the Monitor Options page for the "same" database (which isn't actually the same any more).

If the target database is a High Availability secondary database and a failover occurs (primary fails, secondary takes over), the Favorable Current Settings? column may show [not available].

The reason for this is that the Monitor Options - Change Target Settings section attempts to open a new connection to the secondary database, and the database that used to be the secondary is now the primary so there's no secondary database available. The Foxhound sampling session, however, remains connected to the original database (once the secondary, now the primary) so there is an apparent inconsistency between Favorable Current Settings? showing [not available], and the Foxhound Monitor page showing that everything is OK.

But wait, there's more! If the failed database that was the original primary database is restarted, it will become the new secondary database, and the Favorable Current Settings? column will show actual values rather than [not available]... but those values will be coming from a different database than the Foxhound sampling session is showing. That's because the Monitor Options - Change Target Settings section opens a new connection to the secondary database, and the Foxhound Monitor session remains connected to the original database (once the secondary, now the primary).

The RememberLastStatement server option is supported by target databases running on SQL Anywhere 8 and later, while RememberLastPlan and RequestTiming are supported by SQL Anywhere 10 and later.

See also:

Monitor Options - 14. Change Target Settings

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Software: Version
The Software: Version field tells you what version and build number of SQL Anywhere software is being used to run the target database.
Server version is based on the server-level ProductVersion property.

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
DB File: Version
The DB File: Version field shows the version and build number of the SQL Anywhere software that was used to initialize the target database file.
For databases created with SQL Anywhere 5.5 through 8 the DB File: Version is determined by a variety of methods. In some cases the result may be imprecise, especially for databases that have been processed by the dbupgrade utility.

For database files created with SQL Anywhere 9 and later the DB File: Version is based on the SYSHISTORY table.


Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
GlobalDBID:
The GlobalDBID field is displayed if the SET OPTION PUBLIC.global_database_id statement has been used set the GlobalDBId database property to a non-default value.
The GlobalDBId value is used as the partition number for columns using the DEFAULT GLOBAL AUTOINCREMENT attribute.

It also serves to uniquely identify the database in MobiLink synchronization and SQL Remote replication setups.

The value 0 is often used for the consolidated database, and the values 1, 2, 3... are used for remote databases.

The GlobalDBID is available for adhoc queries; e.g.:

SELECT TOP 1 GlobalDBID
  FROM sample_detail
 WHERE sampling_id = 23
 ORDER BY sample_set_number DESC;

          GlobalDBID 
-------------------- 
                 123 

GlobalDBID is displayed for target databases running on SQL Anywhere 7 and later, and is based on the database-level GlobalDBID property.


Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Database Disposition
The untitled "database disposition" field describes how this target database appears to client connections and its relationship to other databases in a High Availability or Read-Only Scale-Out setup.
Updatable primary database. Arbiter is connected. Partner is connected, synchronized.

Read-only secondary (mirror) database. Arbiter is connected. Partner is connected, synchronized.

Read-only copy database. 

Read-only database.

Updatable database. [default, not displayed]

The database disposition field is displayed for target databases running on SQL Anywhere 8 and later.

For target databases running on SQL Anywhere 8 through 10 it is based on the database-level ReadOnly property, and for SQL Anywhere 11 and later it is based on the database-level properties ArbiterState, MirrorState, PartnerState and ReadOnly.

See also:

Monitor - Server Name
Alert #9 Arbiter unreachable
Alert #10 Partner unreachable
Alert #11 ServerName change
Alert #30 Database read-only
Alert #31 Database updatable

Foxhound 5 » 3. The Monitor Database Page » 3.3 The Monitor Status Area 
Duplicate Foxhound sampling sessions.
This warning message appears on the Monitor page when two or more separate Foxhound sampling sessions exist for the same target database.
 Duplicate Foxhound sampling sessions. There is more than one Foxhound connection to this target database. 

This can happen when two different Foxhound connection strings are created pointing to the same target database, or a ODBC DSN is used as well as a Foxhound connection string. This is almost always a mistake, and it can lead to multiple Alert email messages when the duplicate sampling sessions all detect the same condition; e.g., Alert #1 Database unresponsive.

Foxhound checks for duplicate sampling sessions by counting the number of target database connections with connection names that begin with 'Foxhound-Monitor-'. If you have used section 15. Monitor Connection Settings of the Foxhound Monitor Options page to change the format of those connection names, Foxhound will not detect the duplication.

Administrative Tip: This message is not displayed for any High Availability database because it is a frequent practice to have separate sampling sessions for the underlying partner servers as well as the logical primary and secondary (mirror) servers.

Specifically, this message is not displayed if the Database Disposition is "Updatable primary database ..." or "Read-only secondary (mirror) database ...".

Administrative Tip: This message is not displayed for offline (OFSS) databases because Foxhound doesn't bother checking for direct connections to offline databases; they are contradictory in nature, and if they exist they're probably being used for testing purposes (e.g., to check that OFSS sampling is working ok).

See also the Offline Foxhound Sampling Service (OFSS).

Administrative Tip: If the optional Foxhound Administrator Authentication feature is enabled, Foxhound may not display the "Duplicate Foxhound sampling sessions" message even though multiple "Foxhound-Monitor-nnnn" connections DO appear in the Connections section.

This is done to avoid "false positive" messages caused by bogus but harmless duplicate connections from Foxhound to target databases. Those bogus duplicate connections are the result of a suspected bug in the SQL Anywhere server used to run Foxhound.

The Duplicate Foxhound sampling sessions message is displayed for target databases running on SQL Anywhere 8 and later, and is based on the connection-level Name property.


Foxhound 5 » 3. The Monitor Database Page 
3.4 Top Sample

The Top Sample section shows the database and server data from the most recent successful sample.

[9,9999,99] Adhoc Reporting Primary Keys

Top Sample Old Age

Interval

Response... Heartbeat, Sample, Ping

Throughput... Req, Commits, Bytes

Conns / Parent, Child Conns

Executing, Idle, Waiting Conns

Active Req, Max Req, Unsch Req

Conn Flags Count

Locks Held, Conns Blocked, Waiting Time

CPU

Temp Space, Rollback Log, Uncommitted

Cache Panics, Low Memory, Satisfaction

Checkpoints, Checkpoint Urgency, Recovery Urgency

Disk/Cache: Internal Index, Leaf, Table

Incomplete Reads, Writes

Disk Reads, Disk Writes, Log Writes

Index Adds, Lookups, Satisfaction

Full Index Comps

A "successful sample" is one that actually shows performance data rather than a message like "Database server not found".

Note: The data shown in the Top Sample section might be out of date when compared with the rows shown in Samples and Messages; in fact, it might not even be visible in the Samples and Messages section.

That can happen when the successful sample shown in the Top Sample section is followed chronologically by so many messages and unsuccessful samples (e.g., Database not found) that it doesn't appear on-screen in the Samples and Messages section.

However, the connections shown at the bottom of the Monitor page do correspond with the data shown in the Top Sample.

The Top Sample section consists of 4 or 5 lines:

The other columns are highlighted according to the values in the Peaks section, as follows:

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.

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
[9,9999,99] Adhoc Reporting Primary Keys

The two or three numbers [in square brackets] are important primary key values for writing adhoc queries using All Programs - Foxhound5 - 2 Adhoc Query via ISQL.

The first number in [9,9999,99] is the sampling_id which uniquely identifies the target database in adhoc queries like this:

SELECT * FROM sampling_options WHERE sampling_id = 9;
SELECT * FROM alerts_criteria  WHERE sampling_id = 9;

The second number in [9,9999,99] is generically called the "locator_number" and it is the globally unique primary key for all rows in all associated tables for all target databases.

The meaning of the "locator_number" depends on the type of data displayed in each row on the Monitor and History pages; for the purposes of adhoc queries it is used as primary key value for WHERE clauses and the first number (sampling_id) isn't required:

SELECT * FROM alert                   WHERE alert_occurrence           = 9999;
SELECT * FROM alert_cancelled         WHERE alert_cancelled_occurrence = 9999;
SELECT * FROM all_clear               WHERE all_clear_occurrence       = 9999;
SELECT * FROM autodropped_connection  WHERE autodrop_occurrence        = 9999;
SELECT * FROM email_failure           WHERE email_failure_occurrence   = 9999;
SELECT * FROM ping_log                WHERE ping_id                    = 9999;
SELECT * FROM sample_connection       WHERE sample_set_number          = 9999;
SELECT * FROM sample_detail           WHERE sample_set_number          = 9999;
SELECT * FROM sample_header           WHERE sample_set_number          = 9999;

The third number only appears when a server message is displayed at the top of the Samples and Messages section on the Monitor and History pages:

Samples and
Messages
[14,43562,3925]
10:18:33 AM   -- I. Finished checkpoint of "ddd" (ddd. db) at Fri Jul 12 2019 10:18 Help  (239ms) 

For the purposes of adhoc queries, the third number in [14,43562,3925] is the primary key for server_message, and the first and second numbers (sampling_id and locator_number) aren't required:

SELECT * FROM server_message WHERE inserted_order = 3925;

inserted_order,sampling_id,server_started_at,locator_number,interval_msec,msg_id,msg_text,msg_time,msg_severity,msg_category,msg_database
3925,14,'2019-07-07 10:14:59.765',43562,239,795,Finished checkpoint of "ddd" (ddd.db) at Fri Jul 12 2019 10:18,'2019-07-12 10:18:33.734','INFO','CHKPT','ddd'

Note that server_message.inserted_order column is created as DEFAULT AUTOINCREMENT, so the second number (locator_number) is not actually required to identify server_message rows.

See also:

Adhoc Queries

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Top Sample
The Top Sample column shows the date/time that Foxhound recorded the most recent successful sample.
You can click on the link to open the History page in a separate browser window or tab, scrolled to the associated sample.

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Top Sample Old Age
If the Top Sample timestamp is 15 seconds or older, the age of the sample is displayed in (parentheses) underneath the timestamp.

If sampling is enabled, the sample age is highlighted like this  (25.7s)  until it grows longer than one minute when the highlighting changes to this  (1m 6.3s) .

Performance Tip:


Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Interval
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.

See also:

Alert #1 Database unresponsive
Alert #35 Separate ping failed

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Response... Heartbeat, Sample, Ping
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.

Performance Tip: Heartbeat time may be an unrealistically small measurement of response time when it is calculated by the Offline Foxhound Sample Service (OFSS). That's because the OFSS heartbeat code runs on an internal event connection inside the subject database rather than on an external client server connection from the Foxhound monitor.

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

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();
SELECT * INTO #temp3 FROM rroad_connection_properties();
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;
Performance Tip: Sample Time may not be an unrealistically small measurement of response time when it is calculated by the Offline Foxhound Sample Service (OFSS). That's because the OFSS sample time is calculated by an internal event connection inside the subject database rather than on an external client server connection from the Foxhound monitor. Also, some of the processing required to store the sample in the Foxhound database is not included in the OFSS sample time.

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:

  1. 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.

  2. 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.

  3. As an alternative to on/off Sample Schedule settings, ping-only sampling may be specified at various times of the day.

    For example, ping-only sampling might be scheduled during the overnight hours

    • when a large connection pool is mostly idle, or

    • when a heavy load is expected and nobody much cares about performance.

Ping time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.

Note: Ping time is not calculated by the Offline Foxhound Sample Service (OFSS).

See also:

Monitor Options - Ping-Only Sampling
Monitor Options - Include Ping
Monitor Options - Sample Schedule 'P' characters
Alert #1 Database unresponsive
Alert #2 Long heartbeat
Alert #3 Long sample time
Alert #35 Separate ping failed

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Throughput... Req, Commits, Bytes
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 7 and later. It is based on the server-level BytesReceived and BytesSent properties for target databases running on SQL Anywhere 7 through 12, and on the database-level BytesReceived and BytesSent properties for SQL Anywhere 16 and later.


Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Conns / Parent, Child Conns
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:

See also:

Monitor - Connections - Conn #, User, OS User, IP, Name
Monitor - Connections - CPU, Child Conns
Alert #26 Connections
Connection Flag #10 Child connections
Connection Flag #19 User id usage
Connection Flag #20 OS user usage
Connection Flag #21 IP address usage
Connection Flag #22 Connection name usage

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Executing, Idle, Waiting Conns
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:

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:

The Executing, Idle, Waiting Conns columns are displayed for target databases running on SQL Anywhere 9 and later.

See also:

Monitor - Locks Held, Conns Blocked, Waiting Time
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Active Req, Max Req, Unsch Req

Active Req is the number of server workers that are currently handling client-side requests. SQL Anywhere does not need a separate thread for each connection, just each active request.

A "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 cannot exceed Max Req, and it includes connections that are blocked.

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 dbsrv -gn option).

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 is the number of requests that are waiting in the queue and have not been picked up for servicing yet.

Unsch Req is based on the server-level UnschReq property.

Caution: Active Req, Max Req and Unsch Req values may appear inflated if there is more than one database running on the same SQL Anywhere server. That's because they include requests for all databases running on the server, not just this one target database.

Performance Tips:

See also:

Alert #14 Unscheduled requests

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Conn Flags Count
Conn Flags Count is the total number of Connection Flags that are currently set for all connections.
Performance Tip: A large Conn Flags Count may indicate that one or more performance bottlenecks may be found at the connection level.

Administrative Tip: You can force the connection flags to be recalculated using all the samples recorded for this target database; see Recalculate Connection Flags on the Adhoc Queries Help page.

Conn Flags Count is calculated for target databases running on all versions of SQL Anywhere that are supported by Foxhound.

See also:

Adhoc Queries - Recalculate Connection Flags.
Monitor Options - Connection Flag Settings
Monitor - Connections - Flags

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Locks Held, Conns Blocked, Waiting Time
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 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 10 and later, and is based on the sum of the connection-level ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties.

See also:

Monitor - Executing, Idle, Waiting Conns
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
CPU
The CPU column shows two values: the percent used during the preceding interval (nn%) and the number of processors used (of n).

The CPU percentage is 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.

Performance Tip: The CPU time and the processor count are measured at the SQL Anywhere server level and they include all the databases being run on that server.

If the server-level CPU percentage is much higher than the sum of all the connection-level CPU values, the time may be going to connections running on some other database sharing the same 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 7 and later, and are based on the server-level NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU properties.

See also:

Monitor - Connections - CPU, Child Conns
Alert #4 CPU usage
Alert #27 Connection CPU
Connection Flag #5 CPU usage
Connection Flag #24 Total CPU time
AutoDrop #5 CPU usage

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Temp Space, Rollback Log, Uncommitted
The Temp Space column shows the total amount of temporary 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 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 space any individual connection can use on a target database running on SQL Anywhere 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 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 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 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.

See also:

Monitor - Temp DBSpace - Size, Used, Frags, Avail, File
Monitor - Connections - Temp Space, Rollback Log, Uncommitted
Alert #6 Temp disk space
Alert #21 Temp space usage
Alert #22 Conn temp space usage
Alert #32 Rollback log usage
Alert #33 Uncommitted operations
Alert #34 Long uncommitted
Connection Flag #4 Temp space usage
Connection Flag #7 Rollback log usage
Connection Flag #8 Uncommitted operations
Connection Flag #9 Long uncommitted
Connection Flag #23 Total transaction time
AutoDrop #4 Temp space usage

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Cache Panics, Low Memory, Satisfaction
Cache Panics is the number of times the target server failed to find a cache page to allocate in the previous interval.
Caution: Cache Panics values may appear inflated if there is more than one database running on the same SQL Anywhere server. That's because they include all databases running on the server, not just this one target database.

Cache Panics is displayed for target databases running on SQL Anywhere 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 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:

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 5 and 6, and on the database-level CacheHits and CacheRead properties for SQL Anywhere 7 and later.

See also:

Monitor - Cache
Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Low Memory, Cache Satisfaction
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Checkpoints, Checkpoint Urgency, Recovery Urgency
Checkpoints is the number of checkpoint operations that have been executed 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.

Performance Tip: If you see an "storm" of checkpoint operations, they may be caused by certain SQL statements (such as LOAD TABLE) that have "checkpoint" as a side-effect; here's the full list:

Checkpoints is based on the server-level Chkpt property for target databases running on SQL Anywhere 5 and 6, and on the database-level Chkpt property for SQL Anywhere 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 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 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.

See also:

Alert #17 Checkpoint urgency
Alert #18 Recovery urgency

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Disk/Cache: Internal Index, Leaf, 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 in the previous interval.
Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere 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 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 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.

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%".

See also:

Monitor - Cache
Monitor - Cache Panics, Low Memory, Satisfaction.
Monitor - Connections - Low Memory, Cache Satisfaction
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Incomplete Reads, Writes
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 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.

Incomplete Writes is displayed for target databases running on SQL Anywhere 17, and is based on the database-level CurrWrite property.

See also:

Monitor - Disk Reads, Disk Writes, Log Writes
Monitor - Connections - Disk Reads, Disk Writes, Log Writes
Alert #15 Incomplete I/O
Alert #16 I/O operations
Connection Flag #12 Disk read rate
Connection Flag #13 Disk write rate
Connection Flag #14 Log write rate
Connection Flag #25 Total disk reads
Connection Flag #26 Total disk writes
Connection Flag #27 Total log writes

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Disk Reads, Disk Writes, Log Writes
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 5 and 6, and on the database-level DiskRead property for SQL Anywhere 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 5 and 6, and on the database-level DiskWrite property for SQL Anywhere 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 5 and 6, and on the database-level LogWrite property for SQL Anywhere 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.

See also:

Monitor - Incomplete Reads, Writes
Monitor - Connections - Disk Reads, Disk Writes, Log Writes
Alert #15 Incomplete I/O
Alert #16 I/O operations
Connection Flag #12 Disk read rate
Connection Flag #13 Disk write rate
Connection Flag #14 Log write rate
Connection Flag #25 Total disk reads
Connection Flag #26 Total disk writes
Connection Flag #27 Total log writes

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Index Adds, Lookups, Satisfaction
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.
It may also indicate that a large number of row updates are changing the values of index columns, causing new index entries to be added even though no new rows are being inserted.

Index column updates may be performed directly, or indirectly when some other non-indexed columns are updated but there is an index on a DEFAULT TIMESTAMP column that is implicitly changed when any other columns are updated.

Index Adds is based on the server-level IndAdd property for target databases running on SQL Anywhere 5.5 and 6, and on the database-level IndAdd property for target databases running on SQL Anywhere 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 5.5 and 6, and on the database-level IndLookup property for target databases running on SQL Anywhere 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: A small Index Satisfaction value (less than 100%) may indicate that a performance bottleneck is caused by full index comparisons.
A full index comparison occurs when data stored in the physical index pages is not sufficient to satisfy an index lookup, and additional index data must be retrieved from the physical table pages.

Index Satisfaction is based on the server-level IndLookup and FullCompare properties for target databases running on SQL Anywhere 5.5 and 6, and on the database-level IndLookup and FullCompare properties for target databases running on SQL Anywhere 7 and later.

See also:

Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Full Index Comps
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Index Adds, Lookups, Satisfaction
Monitor - Connections - Full Index Comps
Connection Flag #15 Index add rate
Connection Flag #16 Index lookup rate
Connection Flag #17 Index satisfaction
Connection Flag #18 Full index comp rate
Connection Flag #28 Total index adds
Connection Flag #29 Total index lookups
Connection Flag #30 Total full index comps
SQL Anywhere's Graphical Plan With Statistics feature

Foxhound 5 » 3. The Monitor Database Page » 3.4 Top Sample 
Full Index Comps
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: A large Full Index Comps value (more than zero) may indicate that a performance bottleneck is caused by full index comparisons.
A full index comparison occurs when data stored in the physical index pages is not sufficient to satisfy an index lookup, and additional index data must be retrieved from the physical table pages.

Full Index Comps is based on the server-level FullCompare property for target databases running on SQL Anywhere 5.5 and 6, and on the database-level FullCompare property for target databases running on SQL Anywhere 7 and later.

See also:

Monitor - Connections - Full Index Comps
Monitor - Index Adds, Lookups, Satisfaction
Monitor - Connections - Index Adds, Lookups, Satisfaction
Connection Flag #16 Index lookup rate
Connection Flag #17 Index satisfaction
Connection Flag #18 Full index comp rate
Connection Flag #30 Total full index comps
SQL Anywhere's Graphical Plan With Statistics feature


Foxhound 5 » 3. The Monitor Database Page 
3.5 Peaks

The Peaks section consists of a single line of peak values, where "peak" means "largest" for most values, and "smallest" for the Cache Satisfaction and Index Satisfaction percentages.

Peaks since

Response... Heartbeat, Sample, Ping Peaks

Throughput... Req, Commits, Bytes Peaks

Conns / Parent, Child Conns Peaks

Executing, Idle, Waiting Conns Peaks

Active Req, Max Req, Unsch Req Peaks

Conn Flags Count

Locks Held, Conns Blocked, Waiting Time Peaks

CPU Peaks

Temp Space, Rollback Log, Uncommitted Peaks

Cache Panics, Low Memory, Satisfaction Peaks

Checkpoints, Checkpoint Urgency, Recovery Urgency Peaks

Disk/Cache: Internal Index, Leaf, Table Peaks

Incomplete Reads, Writes Peaks

Disk Reads, Disk Writes, Log Writes Peaks

Index Adds, Lookups, Satisfaction Peaks

Full Index Comps Peak

Administrative Tip: You can force the Peaks row to be recalculated using all the samples recorded for this target database; see Recalculate Peaks on the Adhoc Queries Help page.

You can click on an individual peak value to open the History page in a separate browser window or tab, scrolled to the sample holding that peak value.

Note: The Peaks section will disappear for a short time when you press the button. The recording of peak values will automatically restart with the next successful sample and the Peaks section will reappear.

Performance Tip: Think twice before pressing the button. That's because Foxhound's ability to save or restore old peak values is limited; see Recalculate Peaks.

One reason to reset the peaks is when most of the peaks are so old they're no longer relevant; for example, the peaks predate major improvements made to database performance or they point to samples that have been purged,

If a peak isn't shown as a hyperlink, the corresponding sample data may have been deleted by the background purge process. See the Foxhound Options page for more information on purging sample data.

Some peak values are displayed as rates (e.g., bytes per second nn/s) rather than counts or amounts like nnk.

The reason for this is that the interval between samples can vary, and a true comparison of "larger versus smaller" should take that into account.

For example, a server that processed Bytes of 5M in an 8 second interval was actually busier than one that processed 6M in a 12 second interval (as far as Bytes is concerned).

Peak values are used to determine almost all of the color highlighting in the other sections (Top Sample and Recent Samples) above and below the Peaks section, as follows:


Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Peaks since
The Peaks since link identifies the earliest sample that was used to compute peak values.
This will be the first sample ever recorded for this database, or the first sample recorded after the most recent pressing of the button.

You can click on this link to open the History page in a separate browser window or tab, scrolled to this sample.

If the Peaks since timestamp isn't shown as a hyperlink, the corresponding sample data may have been deleted by the background purge process. See the Foxhound Options page for more information on purging sample data.

Administrative Tip: You can force the Peaks row to be recalculated using all the samples recorded for this target database; see Recalculate Peaks on the Adhoc Queries Help page.


Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Response... Heartbeat, Sample, Ping Peaks
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 peak shows the longest time 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.

Performance Tip: Heartbeat time may be an unrealistically small measurement of response time when it is calculated by the Offline Foxhound Sample Service (OFSS). That's because the OFSS heartbeat code runs on an internal event connection inside the subject database rather than on an external client server connection from the Foxhound monitor.

Heartbeat time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.

The Response... Sample time peak shows the longest time 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.:

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();
SELECT * INTO #temp3 FROM rroad_connection_properties();
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;
Performance Tip: Sample Time may not be an unrealistically small measurement of response time when it is calculated by the Offline Foxhound Sample Service (OFSS). That's because the OFSS sample time is calculated by an internal event connection inside the subject database rather than on an external client server connection from the Foxhound monitor. Also, some of the processing required to store the sample in the Foxhound database is not included in the OFSS sample time.

Sample time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.

The Response... Ping time peak column shows the longest time 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:

  1. 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.

  2. 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.

  3. As an alternative to on/off Sample Schedule settings, ping-only sampling may be specified at various times of the day.

    For example, ping-only sampling might be scheduled during the overnight hours

    • when a large connection pool is mostly idle, or

    • when a heavy load is expected and nobody much cares about performance.

Ping time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.

Note: Ping time is not calculated by the Offline Foxhound Sample Service (OFSS).

See also:

Monitor Options - Ping-Only Sampling
Monitor Options - Include Ping
Monitor Options - Sample Schedule 'P' characters
Alert #1 Database unresponsive
Alert #2 Long heartbeat
Alert #3 Long sample time
Alert #35 Separate ping failed

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Throughput... Req, Commits, Bytes Peaks
Throughput, also known as bandwidth, is a measure of how much work the database has performed:

The Throughput... Req peak is highest rate at which the server started processing a new request or resumed processing an existing request in a single 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)

The Throughput... Commits peak is the highest rate at which COMMIT operations were executed in a single 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.

The Throughput... Bytes peak is the highest rate at which data was sent and received for client server connections in a single 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 7 and later. It is based on the server-level BytesReceived and BytesSent properties for target databases running on SQL Anywhere 7 through 12, and on the database-level BytesReceived and BytesSent properties for SQL Anywhere 16 and later.


Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Conns / Parent, Child Conns Peaks
The Conns peak shows the largest number of connections that existed on a target database running on SQL Anywhere 11 or earlier, with Foxhound itself counting as 1 connection.

The Parent, Child Conns peaks show the largest numbers of primary (parent) and internal (child) connections on a target database 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:

See also:

Monitor - Connections - Conn #, User, OS User, IP, Name
Monitor - Connections - CPU, Child Conns
Alert #26 Connections
Connection Flag #10 Child connections
Connection Flag #19 User id usage
Connection Flag #20 OS user usage
Connection Flag #21 IP address usage
Connection Flag #22 Connection name usage

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Executing, Idle, Waiting Conns Peaks
The Executing, Idle, Waiting Conns peaks show the largest numbers of connections that 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:

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:

The Executing, Idle, Waiting Conns columns are displayed for target databases running on SQL Anywhere 9 and later.

See also:

Monitor - Locks Held, Conns Blocked, Waiting Time
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Active Req, Max Req, Unsch Req Peaks

Active Req is the number of server workers that are currently handling client-side requests. SQL Anywhere does not need a separate thread for each connection, just each active request.

A "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 cannot exceed Max Req, and it includes connections that are blocked.

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 dbsrv -gn option).

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 is the number of requests that are waiting in the queue and have not been picked up for servicing yet.

Unsch Req is based on the server-level UnschReq property.

Caution: Active Req, Max Req and Unsch Req values may appear inflated if there is more than one database running on the same SQL Anywhere server. That's because they include requests for all databases running on the server, not just this one target database.

Performance Tips:

See also:

Alert #14 Unscheduled requests

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Conn Flags Count
Conn Flags Count is the largest total number of Connection Flags set for all connections.
Performance Tip: A large Conn Flags Count may indicate that one or more performance bottlenecks may be found at the connection level.

Administrative Tip: You can force the connection flags to be recalculated using all the samples recorded for this target database; see Recalculate Connection Flags on the Adhoc Queries Help page.

Conn Flags Count is calculated for target databases running on all versions of SQL Anywhere that are supported by Foxhound.

See also:

Adhoc Queries - Recalculate Connection Flags.
Monitor Options - Connection Flag Settings
Monitor - Connections - Flags

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Locks Held, Conns Blocked, Waiting Time Peaks
The Locks Held peak is the largest 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.

The Conns Blocked peak is the largest number of connections that were blocked. The Conns Blocked column will be empty if connection details are not being recorded; see Foxhound Options - Connection sampling threshold.

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 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.

The Waiting Time peak is the largest value of 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 10 and later, and is based on the sum of the connection-level ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties.

See also:

Monitor - Executing, Idle, Waiting Conns
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
CPU Peaks
The CPU peak section shows the largest percentage of CPU time used during a single interval (nn%) plus the largest number of processors used (of n).

The CPU percentage is 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.

Performance Tip: The CPU time and the processor count are measured at the SQL Anywhere server level and they include all the databases being run on that server.

If the server-level CPU percentage is much higher than the sum of all the connection-level CPU values, the time may be going to connections running on some other database sharing the same 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 7 and later, and are based on the server-level NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU properties.

See also:

Monitor - Connections - CPU, Child Conns
Alert #4 CPU usage
Alert #27 Connection CPU
Connection Flag #5 CPU usage
Connection Flag #24 Total CPU time
AutoDrop #5 CPU usage

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Temp Space, Rollback Log, Uncommitted Peaks
The Temp Space peak shows the largest amount of temporary 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 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 space any individual connection can use on a target database running on SQL Anywhere 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 8 and later and is based on the database-level PageSize property and the sum of the connection-level TempFilePages property.

The Rollback Log peak shows the largest amount of space in the rollback log 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 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.

The Uncommitted peak shows the largest number of operations 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 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.

See also:

Monitor - Temp DBSpace - Size, Used, Frags, Avail, File
Monitor - Connections - Temp Space, Rollback Log, Uncommitted
Alert #6 Temp disk space
Alert #21 Temp space usage
Alert #22 Conn temp space usage
Alert #32 Rollback log usage
Alert #33 Uncommitted operations
Alert #34 Long uncommitted
Connection Flag #4 Temp space usage
Connection Flag #7 Rollback log usage
Connection Flag #8 Uncommitted operations
Connection Flag #9 Long uncommitted
Connection Flag #23 Total transaction time
AutoDrop #4 Temp space usage

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Cache Panics, Low Memory, Satisfaction Peaks
Cache Panics is the highest rate at which the target server failed to find a cache page to allocate.
Caution: Cache Panics values may appear inflated if there is more than one database running on the same SQL Anywhere server. That's because they include all databases running on the server, not just this one target database.

Cache Panics is displayed for target databases running on SQL Anywhere 9 and later, and is based on the server-level CachePanics property.

Low Memory is the highest rate at which the target server had to change a query execution plan because cache memory ran low.

Low Memory is displayed for target databases running on SQL Anywhere 8 or later and is based on the database-level QueryLowMemoryStrategy property.

Cache Satisfaction is the lowest percentage of times a database page lookup was satisfied by the cache.

Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck:

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 5 and 6, and on the database-level CacheHits and CacheRead properties for SQL Anywhere 7 and later.

See also:

Monitor - Cache
Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Low Memory, Cache Satisfaction
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Checkpoints, Checkpoint Urgency, Recovery Urgency Peaks
Checkpoints is the highest number of CHECKPOINT operations that have been executed by the server in any sample.

Checkpoint Urgency is the highest percentage of the SET OPTION PUBLIC.checkpoint_time that was reached in any sample.

Checkpoint Urgency is displayed for target databases running on SQL Anywhere 7 and later, and is based on the database-level CheckpointUrgency property.

Recovery Urgency is the highest estimated time required to recover the database when restarting after an abnormal stoppage, expressed as a percentage of the SET OPTION PUBLIC.recovery_time, that was reached in any sample.

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 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.

See also:

Alert #17 Checkpoint urgency
Alert #18 Recovery urgency

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Disk/Cache: Internal Index, Leaf, Table Peaks
The Disk/Cache: Internal Index peak is the highest percentage ratio between the number of index internal-node pages that have been read from disk and from the cache.
Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere 7 and later, and is based on the database-level DiskReadIndInt and CacheReadIndInt properties.

The Disk/Cache: Leaf peak is the highest percentage ratio between the number of index leaf pages that have been read from disk and from the cache.

Disk/Cache: Leaf is displayed for target databases running on SQL Anywhere 7 and later, and is based on the database-level DiskReadIndLeaf and CacheReadIndLeaf properties.

The Disk/Cache: Table peak is the highest percentage ratio between the number of table pages that have been read from disk and from the cache.

Disk/Cache: Table is displayed for target databases running on SQL Anywhere 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.

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%".

See also:

Monitor - Cache
Monitor - Cache Panics, Low Memory, Satisfaction.
Monitor - Connections - Low Memory, Cache Satisfaction
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Incomplete Reads, Writes Peaks
The Incomplete Reads peak is the highest 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 17, and is based on the database-level CurrRead property.

The Incomplete Writes peak is the highest 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.

Incomplete Writes is displayed for target databases running on SQL Anywhere 17, and is based on the database-level CurrWrite property.

See also:

Monitor - Disk Reads, Disk Writes, Log Writes
Monitor - Connections - Disk Reads, Disk Writes, Log Writes
Alert #15 Incomplete I/O
Alert #16 I/O operations
Connection Flag #12 Disk read rate
Connection Flag #13 Disk write rate
Connection Flag #14 Log write rate
Connection Flag #25 Total disk reads
Connection Flag #26 Total disk writes
Connection Flag #27 Total log writes

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Disk Reads, Disk Writes, Log Writes Peaks
The Disk Reads peak is the highest rate at which pages were read from disk.
Disk Reads is based on the server-level DiskRead property for target databases running on SQL Anywhere 5 and 6, and on the database-level DiskRead property for SQL Anywhere 7 and later.

The Disk Writes peak is the highest rate at which modified pages were written to disk.

Disk Writes is based on the server-level DiskWrite property for target databases running on SQL Anywhere 5 and 6, and on the database-level DiskWrite property for SQL Anywhere 7 and later.

The Log Writes peak the highest rate at which log pages were written to the transaction log.

Log Writes is based on the server-level LogWrite property for target databases running on SQL Anywhere 5 and 6, and on the database-level LogWrite property for SQL Anywhere 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.

See also:

Monitor - Incomplete Reads, Writes
Monitor - Connections - Disk Reads, Disk Writes, Log Writes
Alert #15 Incomplete I/O
Alert #16 I/O operations
Connection Flag #12 Disk read rate
Connection Flag #13 Disk write rate
Connection Flag #14 Log write rate
Connection Flag #25 Total disk reads
Connection Flag #26 Total disk writes
Connection Flag #27 Total log writes

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Index Adds, Lookups, Satisfaction Peaks
The Index Adds peak is the highest rate at which entries were added to indexes.
Performance Tip: A large Index Adds value may indicate that a large number of row inserts is a performance bottleneck.
It may also indicate that a large number of row updates are changing the values of index columns, causing new index entries to be added even though no new rows are being inserted.

Index column updates may be performed directly, or indirectly when some other non-indexed columns are updated but there is an index on a DEFAULT TIMESTAMP column that is implicitly changed when any other columns are updated.

Index Adds is based on the server-level IndAdd property for target databases running on SQL Anywhere 5.5 and 6, and on the database-level IndAdd property for target databases running on SQL Anywhere 7 and later.

The Index Lookups peak is the highest rate at which entries were looked up in an index.

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 5.5 and 6, and on the database-level IndLookup property for target databases running on SQL Anywhere 7 and later.

The Index Satisfaction peak is the lowest percentage of times that an index lookup was satisfied by the index without having to retrieve more information from the table data.

Performance Tip: A small Index Satisfaction value (less than 100%) may indicate that a performance bottleneck is caused by full index comparisons.
A full index comparison occurs when data stored in the physical index pages is not sufficient to satisfy an index lookup, and additional index data must be retrieved from the physical table pages.

Index Satisfaction is based on the server-level IndLookup and FullCompare properties for target databases running on SQL Anywhere 5.5 and 6, and on the database-level IndLookup and FullCompare properties for target databases running on SQL Anywhere 7 and later.

See also:

Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Full Index Comps
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Index Adds, Lookups, Satisfaction
Monitor - Connections - Full Index Comps
Connection Flag #15 Index add rate
Connection Flag #16 Index lookup rate
Connection Flag #17 Index satisfaction
Connection Flag #18 Full index comp rate
Connection Flag #28 Total index adds
Connection Flag #29 Total index lookups
Connection Flag #30 Total full index comps
SQL Anywhere's Graphical Plan With Statistics feature

Foxhound 5 » 3. The Monitor Database Page » 3.5 Peaks 
Full Index Comps Peak
The Full Index Comps peak shows the highest rate at which additional information had to be obtained from the table data in order to satisfy an index lookup.
Performance Tip: A large Full Index Comps value (more than zero) may indicate that a performance bottleneck is caused by full index comparisons.
A full index comparison occurs when data stored in the physical index pages is not sufficient to satisfy an index lookup, and additional index data must be retrieved from the physical table pages.

Full Index Comps is based on the server-level FullCompare property for target databases running on SQL Anywhere 5.5 and 6, and on the database-level FullCompare property for target databases running on SQL Anywhere 7 and later.

See also:

Monitor - Connections - Full Index Comps
Monitor - Index Adds, Lookups, Satisfaction
Monitor - Connections - Index Adds, Lookups, Satisfaction
Connection Flag #16 Index lookup rate
Connection Flag #17 Index satisfaction
Connection Flag #18 Full index comp rate
Connection Flag #30 Total full index comps
SQL Anywhere's Graphical Plan With Statistics feature


Foxhound 5 » 3. The Monitor Database Page 
3.6 Samples and Messages

The Samples and Messages section shows the most recent 10 samples and messages. This section is kept up to date when the page is refreshed whereas the Top Sample section may or may not change.

[9,9999,99] Adhoc Reporting Primary Keys

Samples and Messages Column

Interval

Response... Heartbeat, Sample, Ping

Throughput... Req, Commits, Bytes

Conns / Parent, Child Conns

Executing, Idle, Waiting Conns

Active Req, Max Req, Unsch Req

Conn Flags Count

Locks Held, Conns Blocked, Waiting Time

CPU

Temp Space, Rollback Log, Uncommitted

Cache Panics, Low Memory, Satisfaction

Checkpoints, Checkpoint Urgency, Recovery Urgency

Disk/Cache: Internal Index, Leaf, Table

Incomplete Reads, Writes

Disk Reads, Disk Writes, Log Writes

Index Adds, Lookups, Satisfaction

Full Index Comps

Monitor Messages

Server Messages

Alert, All Clear and Cancelled Messages

AutoDropped Messages

Email Failure Messages

The color highlighting in the Samples and Messages section is controlled by values in the Peaks section above it, as follows:


Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
[9,9999,99] Adhoc Reporting Primary Keys

The two or three numbers [in square brackets] are important primary key values for writing adhoc queries using All Programs - Foxhound5 - 2 Adhoc Query via ISQL.

The first number in [9,9999,99] is the sampling_id which uniquely identifies the target database in adhoc queries like this:

SELECT * FROM sampling_options WHERE sampling_id = 9;
SELECT * FROM alerts_criteria  WHERE sampling_id = 9;

The second number in [9,9999,99] is generically called the "locator_number" and it is the globally unique primary key for all rows in all associated tables for all target databases.

The meaning of the "locator_number" depends on the type of data displayed in each row on the Monitor and History pages; for the purposes of adhoc queries it is used as primary key value for WHERE clauses and the first number (sampling_id) isn't required:

SELECT * FROM alert                   WHERE alert_occurrence           = 9999;
SELECT * FROM alert_cancelled         WHERE alert_cancelled_occurrence = 9999;
SELECT * FROM all_clear               WHERE all_clear_occurrence       = 9999;
SELECT * FROM autodropped_connection  WHERE autodrop_occurrence        = 9999;
SELECT * FROM email_failure           WHERE email_failure_occurrence   = 9999;
SELECT * FROM ping_log                WHERE ping_id                    = 9999;
SELECT * FROM sample_connection       WHERE sample_set_number          = 9999;
SELECT * FROM sample_detail           WHERE sample_set_number          = 9999;
SELECT * FROM sample_header           WHERE sample_set_number          = 9999;

The third number only appears when a server message is displayed at the top of the Samples and Messages section on the Monitor and History pages:

Samples and
Messages
[14,43562,3925]
10:18:33 AM   -- I. Finished checkpoint of "ddd" (ddd. db) at Fri Jul 12 2019 10:18 Help  (239ms) 

For the purposes of adhoc queries, the third number in [14,43562,3925] is the primary key for server_message, and the first and second numbers (sampling_id and locator_number) aren't required:

SELECT * FROM server_message WHERE inserted_order = 3925;

inserted_order,sampling_id,server_started_at,locator_number,interval_msec,msg_id,msg_text,msg_time,msg_severity,msg_category,msg_database
3925,14,'2019-07-07 10:14:59.765',43562,239,795,Finished checkpoint of "ddd" (ddd.db) at Fri Jul 12 2019 10:18,'2019-07-12 10:18:33.734','INFO','CHKPT','ddd'

Note that server_message.inserted_order column is created as DEFAULT AUTOINCREMENT, so the second number (locator_number) is not actually required to identify server_message rows.

See also:

Adhoc Queries

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Samples and Messages
The Samples and Messages column shows the date/time that Foxhound recorded each sample or message.

You can click on these links to open the History page in a separate browser window or tab, scrolled to the associated sample.


Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Interval
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.

See also:

Alert #1 Database unresponsive
Alert #35 Separate ping failed

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Response... Heartbeat, Sample, Ping
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.

Performance Tip: Heartbeat time may be an unrealistically small measurement of response time when it is calculated by the Offline Foxhound Sample Service (OFSS). That's because the OFSS heartbeat code runs on an internal event connection inside the subject database rather than on an external client server connection from the Foxhound monitor.

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

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();
SELECT * INTO #temp3 FROM rroad_connection_properties();
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;
Performance Tip: Sample Time may not be an unrealistically small measurement of response time when it is calculated by the Offline Foxhound Sample Service (OFSS). That's because the OFSS sample time is calculated by an internal event connection inside the subject database rather than on an external client server connection from the Foxhound monitor. Also, some of the processing required to store the sample in the Foxhound database is not included in the OFSS sample time.

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:

  1. 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.

  2. 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.

  3. As an alternative to on/off Sample Schedule settings, ping-only sampling may be specified at various times of the day.

    For example, ping-only sampling might be scheduled during the overnight hours

    • when a large connection pool is mostly idle, or

    • when a heavy load is expected and nobody much cares about performance.

Ping time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.

Note: Ping time is not calculated by the Offline Foxhound Sample Service (OFSS).

See also:

Monitor Options - Ping-Only Sampling
Monitor Options - Include Ping
Monitor Options - Sample Schedule 'P' characters
Alert #1 Database unresponsive
Alert #2 Long heartbeat
Alert #3 Long sample time
Alert #35 Separate ping failed

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Throughput... Req, Commits, Bytes
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 7 and later. It is based on the server-level BytesReceived and BytesSent properties for target databases running on SQL Anywhere 7 through 12, and on the database-level BytesReceived and BytesSent properties for SQL Anywhere 16 and later.


Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Conns / Parent, Child Conns
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:

See also:

Monitor - Connections - Conn #, User, OS User, IP, Name
Monitor - Connections - CPU, Child Conns
Alert #26 Connections
Connection Flag #10 Child connections
Connection Flag #19 User id usage
Connection Flag #20 OS user usage
Connection Flag #21 IP address usage
Connection Flag #22 Connection name usage

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Executing, Idle, Waiting Conns
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:

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:

The Executing, Idle, Waiting Conns columns are displayed for target databases running on SQL Anywhere 9 and later.

See also:

Monitor - Locks Held, Conns Blocked, Waiting Time
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Active Req, Max Req, Unsch Req

Active Req is the number of server workers that are currently handling client-side requests. SQL Anywhere does not need a separate thread for each connection, just each active request.

A "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 cannot exceed Max Req, and it includes connections that are blocked.

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 dbsrv -gn option).

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 is the number of requests that are waiting in the queue and have not been picked up for servicing yet.

Unsch Req is based on the server-level UnschReq property.

Caution: Active Req, Max Req and Unsch Req values may appear inflated if there is more than one database running on the same SQL Anywhere server. That's because they include requests for all databases running on the server, not just this one target database.

Performance Tips:

See also:

Alert #14 Unscheduled requests

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Conn Flags Count
Conn Flags Count is the total number of Connection Flags that are currently set for all connections.
Performance Tip: A large Conn Flags Count may indicate that one or more performance bottlenecks may be found at the connection level.

Administrative Tip: You can force the connection flags to be recalculated using all the samples recorded for this target database; see Recalculate Connection Flags on the Adhoc Queries Help page.

Conn Flags Count is calculated for target databases running on all versions of SQL Anywhere that are supported by Foxhound.

See also:

Adhoc Queries - Recalculate Connection Flags.
Monitor Options - Connection Flag Settings
Monitor - Connections - Flags

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Locks Held, Conns Blocked, Waiting Time
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 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 10 and later, and is based on the sum of the connection-level ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties.

See also:

Monitor - Executing, Idle, Waiting Conns
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
CPU
The CPU column shows two values: the percent used during the preceding interval (nn%) and the number of processors used (of n).

The CPU percentage is 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.

Performance Tip: The CPU time and the processor count are measured at the SQL Anywhere server level and they include all the databases being run on that server.

If the server-level CPU percentage is much higher than the sum of all the connection-level CPU values, the time may be going to connections running on some other database sharing the same 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 7 and later, and are based on the server-level NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU properties.

See also:

Monitor - Connections - CPU, Child Conns
Alert #4 CPU usage
Alert #27 Connection CPU
Connection Flag #5 CPU usage
Connection Flag #24 Total CPU time
AutoDrop #5 CPU usage

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Temp Space, Rollback Log, Uncommitted
The Temp Space column shows the total amount of temporary 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 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 space any individual connection can use on a target database running on SQL Anywhere 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 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 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 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.

See also:

Monitor - Temp DBSpace - Size, Used, Frags, Avail, File
Monitor - Connections - Temp Space, Rollback Log, Uncommitted
Alert #6 Temp disk space
Alert #21 Temp space usage
Alert #22 Conn temp space usage
Alert #32 Rollback log usage
Alert #33 Uncommitted operations
Alert #34 Long uncommitted
Connection Flag #4 Temp space usage
Connection Flag #7 Rollback log usage
Connection Flag #8 Uncommitted operations
Connection Flag #9 Long uncommitted
Connection Flag #23 Total transaction time
AutoDrop #4 Temp space usage

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Cache Panics, Low Memory, Satisfaction
Cache Panics is the number of times the target server failed to find a cache page to allocate in the previous interval.
Caution: Cache Panics values may appear inflated if there is more than one database running on the same SQL Anywhere server. That's because they include all databases running on the server, not just this one target database.

Cache Panics is displayed for target databases running on SQL Anywhere 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 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:

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 5 and 6, and on the database-level CacheHits and CacheRead properties for SQL Anywhere 7 and later.

See also:

Monitor - Cache
Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Low Memory, Cache Satisfaction
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Checkpoints, Checkpoint Urgency, Recovery Urgency
Checkpoints is the number of checkpoint operations that have been executed 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.

Performance Tip: If you see an "storm" of checkpoint operations, they may be caused by certain SQL statements (such as LOAD TABLE) that have "checkpoint" as a side-effect; here's the full list:

Checkpoints is based on the server-level Chkpt property for target databases running on SQL Anywhere 5 and 6, and on the database-level Chkpt property for SQL Anywhere 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 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 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.

See also:

Alert #17 Checkpoint urgency
Alert #18 Recovery urgency

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Disk/Cache: Internal Index, Leaf, 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 in the previous interval.
Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere 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 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 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.

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%".

See also:

Monitor - Cache
Monitor - Cache Panics, Low Memory, Satisfaction.
Monitor - Connections - Low Memory, Cache Satisfaction
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Incomplete Reads, Writes
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 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.

Incomplete Writes is displayed for target databases running on SQL Anywhere 17, and is based on the database-level CurrWrite property.

See also:

Monitor - Disk Reads, Disk Writes, Log Writes
Monitor - Connections - Disk Reads, Disk Writes, Log Writes
Alert #15 Incomplete I/O
Alert #16 I/O operations
Connection Flag #12 Disk read rate
Connection Flag #13 Disk write rate
Connection Flag #14 Log write rate
Connection Flag #25 Total disk reads
Connection Flag #26 Total disk writes
Connection Flag #27 Total log writes

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Disk Reads, Disk Writes, Log Writes
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 5 and 6, and on the database-level DiskRead property for SQL Anywhere 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 5 and 6, and on the database-level DiskWrite property for SQL Anywhere 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 5 and 6, and on the database-level LogWrite property for SQL Anywhere 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.

See also:

Monitor - Incomplete Reads, Writes
Monitor - Connections - Disk Reads, Disk Writes, Log Writes
Alert #15 Incomplete I/O
Alert #16 I/O operations
Connection Flag #12 Disk read rate
Connection Flag #13 Disk write rate
Connection Flag #14 Log write rate
Connection Flag #25 Total disk reads
Connection Flag #26 Total disk writes
Connection Flag #27 Total log writes

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Index Adds, Lookups, Satisfaction
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.
It may also indicate that a large number of row updates are changing the values of index columns, causing new index entries to be added even though no new rows are being inserted.

Index column updates may be performed directly, or indirectly when some other non-indexed columns are updated but there is an index on a DEFAULT TIMESTAMP column that is implicitly changed when any other columns are updated.

Index Adds is based on the server-level IndAdd property for target databases running on SQL Anywhere 5.5 and 6, and on the database-level IndAdd property for target databases running on SQL Anywhere 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 5.5 and 6, and on the database-level IndLookup property for target databases running on SQL Anywhere 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: A small Index Satisfaction value (less than 100%) may indicate that a performance bottleneck is caused by full index comparisons.
A full index comparison occurs when data stored in the physical index pages is not sufficient to satisfy an index lookup, and additional index data must be retrieved from the physical table pages.

Index Satisfaction is based on the server-level IndLookup and FullCompare properties for target databases running on SQL Anywhere 5.5 and 6, and on the database-level IndLookup and FullCompare properties for target databases running on SQL Anywhere 7 and later.

See also:

Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Full Index Comps
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Index Adds, Lookups, Satisfaction
Monitor - Connections - Full Index Comps
Connection Flag #15 Index add rate
Connection Flag #16 Index lookup rate
Connection Flag #17 Index satisfaction
Connection Flag #18 Full index comp rate
Connection Flag #28 Total index adds
Connection Flag #29 Total index lookups
Connection Flag #30 Total full index comps
SQL Anywhere's Graphical Plan With Statistics feature

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Full Index Comps
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: A large Full Index Comps value (more than zero) may indicate that a performance bottleneck is caused by full index comparisons.
A full index comparison occurs when data stored in the physical index pages is not sufficient to satisfy an index lookup, and additional index data must be retrieved from the physical table pages.

Full Index Comps is based on the server-level FullCompare property for target databases running on SQL Anywhere 5.5 and 6, and on the database-level FullCompare property for target databases running on SQL Anywhere 7 and later.

See also:

Monitor - Connections - Full Index Comps
Monitor - Index Adds, Lookups, Satisfaction
Monitor - Connections - Index Adds, Lookups, Satisfaction
Connection Flag #16 Index lookup rate
Connection Flag #17 Index satisfaction
Connection Flag #18 Full index comp rate
Connection Flag #30 Total full index comps
SQL Anywhere's Graphical Plan With Statistics feature

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Monitor Messages

Whenever the Foxhound Monitor is unable to gather samples it displays a pair of messages on the Monitor and History pages:

For example...

[date-time-6] ... -- Foxhound stopped -- 
[date-time-5] ... -- Foxhound stopped at -- 
[date-time-4] ... -- Sampling stopped -- 
[date-time-3] ... -- Sampling stopped at -- 
[date-time-2] ... -- Database server not found -- 
[date-time-1] ... -- Database server not found at -- 

Here's an alphabetic list of most Monitor Messages:

-- [Adaptive Server Anywhere]Communication link failure: Communication error --
-- [Adaptive Server Anywhere]Communication link failure: Communication error --

Foxhound has failed to connect to a target database that uses an older version of SQL Anywhere such as Adaptive Server Anywhere 6, possibly because the database is not running.

You may also see the message "Error in TCPIP port options" repeatedly appear in a popup dialog box as Foxhound tries over ... and ... over ... again to connect.

Try using ISQL to connect to the target database from the same computer that's running Foxhound.

-- [Adaptive Server Anywhere]Unable to connect to database server: Database server not running --
-- [Adaptive Server Anywhere]Unable to connect to database server: Database server not running at --

Foxhound has failed to connect to a database running on older version of SQL Anywhere, probably Adaptive Server Anywhere 6 through 9, because it was unable to find the server.

The ODBC setup and driver are probably OK... the problem is most likely that the server isn't running, or the server name is incorrect.

Try using ISQL to connect to the target database from the same computer that's running Foxhound.

-- Connection error: Found server but communication error occurred --
-- Connection error: Found server but communication error occurred at --

Foxhound's connection to your target database failed for some reason.

The target server may have been in the process of terminating, in which case this error message may be followed by "Database server not found".

-- Connection was terminated --
-- Connection was terminated at --

Foxhound's connection to your target database was terminated for some reason.

The target database may have been in the process of being stopped, in which case this error message may be followed by "Specified database not found".

-- Data source name not found and no default driver specified --
-- Data source name not found and no default driver specified at --

If you are connecting via the String tab and you have not specified a DSN, make sure to specify a DRIVER.

Look for a syntax error in the connection string, such as a comma used as a separator instead of a semicolon; e.g.,

ENG=ddd17; DBN=ddd17; UID=dba; PWD=sql; HOST=localhost, DRIVER=SQL Anywhere 17;

If you are connecting via the String tab and there is a space ahead of the DRIVER parameter, try removing it. Certain versions of SQL Anywhere have a problem with that.

If you are connecting via the String tab and you have specified a DSN, make sure it exists.

If you are using the 64-bit version of SQL Anywhere to run Foxhound, check to see if the ODBC DSN for the target database only works with the 32-bit version of SQL Anywhere, or vice versa.

See also: Connecting to Target Databases

-- Database server not found --
-- Database server not found at --

Here are some possibilities:

Tip: To see more information about the "Database server not found" condition, try turning on the Foxhound Include Separate Ping feature; the resulting "Ping also failed" messages may reveal the underlying cause for the problem.

See also...
Connecting to Target Databases
Monitor Options - Ping Settings

-- DSN not found --
-- DSN not found at --

If you are using a User DSN to connect to your target database, and then you restart Foxhound as a Windows service, try recreating the DSN as a System DSN.

Administrative Tip: The Foxhound Menu - ODBC Administrator button is disabled when the Foxhound engine is running as a service on the local computer because services cannot launch interactive user interfaces; e.g., the ODBC Administrator interface. In this case, try starting the ODBC Administrator separately, via Control Panel - Administrative Tools - ODBC Data Sources.

Also, try specifying a HOST address even if the target database is running on the same computer:

ODBC Configuration for SQL Anywhere
   Login tab
      User ID:       xxx
      Password:      yyy
      Action:        Connect to a running database on another computer
      Host:          localhost
      Server name:   sss
      Database name: ddd

See also: Connecting to Target Databases

-- Foxhound Extended edition is required for more than 100 connections on one target database - sampling stopped --
-- Foxhound Extended edition is required for more than 100 connections on one target database - sampling stopped at --

Some features are not available in the Basic and Rental editions of Foxhound. In this case, you need the Extended Edition if your target database has more than 100 client connections.

To continue sampling you must obtain an Extended edition registration key and enter it on the Foxhound About page.

See also: Limitations

-- Foxhound Extended edition is required for more than 1 physical processor on the target server - sampling stopped --
-- Foxhound Extended edition is required for more than 1 physical processor on the target server - sampling stopped at --

Some features are not available in the Basic and Rental editions of Foxhound. In this case, the number of physical processors as given by the NumPhysicalProcessorsUsed server property is larger than 1. This is not the same as the number of logical processors shown by "Using n CPUs" value in the Foxhound Database Monitor. For example, a single Intel Core2 Quad processor also counts as only 1 physical processor even though it may show up as "Using 4 CPUs".

To continue sampling you must obtain an Extended edition registration key and enter it on the Foxhound About page.

See also: Limitations

-- Foxhound Extended edition is required for use with SQL Anywhere Standard or Advanced edition - sampling stopped --
-- Foxhound Extended edition is required for use with SQL Anywhere Standard or Advanced edition - sampling stopped at --

Some features are not available in the Basic and Rental editions of Foxhound. In this case, you need the Foxhound Extended Edition to gather samples from a target database running on a SQL Anywhere Standard or Advanced edition server.

To continue sampling you must obtain an Extended edition registration key and enter it on the Foxhound About page.

See also: Limitations

-- Foxhound requires target PROPERTY ( CollectStatistics ) = Yes; i.e., do not specify dbsrv -k - sampling stopped --
-- Foxhound requires target PROPERTY ( CollectStatistics ) = Yes; i.e., do not specify dbsrv -k - sampling stopped at --

Foxhound cannot collect performance statistics if the target server CollectStatistics feature is turned off; i.e., if SELECT PROPERTY ( 'CollectStatistics' ) returns 'No'.

This can happen after the target server is started with the -k option, or after CALL sa_server_option ( 'CollectStatistics', 'NO' ) is executed on the target database. When this happens, Foxhound stops sampling and displays these messages:

-- Foxhound requires target PROPERTY ( CollectStatistics ) = Yes; i.e., do not specify dbsrv -k - sampling stopped -- 
-- Foxhound requires target PROPERTY ( CollectStatistics ) = Yes; i.e., do not specify dbsrv -k - sampling stopped at -- 

If this problem was caused by a CALL sa_server_option ( 'CollectStatistics', 'NO' ) on the target database,

If this problem was caused by starting the target server with dbsrv -k,

-- Foxhound stopped --
-- Foxhound stopped at --

Both of these messages are created and displayed when Foxhound is started after a period of being stopped.

The "-- Foxhound stopped at --" timestamp is the approximate time when one of the following occurred:

  1. The Foxhound engine and database were shut down, or

  2. a full online backup process of the Foxhound database was started, and the resulting backup file was subsequently restored and started as the current Foxhound database.

The "-- Foxhound stopped at --" timestamp is approximate because there is no process that records exactly when Foxhound stopped processing, and the timestamp is not calculated until (1) Foxhound is restarted, or (2) the backup was subsequently restored and Foxhound is started.

The subsequent "-- Foxhound stopped --" timestamp marks the end of the period Foxhound was stopped... in other words, it really means "-- Foxhound started at --".

When Foxhound is restarted it will automatically try to reconnect to the target database(s) and start sampling again.

-- Invalid user ID or password --
-- Invalid user ID or password at --

Foxhound should use the correct user id and password to connect to your target database.

The password value is cAsE sEnSiTiVe.

-- Older samples not upgraded at ... --

This "eyecatcher" message is displayed after FOXHOUND5UPGRADE=yyyymmdd has been specified during the Foxhound setup process that upgraded data from an older copy of the Foxhound database.

For example, when you specify this...

*** "FOXHOUND5UPGRADE" specifies how much data is to be upgraded.
***
*** If you want to CHANGE the setting, type in a new value...
***    ALL       - upgrade all the data
***    OPTIONS   - no samples, just the Foxhound options
***    yyyymmdd  - options plus samples since yyyymmdd
***    nnn       - options plus last nnn days of samples
***    NOTHING   - don't upgrade any data
*** and press Enter to continue.
***
*** If you LIKE the current setting...
***    FOXHOUND5UPGRADE=ALL which means upgrade all the data
*** just press Enter.
******************************************************************
Current FOXHOUND5UPGRADE=ALL
New     FOXHOUND5UPGRADE=Jul 31 2015 12:00:07 AM 

...you'll see something this when you scroll to the bottom of the Sample History page:

Jul 31 2015 12:00:07 AM   10s   - / 203ms / -   63/s / 10/s / 51k/s   ...
Jul 31 2015 12:00:07 AM   -   -- Older samples not upgraded at Jul 10 2019 9:58:59 AM (?) -- 

-- Permission denied: Cannot CREATE PROCEDURE rroad_ ... --
-- Permission denied: Cannot CREATE PROCEDURE rroad_ ... at --

Foxhound has been unable to create one or more of the following procedures on the target database:
rroad_connection_properties

rroad_database_properties

rroad_engine_properties

Foxhound will still work but performance of Foxhound itself may be affected, especially if there are a lot of connections to the target database. One solution is to GRANT RESOURCE to the user id Foxhound uses to connect to the target database.

Another solution is to create the procedures yourself on the target database, with the Foxhound user id as the owner; see How To Install Foxhound SPs

-- Permission denied: you do not have permission to execute a statement of this type --
-- Permission denied: you do not have permission to execute a statement of this type at --

The Foxhound user id may not be allowed to perform some critical operation on your target database; see Connecting to Target Databases

If the target is a SQL Anywhere High Availability arbiter server without a database, Foxhound cannot open a direct ODBC connection because there's no database.

In this case, one alternative is to switch to ping-only sampling; see section 16. Ping Settings - Perform Ping-Only Sampling on the Monitor Options page. Another alternative is to restart the arbiter server with its own (empty?) database so Foxhound can start sampling via a direct ODBC connection to the target database.

-- Permission denied: you do not have permission to execute the procedure ... --
-- Permission denied: you do not have permission to execute the procedure ... at --

The Foxhound user id may not be allowed to call an important SQL Anywhere procedure on your target database; see Connecting to Target Databases

-- Pick a valid ODBC DSN, and specify the user id and password on the Foxhound DSN tab or inside the DSN itself --
-- Pick a valid ODBC DSN, and specify the user id and password on the Foxhound DSN tab or inside the DSN itself at --

When using the DSN tab on the Foxhound menu to choose Display Schema or Monitor Database, both the user id and password values must be specified together, in the same place: either explicitly in the User Id: and Password: fields on the menu, or inside the target database ODBC DSN itself.

Currently, Foxhound cannot deal with the following scenarios:

This message may also appear in a Database Monitor session that was once able to connect to the target database via an ODBC User DSN, but is no longer able to connect because the Foxhound database was started as a service and thus requires System DSNs in order to connect to target databases. Try deleting the User DSN and recreating it as a System DSN; that will allow Foxhound to reconnect and resume sampling in the old sampling session rather that starting a new session.

Administrative Tip: The Foxhound Menu - ODBC Administrator button is disabled when the Foxhound engine is running as a service on the local computer because services cannot launch interactive user interfaces; e.g., the ODBC Administrator interface. In this case, try starting the ODBC Administrator separately, via Control Panel - Administrative Tools - ODBC Data Sources.

See also: Connecting to Target Databases

-- Ping-only sampling -- Ping OK
-- Ping-only sampling -- Ping failed -- [reason]
-- Ping-only sampling at --

The Ping-only sampling at message appears when Ping-only sampling begins, possibly because a sampling schedule was in effect and a 'P' character was reached.

The Ping-only sampling message shows the current status: Ping OK versus Ping failed.

As time passes, this message is updated to show the most recent status; Foxhound doesn't display a history of Ping-only status changes.

Here's an example of a target database that was shut down and restarted while Ping-only sampling was in effect, and then sampling was stopped; only the final Ping failed status is displayed:

1:54:36 PM  4m 52s  -- Sampling stopped -- 
1:49:44 PM          Cancelled  - Alert #1: Database unresponsive Foxhound has been unable to gather samples for 20s or longer.  
1:49:43 PM    9.9s  -- Sampling stopped at -- 
1:49:33 PM   29.4m  -- Ping-only sampling -- Ping failed -- Database server not found
1:44:27 PM          Alert #1: Database unresponsive Foxhound has been unable to gather samples for 20s or longer.  
1:27:46 PM          All Clear  - Alert #1: Database unresponsive Foxhound has been unable to gather samples for 20s or longer.  
1:26:39 PM          Alert #1: Database unresponsive Foxhound has been unable to gather samples for 20s or longer.  
1:23:33 PM          All Clear  - Alert #1: Database unresponsive Foxhound has been unable to gather samples for 20s or longer.  
1:21:41 PM          Alert #1: Database unresponsive Foxhound has been unable to gather samples for 20s or longer.  
1:20:10 PM    9.8s  -- Ping-only sampling at -- 

See also...
Monitor Options - Ping-Only Sampling
Monitor Options - Sample Schedule

-- Procedure 'rroad_ ... _properties' not found --
-- Procedure 'rroad_ ... _properties' not found at --

This exception may appear after Foxhound has been upgraded to a new version or build:
2019-06-28 16:02:12.981 Full Build 5323a 1000000068 204.q5(204eh-c4) SQLCODE = -660, SQLSTATE = WO005, 
ERRORMSG() = Procedure 'rroad_connection_properties' not found [5-primary_demo]

This message may be an unwanted side-effect of having two separate Foxhound sampling sessions for the same target database; e.g., one sampling session for a High Availability primary database and another sampling session for the partner database which is acting as the primary. In this case, the symptom may go away when Foxhound tries connecting again.

If the problem persists, and Foxhound cannot connect to the target database, try stopping and restarting sampling for that database.

-- Remote server does not have the ability to support this statement --
-- Remote server does not have the ability to support this statement at --

Foxhound uses SQL Anywhere's "Remote Server" feature to gather data from your target database via proxy tables and proxy procedure calls.

This message is unexpected, and it indicates something has gone wrong with the Remote Server access. Try connecting to the target database again, and if that doesn't work contact breck.carter@gmail.com for assistance.

See also: Connecting to Target Databases

-- Sampling stopped --
-- Sampling stopped at --

The "-- Sampling stopped at --" message is displayed when you explicitly request that sampling be stopped, by pressing one of these:

The "-- Sampling stopped at --" timestamp marks the beginning of a period sampling was stopped for this target database.

The subsequent "-- Sampling stopped --" timestamp marks the end of the period sampling was stopped... in other words, it really means "-- Sampling started at --".

-- Sampling stopped by Safe Startup --
-- Sampling stopped by Safe Startup at --

You have started Foxhound with Safe Startup enabled.

See also: Safe Mode Startup

-- Sampling stopped by schedule --
-- Sampling stopped by schedule at --

Sampling has been stopped according the sampling schedule.

See also: Monitor Options - Sample Schedule

-- Sampling stopped while waiting for activation --
-- Sampling stopped while waiting for activation at --

One or more sampling sessions were running when Foxhound was upgraded to a new version that requires activation, and sampling has been stopped because you have not yet activated Foxhound.

To start sampling again you must obtain a Foxhound registration key for the new version and enter it on the Foxhound Activation page.

-- Specified database not found --
-- Specified database not found at --

Foxhound's connection to your target database was able to find the server but not the database.

This message may appear for a short time while a High Availability partner database is starting up.

If this situation persists, it may be caused by an attempt to use a complete HA restart to switch roles; for example:
  • both HA partner databases were stopped, then

  • the two partners are being started separately, one after the other, and

  • the first partner started is the one that was acting as secondary (mirror) before they were shut down.

  • This is likely to fail; just because a partner is being started first doesn't mean it will automatically regain the role of primary.

  • Try starting the second partner so it can assume its previous role as primary, which will then allow to first partner to resume as secondary,

  • then use some other method to switch roles... or better yet, stop fretting about which computer has which role :)

-- Start of OFSS batch number nnn --

This eyecatcher message marks the beginning of a batch of OFSS samples that was created by the start_OFSS_monitor() procedure on the subject database and later loaded into the Foxhound database by the load_OFSS_samples() procedure.

-- Target SQL Anywhere database version ... not supported - sampling stopped --
-- Target SQL Anywhere database version ... not supported - sampling stopped at --

Sampling has been stopped because the target database version is not one of 5, 6, 7, 8, 9, 10, 11, 12, 16 or 17.

See also: Requirements

-- Target SQL Anywhere server version ... not supported - sampling stopped --
-- Target SQL Anywhere server version ... not supported - sampling stopped at --

Sampling has been stopped because the target database server is not one of 5, 6, 7, 8, 9, 10, 11, 12, 16 or 17.

See also: Requirements

-- The remote table ' ... SYS.DUMMY' could not be found --
-- The remote table ' ... SYS.DUMMY' could not be found at --

Try using a earlier version of the ODBC driver for Foxhound's connection to your target database; e.g., try changing DRIVER=SQL Anywhere 17 to DRIVER=SQL Anywhere 16 or earlier, especially when the target database is running on a 32-bit instance of SQL Anywhere 9 or earlier.

-- The specified DSN contains an architecture mismatch between the Driver and Application --
-- The specified DSN contains an architecture mismatch between the Driver and Application at --

In most cases Foxhound this message means Foxhound is running on a 64-bit instance of SQL Anywhere 17 but is trying to use a older 32-bit ODBC driver to connect to your 32-bit target database.

Try running Foxhound on a 32-bit instance of SQL Anywhere 17, or use a backward compatible 64-bit ODBC driver to connect to your target database; e.g. DRIVER=SQL Anywhere 16.

Most recent SQL Anywhere ODBC drivers are backward compatible. However, DRIVER=SQL Anywhere 17 does seem to have problems; e.g., it returns a bogus error message "The remote table 'XXX..SYS.DUMMY' could not be found" when used by Foxhound running on a 64-bit instance of SQL Anywhere 17 to try to connect to a 32-bit target database.

See also...
Environment Variables - FOXHOUND5BIN
Tip: Connecting via 32-bit ODBC may not be necessary

-- Value 18446744073709551615 out of range for destination --
-- Value 18446744073709551615 out of range for destination at --

Foxhound cannot collect performance statistics if the target server CollectStatistics feature is turned off; i.e., if SELECT PROPERTY ( 'CollectStatistics' ) returns 'No'.

This can happen after the target server is started with the -k option, or after CALL sa_server_option ( 'CollectStatistics', 'NO' ) is executed on the target database. When this happens, Foxhound stops sampling and displays these messages:

-- Foxhound requires target PROPERTY ( CollectStatistics ) = Yes; i.e., do not specify dbsrv -k - sampling stopped -- 
-- Foxhound requires target PROPERTY ( CollectStatistics ) = Yes; i.e., do not specify dbsrv -k - sampling stopped at -- 

You may also see messages like this:

-- Value 18446744073709551615 out of range for destination --
-- Value 18446744073709551615 out of range for destination at --

If this problem was caused by a CALL sa_server_option ( 'CollectStatistics', 'NO' ) on the target database,

- you may be able to fix it with a CALL sa_server_option ( 'CollectStatistics', 'YES' )

- then clicking on Start Sampling on the Foxhound Monitor page.

If this problem was caused by starting the target server with dbsrv -k,

- the CALL might not work; you may see another message Value 18446744073709551615 out of range for destination.

- In that case, try stopping the target server, and then restarting it without the dbsrv -k option.

- After that, you may have to click Start Sampling on the Foxhound Monitor page.

-- [other-message] --
-- [other-message] at --

Some Monitor Messages are not documented here. Depending on the version of SQL Anywhere running your target database, you may find more information in one of these Help topics:
SQL Anywhere 17 Help
   SQL Anywhere error messages listed by message text

SQL Anywhere 16, 12, 11 Help
   SQL Anywhere error messages sorted by message text (A-C) 
   SQL Anywhere error messages sorted by message text (D-K) 
   SQL Anywhere error messages sorted by message text (L-S) 
   SQL Anywhere error messages sorted by message text (T-Z) 
   SQL Anywhere error messages sorted by message text (special characters) 

SQL Anywhere 10, 9, 8, 7, 6 Help
   Database Error Messages - Alphabetic list of error messages

SQL Anywhere 5.5 Help
   SQL Anywhere Database Error Messages - Alphabetic list of error messages

You can also contact breck.carter@gmail.com for assistance.


Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Server Messages
The msg_text column values returned by the sa_server_messages() stored procedure in the target database are displayed in the Samples and Messages section of the Monitor and Sample History pages.
Note: Empty msg_text values are excluded.

The msg_time column is used to determine the order for display, allowing server messages to be matched with corresponding Foxhound samples, alerts and other messages.

Note: Like all the other rows displayed on the Monitor and Sample History pages, the server message rows are displayed in reverse order, with the most recent messages appearing at the top.

The msg_severity values 'INFO', 'WARN' and 'ERR' are shown as I, W and E ahead of the message text, with W and E messages highlighted as  banner warnings .

Performance Tip: You can execute MESSAGE TO CONSOLE statements on the target database to have your own messages appear on the Foxhound Monitor and Sample History pages:

MESSAGE STRING ( 'INFO message' ) TO CONSOLE;
MESSAGE STRING ( 'WARN message' ) TYPE WARNING TO CONSOLE;
MESSAGE STRING ( 'ERR message'  ) TYPE ACTION TO CONSOLE;

1:17:09 PM          -- E. ERR message (?) (2ms) 
1:17:09 PM          -- W. WARN message (?) (11ms) 
1:17:09 PM          -- I. INFO message (?) (1m 53s) 

The intervals between successive msg_time values are shown in (parentheses) after the message text.

Note: These inter-message intervals are not related to the Foxhound "Interval" column, but they are useful in their own right. For example, the interval between these successive "Starting checkpoint" and "Finished checkpoint" messages tells you the checkpoint took 258ms:
Mar 1 4:30:35 PM          -- I. Finished checkpoint of "ddd16" (ddd16.db) at Sat Mar 31 2018 09:55 (?) (258ms) 
Mar 1 4:30:35 PM          -- I. Starting checkpoint of "ddd16" (ddd16.db) at Sat Mar 31 2018 09:55 (?) (1m 1.8s)

These inter-message intervals are also displayed for your own MESSAGE TO CONSOLE statements.

Note: There are two kinds of server messages created by SQL Anywhere:

That distinction doesn't matter when the Foxhound target database is the only one running on the server: Foxhound stores and displays all the messages.

However, when the Foxhound target database is not the only one on the server, Foxhound stores and displays all the server-level messages but only those database-level messages that apply to the target database.

This makes a huge difference if there are, for example, 100 Foxhound target databases on one instance of dbsrv*.exe. Separate pairs of checkpoint messages are created every few minutes for each and every database, and it would be a huge waste of space (both database and display) for all 100 pairs of checkpoint messages to be stored and displayed for each and every target... 20,000 messages in total.

Performance Tip: You can disable and enable the gathering of all server messages using the Server Messages section of the Monitor Options page.

Performance Tip: You can show and hide server messages that have already been gathered; see the Server Messages section of the Monitor Options page.

This can be valuable if you change your mind later on and want to see the server messages, or you want to run adhoc queries on the server_message table.

Performance Tip: You may see gaps in server messages gathered from your target database.

That can happen after SQL Anywhere applies the MessageCategoryLimit setting to delete old messages with the same category and severity.

You can change this setting by executing this statement on your target database (this CALL shows the default value):

CALL sa_server_option ( 'MessageCategoryLimit', '400' );

The server messages are provided by the sa_server_messages() stored procedure in target databases running on SQL Anywhere 11 and later.

See also:

Monitor Options - Server Messages

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Alert, All Clear and Cancelled Messages

Alert, All Clear and Alert Cancelled messages are displayed in several locations, sorted according to date:

Here's what they look like:

1:55:45 PM   Alert #17: Checkpoint urgency  HelpHelp  The Checkpoint Urgency has been 1% or more for 1 or more recent samples. Email not sent because Alert Emails were disabled.

1:55:26 PM   All Clear    - Alert #17: Checkpoint urgency  HelpHelp  The Checkpoint Urgency has been 1% or more for 1 or more recent samples. Email not sent because Alert Emails were disabled.

2:21:51 PM   Cancelled    - Alert #19: Cache size  HelpHelp  The cache has reached 1% of its maximum size for 1 or more recent samples. Email not sent because Alert Emails were disabled.

The  Help  Help icon goes to the Alert-specific topic in the Help.

The  Help  Gear icon goes to the Alert-specific entry on the Monitor Options page so you can change the threshold settings.

See also:

Monitor Options - Alert Criteria

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Autodropped Messages

Autodropped messages are displayed in two formats.

The first format appears Samples and Messages sections of the Monitor and History pages and looks like this:

Apr 30 2:12:05 PM   Autodropped:    1798 / u.wouters / Breck / - / app -   #4 Temp space usage  HelpHelp  This connection has been using 1k or more (currently 12k) of temporary space for 1 or more (currently 1) samples. Email sent, status unknown.

The second format appears in the Connection Samples sections on the Monitor, History and Connection History pages:

  1798 / u.wouters / Breck / - / app    AutoDropped for this reason:   #4 Temp space usage  HelpHelp  This connection has been using 1k or more (currently 20k) of temporary space for 1 or more (currently 1) samples

AutoDrop Result: OK... issued at 2019-04-30 14:12:05.876, processed at 2019-04-30 14:12:05.939

The  Help  Help icon goes to the AutoDrop-specific topic in the Help

The  Help  Gear icon goes to the AutoDrop-specific entry on the Monitor Options page so you can change the threshold settings.

See also:

Monitor Options - AutoDrop Criteria

Foxhound 5 » 3. The Monitor Database Page » 3.6 Samples and Messages 
Email Failure Messages

Email Failure messages appear in the Monitor, Sample History and Connection History pages when Foxhound tries and fails to send emails associated with Alert, All Clear, Alert Cancelled and AutoDrop messages.

The Email Failure messages don't appear right away when there's a delay before Foxhound receives notification of the failure.

Here is an example where a Cancelled message appears right way, with the phrase "Email to be sent, status unknown" because Foxhound hasn't yet received a response:

Cancelled  - Alert #26: Connections Help   The number of connections has reached 2 or more for 2 or 
   more recent samples. Email to be sent, status unknown. 

After a while, a separate Email Failure message appears, and the original Cancelled message is modified with the phrase "Email failed with status code 5":

Email Failure  - Email failed: subject = "CANCELLED Alert #26 Connections - "ddd16 / XPS / ddd16 / ddd16""; 
   email type = SMTP; status code = 5;    meaning = "Connect error"; error code = 10013; error text = "n/a"; 
   Google search = Google: smtp error 10013 

...

Cancelled  - Alert #26: Connections Help   The number of connections has reached 2 or more for 2 or 
   more recent samples. Email failed with status code 5. 

Some "Email failed:" messages are simple:

Email failed: Email address(es) empty when sending "Foxhound Test Alert Email (2019-04-28 09:35:55)".

and others are more complex:

Email failed: subject = "xxx"; 
   email type = xxxx;  
   status code = n;  
   meaning = "xxx";  
   error code = nnn;  
   error text = "xxx";  
   Google search = xxx

subject          the email subject created by Foxhound

email type       SMTP or MAPI

status code      returned by the SQL Anywhere 17 procedures xp_startsmtp, 
                 xp_startmail and xp_sendmail 

meaning          as described by the SQL Anywhere 17 Help topic "Status codes for 
                 MAPI and SMTP system procedures"

                 status code   meaning
                 -----------   -------
                    -1         Unknown error (see error code and text)
                     0         Success
                     1         An invalid parameter was supplied
                     2         Out of memory
                     3         xp_startmail or xp_startsmtp was not called
                     4         Bad host name
                     5         Connect error
                     6         Secure connection error
                     7         MAPI functions are not available
                     8         undocumented (check SMTP server)
                   other       undocumented

error code       returned by the SQL Anywhere 17 procedure xp_get_mail_error_code

error text       returned by the SQL Anywhere 17 procedure xp_get_mail_error_text,
                   with embedded urls reformatted as HTML anchor links.

Google search    search Google for [email type] error [error code]

n/a              used for zero, NULL or empty string values

See also:

Foxhound Options - Global Email Settings


Foxhound 5 » 3. The Monitor Database Page 
3.7 Connections

The Connections section shows up to 10 connections as they existed when the Top Sample was recorded.

You can click on the "at timestamp" link to open a new History page with the Samples and Messages section scrolled to the successful sample recorded at that timestamp.

Click on a title below to sort it...

Favorable?

[9,9999,99] Adhoc Reporting Primary Keys

Hide Details / Show Details

Conn #, User, OS User, IP, Name

Time Connected, Total Transaction Time

Throughput... Req, Commits, Bytes

Conn Flags Count

Locks Held, Conns Blocked, Transaction Time

Waiting Time, Busy, Wait, Idle

CPU, Child Conns

Temp Space, Rollback Log, Uncommitted

Low Memory, Cache Satisfaction

Time Since Last Request

Current Req Status

Disk/Cache: Internal Index, Leaf, Table

Disk Reads, Disk Writes, Log Writes

Index Adds, Lookups, Satisfaction

Full Index Comps

Isolation Level

AutoDropped for this reason:

Autodrop Result:

Flags:

Blocked By:

Block Reason:

Locked Row Query:

Last Statement:

Last Plan Text:

To see more connections, or to see connections as they existed for earlier samples, switch to the History page.

You can also see the history of an individual connection by clicking on the Conn #, User, OS User, IP, Name link.

This section will be empty if connection details are not being recorded; see Foxhound Options - Connection sampling threshold.

The up or down arrow to the left of a single column title indicates that entries in this section are sorted in ascending or decreasing order by that column. Click on that column title to change the direction of that sort (ascending to or from descending), or click on a different column title to sort the entries by that column.

Note: If you add up the connection-level figures like "Req" you might not get the same number as shown in the server-level "Req" column. That's because the connection-level and server-level statistics are recorded at slightly different times, and one value might lag behind the other.


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Click on a title below to sort it, or click here to sort on ...
Some of the connection-level data appears on separate lines (e.g., Last Statement:) so there are no column titles to click on when you want to sort on that data. In these cases you have two choices to sort the connection entries:
  1. Click on the title text on the line; e.g., click on Last Statement:

  2. Click on the title text in the "... or click here to sort on ..." line if there are no connection entries with that data on the display.

If Conn # is selected as the sort column, the connection number of child connections is used as a secondary sort column so parent and child connections appear together.

If some other column (not Conn#) is selected as the primary sort column, Conn # is used as a secondary sort column. In this case parent and child connections will not appear together.

For more help on these lines:


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Favorable? xxx
This message appears when "xxx" contains one or more N's:

Favorable? xxx  Some data may be missing because settings on the target database are not favorable to the recording of data. 

The Favorable? field displays a combination of three Y, N and - characters to indicate whether or not three settings on the target database are (or were) favorable to the recording of data for Foxhound to display on the Monitor, History and Connection History pages:

You can click on the Favorable? link to open Monitor Options - 14. Change Target Settings to change these three settings:

  1. The RememberLastPlan server option controls whether or not anything is displayed in this connection-level field:

    • Last Plan Text

  2. The RememberLastStatement server option controls whether or not anything is displayed in these connection-level fields:

    • Blocked Statement
    • Last Statement

  3. The RequestTiming server option controls whether or not anything is displayed in these connection-level columns:

    • Throughput... Req
    • Waiting Time
    • Busy, Wait, Idle

Administrative Tip: The Favorable? link to the Monitor Options page is not available for offline (OFSS) subject databases because Foxhound is not connected to those databases. Instead, you can directly set the server options yourself on your subject databases.

Performance Tip: Number 2 (RememberLastStatement) is by far the most useful of the three.

Performance Tip: Changes to these server options only affect future samples gathered by Foxhound; there is no way to go back and "fix" historical data.

Performance Tip: Changes to these server options on a High Availability primary database or a Read-Only Scale-Out root database will not be automatically transmitted to a secondary or copy database.

However, since these are server options rather than database options, you can use the Favorable? field on the secondary or copy database to make the same changes even though the Foxhound connections to those databases are read-only.

Performance Tip: After a High Availability failover, it is possible for the Favorable? field on the Monitor page for the secondary database to show different values from the Change Target Settings section on the Monitor Options page for the "same" database (which isn't actually the same any more).

If the target database is a High Availability secondary database and a failover occurs (primary fails, secondary takes over), the Favorable Current Settings? column may show [not available].

The reason for this is that the Monitor Options - Change Target Settings section attempts to open a new connection to the secondary database, and the database that used to be the secondary is now the primary so there's no secondary database available. The Foxhound sampling session, however, remains connected to the original database (once the secondary, now the primary) so there is an apparent inconsistency between Favorable Current Settings? showing [not available], and the Foxhound Monitor page showing that everything is OK.

But wait, there's more! If the failed database that was the original primary database is restarted, it will become the new secondary database, and the Favorable Current Settings? column will show actual values rather than [not available]... but those values will be coming from a different database than the Foxhound sampling session is showing. That's because the Monitor Options - Change Target Settings section opens a new connection to the secondary database, and the Foxhound Monitor session remains connected to the original database (once the secondary, now the primary).

The RememberLastStatement server option is supported by target databases running on SQL Anywhere 8 and later, while RememberLastPlan and RequestTiming are supported by SQL Anywhere 10 and later.

See also:

Monitor Options - 14. Change Target Settings

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
[9,9999,99] Adhoc Reporting Primary Keys

The two or three numbers [in square brackets] are important primary key values for writing adhoc queries using All Programs - Foxhound5 - 2 Adhoc Query via ISQL.

The first number in [9,9999,99] is the sampling_id which uniquely identifies the target database in adhoc queries like this:

SELECT * FROM sampling_options WHERE sampling_id = 9;
SELECT * FROM alerts_criteria  WHERE sampling_id = 9;

The second number in [9,9999,99] is generically called the "locator_number" and it is the globally unique primary key for all rows in all associated tables for all target databases.

The meaning of the "locator_number" depends on the type of data displayed in each row on the Monitor and History pages; for the purposes of adhoc queries it is used as primary key value for WHERE clauses and the first number (sampling_id) isn't required:

SELECT * FROM alert                   WHERE alert_occurrence           = 9999;
SELECT * FROM alert_cancelled         WHERE alert_cancelled_occurrence = 9999;
SELECT * FROM all_clear               WHERE all_clear_occurrence       = 9999;
SELECT * FROM autodropped_connection  WHERE autodrop_occurrence        = 9999;
SELECT * FROM email_failure           WHERE email_failure_occurrence   = 9999;
SELECT * FROM ping_log                WHERE ping_id                    = 9999;
SELECT * FROM sample_connection       WHERE sample_set_number          = 9999;
SELECT * FROM sample_detail           WHERE sample_set_number          = 9999;
SELECT * FROM sample_header           WHERE sample_set_number          = 9999;

The third number only appears when a server message is displayed at the top of the Samples and Messages section on the Monitor and History pages:

Samples and
Messages
[14,43562,3925]
10:18:33 AM   -- I. Finished checkpoint of "ddd" (ddd. db) at Fri Jul 12 2019 10:18 Help  (239ms) 

For the purposes of adhoc queries, the third number in [14,43562,3925] is the primary key for server_message, and the first and second numbers (sampling_id and locator_number) aren't required:

SELECT * FROM server_message WHERE inserted_order = 3925;

inserted_order,sampling_id,server_started_at,locator_number,interval_msec,msg_id,msg_text,msg_time,msg_severity,msg_category,msg_database
3925,14,'2019-07-07 10:14:59.765',43562,239,795,Finished checkpoint of "ddd" (ddd.db) at Fri Jul 12 2019 10:18,'2019-07-12 10:18:33.734','INFO','CHKPT','ddd'

Note that server_message.inserted_order column is created as DEFAULT AUTOINCREMENT, so the second number (locator_number) is not actually required to identify server_message rows.

See also:

Adhoc Queries

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Hide Details / Show Details
The Hide Details / Show Details buttons alternate between hiding and displaying the following lines for each connection:

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Conn #, User, OS User, IP, Name
The Conn #, User, OS User, IP, Name columns identify each connection.
Performance Tip: Click on this link to open a new Connection History page showing just this one single connection.
(This works for target databases running on SQL Anywhere 9 or later.)

Conn # is the connection number of the connection.

SQL Anywhere assigns a unique connection number to each connection started since the server started. The numbering is unique across multiple databases running on the same server, and there will never be two connections with the same number until the SQL Anywhere server is stopped and restarted.

Small consecutive numbers 1 through 999,999,999 are assigned to external client server connections and to web service (CREATE SERVICE) connections. If the range 1 through 999,999,999 is exceeded the numbering starts at 1 again, but numbers already in use are skipped.

Larger numbers like 1000000003 are assigned to event (CREATE EVENT) connections and to so-called "internal temporary" connections that are created behind-the-scenes by SQL Anywhere.

Some internal connections are "child" connections that have been started by some other "parent" connection.

In that case two numbers are shown in the Conn # column with the first number being the parent connection number and the second number being the actual connection number for the internal connection; e.g., 1 1000000090

Conn #, User, OS User, IP, Name
1 / DBA / Breck / - / ddd16-1
1 1000000090 / - / - / - / INT: Exchange
1 1000000091 / - / - / - / INT: Exchange
1 1000000092 / - / - / - / INT: Exchange
1 1000000093 / - / - / - / INT: Exchange
1 1000000094 / - / - / - / INT: Exchange
1 1000000095 / - / - / - / INT: Exchange
1 1000000096 / - / - / - / INT: Exchange
1 1000000097 / - / - / - / INT: Exchange

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.

Performance Tip: To see child connections together with their parent connections, click on the "Conn #" column title to sort the connection section on that column.

Performance Tip: The frequent appearance of multiple child connections (like the "INT: Exchange" entries shown above) may indicate a bottleneck caused by runaway intra-query parallelism.

Performance Tip: Be on the lookout for short-lived "INT: Exchange" connections that may indicate a waste of resources.

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 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.
Note that NodeAddress properties containing the string 'NA' are treated the same as empty, blank and NULL values, and are displayed as '-'.

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:

Name is based on the connection-level Name property.

See also:

Monitor - Conns / Parent, Child Conns
Monitor - Connections - CPU, Child Conns
Alert #26 Connections
Connection Flag #10 Child connections
Connection Flag #19 User id usage
Connection Flag #20 OS user usage
Connection Flag #21 IP address usage
Connection Flag #22 Connection name usage

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Time Connected, Total Transaction Time
Time Connected is the elapsed time between the time this connection was established and the sample time.
Time Connected is displayed for target databases running on SQL Anywhere 9 and later, and is based on the connection-level LoginTime property.

Total Transaction Time is the approximate sum of transaction running times for this connection since samples were first collected for this connection.

This number is approximate because SQL Anywhere only records the Transaction Start Time, and it discards that value as soon as a transaction is complete. Since Foxhound only records samples every 10 seconds, short transactions may be entirely or partly missed.

Total Transaction Time is different from Transaction Time. The Transaction Time column shows how long the current transaction has been running, and it returns to zero when the transaction finishes, whereas the Total Transaction Time continues to grow as the connection starts and finishes multiple transactions.

Performance Tip: A large Total Transaction Time may indicate that that long-running transactions are a performance bottleneck.

Total Transaction Time is displayed for target databases running on SQL Anywhere 8 and later, and is based on the connection-level TransactionStartTime property.

See also:

Monitor - Temp Space, Rollback Log, Uncommitted
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Temp Space, Rollback Log, Uncommitted
Alert #28 Long transaction
Alert #32 Rollback log usage
Alert #33 Uncommitted operations
Alert #34 Long uncommitted
Connection Flag #3 Long transaction
Connection Flag #7 Rollback log usage
Connection Flag #8 Uncommitted operations
Connection Flag #9 Long uncommitted
Connection Flag #23 Total transaction time
AutoDrop #3 Long transaction

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Throughput... Req, Commits, Bytes
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 10 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 8 and later, and is based on the connection-level BytesReceived and BytesSent properties.


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Conn Flags Count
Conn Flags Count is the total number of Connection Flags that are currently set for this connection.
Performance Tip: A large Conn Flags Count may indicate this connection is responsible for one or more performance bottlenecks.

Administrative Tip: You can force the connection flags to be recalculated using all the samples recorded for this target database; see Recalculate Connection Flags on the Adhoc Queries Help page.

Conn Flags Count is calculated for target databases running on all versions of SQL Anywhere that are supported by Foxhound.

See also:

Adhoc Queries - Recalculate Connection Flags.
Monitor Options - Connection Flag Settings
Monitor - Connections - Flags

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Locks Held, Conns Blocked, Transaction Time
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 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.

Each connection can only have one database transaction in progress at any given point in time; there is no such thing as "nested transactions", and if an application wants to run two different database transactions at the same time it must use two different database connections.

Performance Tip: A long Transaction Time may indicate that a long-running transaction is a performance bottleneck.

If the Locks Held number is larger than zero, it means other connections may be prevented from updating (and possibly even reading) rows this connection has locked; if that actually happens, this connection's Conn # will show up in the Blocked by Conn # for the other connections.

If the Uncommitted number is larger than Locks Held, it may be that this connection is repeatedly updating the same rows without committing the changes.

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.

See also:

Monitor - Executing, Idle, Waiting Conns
Monitor - Locks Held, Conns Blocked, Waiting Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Alert #28 Long transaction
Alert #34 Long uncommitted
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #3 Long transaction
Connection Flag #6 Locks
Connection Flag #9 Long uncommitted
Connection Flag #23 Total transaction time
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #3 Long transaction
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Waiting Time, Busy, Wait, Idle

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

The Busy, Wait, Idle columns are displayed for target databases running on SQL Anywhere 10 and later, and are based on the connection-level LoginTime, ReqTimeActive, ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties:

   total_time = time since LoginTime
   busy_time  = ReqTimeActive - ReqTimeBlockIO - ReqTimeBlockContention - ReqTimeBlockLock
   wait_time  = ReqTimeUnscheduled + ReqTimeBlockIO + ReqTimeBlockContention + ReqTimeBlockLock 
   idle_time  = total_time - busy_time - wait_time


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
CPU, Child Conns
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.

See also:

Monitor - Conns / Parent, Child Conns
Monitor - CPU
Monitor - Connections - CPU, Child Conns
Alert #4 CPU usage
Alert #27 Connection CPU
Connection Flag #5 CPU usage
>Connection Flag #10 Child connections
Connection Flag #24 Total CPU time
AutoDrop #5 CPU usage

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Temp Space, Rollback Log, Uncommitted
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 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 space any individual connection can use on a target database running on SQL Anywhere 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 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.

See also:

Monitor - Temp DBSpace - Size, Used, Frags, Avail, File
Monitor - Temp Space, Rollback Log, Uncommitted

Alert #6 Temp disk space
Alert #21 Temp space usage
Alert #22 Conn temp space usage
Connection Flag #4 Temp space usage
AutoDrop #4 Temp space usage
Adhoc Query: Connection-level temporary space usage

Alert #32 Rollback log usage
Connection Flag #7 Rollback log usage

Alert #33 Uncommitted operations
Alert #34 Long uncommitted
Connection Flag #8 Uncommitted operations
Connection Flag #9 Long uncommitted
Connection Flag #23 Total transaction time


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Low Memory, Cache Satisfaction
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 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:

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.

See also:

Monitor - Cache
Monitor - Cache Panics, Low Memory, Satisfaction.
Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Low Memory, Cache Satisfaction
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Time Since Last Request
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.


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Current Req Status
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.

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:

Current Req Status is displayed for target databases running on SQL Anywhere 9 and later.

See also:

Monitor - Executing, Idle, Waiting Conns
Monitor - Locks Held, Conns Blocked, Waiting Time
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Disk/Cache: Internal Index, Leaf, 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 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 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 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.

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%".

See also:

Monitor - Cache
Monitor - Cache Panics, Low Memory, Satisfaction.
Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Low Memory, Cache Satisfaction
Alert #19 Cache size
Alert #20 Cache satisfaction
Alert #29 Cache panics
Connection Flag #11 Cache satisfaction

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Disk Reads, Disk Writes, Log Writes
Disk Reads is the pages per second rate at which pages were read from disk for this connection in the previous interval.
Disk Reads is based on the connection-level DiskRead property..

Disk Writes is the pages per second rate at which modified pages were written to disk for this connection in the previous interval.

Disk Writes is based on the connection-level DiskWrite property..

Log Writes is the pages per second rate at which pages were written to the transaction log for this connection in the previous interval.

Log Writes is based on the connection-level LogWrite property.

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.

See also:

Monitor - Incomplete Reads, Writes
Monitor - Disk Reads, Disk Writes, Log Writes
Alert #15 Incomplete I/O
Alert #16 I/O operations
Connection Flag #12 Disk read rate
Connection Flag #13 Disk write rate
Connection Flag #14 Log write rate
Connection Flag #25 Total disk reads
Connection Flag #26 Total disk writes
Connection Flag #27 Total log writes

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Index Adds, Lookups, Satisfaction
Index Adds is the entries per second 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.
It may also indicate that a large number of row updates are changing the values of index columns, causing new index entries to be added even though no new rows are being inserted.

Index column updates may be performed directly, or indirectly when some other non-indexed columns are updated but there is an index on a DEFAULT TIMESTAMP column that is implicitly changed when any other columns are updated.

Index Adds is based on the connection-level IndAdd property.

Index Lookups is the entries per second 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.

Performance Tip: A small Index Satisfaction value (less than 100%) may indicate that a performance bottleneck is caused by full index comparisons.
A full index comparison occurs when data stored in the physical index pages is not sufficient to satisfy an index lookup, and additional index data must be retrieved from the physical table pages.

Index Satisfaction is displayed for target databases running on all versions of SQL Anywhere that are supported by Foxhound, and is based on the connection-level IndLookup and FullCompare properties.

See also:

Monitor - Disk/Cache: Internal Index, Leaf, Table
Monitor - Index Adds, Lookups, Satisfaction
Monitor - Full Index Comps
Monitor - Connections - Disk/Cache: Internal Index, Leaf, Table
Monitor - Connections - Full Index Comps
Connection Flag #15 Index add rate
Connection Flag #16 Index lookup rate
Connection Flag #17 Index satisfaction
Connection Flag #18 Full index comp rate
Connection Flag #28 Total index adds
Connection Flag #29 Total index lookups
Connection Flag #30 Total full index comps
SQL Anywhere's Graphical Plan With Statistics feature

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Full Index Comps
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: A large Full Index Comps value (more than zero) may indicate that a performance bottleneck is caused by full index comparisons.
A full index comparison occurs when data stored in the physical index pages is not sufficient to satisfy an index lookup, and additional index data must be retrieved from the physical table pages.

Full Index Comps is displayed for target databases running on all versions of SQL Anywhere that are supported by Foxhound, and is based on the connection-level FullCompare property.

See also:

Monitor - Full Index Comps
Monitor - Index Adds, Lookups, Satisfaction
Monitor - Connections - Index Adds, Lookups, Satisfaction
Connection Flag #16 Index lookup rate
Connection Flag #17 Index satisfaction
Connection Flag #18 Full index comp rate
Connection Flag #30 Total full index comps
SQL Anywhere's Graphical Plan With Statistics feature

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Isolation Level
Isolation Level shows the current isolation level and the updatable statement snapshot isolation level settings for this connection:
isolation level
0
1 No dirty reads
2 Repeatable reads
3 Serializable
Snapshot
Statement-snapshot
Readonly-statement-snapshot + n, where n is the updatable_statement_isolation value 0, 1, 2 or 3

Note that a connection can make local changes to the isolation level used within a query, and the value displayed here does not reflect those local changes. It does, however, reflect changes made via the SET TEMPORARY OPTION statement.

Isolation Level is displayed for target databases running on SQL Anywhere 7 and later, and is based on the connection-level Isolation_level property for target databases running on SQL Anywhere 7 through 9, and on the connection-level Isolation_level and updatable_statement_isolation properties for version 10 and later.


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
AutoDropped for this reason:
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.


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Autodrop Result:
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.


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Flags:

For a description of the Connection-Level Flags feature see Monitor Options - Flag Settings.

If one or more Connection Flags are set for this connection, they will be displayed here.

To see more information about each flag, switch to the Sample History or Connection History pages to see individual Help buttons.

Administrative Tip: You can force the connection flags to be recalculated using all the samples recorded for this target database; see Recalculate Connection Flags on the Adhoc Queries Help page.

See also:

Adhoc Queries - Recalculate Connection Flags.
Monitor Options - Connection Flag Settings
Monitor - Connections - Flags

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Blocked By:
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 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 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.
Note that NodeAddress properties containing the string 'NA' are treated the same as empty, blank and NULL values, and are displayed as '-'.

Blocked By: Name is the connection name of the blocking connection.

Blocked By: Name is based on the connection-level Name property.

See also:

Monitor - Executing, Idle, Waiting Conns
Monitor - Locks Held, Conns Blocked, Waiting Time
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Block Reason:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Block Reason:
The Block Reason: line describes the lock that's blocking the connection; e.g.:
Block Reason Example Explanation
Lock on inventory Foxhound didn't find which lock was responsible for a block because it is limited to checking 100 locks held by the blocking connection.
Mutex Connection Exclusive lock on dba.test_mutex One connection is blocked when two connections run LOCK MUTEX IN EXCLUSIVE MODE.
Position Transaction Phantom lock on GROUPO.Departments A SELECT at isolation level 3 has blocked an INSERT.
Row Transaction Intent, Row Transaction Write lock on DBA.t A row change has blocked another row change in a Version 10 or later database.
Row Transaction Intent, Row Transaction Write, Row Transaction WriteNoPK lock on dba.t A no-primary-key row change has blocked another no-primary-key row change in a Version 12 or later database.
Row Transaction Intent, Row Transaction WriteNoPK lock on DBA.t A no-primary-key row change has blocked another no-primary-key row change in a Version 12 or later database.
Row Transaction Read lock on dba.t A SELECT at isolation level 2 has blocked a row change.
Row Transaction Write lock on DBA.t A row change has blocked another row change, or a row change has blocked a SELECT at isolation level 1
Schema Transaction Exclusive lock on DBA.t A LOCK TABLE IN EXCLUSIVE MODE has blocked an INSERT.
Schema Transaction Shared lock on DBA.inventory A SELECT has blocked an ALTER TABLE. Note that this lock isn't counted in the Locks Held column.
Semaphore Connection lock on dba.test_semaphore A CREATE SEMAPHORE test_semaphore START WITH 0 has blocked a WAITFOR SEMAPHORE test_semaphore. Note that this lock isn't counted in the Locks Held column.
Table Transaction Intent lock on dba.t An UPDATE will block UNLOAD FROM TABLE t, but not UNLOAD SELECT * FROM t.
(E) Exclusive row lock on DBA.t A row change has blocked another row change in a Version 7, 8 or 9 or later database.

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 for SQL Anywhere 10 and later:

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, Read, Intent, ReadPK, Write, WriteNoPK, Surrogate, Phantom, Insert  
         [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.

See also Alert #23 Blocked connections and Alert #24 Conn blocking others.

Block Reason is displayed for target databases running on SQL Anywhere 7 and later, and is based on

See also:

Monitor - Executing, Idle, Waiting Conns
Monitor - Locks Held, Conns Blocked, Waiting Time
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Locked Row Query:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Locked Row Query:
The Locked Row Query: line displays a SELECT statement you can copy and paste into dbisql to find the row in the target database that is locked; e.g.,
Locked Row Query: SELECT * FROM DBA.t WHERE ROWID ( t ) = 37814272;

Locked Row Query is displayed for target databases running on SQL Anywhere 10 and later, and is based on the sa_locks() procedure.

See also:

Monitor - Executing, Idle, Waiting Conns
Monitor - Locks Held, Conns Blocked, Waiting Time
Monitor - Connections - Locks Held, Conns Blocked, Transaction Time
Monitor - Connections - Current Req Status
Monitor - Connections - Blocked By:
Monitor - Connections - Block Reason:
Alert #23 Blocked connections
Alert #24 Conn blocking others
Alert #25 Locks
Connection Flag #1 Blocking others
Connection Flag #2 Blocked by other
Connection Flag #6 Locks
AutoDrop #1 Blocking others
AutoDrop #2 Blocked by other
AutoDrop #6 Locks

Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Last Statement:
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.


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections 
Last Plan Text:
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.


Foxhound 5 » 3. The Monitor Database Page » 3.7 Connections