Dealing With Out-Of-Date Peaks

The Foxhound "Peaks since" line lets you jump directly to the most extreme value of each performance statistic on the History page; e.g., 28 Unsch Req, 79 blocked connections, 100% CPU and so on:

Over time, however, the Peaks line can become out-of-date when the Foxhound purge deletes old samples.

For example, after the Purge deleted the sample with 28 Unsch Req, the Peaks line still showed that value but you could no longer click on it:

Foxhound 5 now lets you refresh the Peaks line by calling the new rroad_recalculate_peaks() stored procedure.

The rroad_recalculate_peaks() procedure updates all the out-of-date peaks with clickable values; e.g.,13 Unsch Req:

Tip: You must use the new shortcut Foxhound5 - 11 Admin Update via ISQL to call rroad_recalculate_peaks(). The default password is SQL, but you can change that; see this topic in the Help.

---

DIY CREATE VIEW Statements In Foxhound 5

Open access is a hallmark of Foxhound:

Foxhound provides read-only SQL access to all the historical data pertaining to your database: it's your data, you own it.

Foxhound Version 5 extends that to include CREATE VIEW and other DDL statements; here's what you can and cannot do:

no CREATE EVENT no CREATE EXISTING TABLE yes CREATE FUNCTION no CREATE FUNCTION [External call] yes CREATE INDEX no CREATE MATERIALIZED VIEW yes CREATE PROCEDURE no CREATE PROCEDURE [External call] no CREATE SEQUENCE no CREATE SERVICE yes CREATE TABLE yes CREATE TEMPORARY PROCEDURE no CREATE TRIGGER no CREATE USER yes CREATE VARIABLE [Connection-scope] no CREATE VARIABLE [Database-scope] yes CREATE VIEW yes DELETE yes INSERT yes SELECT yes TRUNCATE yes UNLOAD yes UPDATE Note that DELETE, INSERT, TRUNCATE and UPDATE statements are allowed, but they'll only work on tables you create, not the pre-existing Foxhound tables.

For Example: Active Alerts

There are two definitions for "Active Alert":

1. An Alert that is currently active, and
   

2. an Alert that was active at some earlier point in time.
   

Adhoc queries for the first kind are easy; there's a column for that:

SELECT * FROM alert WHERE alert_is_clear_or_cancelled = 'N' AND sampling_id = 3;

Foxhound 5 displays the second kind in the new scroll to Active Alert drop-down shown in this earlier blog post:

Here's prototype query for the same thing, using a proposed view named "active_alert":

SELECT alert_number, alert_title FROM active_alert WHERE sampling_id = 3 AND sample_set_number = 2726 ORDER BY alert_occurrence DESC; alert_number alert_title ------------ ------------------------- 1 Database unresponsive 16 I/O operations 13 File fragmentation

Do-It-Yourself: CREATE VIEW ADHOC.active_alert

With Foxhound 5 you can now CREATE your own views:

CREATE VIEW ADHOC.active_alert AS SELECT sample_header.sampling_id, sample_header.sample_set_number, alert.alert_occurrence, alert.alert_number, LEFT ( alert_title.alert_title, 25 ) AS alert_title, active_alert_range.clear_or_cancelled_locator_number FROM sample_header LEFT OUTER JOIN active_alert_range ON active_alert_range.sampling_id = sample_header.sampling_id AND active_alert_range.active_locator_number <= sample_header.sample_set_number AND active_alert_range.clear_or_cancelled_locator_number > sample_header.sample_set_number LEFT OUTER JOIN alert ON alert.alert_occurrence = active_alert_range.active_locator_number LEFT OUTER JOIN alert_title ON alert_title.alert_number = alert.alert_number;

The Foxhound drop-down list box uses a query like this to display first two columns, and it also uses the third column (alert_occurrence) to implement the "scroll to Active Alert" feature:

SELECT alert_number, alert_title, alert_occurrence FROM active_alert WHERE sampling_id = 3 AND sample_set_number = 2726 ORDER BY alert_occurrence DESC; alert_number alert_title alert_occurrence ------------ ------------------------- -------------------- 13 File fragmentation 2703 16 I/O operations 2441 13 File fragmentation 155

