the source of each performance statistic is documented; e.g., "The Cache and % of Max fields are displayed for target databases running on SQL Anywhere version 7 and later, and are based on the server-level CurrentCacheSize and MaxCacheSize properties."

2. It's Easy To Open And Close The Book.

In Foxhound 3 the Help was always on display.

The bad news was, the Help was always on display, always taking up room on the screen.

Sure, you could turn off the Help, but that was hard, and once you turned it off it stayed invisible until you turned it on again.

In Foxhound 4 it's easy to close the Help by clicking X, and easy to open again by clicking any of the context-sensitive ? icons... no more checking/unchecking Show Help over on the Foxhound Options page and then having to click refresh.

3. Banner Warnings About Unintended Consequences

Every target database has its own full set of Monitor Options in Foxhound, making "options management" the hardest part of using Foxhound.

Couple that with the unintended consequences of "getting it wrong" when you set up conflicts between different options, and "hardest" becomes "really hard". For example, there are no less than five different ways you can accidentally disable the Alert Email Schedule feature.

This Alert Email Schedule has no effect when the Enable Emails checkbox is unchecked (see 1. Global Overrides). This Alert Email Schedule has no effect when the Enable Schedules checkbox is unchecked (see 1. Global Overrides). This Alert Email Schedule has no effect when the Send Alert Emails checkbox is unchecked (see 7. Email Setup). This Alert Email Schedule has no effect when the Alert Email Address(es) field is empty (see 7. Email Setup). Only Alert #1 emails are sent when Ping-Only Sampling is performed (see 16. Ping Settings).

4. Drop-Down List Boxes Everywhere

It used to be a real chore to switch from one target database to another on the Monitor Options page. Foxhound 4 fixed that with drop-down list boxes spread all over the Monitor Options page that let you switch from target to target.

...but that's not the only place these drop-down list boxes appear.

They let you quickly change from one target to another on the Monitor page itself, and on the Sample History page:

That's what makes this a Cool New Feature: Those list boxes were originally designed just for the Monitor Options page, but it made perfect sense to add them to other pages as well.

5. Disable All Alerts

Sometimes it makes sense to enable only one single Alert when looking for a problem. The new Disable All Alerts buttons on the Monitor Option page make that easy: Turn off all 34 alerts, all at once, and then turn one back on.

Two other buttons were added as well: Enable All Alerts to turn them all back on, and Undo All Alerts to recover from an "Oops!"

6. Support Continues For Adhoc Queries

Unlike the builtin SQL Anywhere Monitor, Foxhound lets you run ISQL queries against all the historical data pertaining to your databases: "It's your data, you own it" is one of the Hallmarks of Foxhound.

7. Faster Patches

Patches are popular because (a) you can pick and choose which patch(es) to apply depending of your needs, and (b) patches are fast because you don't have to reinstall Foxhound and upgrade the database.

8. Disk-Versus-Cache Usage

Disk/Cache statistics are displayed for both table and index data, with anything over 1% for extended periods of time indicating a possible performance bottleneck.

These percentages are new to Foxhound 4, and are calculated from the DiskReadTable, CacheReadTable and four other SQL Anywhere statistics that are [cough] less-than-humanly readable.

9. Monitor The Ability To Accept New Connections

Sometimes a SQL Anywhere database loses the ability to accept new connections. Foxhound 3 didn't detect that problem because once it was connected to the target database, it stayed connected.

10. Monitor The Arbiter Server

Foxhound's ability to monitor the Arbiter server in a SQL Anywhere High Availability setup was an unexpected new feature of version 4. It was made possible by the new Ping-Only Sampling feature, and is documented in this blog post.

OK, so Foxhound can't just ping a server, it needs a database, so let's specify DBN=utility_db in the connection string.

Tip: You have to specify the -su option when starting the arbiter server if you want to connection via the utility database: e.g., dbsrv16 -su sql or dbsrv17 -su sqlsql. Why sqlsql? Because passwords have to be 6 characters long in SQL Anywhere 17.

But! ... it still doesn't work!

