The Foxhound ping process 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.
The new ping process comes in three flavors:
As an addition to the Foxhound Monitor sampling process.
The separate ping process tests the target database's
ability to accept new connections as well as providing data for a third measure of response time: ping response time.
As a complete 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.
As an addition to Y/N Sample Schedule settings.
"P" for ping-only 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.
The Monitor Options page lets you specify the ping settings for each target database:
The new Foxhound ping process is available in the Extended Edition of Foxhound.
A.2
Foxhound now supports SQL Anywhere 17.
Foxhound now supports target databases that run on any version of SQL Anywhere 6 through 17, including 17.0.4.
Foxhound itself will now run on SQL Anywhere 17, or SQL Anywhere 16 build 2127 or later.
Here are some other new features specific to SQL Anywhere 17:
A.4
No more pink! Black and white and grey are now used for highlighting.
Foxhound has always used colors like this
and this
to highlight important information.
Foxhound 4 now uses white-on-black and
grey for these reasons:
White-on-black text is just as dramatic as any color,
monochrome printers do a really bad job with colors, and
folks with color vision deficiency might have an easier time with the black-grey-white color scheme.
Foxhound 4 also uses white-on-black to highlight important information about Foxhound settings and status values; for example:
Sampling stopped when the Monitor is not gathering any data.
SPs NNN when the Monitor can't call these three
stored procedure on the target database: rroad_connection_properties, rroad_database_properties and rroad_engine_properties.
Favorable? YNY when one or more of the RememberLastPlan,
RememberLastStatement and RequestTiming server options are not set on the target server.
Purge Off when the Foxhound purge isn't deleting anything.
Also, alternating row colors are used for the Monitor, Sample History and other multi-row displays:
A.5
Banner warnings now expose conflicts among different Monitor Options page settings.
Individual settings on the Monitor Options page might be simple to understand and easy to use, but . . . conflicts among different settings aren't so simple or easy.
. . . and these conflicts lead to the most Frequently Asked Questions:
Why aren't Alert emails being sent?
Why isn't the Sample Schedule working?
Why aren't blocked connections being dropped?
Foxhound now warns about these conflicts on the Monitor Options page:
A.6
let you switch among multiple target databases.
If you're dealing with dozens of databases, you might like this new feature best of all!
B.1
Alert #35 - Separate ping failed
- Foxhound has been unable to make a separate ping connection to the target database for [10] or more recent samples.
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.
B.2
Incomplete Reads, Writes columns have been reintroduced for SQL Anywhere 17 target databases.
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.
B.6
Several other connection-level statistics have been changed from cumulative counts to rates of change.
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):
B.7
The DEDICATED_TASK option
can now be set for the Foxhound Monitor connection to the target database.
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.
B.8
Many more context-sensitive
Help links now appear on the Monitor, Sample History and Connection History pages.
Previously there was only one
link for each major section on the page,
but now there are separate links for almost every display title.
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.
B.12
Multiple
let you switch among different option sets on the
Monitor Options page.
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
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:
B.13
The Monitor and History pages have been redesigned to show more data in less space.
The Status section at the top of the Monitor and Sample History pages now includes detailed
information about the database, transaction log and temporary files: the file size, percent
used, number of fragments and the available space on the disk drive:
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
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:
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.
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.
Foxhound 3 introduced the Change Target Settings popup window that let you change the
RememberLastPlan, RememberLastStatement and RequestTiming server options on the target
database to be more or less favorable to the recording of data for Foxhound to display
on the Monitor, History and Connection History pages.
C.2
The Google Custom Search field has been added to all Foxhound Help pages.
The Foxhound Help pages exist in three locations:
in the right-hand frame of other Foxhound pages,
in static *.html files that are available via Start - All Programs - Foxhound4 - Help and
on the Foxhound website.
Previously the Google Custom Search field only appeared in the right-hand Help frame of other Foxhound pages, now it appears in all Foxhound Help pages.
C.3
The difference between server requests and client-server requests is
explained in the Help.
The following paragraph now appears in several places in the Help, wherever the "Req" column appears:
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.
Foxhound 3 introduced the Hide Details and Show Details buttons to let you reduce scrolling in the connection sections of the Sample History and Connection History pages.
In Foxhound 4, those buttons have been added to the Monitor page to hide and show the following lines for each connection:
AutoDrop Result:
Blocked By:
Block Reason:
Locked Row Query:
Last Statement:
Last Plan Text:
C.5
The effect of the connection section Hide Details / Show Details buttons is now persistent across the
Monitor,
Sample History and
Connection History pages.
In Foxhound 3 the effect of the new Hide Details / Show Details buttons was local to the connection section of each Sample History and Connection History page and it
did not persist beyond the point each page was redisplayed.
That has changed in Foxhound 4 with the introduction of a global Hide/Show setting whose effect applies across all pages that have Hide Details / Show Details buttons.
The setting also persists until changed by another press of the Hide Details / Show Details button on any connection section.
C.6
All Clear immediately issued for Alert #1 Database Unavailable when a sample is successfully recorded.
Previously, an All Clear might not have been immediately issued for Alert #1 as soon as Foxhound was able to connect to
the target database and record a sample. This delay often resulted in the impossible combination of
"Sampling OK" and "Alert #1 Database unavailable" appearing together on the Monitor page.
The problem was caused by the fact a separate process inside Foxhound handled Alert #1 processing including the issuing of an All Clear, and that
process runs on a different schedule than the sampling process.
Now, Foxhound issues an All Clear for Alert #1 as part of the sampling process, as soon as a sample is successfully recorded.
C.7
The patch process has been made more robust.
The patch process was introduced in Foxhound 3 so minor changes could be made without a complete reinstallation.
In Foxhound 4 the patch process has been changed to deal with more situations; for example:
The code that applies patches can itself be patched; no part of Foxhound is off-limits to the patch process.
If the patch file has become corrupted, the patch process will detect the problem and provide helpful diagnostics.
This improvement is accompanied by the following behavior changes:
A single patch file will only be processed once.
Foxhound will always stop after processing a patch file, requiring you to start Foxhound twice when applying a patch.
C.8
The Foxhound Activation page opens the Foxhound Menu page in the same window.
Previously, when you clicked on the Foxhound Menu link after successfully entering an activation key, the Foxhound Activation page stayed open
and the Foxhound Menu page opened in a new browser window. Foxhound often opens new pages in new browser windows, but in this case it made no sense:
the Foxhound Activation page is of no further use.
Starting with Foxhound 4 the Foxhound Menu page is opened in the same browser window, replacing the Foxhound Activation page (yes, this is no big deal, just a minor improvement :)
The Foxhound About page now also displays the Windows Process Identifier for the SQL Anywhere 16 server that's running the Foxhound database.
C.11
The Monitor tab
on the Foxhound Menu page now displays the sampling_id in the new ID column.
The sampling_id uniquely identifies each Foxhound sample session (target database).
This primary key value is helpful when running adhoc queries on the Foxhound database:
C.12
Small Heartbeat
and other elapsed time values are now shown exactly as measured.
In Foxhound 3 and earlier versions, elapsed time values in the range 1 to 99 milliseconds were shown as 0.1s, with the following
(now rejected) justification:
Although Heartbeat and Sample times are calculated to the millisecond (0.001 second), Foxhound doesn't show them
with any more precision than one decimal place (0.1 second) because the underlying logic is no more precise than that.
Sample times are simply rounded to the nearest tenth of a second.
However, Heartbeat times are often very small and would appear as zero if they were rounded to the nearest tenth
of a second. Because it may be important to see the difference between zero and non-zero values, Heartbeat
times are handled differently: zero values are shown as 0s and non-zero values between 1 and 99 milliseconds
are changed to 100 milliseconds; i.e., the smallest non-zero value shown is 0.1s even if the calculated Heartbeat
time is as small as 0.001 second.
Foxhound 4 now shows elapsed time values exactly as they are measured, down to the millisecond; for example 99 milliseconds
is shown as 99ms rather than 0.1s.
It is important to note 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.
This change affects the following displayed values only; the values in the Foxhound database have always been stored exactly as measured.
Foxhound Menu - Monitor tab: Heartbeat
Database-level sample data: Server Running Time, Age, Interval, Heartbeat, Sample Time, Waiting Time, CPU
Connection-level sample data: Time Connected, Transaction Time, Waiting Time, Time Since Last Request
Foxhound dbsrv16 -o console log text file: SQL Anywhere startup time, Foxhound startup time, Total startup time
C.13
Data rates
10 or larger are rounded to the nearest integer for more concise display.
Data rates are often displayed as k/s, M/s or G/s.
In Foxhound 3, when the displayed rate was 100 or
larger it was rounded to the nearest integer value, and smaller values were shown with a single decimal
digit; e.g., 21.1k/s and 10.8M/s.
In Foxhound 4 data rates 10 or larger are rounded so that 21.1k/s and 10.8M/s are now displayed as 21k/s and 11M/s.
This makes the Monitor, Sample History and Connection History pages a bit easier to read.
This affects how data rates are displayed, not stored. Columns in the Foxhound database hold the
original data with no rounding.
C.14
The description of Transaction Time
in the Help has been improved.
Here's what the Help now says about Transaction Time in several sections (Monitor, Sample History, Connection History,
Alert #28 - Long transaction, Alert #34 - Long uncommitted and AutoDrop #3 - Long transaction):
Transaction Time is the length of time since the database was first modified
by this connection after a COMMIT or ROLLBACK.
Performance Tip: A long Transaction Time may indicate that a
long-running transaction is a performance bottleneck.
Note that while it is possible for a SELECT to acquire locks and thus block other connections (e.g., a shared
lock obtained by a SELECT will block an ALTER TABLE), a SELECT does not
count as a modification as far as the Transaction Time is concerned.
In other words, a SELECT does not start a transaction.
Transaction Time is displayed for target databases running on SQL Anywhere 8 and later,
and is based on the connection-level TransactionStartTime property.
C.15
FAQ aliases are now displayed on each FAQ topic page.
Previously, secondary or alias titles were not displayed in the individual
FAQ pages even though they were included in the FAQ table of contents.
Now those aliases are also displayed on the individual FAQ pages so they can be indexed
by Google.
Here's an example of five different error messages that share one topic in the FAQ;
previously, all five appeared in the table of contents but
only the first one was displayed on the topic page:
Message: Can't start HTTP listener on address 127.0.0.1:80
Message: Can't start HTTP listener on address (::):80
Message: Can't start HTTP listener on address 0.0.0.0:80
Message: Could not bind to address 0.0.0.0:80
Message: TCP/IP link, function bind, error code 10048
Here's an example of a "Why?" question with a "How?" alias:
Question: Why isn't the new value of the FOXHOUND4 environment variable available immediately after Foxhound is installed?
Question: How do I get the new value of the FOXHOUND4 environment variable to show up after Foxhound is installed?
Here's an example of five different questions that all share the same likely answer:
Question: Why don't the schedules work?
Question: Why didn't the Alert Email Schedule go into effect when it was supposed to?
Question: Why didn't the AutoDrop Schedule go into effect when it was supposed to?
Question: Why didn't the Connection Sample Schedule go into effect when it was supposed to?
Question: Why didn't the Sample Schedule go into effect when it was supposed to?
C.16
The SQL Anywhere server and database names for the Foxhound database are displayed in the console log when Foxhound starts.
This will assist debugging large deployments where Foxhound is started as a service and it isn't
otherwise clear from the console log what server name has been specified on the dbsrv16 command line.
Here's an excerpt from the C:\ProgramData\RisingRoad\Foxhound4\foxhound4_debug.txt file; the
new "Foxhound server name:" and "Foxhound database name:" contain information that
isn't included in the "Foxhound command line:" because dbsrv16.exe is started as a service:
I. 10/25 13:28:48. Now accepting requests
I. 10/25 13:28:49. ---------------------------------------
I. 10/25 13:28:49. Foxhound Version 4.0 by RisingRoad - GA Edition 4.0.4602a built at 2015 10 25 12:08
in a SQL Anywhere 16.0.0.2127 database created at 2015 10 25 12:08
I. 10/25 13:28:49. ---------------------------------------
I. 10/25 13:28:49. No unused patch files were detected.
I. 10/25 13:28:49. Foxhound folder: C:\ProgramData\RisingRoad\Foxhound4
I. 10/25 13:28:49. Foxhound command line: "C:\Program Files\SQL Anywhere 16\Bin64\dbsrv16.exe"
-hvSQLANYs_Foxhound4
I. 10/25 13:28:49. Foxhound server name: foxhound4svc
I. 10/25 13:28:49. Foxhound database name: f
I. 10/25 13:28:49. Foxhound Windows API support DLL: rroad464.dll
I. 10/25 13:28:49. SQL Anywhere startup time: 6.3s
I. 10/25 13:28:49. Foxhound startup time: 1.8s
I. 10/25 13:28:49. Total startup time: 8.1s
I. 10/25 13:28:49. Foxhound now accepting requests.
The String tab on the Foxhound Menu page has been changed to include "SQL Anywhere Native" and "SQL Anywhere 17"
and to show "\bin64\dbsrv..." instead of "\bin32\dbeng..." when the Show Examples button is clicked.
C.18
The "HA:" field has been replaced with an untitled "database disposition" field
that applies to all databases.
Previously, the "HA:" field only appeared for databases that were participating in a high-availability (HA) setup.
Now, an untitled "database disposition" field includes HA, scale-out copy and read-only databases:
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]
Here's what the database disposition message looks like after an HA failover
(the updatable primary database was originally running on server1, but that server failed so the read-only secondary database on server2 took over the role as updatable primary):
Previously, long constraint names caused the Table Curiosity section of Foxhound's Display Schema page to grow so wide you had to scroll to the right to read it.
Now, long names are allowed to wrap so the Table Curiosity messages are easier to read.
C.20
Two security solutions involving simple GRANT EXECUTE statements have been added to the
Connecting to Target Databases section of the Help.
Previously, the GRANT DBA statement was the only solution offered for the problems of Foxhound not being able to call sa_disk_free_space() and sa_locks()
in target databases running on SQL Anywhere version 12 or earlier.
Now, GRANT EXECUTE statements are offered as less-drastic alternatives to GRANT DBA:
Target SQL
Anywhere
Version Symptom Reason Solution(s)
-------------- ------------------------- --------------------- ---------------------------
12 and earlier Foxhound doesn't display Foxhound is not GRANT EXECUTE ON
free space on the target allowed to call the dbo.sa_disk_free_space
database disk drives, sa_disk_free_space() TO FOXHOUND;
and doesn't issue Alerts procedure on the
5, 6, 7 or 8 about low target database. -- or --
disk space.
GRANT DBA TO FOXHOUND;
-- this also solves all
the other problems.
-------------- ------------------------- --------------------- ---------------------------
12 and earlier Foxhound doesn't display Foxhound is not GRANT EXECUTE ON
detailed information allowed to call the dbo.sa_locks
about why a connection is sa_locks() procedure TO FOXHOUND;
blocked. on the target
target database. -- or --
GRANT DBA TO FOXHOUND;
-- this also solves all
the other problems.
-------------- ------------------------- --------------------- ---------------------------
C.21
Terabytes are now abbreviated as "T".
Foxhound used to stop at G but not anymore: 1,199G is now shown as 1.2T.
What's next? P, E, Z, Y... (petabyte, exabyte, zettabyte, yottabyte...)
D. Behavior Changes
D.1
The data upgrade process may take a long time to calculate new connection-level statistics.
In Foxhound 3 and earlier several connection-level columns contained cumulative counts
which were less useful than the rates of change shown by other columns.
The good news is the following connection-level columns now show rates of change for connection-level data:
The bad news is it may take a very long time to upgrade a large Foxhound Version 1, 2 or 3 database to Foxhound 4.
The reason is that a large Foxhound database may contain millions of sample_connection rows,
and the data upgrade process has to calculate these new columns from scratch:
2016-05-15 14:13:32.872 DATA UPGRADE: Calculating Foxhound Monitor connection-level rates, batch 1 of 94 of 9,303,662 total rows.
2016-05-15 14:13:49.783 DATA UPGRADE: Calculating Foxhound Monitor connection-level rates, batch 2 of 94 of 9,303,662 total rows.
2016-05-15 14:14:06.878 DATA UPGRADE: Calculating Foxhound Monitor connection-level rates, batch 3 of 94 of 9,303,662 total rows
...
2016-05-15 15:19:39.734 DATA UPGRADE: Calculating Foxhound Monitor connection-level rates, batch 92 of 94 of 9,303,662 total rows.
2016-05-15 15:20:27.111 DATA UPGRADE: Calculating Foxhound Monitor connection-level rates, batch 93 of 94 of 9,303,662 total rows.
2016-05-15 15:21:44.110 DATA UPGRADE: Calculating Foxhound Monitor connection-level rates, batch 94 of 94 of 9,303,662 total rows.
Performance Tip: To make the data upgrade process go a lot faster, tell it to ignore old samples:
Type a different value when the post-setup process prompts you for a new value of
FOXHOUND4UPGRADE; for example:
Current FOXHOUND4UPGRADE=ALL
New FOXHOUND4UPGRADE=OPTIONS - copy everything EXCEPT old sample data
Current FOXHOUND4UPGRADE=ALL
New FOXHOUND4UPGRADE=20160501 - copy everything EXCEPT sample data recorded before May 1, 2016
This applies to the version of SQL Anywhere being used to run Foxhound itself,
and has nothing to do with the version of SQL Anywhere being used to run your target databases
(which can be any of 6, 7, 8, 9, 10, 11, 12, 16 and 17).
The delivered Foxhound shortcuts (such as All Programs - Foxhound4 - Start Foxhound via default browser) choose which version as follows:
IF EXIST "!SQLANY17!" (
SET SDIR=!SQLANY17!
SET SPGM=dbsrv17.exe
)
IF EXIST "!SQLANY16!" (
SET SDIR=!SQLANY16!
SET SPGM=dbsrv16.exe
)
IF /I "!FOXHOUND4SVER!z"=="17z" (
SET SDIR=!SQLANY17!
SET SPGM=dbsrv17.exe
)
IF /I "!FOXHOUND4SVER!z"=="16z" (
SET SDIR=!SQLANY16!
SET SPGM=dbsrv16.exe
)
IF EXIST "!SDIR!\Bin32" (SET BIN=Bin32)
IF EXIST "!SDIR!\Bin64" (SET BIN=Bin64)
IF "%FOXHOUND4BIN%z"=="Bin32z" (SET BIN=Bin32)
IF "%FOXHOUND4BIN%z"=="Bin64z" (SET BIN=Bin64)
D.3
Foxhound no longer displays "-" for unavailable Locked Row Query values.
When Foxhound is unable to show a SELECT statement that you can use to display the locked row affecting a blocked connection,
because the information is not available or doesn't apply (as with a block caused by a mutex lock), Foxhound no longer shows
the "Locked Row Query:" line at all.
Previously Foxhound displayed a dash "-".
D.4
The "Totals:" line has been removed from the Monitor and History pages.
Previous versions of Foxhound displayed a "Totals:" line in the "Most Recent Sample" and "Top Sample"
sections of the Monitor, Sample History and Connection History pages.
D.5
The new Foxhound transaction log is deleted and recreated after an old copy of Foxhound is upgraded.
When an old copy of the Foxhound database is upgraded to a new version of Foxhound,
or even a re-installation of the same version of Foxhound,
the data upgrade process may greatly inflate the size of the new transaction log.
Since the new transaction log isn't required for recovery at this point (the installation can simply be re-run),
the installation process now deletes the new transaction log after the upgrade is finished.
When Foxhound is started for the first time after that, the SQL Anywhere server creates a new, empty transaction log.
D.6
The current timestamp has been moved to the menu line on the Monitor and History pages.
Previously, the current timestamp appeared at the top left corner of the Monitor and Sample History pages.
To reduce confusion with sample timestamps the current timestamp has been moved to top right end of the menu line.
Also, the current timestamp has been added to the menu line on the Connection History page.
D.7
The GlobalDBID (global_database_id) has been moved from page titles to the Status area of the Monitor page.
Previously, if the SET OPTION PUBLIC.global_database_id statement had been used to specify a non-default value for the GlobalDBID property, that value was shown in parentheses in the Foxhound page titles; e.g.,
DSN: SQL Anywhere 16 Demo (123)
To reduce confusion and clutter, the GlobalDBID value has been moved to the Status area at the top of the Monitor and Sample History pages, and given a title:
GlobalDBID: 123
The GlobalDBID value is now 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
D.8
Out-of-range CurrIO values are no longer set to zero.
Previously, wildly-out-of-range values of the SQL Anywhere database property CurrIO
were adjusted before being stored in sample_detail.CurrIO for adhoc reporting:
IF CurrIO < 0
OR CurrIO > 4000000000 THEN
SET CurrIO = 0
That adjustment has been removed in Foxhound 4 because:
The adjustment did not fix all the problems with CurrIO values.
Foxhound does not display CurrIO on the Monitor or Sample History pages.
The CurrIO property should not be trusted for target databases running on SQL Anywhere 16 or earlier (in particular,
Version 7 returns negative values, Version 10 returns huge values, and other versions return non-zero values for idle databases).
The CurrIO property may now be reliable for target databases running on SQL Anywhere 17 but it is not necessary
because Foxhound 4 displays the CurrRead and CurrWrite database properties in the Incomplete Reads, Writes columns.
However, if you do include sample_detail.CurrIO in your adhoc queries for target databases
running on SQL Anywhere 16 or earlier, this behavior change may affect the values you see.
D.9
Some email settings have been moved around.
"Where do I set up email?" is one of the most frequently asked questions about Foxhound.
The answer is, some global email settings go on the Foxhound Options page, and other
email settings specific to individual target databases go on the Monitor Options page.
It's not a perfect answer ("what does "some" mean?") but the situation is getting simpler.
In Foxhound 4, the following settings
Foxhound Options - 2. Global Email Settings - Default email address(es) for Alerts and AutoDrop Notices:
Foxhound Options - 2. Global Email Settings - Send Test Email
The Monitor Options - 7. Email Setup - Send Alert emails: checkbox is no longer automatically
unchecked when the list of email addresses is empty; it's up to you to fix inconsistencies.
The Monitor Options section 10. AutoDrop Notice Email Setup has been completely removed;
the Send AutoDrop Notice emails: checkbox has been moved to section 7. Email Setup.
When you press Monitor Options - Save And Restore Monitor Options - Use Extreme Settings, the
Monitor Options - 7. Email Setup - Send Alert emails: checkbox is checked if the Email address(es) for Alerts: field is not empty.
Otherwise, the checkbox setting is not changed when you press Use Extreme Settings.
...and the same is now true for the Send AutoDrop Notice emails: checkbox and Email address(es) for AutoDrop Notices: field.
D.10
The Freeze Frame Heights setting on the Sample History Menu is updated when Foxhound 4 is installed.
If you upgrade an existing copy of Foxhound Version 3 or earlier to Foxhound 4, you may notice the frame heights on the Sample History page are different... that's because the initial Freeze Frame Heights setting is changed by the Foxhound 4 data upgrade process.
If you don't like the new frame heights, just manually resize the frames and press Freeze Frame Heights to save the new sizes.
D.11
New Alerts (e.g. Alert #35 - Separate ping failed)
are enabled by default for existing target databases after upgrading from earlier versions of Foxhound.
When upgrading from Foxhound 3 to Foxhound 4, the following new Alert is enabled by default for existing target databases:
Alert #35. Separate ping failed.
Foxhound has been unable to make a separate ping connection to the target database for [10] or more recent samples.
When upgrading from Foxhound 1 or 2 to Foxhound 4, the following new Alerts are enabled by default for existing target databases:
Alert #32. Rollback log usage.
The total rollback log space used by all connections has been [1G] or larger for [10] or more recent samples.
Alert #33. Uncommitted operations.
The total number of uncommitted operations for all connections has reached [1,000,000] or more during [10] or more recent samples.
Alert #34. Long uncommitted.
The number of uncommitted operations has reached [1,000,000] or more while the transaction running time has reached [1m] or more for at least one connection.
Alert #35. Separate ping failed.
Foxhound has been unable to make a separate ping connection to the target database for [10] or more recent samples.
Previously, when upgrading to a new version of Foxhound, new Alerts were disabled by default for existing target databases.
This change has been made because one of the minor hallmarks of Foxhound has always been "Alerts are enabled by default".
Previously, this was true for new Alerts on new target databases, and now it is also true for new Alerts on existing target databases.
D.12
The column titles in the Most Recent Sample
and Top Sample
sections are no longer highlighted for peak values.
Previously, the column titles were color highlighted to match the column values when those values were within 50% or 80% of the peak values, in the following sections:
To reduce clutter and confusion those column titles are no longer highlighted; the data values are still highlighted, and that should be more than sufficient to draw your attention.
The Heartbeat, Sample and Ping columns may or may not be measurements of "latency"; it depends on the definition you choose.
There is no question, however, that Heartbeat, Sample and Ping measure three different kinds of "response" time, and that's why the title's been changed.
If you turn on the new Foxhound ping process to check that your target database is accepting new
connections, you may notice unexpectedly high connection numbers are assigned to other client connections.
For example,
if you start an ISQL session on an idle database,
and that connection is assigned a connection number of 1484,
and then you start a second ISQL session a few minutes later,
you might expect that session to be assigned connection number 1485,
but it is assigned 1606 instead because the Foxhound ping process has used up the intervening numbers.
D.15
The Foxhound "post-setup" process runs at an earlier point in the InstallShield setup.
Previously, the post-setup process ran after the InstallShield setup was finished. On some computer systems, this resulted in "Access denied" errors for certain files.
To avoid those errors Foxhound 4 now runs the post-setup process as the last step before the InstallShield setup finishes.
D.16
The initial width of the Help frame is set to 50% after an upgrade from an earlier version of Foxhound.
If you found the Help to be less than helpful in a previous version of Foxhound, you may have shrunk the size of the Help frame or turned it off together.
To encourage you to have a second look at the Help in Version 4, the upgrade process changes the frame width to take up half the page.
You can shrink it again, or close it by clicking on the X... and it's easy to open by clicking on one of the (?) icons.
D.17
The MultiProgrammingLevel for Foxhound's own database has been increased from -gn 120 to -gn 220.
All the Foxhound shortcuts have been changed to specify the dbsrv -gn 220 option instead of -gn 120.
This was necessary to support ping-only sampling for 100 target databases.
Note: This change only affects Foxhound's own database server; it has nothing to do with your target databases.
If you run Foxhound in a memory-constrained environment,
and you don't gather samples for large numbers of target database,
and/or you don't use ping-only sampling,
then feel free to lower the -gn value in the various batch files that start the Foxhound database;
However, if the MultiProgrammingLevel is too low to support all your target databases, Foxhound may
experience a storm (i.e. loop) of exceptions: SQLCODE = -307, SQLSTATE = 40W06, ERRORMSG() = All threads are blocked.
D.18
Official recognition of target databases running on SQL Anywhere 5.5 software has been removed from
Foxhound documentation.
Existing code that supports SQL Anywhere 5.5 target databases has not been removed from Foxhound 4, and
Foxhound 4 has been tested using target databases created with SQL Anywhere 5.5 and running on SQL Anywhere 6 software:
5. SQL Anywhere versions 6, 7, 8, 9, 10, 11, 12, 16 and 17 for target databases - Foxhound has also been
tested with target databases created with SQL Anywhere 5.5 and running on SQL Anywhere 6 software.
6. SQL Anywhere 16 (build 2127 or later) or SQL Anywhere 17 for Foxhound - Foxhound works with target
databases using 6 to 17 of SQL Anywhere but the Foxhound engine itself needs the 32-bit or 64-bit
non-authenticated version of SQL Anywhere 16 (build 2127 or later) or SQL Anywhere 17 to run.
E. Bug Fixes
E.1
The Delete button on the String tab of the Foxhound Menu page no longer causes a foreign key violation.
Previously, if the Delete button on the String tab of the Foxhound Menu page was used to delete a connection string that also appeared on the
Monitor tab, the connection string would still appear on the Monitor tab, and any attempt to display the Monitor or Sample History page
for that connection string would fail with a foreign key violation.
This problem has been fixed by no longer doing anything when the Delete button on the String tab of the Foxhound Menu page
is pressed for a connection string that is also appears on the Monitor tab.
If it is necessary to delete a connection string that appears on both tabs, it is first necessary to press the Delete link
on the Monitor tab (to delete the samples), then press the Delete button on the String tab (to delete the connection string).
E.2
The data upgrade process no longer causes foreign key violations by renaming old default Adhoc Schema connection strings.
Previously, the data upgrade process renamed the default Foxhound 2 connection string 'Adhoc Schema - autostart and connect'
to 'old Foxhound 2 Adhoc Schema - autostart and connect' so it would not be confused with the new default connection string
'Foxhound 4 Adhoc Schema - autostart and connect'.
This caused a foreign key violation if the Monitor Database process had previously been used on the old Adhoc Schema connection
string name; this violation occurred when an attempt was made to display those samples using the new version of Foxhound.
This problem was very rare because the Monitor Database process was rarely used on the Adhoc Schema database (which is empty, and
only intended for Display Schema purposes).
This problem has been fixed by no longer renaming any connection strings during the data upgrade process.
This means no attempt is made to solve the original problem (user confusion between the old and new Adhoc Schema connection string names).
E.3
The Activation process no longer fails after upgrading from an old database that has never been activated.
Previously the Activation page would fail to work properly during a second attempt to install Foxhound if the first
attempt installed the Foxhound database but didn't activate it. In that case, the second attempt to install Foxhound
would incorrectly perform the data upgrade process on the unactivated database from the first attempt, and the Activation
page would then incorrectly display the following message even if a valid key was provided:
Please provide the correct type of registration key.
[Unsupported activation expected [Unsupported edition name GA]] was expected,
but a New Extended Key was provided instead.
This problem has been fixed by preventing the data upgrade process from copying any data from an old database that has
never been activated (and therefore contains no data worth upgrading):
Data cannot be copied from an old Foxhound database that has never been activated.
If you see the new message, run the Start - Foxhound4 - Tools - Unsetup Foxhound process,
delete the Foxhound4 folder, and then install Foxhound again.
E.4
The Monitor Options page no longer fails with a "User-defined exception" when the sampling session is deleted.
Previously it was possible to use the
Monitor Options - Manage Multiple Monitor Sessions feature
to delete a sampling session while the options for that session were being displayed on the same page.
This resulted in the Monitor Options page being replaced by a "User-defined exception signaled" message.
That problem has been fixed by automatically switching the Monitor Options page to the Default Settings, together with this message:
Warning: The Monitor Options set has been switched to [Default Settings] because the previous Monitor Session has been deleted.
E.5
It is now possible to start a not-yet-activated 'GA' copy of Foxhound with a SQL Anywhere server name other from "foxhound4".
Previously it was necessary to start each freshly-installed copy of Foxhound
with the server name set to "foxhound4" in order to activate it, after which
Foxhound could be stopped and restarted with a different server name as long as it had been
activated as the Extended Edition.
This was an inconvenience when multiple copies of the Extended Edition were being
installed and activated on the same computer, where each copy requires a different SQL Anywhere server name.
That problem has been fixed by allowing a not-yet-activated 'GA' copy of Foxhound to be started
regardless of the server name specified.
E.6
Serious error messages have been moved from the database console log to the HTML interface.
Previously some serious setup error messages were easy to miss because they only appeared in the Foxhound database console log.
The following messages are now displayed in the Foxhound HTML output:
Only the Extended edition of Foxhound can be started with a
SQL Anywhere server name other than "foxhound4".
Foxhound itself cannot be run using the OEM or AUTHENTICATED
editions of SQL Anywhere.
The Foxhound database must run on its own SQL Anywhere engine.
E.7
Error messages are now displayed when the buttons don't work in section
14. Change Target Settings of the Monitor Options page.
Previously, when Foxhound did not have permission to call the sa_server_option() procedure on the target database, no indication of any problem
was displayed even though the Yes and No buttons had no effect in section 14. Change Target Settings of the Monitor Options page.
Now, when a button is pressed, one of the following messages are displayed:
OK
-- or --
Foxhound:-121;42501;Permission denied: you do not have permission to execute the procedure "sa_server_option"
Previously, the Status Area on the Sample History page incorrectly contained the most recent
information about the server and database, similar to the behavior of the Status Area on the Monitor page.
Now, the Status Area on the Sample History page contains information that matches the age of
the data in the Top Sample section on the same page.
Well, they work more-or-less as expected; some of the time-based scroll amounts (e.g., 3 hours) exhibit [cough]
interesting behavior when there's a large gap in sampling around the destination :)
E.10
The Interval value for a successful sample following an unsuccessful sample is calculated correctly.
Previously, the Interval value for successful sample was incorrectly set to zero if it immediately followed a message line or other "unsuccessful" sample.
For example, the Interval column would display a dash "-" (zero) in the Interval column for
a successful sample that immediately follows a "Timed out" message, with the result that the
Interval column values did come close to accounting for all the time passing.
Now, those situations are correctly handled and a non-zero values are displayed in the Interval column.
E.11
The message "Please fill in both User Id and Password, or leave both empty" is now correctly displayed
on the Foxhound Menu page.
Previously, if the user id was filled in but the password was left empty on the DSN tab of the Foxhound Menu page,
and the Monitor Database button was pressed, Foxhound would ignore the user id and password values rather than
displaying the message "Please fill in both User Id and Password, or leave both empty."
Foxhound did handle the Display Schema button correctly, just not the Monitor Database button.
This led to incorrect and/or confusing behavior; e.g., Foxhound might be able to connect to the target
database for the purpose of gathering samples but the separate ping process would fail.
This omission has now been fixed.
E.12
An All Clear for Alert #13 File fragmentation
is now correctly issued when the number of fragments falls below the threshold amount.
Previously, Foxhound waited until the number of file fragments fell to zero before issuing
an All Clear for Alert #13 File fragmentation, which would never happen because the 1 is the
smallest possible value.
This error has now been fixed; it was discovered when the Help was being upgraded to include more information:
This Alert is issued when the number of fragments has reached or exceeded the threshold amount.
A subsequent All Clear #13 is issued as soon as the number of fragments as fallen below the
threshold amount; there is no waiting period as with some other alerts.
E.13
Invalid dependencies in rroad4.dll and rroad464.dll have been removed to avoid "Could not load dynamic library" errors.
Previously, the Foxhound database would fail to start on some computer systems that were missing certain Windows system DLL files such as MSVCR90.DLL.
Foxhound's dependencies on those DLL files were unnecessary and have now been eliminated.
E.14
The Monitor Options page now correctly cancels alerts when the criteria has changed.
Previously the Monitor Options page did not correctly record the original "alert in effect at" timestamp
when issuing an Alert Cancelled when the criteria has been changed.
This could result in cancelled alerts being displayed as still active on the Foxhound Menu - Sampling tab;
e.g., two Alert #1 messages for the same target database when one Alert #1 is active and the other was cancelled earlier.
This could also affect adhoc reporting when the alert_cancelled.alert_in_effect_at column contained
the incorrect value '1900-01-01 00:00:00.000' instead of a correct value like '2016-09-24 08:01:36.359'
which matches the alert.alert_in_effect_at in the corresponding alert row.
This has now been fixed.
E.15
The alert_union view for adhoc reporting has been fixed to correctly use the alert_in_effect_at columns.
Previously the alert_in_effect_at columns in the alert, alert_cancelled and all_clear views were not correctly used
in predicates that matched rows in the three views. This has now been fixed.
Also, the alert_is_clear_or_cancelled columns are returned as literal 'Y' values for
alert_cancelled and all_clear rows because by definition, the corresponding alerts are no longer active.
Here are the four affected rows in the CREATE VIEW:
AND rroad_all_clear.alert_in_effect_at = rroad_alert.alert_in_effect_at ) -- FIXED
AND rroad_alert_cancelled.alert_in_effect_at = rroad_alert.alert_in_effect_at ) -- FIXED
'Y' AS alert_is_clear_or_cancelled -- FIXED
'Y' AS alert_is_clear_or_cancelled -- FIXED
Here's the affected CREATE VIEW (alert_union is based on rroad_alert_union):
CREATE VIEW rroad_alert_union AS
SELECT CAST ( 'Alert' AS VARCHAR ( 20 ) ) AS record_type,
rroad_alert.sampling_id AS sampling_id,
rroad_sampling_options.selected_tab AS selected_tab,
rroad_sampling_options.selected_name AS selected_name,
rroad_alert.sample_set_number AS sample_set_number,
rroad_alert.alert_number AS alert_number,
rroad_alert.alert_in_effect_at AS recorded_at,
rroad_alert.alert_description AS alert_description,
rroad_alert.email_status AS email_status,
CAST ( IF EXISTS ( SELECT *
FROM rroad_all_clear
WHERE rroad_all_clear.sampling_id = rroad_alert.sampling_id
AND rroad_all_clear.alert_number = rroad_alert.alert_number
AND rroad_all_clear.alert_in_effect_at = rroad_alert.alert_in_effect_at ) -- FIXED
OR EXISTS ( SELECT *
FROM rroad_alert_cancelled
WHERE rroad_alert_cancelled.sampling_id = rroad_alert.sampling_id
AND rroad_alert_cancelled.alert_number = rroad_alert.alert_number
AND rroad_alert_cancelled.alert_in_effect_at = rroad_alert.alert_in_effect_at ) -- FIXED
THEN 'Y'
ELSE 'N'
ENDIF AS VARCHAR ( 1 ) ) AS alert_is_clear_or_cancelled
FROM rroad_alert
INNER JOIN rroad_sampling_options
ON rroad_sampling_options.sampling_id = rroad_alert.sampling_id
UNION ALL
SELECT CAST ( 'All Clear' AS VARCHAR ( 20 ) ) AS record_type,
rroad_all_clear.sampling_id AS sampling_id,
rroad_sampling_options.selected_tab AS selected_tab,
rroad_sampling_options.selected_name AS selected_name,
rroad_all_clear.sample_set_number AS sample_set_number,
rroad_all_clear.alert_number AS alert_number,
rroad_all_clear.alert_all_clear_at AS recorded_at,
rroad_all_clear.alert_description AS alert_description,
rroad_all_clear.email_status AS email_status,
'Y' AS alert_is_clear_or_cancelled -- FIXED
FROM rroad_all_clear
INNER JOIN rroad_sampling_options
ON rroad_sampling_options.sampling_id = rroad_all_clear.sampling_id
UNION ALL
SELECT CAST ( 'Cancelled' AS VARCHAR ( 20 ) ) AS record_type,
rroad_alert_cancelled.sampling_id AS sampling_id,
rroad_sampling_options.selected_tab AS selected_tab,
rroad_sampling_options.selected_name AS selected_name,
rroad_alert_cancelled.sample_set_number AS sample_set_number,
rroad_alert_cancelled.alert_number AS alert_number,
rroad_alert_cancelled.alert_all_clear_at AS recorded_at,
rroad_alert_cancelled.alert_description AS alert_description,
rroad_alert_cancelled.email_status AS email_status,
'Y' AS alert_is_clear_or_cancelled -- FIXED
FROM rroad_alert_cancelled
INNER JOIN rroad_sampling_options
ON rroad_sampling_options.sampling_id = rroad_alert_cancelled.sampling_id
UNION ALL
SELECT CAST ( 'Placeholder' AS VARCHAR ( 20 ) ) AS record_type,
rroad_sampling_options.sampling_id AS sampling_id,
rroad_sampling_options.selected_tab AS selected_tab,
rroad_sampling_options.selected_name AS selected_name,
0 AS sample_set_number,
0 AS alert_number,
'1900-01-01' AS recorded_at,
'' AS alert_description,
'' AS email_status,
CAST ( '?' AS VARCHAR ( 1 ) ) AS alert_is_clear_or_cancelled
FROM rroad_sampling_options
WHERE NOT EXISTS ( SELECT *
FROM rroad_alert
WHERE rroad_alert.sampling_id = rroad_sampling_options.sampling_id )
AND NOT EXISTS ( SELECT *
FROM rroad_all_clear
WHERE rroad_all_clear.sampling_id = rroad_sampling_options.sampling_id )
AND NOT EXISTS ( SELECT *
FROM rroad_alert_cancelled
WHERE rroad_alert_cancelled.sampling_id = rroad_sampling_options.sampling_id );
F.2
A cross-table row numbering system now makes adhoc query UNION ORDER BY clauses easier to code.
A new numbering system has been introduced to assign globally-unique primary key
values across several tables that are commonly appear together in adhoc UNION queries; here's the full story:
Each time Foxhound records a sample, it assigns a globally-unique sample_set_number to that sample as follows:
The primary key column sample_set_number of the rroad_sample_set table (sample_header view)
is defined as UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT.
The sample_set_number is also stored in two child tables:
rroad_group_1_property_pivot (sample_detail view) and
Starting in Foxhound 4, the rroad_sample_set DEFAULT AUTOINCREMENT specification is
also used to generate primary key columns in several other tables via GET_IDENTITY() calls.
The reason for this is to make ORDER BY clauses easier to code when joining multiple tables.
For example, when gathering sample_detail and alert rows via UNION to display sample data together with alert messages,
the globally-unique (cross-table) primary keys make a simple UNION ORDER BY clause possible.
Here is a list of all the tables affected by this cross-table primary key generation:
The data upgrade process renumbers the affected primary key columns in Foxhound 3 and
earlier database when they are upgraded to Foxhound 4.
This renumbering process assigns an autoincrement primary key value that is globally
unique across seven different tables (rroad_sample_set plus
the six tables that now use GET_IDENTITY ( 'rroad_sample_set' )).
For example, a sample set number of 10700 in Foxhound 3 may become 8363 in Foxhound 4.
The sample timestamp (e.g. Jul 30 11:03:27 AM) will remain the same, and that value may
be used in the Sample History - Go to: field instead of 10700.
Performance Tip:
It's OK if you don't understand the explanation above...
For more information and free help writing complex adhoc queries contact
Breck.Carter@gmail.com.
F.3
Lots of new columns are available for adhoc queries.
Comments have been added to all the CREATE VIEW statements which you can view by following
the instructions shown here.
For your convenience, those comments are repeated here...
CREATE VIEW alerts_criteria ...
-----------------------------------------------------------------------------------------------------
-- alerts_criteria -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- New column: autodrop_mail_recipients LONG VARCHAR
-- New column: autodrop_mail_recipients_same_as_for_alerts VARCHAR ( 1 )
-- Not used: alert15_threshold_CurrIO BIGINT
-- Not used: alert15_threshold_CurrIO_sample_count BIGINT
-- Not used: alert15_actual_CurrIO_threshold_reached_sample_counter BIGINT
-- New column: alert15_threshold_CurrRead_CurrWrite BIGINT
-- New column: alert15_threshold_CurrRead_CurrWrite_sample_count BIGINT
-- New column: alert15_actual_CurrRead_CurrWrite_threshold_reached_sample_counter BIGINT
-- New column: monitor_dedicated_task_enabled VARCHAR ( 1 )
-- New column: monitor_dedicated_task_status LONG VARCHAR
-- New column: monitor_connection_name_template VARCHAR ( 100 )
-- New column: ping_only_sampling VARCHAR ( 1 )
-- New column: ping_enabled_for_successful_samples VARCHAR ( 1 )
-- New column: ping_enabled_for_lost_samples VARCHAR ( 1 )
-- New column: ping_repeated_during_outages VARCHAR ( 1 )
-- New column: ping_connection_name_template VARCHAR ( 100 )
CREATE VIEW autodropped_connection ...
-----------------------------------------------------------------------------------------------------
-- autodropped_connection -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- Changed column: autodrop_occurrence from BIGINT to UNSIGNED BIGINT
CREATE VIEW global_options ...
-----------------------------------------------------------------------------------------------------
-- global_options -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- New column: show_connection_details VARCHAR ( 1 )
CREATE VIEW peaks ...
-----------------------------------------------------------------------------------------------------
-- peaks -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- New column: peak_CurrRead BIGINT
-- New column: peak_CurrWrite BIGINT
-- New column: peak_ratio_ReadIndInt DECIMAL ( 30, 6 )
-- New column: peak_ratio_ReadIndLeaf DECIMAL ( 30, 6 )
-- New column: peak_ratio_ReadTable DECIMAL ( 30, 6 )
-- New column: peak_successful_ping_elapsed_msec BIGINT
-- New column: peak_CurrRead_sample_set_number UNSIGNED BIGINT
-- New column: peak_CurrWrite_sample_set_number UNSIGNED BIGINT
-- New column: peak_ratio_ReadIndInt_sample_set_number UNSIGNED BIGINT
-- New column: peak_ratio_ReadIndLeaf_sample_set_number UNSIGNED BIGINT
-- New column: peak_ratio_ReadTable_sample_set_number UNSIGNED BIGINT
-- New column: peak_successful_ping_elapsed_msec_sample_set_number UNSIGNED BIGINT
CREATE VIEW ping_log ...
-----------------------------------------------------------------------------------------------------
-- ping_log -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- New view: ping_log
CREATE VIEW run_characteristics ...
-----------------------------------------------------------------------------------------------------
-- run_characteristics -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- New column: startup_checked_ok_on DATE
CREATE VIEW sample_connection ...
-----------------------------------------------------------------------------------------------------
-- sample_connection -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- Removed: ReqCountBlockContention BIGINT
-- Removed: ReqCountBlockIO BIGINT
-- Removed: ReqCountBlockLock BIGINT
-- Removed: ReqCountUnscheduled BIGINT
-- Removed: previous_ApproximateCPUTime DECIMAL ( 30, 6 )
-- Removed: previous_Commit BIGINT
-- Removed: previous_Rlbk BIGINT
-- Removed: interval_Rlbk BIGINT
-- Removed: total_waits BIGINT
-- New column: CacheReadIndInt BIGINT
-- New column: CacheReadIndLeaf BIGINT
-- New column: CacheReadTable BIGINT
-- New column: ClientStmtCacheHits BIGINT
-- New column: ClientStmtCacheMisses BIGINT
-- New column: CurrentLineNumber BIGINT
-- New column: CurrentProcedure LONG VARCHAR
-- New column: Dedicated_task LONG VARCHAR
-- New column: DiskReadIndInt BIGINT
-- New column: DiskReadIndLeaf BIGINT
-- New column: DiskReadTable BIGINT
-- New column: LockObjectOID BIGINT
-- New column: LockObjectType BIGINT
-- New column: max_client_statements_cached BIGINT
-- New column: Max_plans_cached BIGINT
-- New column: max_query_tasks BIGINT
-- New column: priority LONG VARCHAR
-- New column: QueryHeapPages BIGINT
-- New column: ratio_ReadIndInt DECIMAL ( 30, 6 )
-- New column: ratio_ReadIndLeaf DECIMAL ( 30, 6 )
-- New column: ratio_ReadTable DECIMAL ( 30, 6 )
-- New column: rate_bytes DECIMAL ( 30, 6 )
-- New column: rate_Commit DECIMAL ( 30, 6 )
-- New column: rate_ReqCountActive DECIMAL ( 30, 6 )
-- New column: rate_DiskRead DECIMAL ( 30, 6 )
-- New column: rate_DiskWrite DECIMAL ( 30, 6 )
-- New column: rate_FullCompare DECIMAL ( 30, 6 )
-- New column: rate_IndAdd DECIMAL ( 30, 6 )
-- New column: rate_IndLookup DECIMAL ( 30, 6 )
-- New column: rate_LogWrite DECIMAL ( 30, 6 )
CREATE VIEW sample_detail ...
-----------------------------------------------------------------------------------------------------
-- sample_detail -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- Removed: interval_Rlbk BIGINT
-- New column: CacheReadIndInt BIGINT
-- New column: CacheReadIndLeaf BIGINT
-- New column: CacheReadTable BIGINT
-- New column: CheckpointLogSize BIGINT
-- New column: CurrRead BIGINT
-- New column: CurrWrite BIGINT
-- New column: DiskReadIndInt BIGINT
-- New column: DiskReadIndLeaf BIGINT
-- New column: DiskReadTable BIGINT
-- New column: GlobalDBId BIGINT
-- New column: SnapshotIsolationState LONG VARCHAR
-- New column: ratio_ReadIndInt DECIMAL ( 30, 6 )
-- New column: ratio_ReadIndLeaf DECIMAL ( 30, 6 )
-- New column: ratio_ReadTable DECIMAL ( 30, 6 )
-- New column: ping_id UNSIGNED BIGINT
-- New column: ping_was_successful VARCHAR ( 1 )
-- New column: successful_ping_elapsed_msec BIGINT
-- New column: LogFileFragments BIGINT
-- New column: logFileSize BIGINT
-- New column: logFreePages BIGINT
-- New column: LogName LONG VARCHAR
-- New column: tempFileFragments BIGINT
-- New column: TempFileName LONG VARCHAR
-- New column: tempFileSize BIGINT
-- New column: tempFreePages BIGINT
CREATE VIEW sampling_options ...
-----------------------------------------------------------------------------------------------------
-- sampling_options -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- Removed: GlobalDBId BIGINT
-- New column: ping_only_sample_set_number UNSIGNED BIGINT
-- New column: ping_only_sampling_in_progress VARCHAR ( 1 )
CREATE VIEW session_options...
-----------------------------------------------------------------------------------------------------
-- session_options -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- Removed: GlobalDBId BIGINT
CREATE VIEW used_activation_key ...
-----------------------------------------------------------------------------------------------------
-- used_activation_key -- Foxhound Version 4 Changes...
-----------------------------------------------------------------------------------------------------
-- New column: artificial_primary_key INTEGER
G.1
Fix "unable to load dll" messages (previously published as Patch 3)
Fix "unable to load dll" messages:
Could not load dynamic library
Unable to load either rroad4.dll or rroad464.dll
Unable to read rroad4.dll
Unable to read rroad464.dll
(1) This fix delivers 4 new *.dll files and replaces 10 *.bat
files in the Foxhound 4 folder.
These files fix a problem where Foxhound does not start when
it is installed on a computer with SQL Anywhere 17 installed
but not SQL Anywhere 16. Symptoms include the following error
messages:
The program can't start because dblib16.dll is missing
Unable to load either rroad4.dll or rroad464.dll
The fix requires that all *.bat files which start the
Foxhound 4 database using dbsrv16.exe or dbsrv17.exe
must include code that replaces the rroad4.dll and
rroad464.dll with files that support whichever version
of SQL Anywhere is going to be used: 16 or 17.
The fix consists of these lines added to the *.bat files:
REM Code from patch-4729-003-DLLs-Properties...
IF /I "%SPGM%z%BIN%z"=="dbsrv16.exezBin32z" (COPY rroad4_sa16_bin32.dll rroad4.dll)
IF /I "%SPGM%z%BIN%z"=="dbsrv16.exezBin64z" (COPY rroad4_sa16_bin64.dll rroad464.dll)
IF /I "%SPGM%z%BIN%z"=="dbsrv17.exezBin32z" (COPY rroad4_sa17_bin32.dll rroad4.dll)
IF /I "%SPGM%z%BIN%z"=="dbsrv17.exezBin64z" (COPY rroad4_sa17_bin64.dll rroad464.dll)
Those lines are added to the following *.bat files:
$backup_foxhound4.bat
$start_foxhound4_chrome.bat
$start_foxhound4_chrome_debug.bat
$start_foxhound4_default_browser.bat
$start_foxhound4_default_browser_debug.bat
$start_foxhound4_engine.bat
$start_foxhound4_firefox.bat
$start_foxhound4_firefox_debug.bat
$start_foxhound4_ie.bat
$start_foxhound4_ie_debug.bat
(2) This fix also delivers replacement code for three stored
procedures inside the Foxhound database.
This code fixes a problem where the following connection-level
properties are set to zero when Foxhound cannot create or call
the rroad_connection_properties procedure on the target database;
i.e. when SPs: ..N is displayed on the Foxhound Monitor page:
CacheReadIndInt - displayed in the connection-level
CacheReadIndLeaf Disk/Cache: Internal Index,
CacheReadTable Leaf, Table columns
ClientStmtCacheHits - visible only through
ClientStmtCacheMisses adhoc reporting
CurrentLineNumber
DiskReadIndInt - displayed in the connection-level
DiskReadIndLeaf Disk/Cache: Internal Index,
DiskReadTable Leaf, Table columns
LockObjectOID - used to analyze blocks caused by
LockObjectType semaphores in SQL Anywhere 17
max_client_statements_cached - visible only through
Max_plans_cached adhoc reporting
max_query_tasks
QueryHeapPages
CurrentProcedure
Dedicated_task
priority
This problem does **NOT** affect target databases where Foxhound
**IS** able to call the rroad_connection_properties procedure;
i.e. when SPs: ..N is displayed on the Foxhound Monitor page.
Here is an adhoc query to determine if any of these properties
are being recorded in the most recent connection-level samples:
SELECT TOP 5
sampling_id,
sample_set_number,
connection_number,
CacheReadIndInt, -- - displayed in the connection-level
CacheReadIndLeaf, -- Disk/Cache: Internal Index,
CacheReadTable, -- Leaf, Table columns
ClientStmtCacheHits, -- - visible only through
ClientStmtCacheMisses, -- adhoc reporting
CurrentLineNumber,
DiskReadIndInt, -- - displayed in the connection-level
DiskReadIndLeaf, -- Disk/Cache: Internal Index,
DiskReadTable, -- Leaf, Table columns
LockObjectOID, -- - used to analyze blocks caused by
LockObjectType, -- semaphores in SQL Anywhere 17
max_client_statements_cached, -- - visible only through
Max_plans_cached, -- adhoc reporting
max_query_tasks,
QueryHeapPages,
CurrentProcedure,
Dedicated_task,
priority
FROM sample_connection
ORDER BY sample_set_number DESC;
-- Not OK...
6,936077,35,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,
6,936077,32,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,
6,936060,35,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,
6,936060,32,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,
6,936051,35,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,,,
-- OK...
6,935727,32,21,31,36,0,0,0,0,2,4,0,0,10,20,0,0,,Off,normal
6,935727,31,138,1558,8199,1039,0,219,0,3,1,0,0,10,20,0,0,rroad_connection_properties,Off,normal
6,935710,32,21,31,36,0,0,0,0,2,4,0,0,10,20,0,0,,Off,normal
6,935710,31,137,1538,8092,1025,0,219,0,3,1,0,0,10,20,0,0,rroad_connection_properties,Off,normal
6,935693,31,136,1519,7986,1011,0,219,0,3,1,0,0,10,20,0,0,rroad_connection_properties,Off,normal
G.2
Fix Display Schema for billion-row tables (previously published as Patch 4)
Fix Display Schema for billion-row tables:
Value nnn out of range for destination
This fix updates several stored procedures in the Display Schema
part of Foxhound 4 to solve this exception when a table has more
than 999,999,999 rows in the target database:
SQLCODE = -158
SQLSTATE = 22003
ERRORMSG() = Value nnn out of range for destination.
G.3
Prevent Foxhound from becoming unresponsive when the Monitor cannot connect (previously published as Patch 5)
Prevent Foxhound from becoming unresponsive
when the Monitor cannot connect to a network target database
and an endless loop of bogus error messages appears:
Attempt to reload definition for event "rroad_monitor_sample_loop"
failed due to concurrent event execution.
This fix changes the Monitor Database part of Foxhound 4 to work
around changes in SQL Anywhere 16 and 17 that result in the Foxhound
Monitor becoming unresponsive.
Foxhound may become unresponsive when the monitor is unable to make
a network connection to a target database, and it may be accompanied
by an endless loop of bogus error messages in the Foxhound file
C:\ProgramData\RisingRoad\Foxhound4\foxhound4_debug.txt:
Attempt to reload definition for event "rroad_monitor_sample_loop"
failed due to concurrent event execution.
This fix removes connection timeout and timeout retry processes from
the Foxhound Database Monitor, as well as eliminating explicit CHECKPOINT
statements from the Foxhound Purge process. Also, section 4. Connection
Timeout and section 5. Timeout Retry have been removed from the
Foxhound Options page.
There are also manual alternatives to this fix:
Check "never timeout" on the section 4. Connection Timeout of the
Foxhound Options page.
Run Foxhound on builds 2127 to 2193 of SQL Anywhere 16.
Run Foxhound on build 1359 of SQL Anywhere 17 or earlier.
Click the Stop All Sampling button on the Monitor tab of the
Foxhound Menu page (although the symptom may reappear later).
G.2
Prevent Ping-Only Sampling from becoming unresponsive (previously published as Patch 8)
Prevent Foxhound from becoming unresponsive
when Ping-Only Sampling is stopped and restarted, or
when full sampling is attempted for an arbiter server.
This fix changes the Monitor Database part of Foxhound 4 to fix
these problems with Ping-Only Sampling and the way Foxhound handles
High Availability (HA) arbiter servers:
(1) Foxhound appears unresponsive when an (incorrect) attempt is made to start
a full sampling session for the utility database on an arbiter server,
rather than (correctly) using ping-only sampling.
Note that the utility database isn't a real database, and doesn't support
the SQL statements Foxhound uses to gather a full set of sample data.
Depending on the history of the arbiter server (running, stopped, restarted)
the Monitor page may display a variety of misleading messages:
Sampling Running
(not connected yet)
Sampling starting...
Database server not found
The Display Diagnostics button on the Foxhound Options page may show that
a pair of "Permission denied" exceptions are appearing every 10 seconds:
122 - 2017-07-22 15:33:37.759 Full Build 4729a 1000000095 204.b5(204eh-d7)
SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p001': [Sybase]
[ODBC Driver][SQL Anywhere]Permission denied: you do not have permission
to execute a statement of this type [1-arbiter_demo]
121 - 2017-07-22 15:33:37.758 Full Build 4729a 1000000095 240a1a(240eh1)
SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p001': [Sybase]
[ODBC Driver][SQL Anywhere]Permission denied: you do not have permission
to execute a statement of this type [1-arbiter_demo]
After this fix is applied, the "Permission denied" message is prominently
displayed on the Monitor page so you know why Foxhound isn't responding.
Also, the Display Diagnostics button no longer shows a never-ending list
of "Permission denied" exceptions.
With or without this fix, however, you still have to use ping-only
sampling if only the utility database is available on the arbiter server:
Check Perform Ping-Only Sampling in section 16. Ping Settings of the
Monitor Options page.
(2) When ping-only sampling is used to monitor a target database,
and the target database is stopped and restarted, Foxhound
continues to (incorrectly) display "Database server not found"
or "Specified database not found" in the Monitor Status fields
on the Monitor Foxhound Menu pages.
This happens even though the Monitor page (correctly) shows
"Ping-only sampling -- Ping OK", as well as correctly showing
"All Clear - Alert #1" if the target database had been down long
enough for Alert #1 to appear.
This problem is very common with HA arbiter servers because ping-only
sampling is the only way Foxhound can connect to the utility database.
Without this fix, the workaround is to manually click Stop Sampling,
then Start Sampling to clear the bogus error message.
G.5
Prevent Foxhound from becoming unresponsive on startup (previously published as Patch 10)
Prevent Foxhound from becoming unresponsive
on startup, especially when the Foxhound database is very large.
This fix changes the database startup portion of Foxhound 4 to make it run
faster. This will prevent Foxhound from being completely unresponsive when
it is first started, especially when the Foxhound database is very large.
This fix adds the following table columns, as well as code to maintain
and use these columns to optimize queries:
ALTER TABLE rroad_alert
ADD alert_is_clear_or_cancelled VARCHAR ( 1 ) NOT NULL DEFAULT 'N';
ALTER TABLE rroad_sampling_options
ADD latest_sample_set_number UNSIGNED BIGINT NOT NULL DEFAULT 0;
G.6
The patch process now skips files that don't have the matching build number in the file name.
Previously the Foxhound patch process would attempt to process any file named patch*.* and
would fail if the patch was intended for a different build number.
Now the patch process only attempts to process files named patch*xxx*.* where xxx is
the matching build number, and ignores other files. This prevents older patch files
from preventing Foxhound from starting after a new build is installed.
G.7
The purge process has been changed to delete more data during each run.
Previously the Foxhound purge process may not have been able to control
database growth.
Now the purge process has been changed to delete more data during each run.
In particular, setting 10 of the Purge speed option has been changed to
delete ten times as many rows as previously.
Also, the "Purge uninteresting connection data" feature has been removed to
speed up the purge process by eliminating this complex task.
icons opens the Help frame, and