For example, when the user clicks on "#16 I/O operations" the History page is scrolled to the Alert #16 on line 2441:

When you use active_alert in your own adhoc queries, you have access to several columns including clear_or_cancelled_locator_number which tells you when (and if) each Alert became inactive.

Here's what the whole result set looks like:

SELECT * FROM active_alert WHERE sampling_id = 3 AND sample_set_number = 2726 ORDER BY alert_occurrence DESC; sampling_id sample_set_number alert_occurrence alert_number alert_title clear_or_cancelled_locator_number ----------- -------------------- -------------------- ------------ ------------------------- --------------------------------- 3 2726 2703 1 Database unresponsive 2745 3 2726 2441 16 I/O operations 2830 3 2726 155 13 File fragmentation 2829

The active_alert view is based on several views built-in to Foxhound:

active_alert_range One row per alert containing from and to locator numbers and timestamps for the range of lines in the History page for which this Alert was active. alert One row per alert. alert_cancelled One row per alert cancellation; indirectly referenced by active_alert_range. alert_title One row for the text title corresponding to each Alert number. all_clear One row per alert all-clear; indirectly referenced by active_alert_range. sample_header One row for each sample recorded by the Foxhound Database Monitor, holding various server and database-level properties and computed columns.

For more information about writing and running adhoc SQL in Foxhound 5, see this chapter in the Help.

---

Scrolling to Active Alerts in Foxhound 5

Alerts have always been a big deal in Foxhound... they're the main reason Foxhound sends emails, and they're the main reason people buy Foxhound.

Alerts draw your immediate attention, but they're also important after-the-fact: "When did it happen, and why?"

Prior to Foxhound Version 5 the only way to find Alerts was to click on the Message links on the History page.

Sadly, that means click click clicking through all the mismatches:

Foxhound 5 fixes that by keeping track of Active Alerts, the zero or more Alerts that were in effect (not yet cleared or cancelled) at any particular point in time.

For each row on the History page, the Active Alerts drop-down list box shows you what was in effect:

Now you can click on a link to go to "When did it happen?"

---

The Unexpected Benefits Of Conn Flags Count

Some Foxhound 5 connection flags are more important than others, like #9 - Long uncommitted:

Performance Tip: This Flag is useful if you want to be warned when a single connection is sucking all the air out of the room; i.e., when a long-running transaction has a performed a large amount of work that is not yet committed to the database.

Other connection flags might not be so important, especially if they appear for single connections. For example, Connection Flag #15 - Index add rate might just mean one connection is doing a bit more work than normal.

But what if many connections each have one flag set, or worse, what if many connections have many flags?

That's where Conn Flags Count comes in

At the database sample level, Conn Flags Count is the total number of connection flags set across all connections for one sample; large values will draw your attention to where there may be many bottlenecks:

At the connection level, Conn Flags Count is the number of connection flags set for one connection. You can sort the connections to bring the interesting connections to the top:

Conn Flags Count Was An Afterthought

When the Connection Flags feature was first added to Foxhound 5,

• the flags_set_count column wasn't displayed,
  

• then it was displayed in the Connections section because "if it's worth calculating, it's worth showing",
  

• then it was made sortable because "if it's worth showing, it's worth sorting",
  

• then the Samples & Messages section got its own Conn Flags Count,
  

• and the Peaks row got one too:
  

Now Conn Flags Count has become a valuable feature in its own right.

---

Mini-Backups in Foxhound 5

Quick! When was the last time you backed up your Foxhound database?

If you answered "never" that's OK, you shouldn't have to... a tool like Foxhound should look after its own backups.

And now Foxhound Version 5 does just that:

Every time you change any options and settings, Foxhound creates a "mini-backup" containing just those options and settings.

Mini-backup files don't take up much space because they don't contain any sample data:

To restore from a Mini-Backup file, click on the Foxhound Windows shortcut 9 Mini-Restore:

The Mini-Restore starts by opening the Activation page on an empty copy of the Foxhound database:

Use Option 2 to pick a mini-backup file and then click on Run Mini-Restore.

The next thing you'll see is sampling up and running again:

All the old monitor samples are gone, but all your intellectual property (the options and settings) has been restored.

For more information see Mini-Backup and Mini-Restore.

---

Read-Only Browser Mode In Foxhound 5

Until now, Foxhound has lacked any form of browser login or authentication...

Anyone who can open Foxhound in a browser can see (and do) everything offered by Foxhound.

The SQL Anywhere Monitor requires a user name and password to login but unless you work hard at changing passwords and creating users, it behaves much the same as Foxhound prior to Version 5...

Anyone can use admin / admin to see (and do) everything offered by the SQL Anywhere Monitor.

Foxhound Version 5 now offers a new and different form of browser login. It's called "Administrator Authentication" and it has these characteristics:

1. It's optional, you can turn it on and off, and the default is "no login required, for anything".
   

2. It implements two modes of operation...
   
   
   • Read-Only mode allows everyone to see everything but not change anything, and
     

   • Administrator mode requires a username and password to update Foxhound options and settings.
     

     
     

3. An unlimited number of browser sessions may use Read-only mode, with no user name or password required.
   

4. Only one browser session at a time can use Administrator mode.
   

5. A browser session will remain logged in to Administrator mode until
   
   
   • the session or cookie times out (default 12 hours),
     

   • the logged-in user clicks Logout, or
     

   • some other session logs in to Administrator mode, which silently logs out the session already logged in.
     

For more information see Administrator Authorization in Foxhound 5.

---

Server Messages: Another Release-Defining Feature in Foxhound Version 5

From time to time the SQL Anywhere server sends important error, warning and diagnostic messages to three separate destinations:

• the "server console" window. . . unless that window doesn't exist because the server was started as a Windows service,
  

• the text file named in the -o filename.txt option. . . unless that option was omitted from the server startup command line, and
  

• result sets returned by sa_server_messages(). . . if you call it.
  

The capture and display of sa_server_messages() data is one of the features that makes Foxhound 5 stand apart from previous versions.

Foxhound merges server messages and monitor samples in descending order by timestamp, making it easy to see "What else was going on?" at any particular point.

Here's what server messages look like for a secondary (mirror) server that became the primary after a  failover :

Your applications can write their own server messages, and they will appear in the Foxhound Monitor and History pages like this:

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

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

For more information see The Sample History Page - Server Messages.

---

Connection Flags: A Release-Defining Feature in Foxhound Version 5

Connection Flags are one of the features that make Foxhound 5 stand apart from previous versions. Connection Flags extend the usefulness of database-level Alerts and Peaks down into the nether regions of individual connections by letting you jump directly to connection samples that exhibit suspicious behavior.

Previously, you could sort connection samples in descending order by CPU time to bring high-CPU connections to the top of the list, and then sort on Transaction Time to see long-running connections at the top, but not both at the same time.

With Connection Flags you can sort on  Conn Flags Count  to see connections with Flag #5 - CPU usage, Flag #3 - Long transaction, and 28 other flags all sorted to the top if the list.

For more information about Connection Flags see section 17. Flag Settings in the Foxhound 5 docs.

---

SQL Anywhere Is NOT Dead

---

Is SQL Anywhere dead?
Not according to the SAP Store...

Is SQL Anywhere dead?
Not according to SAP support...

Is SQL Anywhere dead?
Not according Novalsys...

Is SQL Anywhere dead?
Not according to Foxhound...

---

---

Top Five Flaws In Foxhound

Do you see anything that looks familiar?

1. The transaction log is too big!

2. The database is too big!

3. The purge doesn't work very well!

4. There's no Undo for Reset Peaks!

5. Foxhound emails don't display properly!

---

1. The transaction log is too big!

"Too big" is too polite a term for a file that fills up the hard drive, and when you run the Purge it grows even bigger!

Foxhound Version 5 solves this problem by adding -m "Truncate Log On Checkpoint" to the dbsrv17 command that starts Foxhound.