SERVER=ARBITER_PROD; DBN=utility_db; UID=DBA; PWD=sqlsql; HOST=localhost:55501; DRIVER=SQL Anywhere Native;
2:19:13 PM 1m .2s -- Permission denied: you do not have permission to execute a statement of this type --
2:19:09 PM Alert #1: Database unresponsive. Foxhound has been unable to gather samples for 1m or longer. Email not sent because Alert Emails were disabled.
2:18:13 PM 5.1s -- Permission denied: you do not have permission to execute a statement of this type at --

Monday, October 9, 2017

Answer: Foxhound uses SQL Anywhere for its database, and the only way to shrink the size of a SQL Anywhere database file is to unload, recreate and reload the file from scratch. The good news is, that whole process is automatic when you reinstall Foxhound on an existing installation:

First, Foxhound creates a new, empty copy of the database file.

Second, Foxhound finds the old database file,

then it asks you how much of the old data you want to copy to the new database: some, all, none, just the options.

Finally, Foxhound copies and loads the data, using just enough space to hold it all.

In this context, "shrink" means "don't copy the empty space, and don't allocate space for data that isn't copied", and "reorganize" means the copy process implicitly organizes the data as it is loaded.

Step 2: Decide How Much Data To Copy

Let's say you want to save half the data, thus shrinking the file by 50%.

Foxhound doesn't understand "half", but it does understand "only copy samples recorded after yyyymmdd".

To convert "half" into "yyyymmdd", start ISQL and run this query:

All Programs - Foxhound4 - Tools - Adhoc Query Foxhound Database via ISQL
SELECT CAST ( SYSTAB.count * 0.50 AS INTEGER ) AS half
FROM SYSTAB
WHERE SYSTAB.table_name = 'rroad_sample_set';
half
-----------
867871
-- The space used by the foxhound4.db file is more-or-less determined by the number
-- of rows in the rroad_sample_set table, which contains one row for each sample
-- sample recorded by the Foxhound Monitor.
-- In other words, the "half" calculated here is "half the samples", not "half the bytes".

Now use "half" as the START AT value in this query:

SELECT TOP 1 START AT 867871
DATEFORMAT ( sample_header.sample_finished_at, 'yyyymmdd' ) AS yyyymmdd
FROM sample_header
ORDER BY sample_header.sample_set_number;
yyyymmdd
--------
20170808
-- The sample_finished_at column is used to turn the row number 867871 into a yyyymmdd date.
-- The sample_header view is used because Foxhound only allows SELECT statements on the
-- adhoc query views (sample_header), not the underlying tables (rroad_sample_set).

Step 3: Run Foxhound's "Unsetup"

You don't have to stop Foxhound to reinstall the software, but you do have to run the special "unsetup" process to prepare for the reinstallation:

All Programs - Foxhound4 - Tools - Unsetup Foxhound
-- The "unsetup" process gets rid of the Windows shortcuts and other items,
-- but it leaves the Foxhound database alone... and even leaves it running.

If you are already using Foxhound 4, here's why you should consider upgrading to build 4740:

Build 4740 "rolls up" all five patches that were previously released for the original Foxhound 4 build 4729.

Several performance problems have been fixed, making it less likely that Foxhound will become unresponsive when the database grows very large.

If you want to use the "Reinstall Foxhound" method to reorganize and shrink the Foxhound database, the new build 4740 will let you do that... unlike the previous Patch 10 which made it impossible to preserve any data if you subsequently reinstalled the original build 4729.

Foxhound's own purge process has been improved, making it more likely the purge will keep database growth under control... and this fix was not included in the previous patches.

On the other hand...

If you don't need any of changes in build 4740, there's no need to upgrade.

If you only need one or two of the fixes, applying a patch to to build 4729 is a lot quicker than upgrading to build 4740.

Either way, here's a tip...

Tip: Set the Purge Speed to 10 Fastest on section 6. Purge of the Foxhound Options page.

Do this whether or not you upgrade to build 4740, if your Foxhound database is growing rapidly.