What's New in Foxhound 4

Alert #35 is issued if Foxhound is successfully gathering other sample data but has not been able to make a separate ping connection to the target database:

An All Clear #35 is issued as soon as Foxhound is able to make a separate ping connection to the target database when recording a successful sample; there is no waiting period for this.

Alert #35 is not issued if Ping-Only Sampling is in effect; Alert #1 - Database unresponsive is issued instead (i.e., when Foxhound is unable to make a ping connection while Ping-Only Sampling is in effect).

The new Foxhound ping process (and Alert #35) is available in the Extended Edition of Foxhound.

Previously, the Incomplete I/Os column was removed from the Monitor and History pages because the SQL Anywhere CurrIO database property on which it was based contained unreliable values.

Starting with Foxhound 4, the Incomplete Reads, Writes columns have been reintroduced for target databases running on SQL Anywhere 17, based on the CurrRead and CurrWrite database properties.

Incomplete Reads is the current number of file reads that have been started but not yet completed, and Incomplete Writes is the current number of file writes that have been started but not yet completed.

Consistently large Incomplete Reads and/or 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.

Previously, Alert #15 - Incomplete I/Os was removed from Foxhound because the SQL Anywhere CurrIO database property on which it was based contained unreliable values.

Starting with Foxhound 4, Alert #15 has been reintroduced for target databases running on SQL Anywhere 17, based on the CurrRead and CurrWrite database properties:

Alert #15 - Incomplete I/Os - The current number of incomplete file reads and/or writes has reached [2] or more for [10] or more recent samples.

Incomplete Reads is the current number of file reads that have been started but not yet completed, and Incomplete Writes is the current number of file writes that have been started but not yet completed.

Consistently large Incomplete Reads and/or 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.

In Foxhound 3 and earlier the database-level Throughput... Bytes columns actually contained values for the SQL Anywhere server as a whole when the server was running more than one database.

That has been improved in Foxhound 4 for target databases running on SQL Anywhere 16 and 17: the Throughput... Bytes columns now show values for the individual target database.

Note: This change does not affect the connection-level Throughput... Bytes columns which continue to show values for single connections on single databases.

In Foxhound 3 and earlier the connection-level Volume... Req, Commits, Bytes columns contained cumulative counts which were less useful than the rates of change calculated for the database-level Throughput columns.

That has been improved in Foxhound 4; the connection-level Throughput columns now show rates of change for connection-level data:

When you upgrade from an older version of Foxhound, the data upgrade process recalculates the connection-level Throughput columns.

For example, Volume... Req, Commits and Bytes values "- / 10 / 3M" from Foxhound 3 are recalculated as Throughput... Req, Commits and Bytes "- / .69/s / 281k/s" in Foxhound 4.

Here's what the Help says about the connection-level Throughput columns now:

---

Throughput, also known as bandwidth, is a measure of how much work the database has performed:

Throughput... Req is the rate at which the server has started processing a new request or resumed processing an existing request for this connection in the previous interval.

Performance Tip: Large Throughput... Req values may indicate that this connection is placing a heavy load on the database.

Note that "request" is an atomic internal unit of work processed by the server for a connection, not a client-server communication request from a client application to the SQL Anywhere server.

The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived column for adhoc queries of connection-level data.

Throughput... Req is displayed for target databases running on SQL Anywhere version 9 and later, and is based on the connection-level ReqCountActive property.

(note that this is different from the server-level Throughput... Req column which is based on the server-level Req property)

Throughput... Commits is the rate at which commit requests have been handled by the server for this connection in the previous interval.

Performance Tip: Large Throughput... Commits values may indicate that this connection is placing a heavy load on the database.

Throughput... Commits is based on the connection-level Commit property.

Throughput... Bytes is the rate at which data has been received by and sent by the server across the client server interface for this connection in the previous interval.

Performance Tip: Large Throughput... Bytes values may indicate that this connection is placing a heavy load on the database.

Throughput... Bytes is displayed for target databases running on SQL Anywhere versions 8 and later, and is based on the connection-level BytesReceived and BytesSent properties.

---

The following connection-level statistics were displayed as cumulative counts (e.g., total pages since login) in Foxhound 3 and earlier, and have now been changed to show more useful rates of change (e.g., pages per second in the previous interval):

• Disk Reads, Disk Writes, Log Writes

• Index Adds, Lookups

• Full Index Comps

The Monitor DEDICATED_TASK Option instructs the sampling process to set DEDICATED_TASK option ON for its connection to the target database.

The Foxhound Monitor will then be given a dedicated request-handling task by the SQL Anywhere server running the target database. This means Foxhound will be more likely to be able to gather samples when the target server is extremely busy or even unresponsive to other connections.

The Monitor Connection Settings section of the Monitor Options page can be used to specify a different setting for each target database, or the same setting for all target databases:

The new Monitor DEDICATED_TASK Option is available in the Extended Edition of Foxhound for target databases running on SQL Anywhere versions 9.0.1 and later.

Previously, all emails went to the same address or set of addresses, even if you wanted performance Alerts to go to one group of people and the AutoDrop Notices to another.

You can still do that, but now have the ability to specify different addresses.

You can also specify overlapping email addresses; for example, everything is sent to technical support while user management gets only the AutoDrop Notices.

Note that AutoDrop processing is available in the Extended Edition of Foxhound.

Foxhound now lets you know if a SQL Anywhere 17 mutex or semaphore is the reason a connection is blocked, as opposed to a traditional table or row lock:

Current Req Status

Current Req Status shows whether this connection was Idle, Waiting, Blocked or Executing.

Foxhound                                                              SQL Anywhere 
Current Req Status           SQL Anywhere Description                 ReqStatus
---------------------------  ---------------------------------------  -----------------
Idle                         The connection is not currently          Idle              
                                processing a request.

Waiting for thread           The connection has work to do and        Unscheduled         
                                is waiting for a worker thread.

Waiting for I/O              The connection is waiting for an I/O.    BlockedIO     
      
Waiting for shared resource  The connection is waiting for access to  BlockedContention    
                             shared database server data structures.

Blocked by lock              The connection is blocked waiting        BlockedLock
                             for a locked row.         

Blocked by mutex             The connection is blocked waiting        BlockedLock
                             for a mutex.         

Blocked by semaphore         The connection is blocked waiting        -         
                             for a semaphore.         

Executing                    The connection is executing a request.   Executing    

This column is based on the BlockedOn, LockObjectType and LockObjectOID properties for Blocked by mutex and semaphore values, and on the ReqStatus property for other values.

Block Reason:

The Block Reason: line describes the lock that's blocking the connection; e.g.:

Block Reason: Row Transaction Write lock on DBA.t

Block Reason: Row Transaction Intent, Row Transaction Write, Row Transaction WriteNoPK lock on dba.t

Block Reason: Mutex Connection Exclusive lock on dba.test_mutex

Block Reason: Semaphore Connection lock on dba.test_semaphore

The format of Block Reason: depends on the level of detail provided to Foxhound by the target database; for example, only SQL Anywhere 17 supports Mutex and Semaphore locks.

Here are the various layouts used, from high to low levels of detail:

Block Reason: [class] [scope/duration] [type] lock on [owner].[object]
              [class] [scope/duration] [type] lock on [object]
              [class] [scope/duration] [type] 
              Lock on [owner].[object]
              Lock on [object]

                  Fields...
         [class]: Schema, Row, Table, Position, Mutex, Semaphore
[scope/duration]: Transaction, Position, Connection   
          [type]: Shared, Exclusive, Write, ...
         [owner]: object creator
        [object]: table, mutex or semaphore name

This value is based on the sa_locks() procedure, the SYSMUTEXSEMAPHORE table, and the BlockedOn, LockObjectType and LockObjectOID properties.

Foxhound now displays three percentage comparisons of disk-versus-cache activity on the Monitor, Sample History and Connection History pages:

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.

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

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

These values are displayed in both the database and connection sections as well as on the database-level Peaks Since line.

Small values (e.g., zero or less than 1%) indicate that the cache is performing well.

It can be a challenge to keep track of Foxhound Monitor Options if you're watching multiple target databases: there's one complete set of options for each target database plus one more set for the [Default Settings].

Now, the Monitor Options page makes it easier to switch among different target databases by giving you separate drop-down list boxes [Default Settings] xxx (DSN) yyy (Connection String) everywhere the target database name is displayed.

Plus, only takes 2 clicks to switch rather than three: click once on the drop-down list to see all the target databases that Foxhound knows about, then point-and-click on a different target database to switch immediately.

Previously the point-and-click wasn't enough, you had click a third time to make the switch.

Plus, you could only see the drop-down list in one location (section 3. Switch to a Different Set of Monitor Options).

That section's gone now, replaced with drop-down lists wherever the target database name is displayed; for example:

In Foxhound 3 and earlier the Foxhound Monitor process used "Foxhound-pnnn" as the connection name on the target database, where nnn was an arbitrary integer 001, 002, etc.

That has changed in Foxhound 4 with the introduction of a user-defined template for the connection name.

The Monitor Connection Settings section of the Monitor Options page can be used to specify a different template for each target database, or a single template for all target databases.

The default template is "Foxhound-Monitor-nnnn" where the nnnn substring is replaced at runtime by the Windows Process Identifier (PID) of the SQL Anywhere dbsrv16.exe or dbsrv17.exe process that is running the Foxhound database. This makes it easier to exactly identify where this connection is coming from when there are a large number of target databases and Foxhound processes running.

Previously, the only way to switch from one target database to another on the Foxhound Monitor and History pages was to go back to the Foxhound Menu page and pick a different database.

Now, there are drop-down list boxes xxx (DSN) yyy (Connection String) on the Monitor and History pages that let you switch with 2 clicks: click once on the drop-down list to see all the target databases that Foxhound knows about, then point-and-click on a different target database to switch immediately:

What about the Connection History page?

No, there's no drop-down list on the Connection History page because it wouldn't make any sense.

The Connection History page is devoted to showing one particular connection, and that connection only appears on one single target database.

Previously, if you purchased the Basic Edition of Foxhound and then discovered you really needed the Extended Edition, you would pay a penalty to upgrade; i.e., the upgrade price was larger than the purchase price difference between the Basic and Extended editions.

Now, the price for a Basic-to-Extended Key is exactly the same as price difference between the Basic and Extended editions.

This price drop solves two problems:

1. Some folks only need the Basic Edition but they aren't sure so they buy the Extended Edition just to be safe, and end up spending too much money.

2. Some folks buy the Basic Edition when they really need the Extended Edition, and the penalty for upgrading was a heavy price to pay (pun intended) for a simple mistake.

...so, if you think you can get away with the Basic Edition, go ahead and buy it, there's no risk.

Previously, the Help topics for individual Alerts were pretty thin... ok, they were content-free :)

That's all changed, with

• Performance Tips,

• cross-reference links to and from other Help pages,

• descriptions of when each Alert and the corresponding All Clear is issued, and

• explanations of what properties are used for each Alert

• as well as which versions of SQL Anywhere are supported by each Alert.