Those two little characters "-m" have two huge consequences: First, the transaction log is wiped clean every single time Foxhound takes a checkpoint, and second, you can no longer count on the transaction log being any use at all in a Foxhound database recovery strategy.

If you care about a recovery strategy (and not everyone does) you're left with three choices:

1. Take the -m back out and go back to however you used to deal with giant Foxhound transaction logs,

2. rely on full backups and give up on restoring recent changes, or

3. use the new Mini-Backup and Mini-Restore features to recover Foxhound options and settings... which is (probably) all you need.

Enhancement: The size of the transaction log is automatically limited by the dbsrv17 -m truncate log on checkpoint.
Behavior Change: The transaction log is no longer available for restoring recent changes after restoring a backup.
New Feature: The Mini-Backup process automatically backs up recent changes to Foxhound options and settings.
New Feature: The Mini-Restore process restores your Foxhound options and settings into an empty foxhound5.db.

---

2. The database is too big!

The Foxhound database file grows at about 100K per monitored connection per hour, which means it gets too big too fast when your database has a lot of connections.

A lot of this space is used by only two columns: Last Statement and Last Plan Text.

Foxhound 5 uses a custom string de-duplication process on these columns to reduce the database size by a lot... sometimes half.

Enhancement: String de-duplication has reduced the size of the Foxhound database file.
Behavior Change: It may take a very long time to upgrade all the data in a very large Foxhound 3 or 4 database.
Behavior Change: The Foxhound database may contain a lot of free space after upgrading a very large database.

---

3. The purge doesn't work very well!

The original Foxhound Purge process was over-designed, which is a polite way to say it was badly designed.

The Purge process in Foxhound 5 has been completely redesigned and rewritten, with all the stupid useless fancy features removed, and a more aggressive approach taken to its primary purpose... which is to "Delete data!"

You can set up a separate 24x7 schedule for the Purge, and there are special "Hourly" and "Midnight" buttons to make administration easier.

New Feature: The Foxhound purge process is now fast enough to keep up with database growth.
Behavior Change: The "Purge uninteresting connection data" feature has been removed.
Behavior Change: The Purge process no longer forces extra checkpoints while it is running.
Behavior Change: By default the Purge process starts once an hour, on the hour.
Behavior Change: The purge process no longer puts "Older samples purged at" in the History page.
Behavior Change: The Purge process may now delete all samples from a stopped sampling session.
New Feature: The Mini-Backup process automatically backs up recent changes to Foxhound options and settings.
New Feature: The Mini-Restore process restores your Foxhound options and settings into an empty foxhound5.db.

---

4. There's no Undo for Reset Peaks!

The "Peaks since" line is one of the most useful features in Foxhound. It lets you jump directly to the most extreme value of each performance statistic on the History page; e.g., to the highest CPU usage, largest Unscheduled Requests, highest Disk Reads and so on.

When the Peaks line is no longer meaningful (say, after a server upgrade) the "Reset Peaks" button clears all the values and starts the peak calculation process over again.

But... if you have ever pressed Reset Peaks by accident you know what "regret" means.

What you want is an "Undo" button; what Foxhound 5 gives you is a "Recalculate Peaks" procedure.

Recalculate Peaks is actually better than Undo: If the old Peaks line contained some values that are now obsolete because the corresponding samples have been deleted, Recalculate Peaks refreshes those values by only looking at samples which still exist.

Enhancement: A procedure to recalculate the "Peaks since" row can be run via ISQL.

---

5. Foxhound emails don't display properly!

Over the years Foxhound has been plagued by random glitches in the way HTML emails are displayed in Google Mail and other browser-based email clients.

Fixing individual symptoms didn't help because new and different glitches kept cropping up.

To solve this problem once and for all, HTML emails in Foxhound 5 are vastly simplified and the details are delivered as attachments:

New Feature: The Attach files to emails: checkbox has been added to the Monitor Options page.
Enhancement: AutoDrop Notice emails have been enhanced to show connection-level sample data.
Usability Improvement: AutoDropped connection messages now contain links to Connection History pages.
Bug Fix: Alert and AutoDrop emails have been changed to display properly in HTML browsers.

---

For more information see Foxhound Version 5.

---