Help for Foxhound 3.0.4386a

Table of Contents     [RisingRoad]


4. The Sample History Page

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

The Sample History page looks just like the Monitor Database page, except...

4.1 The Sample History Menu

4.2 The Sample History Status Area

4.3 Top Sample

4.4 Peaks

4.5 Samples

4.6 Connections


4.1 The Sample History Menu    [Top]

 Back to Menu      DSN: ddd12      New Menu   Schema   Monitor   Foxhound Options   Monitor Options      About 

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

The DSN: / Connection String: title shows the name of the DSN or Connection String that you used to connect to the target database. This identifies the "current target database", a term used in this Help.

Multiple History pages that display the same DSN or Connection String show the same data; they do not represent separate sampling sessions.

If the SET OPTION PUBLIC.global_database_id statement has been used on a target database to set the GlobalDBId property to a non-default value, that value will be shown in (parentheses) after the DSN: or Connection String: title. This makes it easier to tell different remote databases apart in a replicating (SQL Remote) or synchronizing (MobiLink) environment.

The New Menu link opens the main Foxhound menu page in a new browser window or tab.

The Schema link opens the Display Schema page for this target database in a new browser window or tab.

The Monitor link opens the Monitor Database page for this sampling session in a new browser window or tab.

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

The Monitor Options link opens the Monitor Options page in a new browser window or tab.

The button is a context-sensitive link to this Help topic. This button appears in many locations, each of them a link to a different Help topic in this frame.

Tip: To hide the Help for every new page, see the Show Help section on the Foxhound Options page.

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

Scrolling...

 Newest    500    100    20    1 sample     Message
 Month    Week    Day    3 Hours    1 Hour 

Newest - Scroll to the most recent samples for this database

Tip: Click on Newest to scroll to the "new top". If sampling is running, new samples will continue to be gathered, and the History page won't automatically show them unless you scroll up.

500 - Scroll up by 500 to more recent samples for this database

100 - Scroll up by 100 to more recent samples for this database

20 - Scroll up by 20 to more recent samples for this database

1 sample - Scroll up by 1 to more recent samples for this database

Message - Scroll up to the next most recent sample that has an Alert, AutoDrop or other message

Month - Scroll up by one month to more recent samples for this database

Week - Scroll up by one week to more recent samples for this database

Day - Scroll up by one day to more recent samples for this database

3 Hours - Scroll up by three hours to more recent samples for this database

1 Hour - Scroll up by one hour to more recent samples for this database

Message     1 sample    20    100    500    Oldest      Freeze Frame Heights
        1 Hour    3 Hours    Day     Week     Month      Go to: 

Message - Scroll down to the next older sample that has an Alert, AutoDrop or other message

1 sample - Scroll down by 1 to older samples for this database

20 - Scroll down by 20 to older samples for this database

100 - Scroll down by 100 to older samples for this database

500 - Scroll down by 500 to older samples for this database

Oldest - Scroll down to the oldest sample for this database

Click on Freeze Frame Heights to stop Foxhound from resetting the frame heights each time to you scroll up or down or jump to a different sample.

1 Hour - Scroll down by one hour to older samples for this database

3 Hours - Scroll down by three hours to older samples for this database

Day - Scroll down by one day to older samples for this database

Week - Scroll down by one week to older samples for this database

Month - Scroll down by one month to older samples for this database

The Go to: field lets you scroll to a specific sample date/time or sample number. You don't have to be precise, the History page will scroll to the point nearest the value you typed.

The Go to: value is checked to see if it's a valid number first, then a date/time, so "Go to: 20120601" interpreted as a sample number rather than a sample date/time.

Goto: 1 works like Oldest.

Goto: 0 works like Oldest.

Goto: -1 is ignored, as are all values other than non-negative integers and valid date/times.

Go to: 1901-01-01 works like Oldest.

Go to: 9999999999999 works like Newest.

Go to: 2999-01-01 works like Newest.


4.2 The Sample History Status Area    [Top]

Sample Timestamp

The two-line date/time at the left displays the timestamp of the topmost successful sample currently displayed.

Machine, Server, Database

The Machine, Server, Database field shows the host or machine name and the SQL Anywhere server name and database name of the target database.

These fields are based on the MachineName, ServerName and Name properties.

Server, Database Versions

The Server version field tells you what version and build number of SQL Anywhere software was being used to run the target database. This is based on the ProductVersion property.

The Database version field shows the version and build number of the SQL Anywhere software that was used to initialize the target database file. For database files created with SQL Anywhere Version 9 and higher this field is based on the SYSHISTORY table. For database files created with earlier versions (5.5 through 8) Foxhound uses a proprietary process to determine the value to display. For early versions and for databases that have had the upgrade utility run on them this value may be somewhat imprecise.

Running Time, Started At

The Running Time field tells you how long the target server was running. This field is based on the StartTime property.

The Started At field tells you when the target server was started, relative to the computer on which Foxhound is running. This field is based on the StartTime property.

Cache, % of Max

The Cache, % of Max fields tells you how much RAM the server was using for the database cache, and how current cache size compares with the maximum.

These fields are based on the CurrentCacheSize and MaxCacheSize properties.

SPs

The SPs field displays YYY, NNN or some combination of Ys and Ns to indicate whether or not Foxhound had deployed, and was using, these three stored procedure on the target database: rroad_connection_properties, rroad_database_properties and rroad_engine_properties.

These stored procedures are used by Foxhound to improve the performance of the sampling process, but they are not absolutely required. If the user id that Foxhound uses to connect to the target database doesn't have the RESOURCE authority then Foxhound won't be able to CREATE these procedures on the target database.

If SPs isn't all Ys, try stopping and starting sampling to see if the Foxhound's inability to push these procedures to the target database was a transient condition. That won't affect the History display, just the gathering of future samples.

Favorable?

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

You can click on the Favorable? link to open the Change Target Settings popup to change these three settings:

  1. The RememberLastPlan server option controls whether or not anything is displayed in the connection-level "Last Plan Text" field on the Monitor and History pages.
  2. The RememberLastStatement server option controls whether or not anything is displayed in the connection-level "Blocked Statement" and "Last Statement" fields on the Monitor and History pages.
  3. The RequestTiming server option controls whether or not anything is displayed in the following connection-level fields on the Monitor and History pages:
    • Req
    • Busy
    • Waiting
    • Waiting Time

Free Disk Space Sys, Temp, Log, Other

The Free Disk Space Sys, Temp, Log, Other fields show how much free disk space existed on the disk drives holding these database files:

These numbers are based on the properties returned by the sa_disk_free_space procedure. The Log column will be empty for an in-memory database, and the Other column will be empty for any database that doesn't have any secondary database files.

HA:

The HA: field describes the status of the High Availability setup that includes this database.

This field is based on the ArbiterState, MirrorState, PartnerState, ReadOnly and ServerName properties.

Database File:

The Database File: field is the full file specification for the target database.

This field is based on the File property.


4.3 Top Sample    [Top]

The Top Sample section shows the topmost successful sample, if there is one.

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

The Top Sample section consists of 5 or 6 lines:

If the Age: interval grows very long without a new sample being displayed, and you are running an application that is sending a heavy workload to a target server on the network, and both Foxhound and the application are running on the same workstation, try running Foxhound and the test application on different workstations. In this scenario, Foxhound's connection to the target server may not be able to gather connection-level information on a timely basis. In some cases, it may not display the next sample until after the heavy workload is completely finished, and some of the rate calculations may be incorrect; e.g., commits per second, etc.

The color highlighting in this section is controlled by values in the Peaks section below it, as follows:

Dashes "-" are displayed for omitted column values instead of empty spaces. This makes it clear which values are not available or not applicable, and it also makes the resulting text somewhat easier to read when you use copy-and-paste. For example, the "CPU Time" percentage appears as a dash "-" for the first sample because it can only be calculated for second and later samples.

Note: This use of dashes "-" does not apply to columns which have been entirely omitted because the data is not available for the version of SQL Anywhere being used for the target database; e.g., the "CPU Time" column does not appear at all for version 5 and 6 target databases.

Top Sample

The Top Sample column shows the date/time that Foxhound recorded the topmost successful sample.

Interval

The Interval column shows the actual time interval between the previous sample and this one.

Foxhound tries to record a new sample every 10 seconds but the actual interval can vary. A very long interval like 1h 19.8m may indicate the computer was in sleep, standby or hibernate mode.

Latency... Heartbeat, Sample Time

Latency, also known as response time or access time, is a measure of how long it takes the the database to respond to a single request:

The Heartbeat column shows how long it took for Foxhound to execute the SELECT * FROM DUMMY heartbeat or "canarian" query. This number should be very small, and is often displayed as 0s or 0.1s.

The Sample Time column shows how long it took for Foxhound to gather all the performance data for this sample. The sample time should always be longer than the heartbeat time, and it will grow longer with an increasing number of connections because of the extra work involved to analyze each sample.

The Heartbeat and Sample Time numbers are calculated by Foxhound; they are not based on any builtin SQL Anywhere properties.

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.

A long Sample Time may indicate a performance problem with Foxhound rather than the target database; e.g.:

To see where the performance problem lies, repeatedly run the following ISQL script against your target database; if this script runs quickly but Foxhound reports a large Sample Time, the problem lies with Foxhound:
SELECT * INTO #temp1 FROM rroad_engine_properties();
SELECT * INTO #temp2 FROM rroad_database_properties(1);
SELECT * INTO #temp3 FROM rroad_connection_properties();
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;

Throughput... Req, Commits, Bytes

Throughput, also known as bandwidth, is a measure of how many requests the database can respond to per unit of time:

The Req column shows how many times the server has started processing a new request or resumed processing an existing request, during the preceding interval (top number +nnn) and since the target server was started (bottom number nnn).

A request is an atomic unit of work performed for a connection.

The value shown is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

The Commits column shows the approximate total number of COMMIT operations that have been executed by all connections, in the previous interval (top number +nn) and since the server started (bottom number nn).

This number is approximate because a connection may issue a commit and disconnect between two Foxhound samples, and that commit won't be included in this total.

The total since the server started is reset to zero and accumulation starts over again when sampling is stopped and restarted. "Commits" is roughly the same as "transaction count" unless client connections issue a lot of redundant commits.

The Commits column will be empty if connection details are not being recorded; see Foxhound Options - Connection Sampling Threshold.

The Bytes column (formerly displayed as Bytes In / Out) shows the total amount of data received by and sent by the server across client server connections, in the previous interval (top number +nnk) and since the server started (bottom number nnk).

The Bytes value shown is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

These numbers are based on the Req, Commit, BytesReceived and BytesSent properties.

Conns / Parent, Child Conns

The Conns column shows how many connections existed on a target database running on SQL Anywhere 11 or earlier, with Foxhound itself counting as 1 connection.
This number is based on the ConnCount property, and it may differ slightly from the number of connections shown in the connections detail section at the bottom of the page because the ConnCount property and connection details are recorded at slightly different points in time.

The Parent, Child Conns columns show how many primary (parent) and internal (child) connections existed on a target databases running on SQL Anywhere 12 or later, with Foxhound itself counting as 1 parent connection.

The parent connection count is based on the ConnCount property, and the child connection count is calculated by Foxhound as the number of connections with non-zero ParentConnection property values.

The sum of the Parent and Child Conns columns should match the total number of connections shown in the connections detail section at the bottom of the page, but it may differ because the ConnCount property and connection details are recorded at slightly different points in time.

Executing, Idle, Waiting Conns

The Executing, Idle, Waiting Conns columns show how many connections were executing, idle or waiting.

These numbers are based on the ReqStatus property, and they will be empty if connection details are not being recorded; see Foxhound Options - Connection sampling threshold.

Active Req, Max Req, Unsch Req

Active Req shows the number of requests that were being processed.

Max Req is the maximum number of requests that could be processed at one time (the -gn option).

Unsch Req (formerly called Waiting Req) shows the number of requests that were waiting to be processed.

A request is an atomic unit of work performed for a connection.

The values shown are for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

These numbers are based on the ActiveReq, MultiProgrammingLevel, Threads and UnschReq properties.

Locks Held, Conns Blocked, Waiting Time

Locks Held is the total number of locks held by all connections.

If Locks Held grows large during a period of high activity (high CPU, disk), and then *remains* high for a period of low activity, that may indicate an application flaw where a COMMIT is not issued as soon as it should be.

The Locks Held value does not include schema locks. For example, if a SELECT statement holds a schema lock that is blocking another connection from executing an ALTER TABLE statement, Locks Held may be empty even though Conns Blocked is 1.

Conns Blocked is the number of connections that were blocked.

The Conns Blocked column will be empty if connection details are not being recorded; see Foxhound Options - Connection sampling threshold.

Waiting Time is the total time all current connections were blocked or forced to wait during the previous interval.

These numbers are loosely related to one another. They are based on the following properties: LockCount, BlockedOn, ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled.

How To Enable Timing Information

Turn on the capturing of the timing information for each connection:

The RequestTiming setting controls the following SQL Anywhere statistical properties that Foxhound uses in calculations:

Changes to RequestTiming affect new connections, and they may or may not immediately affect existing connections as follows:

CPU Time

The CPU Time column shows four values:

The CPU Time values are for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

The percentages are adjusted for the number of CPUs being used by SQL Anywhere; i.e., if SQL Anywhere is using 25% of the processing resources of each and every one of four CPUs, the number will be shown as 25% rather than 100%. The time used is the total across all CPUs; it is not adjusted in the same manner.

These numbers are based on the following properties: NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU.

Temp Space, Rollback Log, Uncommitted

The Temp Space column shows the total amount of temporary space used by all the connections.

Temporary pages are used for many purposes, most often to hold intermediate results during query processing. Even if there is no temporary file, as with an in-memory no write database, temporary space is allocated and used by the engine.

Rollback Log shows how much space in the rollback log is currently used by all the connections.

Uncommitted shows how many operations have been performed by all the connections but not yet committed.

These numbers are based on the PageSize, TempFilePages, RollbackLogPages and UncommitOp properties.

Cache Panics, Low Memory, Satisfaction

Cache Panics is the number of times the target server failed to find a cache page to allocate, in the previous interval (top number +nn) and since the server started (nn).

The Cache Panics value is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

Low Memory is the number of times the target server had to change a query execution plan because cache memory ran low, in the previous interval (top number +nn) and since the server started (nn).

Cache Satisfaction is the percentage of times a database page lookup was satisfied by the cache, in the previous interval (top number nn%) and since the server started (nn% av).

These numbers are based on the CacheHits, CachePanics, CacheRead and QueryLowMemoryStrategy properties.

Checkpoints, Checkpoint Urgency, Recovery Urgency

Checkpoints is the number of CHECKPOINT operations that have been executed by the server in the previous interval.

Checkpoint Urgency is the percentage of the SET OPTION PUBLIC.checkpoint_time that has elapsed since the previous checkpoint.

Recovery Urgency is the estimated time required to recover the the database when restarting after an abnormal stoppage, expressed as a percentage of the SET OPTION PUBLIC.recovery_time.

Checkpoint Urgency and Recovery Urgency are used by the target server to help decide when to take a checkpoint. They both increase monotonically until a checkpoint is taken, then drop to zero.

The Recovery Urgency may exceed 100% because SQL Anywhere enforces a lower boundary on the interval between successive checkpoints. This lower bound is calculated using the checkpoint_time and recovery_time options, and the default is 2 minutes. If Recovery Urgency increases rapidly immediately after one checkpoint is taken, it may continue far beyond 100% before the next checkpoint is allowed.

These numbers are based on the Chkpt, CheckpointUrgency and RecoveryUrgency properties

Disk Reads, Disk Writes, Log Writes

Disk Reads is the number of pages that have been read from disk, in the previous interval (top number +nn) and since the server started (nn).

Disk Writes is the number of modified pages that have been written to disk, in the previous interval (top number +nn) and since the server started (nn).

Log Writes column shows the number of pages that have been written to the transaction log, in the previous interval (top number +nn) and since the server started (nn).

These numbers are based on the DiskRead, DiskWrite and LogWrite properties.

Index Adds, Lookups, Satisfaction

Index Adds is the number of times an entry has been added to an index, in the previous interval (top number +nn) and since the server started (nn),

Index Lookups is the number of times an entry has been looked up in an index, in the previous interval (top number +nn) and since the server started (nn), and

Index Satisfaction is the percentage of times that an index lookup was satisfied by the index without having to retrieve more information from the table data, in the previous interval (top number nn%) and since the server started (nn% av). This is also called "Index Selectivity".

These numbers are based on the IndAdd, IndLookup and FullCompare properties.

Full Index Comps

Full Index Comps shows how many times additional information had to be obtained from the table data in order to satisfy an index lookup, in the previous interval (top number +nn) and since the server started (nn).

This number is based on the FullCompare property.

DB File, Used, Fragments

DB File is the SYSTEM dbspace file size.

Used is the percentage of the SYSTEM dbspace file that is used to store data.

Fragments is the number of SYSTEM dbspace file fragments.

These numbers are based on the FileSize, FreePages and DBFileFragments properties.


4.4 Peaks    [Top]

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

The Peaks section on the History page looks the same as the Peaks section on the Monitor Database page. What you see in both places are the peak values as they were recorded when the page is displayed. For example, clicking on Reset Peaks on the Monitor Database page will affect what you see on the next History page display.

The Peaks section will disappear from the History page if you scroll back prior to the "Peaks since" timestamp. That's because the peaks don't have any meaning for samples recorded prior to the first peak being recorded.

Clicking on a link in the Peaks section will scroll the History page to that sample in the current browser window or tab, rather than opening a new window.

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

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

The reason for this is that the interval between samples can vary, and a true comparison of "larger versus smaller" should take that into account. For example, a server that processed Bytes of 5M in an 8 second interval was actually busier than one that processed 6M in a 12 second interval (as far as Bytes is concerned).

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

Peaks since

The Peaks since link identifies the earliest sample that was used to compute peak values.

This will be the first sample ever recorded for this database, or the first sample recorded after the most recent pressing of the button.

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

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

Latency... Heartbeat, Sample Times Peaks

The Heartbeat time peak shows the longest time it took for Foxhound to execute the SELECT * FROM DUMMY heartbeat or "canarian" query.

The Sample time peak shows the longest time it took for Foxhound to gather all the performance data for this sample.

The Heartbeat and Sample Time numbers are calculated by Foxhound; they are not based on any builtin SQL Anywhere properties.

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.

A long Sample Time may indicate a performance problem with Foxhound rather than the target database; e.g.:

To see where the performance problem lies, repeatedly run the following ISQL script against your target database; if this script runs quickly but Foxhound reports a large Sample Time, the problem lies with Foxhound:
SELECT * INTO #temp1 FROM rroad_engine_properties();
SELECT * INTO #temp2 FROM rroad_database_properties(1);
SELECT * INTO #temp3 FROM rroad_connection_properties();
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;

Throughput... Req, Commits, Bytes Peaks

The Req peak is highest rate at which the server started processing a new request or resumed processing an existing request.

A request is an atomic unit of work performed for a connection.

The Req value is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

The Commits peak is the highest rate at which COMMIT operations were executed.

The Bytes peak (formerly displayed as Bytes In / Out) is the highest rate at which data was received and sent by the server for client server connections.

The Bytes value is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

These numbers are based on the Req, Commit, BytesReceived and BytesSent properties.

Conns / Parent, Child Conns Peaks

The Conns peak shows the largest number of connections that existed on a target database running on SQL Anywhere 11 or earlier. This number is based on the ConnCount property.

The Parent, Child Conns peaks show the largest numbers of primary (parent) and internal (child) connections on a target databases running on SQL Anywhere 12 or later.

The parent connection count is based on the ConnCount property, and the child connection count is calculated by Foxhound as the number of connections with non-zero ParentConnection property values.

Executing, Idle, Waiting Conns Peaks

The Executing, Idle, Waiting Conns peaks show the largest numbers of connections that were executing, idle or waiting.

These numbers are based on the ReqStatus property, and they will be empty if connection details are not being recorded; see Foxhound Options - Connection sampling threshold.

Active Req, Max Req, Unsch Req Peaks

Active Req shows the largest number of requests that were being processed.

Max Req is the largest maximum number of requests that could be processed at one time (the -gn option).

Unsch Req (formerly called Waiting Req) shows the largest number of requests that were waiting to be processed.

A request is an atomic unit of work performed for a connection.

The values shown are for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

These numbers are based on the ActiveReq, MultiProgrammingLevel, Threads and UnschReq properties.

Locks Held, Conns Blocked, Waiting Time Peaks

Locks Held is the largest total number of locks held by all connections.
If Locks Held grows large during a period of high activity (high CPU, disk), and then *remains* high for a period of low activity, that may indicate an application flaw where a COMMIT is not issued as soon as it should be.

The Locks Held value does not include schema locks. For example, if a SELECT statement holds a schema lock that is blocking another connection from executing an ALTER TABLE statement, Locks Held may be empty even though Conns Blocked is 1.

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

Waiting Time is the largest value of the total time all current connections were blocked or forced to wait during the previous interval.

These numbers are based on the following properties: LockCount, BlockedOn, ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled.

How To Enable Timing Information

Turn on the capturing of the timing information for each connection:

The RequestTiming setting controls the following SQL Anywhere statistical properties that Foxhound uses in calculations:

Changes to RequestTiming affect new connections, and they may or may not immediately affect existing connections as follows:

CPU Time Peaks

The CPU Time peak section shows the largest percentage of CPU time used during a single interval (nn%) plus the largest number of processors used (of n).

The CPU Time peak is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database. Also, the percentage is adjusted for the number of CPUs being used by SQL Anywhere; i.e., if SQL Anywhere is using 25% of the processing resources of each and every one of four CPUs, the number will be shown as 25% rather than 100%.

These numbers are based on the following properties: NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU.

Temp Space, Rollback Log, Uncommitted Peaks

The Temp Space peak shows the largest amount of temporary space used by all the connections.

Temporary pages are used for many purposes, most often to hold intermediate results during query processing. Even if there is no temporary file, as with an in-memory no write database, temporary space is allocated and used by the engine.

Rollback Log peak shows largest amount of space in the rollback log used by all the connections.

Uncommitted peak shows the largest number operations performed by all the connections but not yet committed.

These numbers are based on the PageSize, TempFilePages, RollbackLogPages and UncommitOp properties.

Cache Panics, Low Memory, Satisfaction Peaks

Cache Panics is the highest rate at which the target server failed to find a cache page to allocate.

The Cache Panics value is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

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

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

These numbers are based on the CacheHits, CachePanics, CacheRead and QueryLowMemoryStrategy properties.

Checkpoints, Checkpoint Urgency, Recovery Urgency Peaks

Checkpoints is the highest number of CHECKPOINT operations that have been executed by the server in any sample.

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

Recovery Urgency is the highest estimated time required to recover the the database when restarting after an abnormal stoppage, expressed as a percentage of the SET OPTION PUBLIC.recovery_time, that was reached in any sample. These numbers are based on the Chkpt, CheckpointUrgency and RecoveryUrgency properties

Checkpoint Urgency and Recovery Urgency are used by the target server to help decide when to take a checkpoint. They both increase monotonically until a checkpoint is taken, then drop to zero.

The Recovery Urgency may exceed 100% because SQL Anywhere enforces a lower boundary on the interval between successive checkpoints. This lower bound is calculated using the checkpoint_time and recovery_time options, and the default is 2 minutes. If Recovery Urgency increases rapidly immediately after one checkpoint is taken, it may continue far beyond 100% before the next checkpoint is allowed.

Disk Reads, Disk Writes, Log Writes Peaks

Disk Reads is the highest rate at which pages were read from disk.

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

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

These numbers are based on the DiskRead, DiskWrite and LogWrite properties.

Index Adds, Lookups, Satisfaction Peaks

Index Adds is the highest rate at which entries were added to indexes.

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

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

These numbers are based on the IndAdd, IndLookup and FullCompare properties.

Full Index Comps Peak

Full Index Comps shows the highest rate at which additional information had to be obtained from the table data in order to satisfy an index lookup.

This number is based on the FullCompare property.

DB File, Used, Fragments Peaks

DB File is the largest SYSTEM dbspace file size.

Used is the largest percentage of the SYSTEM dbspace file that has been used to store data.

Fragments is the largest number of SYSTEM dbspace file fragments.

These numbers are based on the FileSize, FreePages and DBFileFragments properties.


4.5 Samples    [Top]

The Samples section on the History page looks the same as the Recent Samples section on the Monitor Database page, except that 100 samples are displayed instead of 10.

Alert and All Clear messages also appear in this section, as well as messages that tell you when sampling was not successful; e.g., "Database server not found" and "Foxhound stopped".

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

[9,9999]

The pair of numbers [in square brackets] above the "Samples" column title are the Foxhound database primary key values sampling_id and sample_set_number for the top sample shown on this page; e.g., [4,1796].

These key values are helpful when running adhoc queries on the Foxhound database.

Samples

The Samples column shows the date/time that Foxhound recorded each sample.

You can click on these links to scroll the associated sample to the top of the page.

Interval

The Interval column shows the actual time interval between the previous sample and this one.

Foxhound tries to record a new sample every 10 seconds but the actual interval can vary. A very long interval like 1h 19.8m may indicate the computer was in sleep, standby or hibernate mode.

Latency... Heartbeat, Sample Times

Latency, also known as response time or access time, is a measure of how long it takes the the database to respond to a single request:

The Heartbeat column shows how long it took for Foxhound to execute the SELECT * FROM DUMMY heartbeat or "canarian" query. This number should be very small, and is often displayed as 0s or 0.1s.

The Sample Time column shows how long it took for Foxhound to gather all the performance data for this sample. The sample time should always be longer than the heartbeat time, and it will grow longer with an increasing number of connections because of the extra work involved to analyze each sample.

The Heartbeat and Sample time numbers are calculated by Foxhound; they are not based on any builtin SQL Anywhere properties.

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.

A long Sample Time may indicate a performance problem with Foxhound rather than the target database; e.g.:

To see where the performance problem lies, repeatedly run the following ISQL script against your target database; if this script runs quickly but Foxhound reports a large Sample Time, the problem lies with Foxhound:
SELECT * INTO #temp1 FROM rroad_engine_properties();
SELECT * INTO #temp2 FROM rroad_database_properties(1);
SELECT * INTO #temp3 FROM rroad_connection_properties();
DROP TABLE #temp1;
DROP TABLE #temp2;
DROP TABLE #temp3;

Throughput... Req, Commits, Bytes

Throughput, also known as bandwidth, is a measure of how many requests the database can respond to per unit of time:

The Req column shows how many times the server has started processing a new request or resumed processing an existing request, during the preceding interval.

A request is an atomic unit of work performed for a connection.

The value shown is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

The Commits column shows the approximate total number of COMMIT operations that have been executed by all connections, in the previous interval.

This number is approximate because a connection may issue a commit and disconnect between two Foxhound samples, and that commit won't be included in this total.

The Commits column will be empty if connection details are not being recorded; see Foxhound Options - Connection Sampling Threshold.

The Bytes column (formerly displayed as Bytes In / Out) shows the total amount of data received by and sent by the server across client server connections, in the previous interval.

The Bytes value shown is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

These numbers are based on the Req, Commit, BytesReceived and BytesSent properties.

Conns / Parent, Child Conns

The Conns column shows how many connections existed on a target database running on SQL Anywhere 11 or earlier, with Foxhound itself counting as 1 connection.
This number is based on the ConnCount property, and it may differ slightly from the number of connections shown in the connections detail section at the bottom of the page because the ConnCount property and connection details are recorded at slightly different points in time.

The Parent, Child Conns columns show how many primary (parent) and internal (child) connections existed on a target databases running on SQL Anywhere 12 or later, with Foxhound itself counting as 1 parent connection.

The parent connection count is based on the ConnCount property, and the child connection count is calculated by Foxhound as the number of connections with non-zero ParentConnection property values.

The sum of the Parent and Child Conns columns should match the total number of connections shown in the connections detail section at the bottom of the page, but it may differ because the ConnCount property and connection details are recorded at slightly different points in time.

Executing, Idle, Waiting Conns

The Executing, Idle, Waiting Conns columns show how many connections were executing, idle or waiting.

These numbers are based on the ReqStatus property, and they will be empty if connection details are not being recorded; see Foxhound Options - Connection sampling threshold.

Active Req, Max Req, Unsch Req

Active Req shows the number of requests that were being processed.

Max Req is the maximum number of requests that could be processed at one time (the -gn option).

Unsch Req (formerly called Waiting Req) shows the number of requests that were waiting to be processed.

A request is an atomic unit of work performed for a connection.

The values shown are for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

These numbers are based on the ActiveReq, MultiProgrammingLevel, Threads and UnschReq properties.

Locks Held, Conns Blocked, Waiting Time

Locks Held is the total number of locks held by all connections.
If Locks Held grows large during a period of high activity (high CPU, disk), and then *remains* high for a period of low activity, that may indicate an application flaw where a COMMIT is not issued as soon as it should be.

The Locks Held value does not include schema locks. For example, if a SELECT statement holds a schema lock that is blocking another connection from executing an ALTER TABLE statement, Locks Held may be empty even though Conns Blocked is 1.

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

Waiting Time is the total time all current connections were blocked or forced to wait during the previous interval.

These numbers are loosely related to one another. They are based on the following properties: LockCount, BlockedOn, ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled.

How To Enable Timing Information

Turn on the capturing of the timing information for each connection:

The RequestTiming setting controls the following SQL Anywhere statistical properties that Foxhound uses in calculations:

Changes to RequestTiming affect new connections, and they may or may not immediately affect existing connections as follows:

CPU Time

The CPU Time column shows the percentage of CPU time used during the preceding interval (nn%) plus the number of processors used (of n).

The CPU Time is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database. Also, the percentage is adjusted for the number of CPUs being used by SQL Anywhere; i.e., if SQL Anywhere is using 25% of the processing resources of each and every one of four CPUs, the number will be shown as 25% rather than 100%.

These numbers are based on the following properties: NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU.

Temp Space, Rollback Log, Uncommitted

The Temp Space column shows the total amount of temporary space used by all the connections.

Temporary pages are used for many purposes, most often to hold intermediate results during query processing. Even if there is no temporary file, as with an in-memory no write database, temporary space is allocated and used by the engine.

Rollback Log shows how much space in the rollback log is currently used by all the connections.

Uncommitted shows how many operations have been performed by all the connections but not yet committed.

These numbers are based on the PageSize, TempFilePages, RollbackLogPages and UncommitOp properties.

Cache Panics, Low Memory, Satisfaction

Cache Panics is the rate at which the target server failed to find a cache page to allocate in the previous interval.

The Cache Panics value is for the target SQL Anywhere server as a whole, not an individual target database when the server is running more than one database.

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

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

These numbers are based on the CacheHits, CachePanics, CacheRead and QueryLowMemoryStrategy properties.

Checkpoints, Checkpoint Urgency, Recovery Urgency

Checkpoints is the number of CHECKPOINT operations that have been executed by the server in the previous interval.

Checkpoint Urgency is the percentage of the SET OPTION PUBLIC.checkpoint_time that has elapsed since the previous checkpoint.

Recovery Urgency is the estimated time required to recover the the database when restarting after an abnormal stoppage, expressed as a percentage of the SET OPTION PUBLIC.recovery_time.

These numbers are based on the Chkpt, CheckpointUrgency and RecoveryUrgency properties

Checkpoint Urgency and Recovery Urgency are used by the target server to help decide when to take a checkpoint. They both increase monotonically until a checkpoint is taken, then drop to zero.

The Recovery Urgency may exceed 100% because SQL Anywhere enforces a lower boundary on the interval between successive checkpoints. This lower bound is calculated using the checkpoint_time and recovery_time options, and the default is 2 minutes. If Recovery Urgency increases rapidly immediately after one checkpoint is taken, it may continue far beyond 100% before the next checkpoint is allowed.

Disk Reads, Disk Writes, Log Writes

Disk Reads is the rate at which pages were read from disk in the previous interval.

Disk Writes is the rate at which modified pages were written to disk in the previous interval.

Log Writes is the rate at which pages were written to the transaction log in the previous interval.

These numbers are based on the DiskRead, DiskWrite and LogWrite properties.

Index Adds, Lookups, Satisfaction

Index Adds is the rate at which entries were added to indexes in the previous interval.

Index Lookups is the rate at which entries were looked up in an indexes in the previous interval.

Index Satisfaction is the percentage of times that an index lookup was satisfied by the index without having to retrieve more information from the table data in the previous interval. This is also called "Index Selectivity".

These numbers are based on the IndAdd, IndLookup and FullCompare properties.

Full Index Comps

Full Index Comps shows the rate at which additional information had to be obtained from the table data in order to satisfy an index lookup, in the previous interval.

This number is based on the FullCompare property.

DB File, Used, Fragments

DB File is the SYSTEM dbspace file size.

Used is the percentage of the SYSTEM dbspace file that is used to store data.

Fragments is the number of SYSTEM dbspace file fragments.

These numbers are based on the FileSize, FreePages and DBFileFragments properties.


4.6 Connections    [Top]

The Connections section shows connections as they existed when the Top Sample was recorded. This section looks the same as the Connections section on the Monitor Database page, except

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

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

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

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

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

Click on a title below to sort it, or click here to sort on ...

Some of the connection-level data appears on separate lines (e.g., Last Statement:) so there are no column titles to click on when you want to sort on that data. In these cases you have two choices to sort the connection entries:
  1. Click on the title text on the line; e.g., click on Last Statement:

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

Hide Details / Show Details

The Hide Details / Show Details buttons alternate between hiding and displaying the following lines for each connection:

Conn #, User, OS User, IP, Name

The Conn #, User, OS User, IP, Name columns identify each connection. You can click on these links to open the Connection History page in a separate browser window or tab, scrolled to the associated sample.

Conn # is the connection number of the connection.

SQL Anywhere assigns a unique connection number to each connection started since the database started. Recent versions of SQL Anywhere assign small numbers (1, 2, 3, ...) to external client connections, and large numbers to events, web services and internal ("temporary") connections (1000000065, 1000000066, ...).

Two numbers are shown in the Conn # column for internal or "temporary" connections that have been started by some other "parent" connection. In this case, the first number will be the parent connection number and the second number will be the actual connection number for the internal connection; e.g., 1 1000000090

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

User is the SQL Anywhere database user id that was used to make the connection; e.g., DBA.

OS User is the operating system user id associated with the client process.

When the OS User is available, it may be easier to use for administrative purposes than the SQL Anywhere user id or the IP address; e.g., when identifying individual users and/or contacting them when connections are dropped.

IP is the network IP address of the client side of the connection; e.g., 192.168.1.104.

Name is the connection name of the connection.

You can use the CON= connection parameter to assign a name to a client connection.

Foxhound's own connections have names like Foxhound_p001, Foxhound_p002, etc.

If no connection name is explicitly specified for a client connection, SQL Anywhere may assign a name like SQL_DBC_c657ef0.

Event connections have the event name assigned as connection name and web services use the service name.

Older versions of SQL Anywhere do not assign connection names to internal or "temporary" connections, while newer versions assign names like "INT: Exchange"

These columns are based on the Number, Userid, OSUser, NodeAddress and Name properties.

Note: The columns displayed here are not perfectly unique when it comes to identifying a connection in the Foxhound database because a SQL Anywhere server will start using connection numbers 1, 2, 3 after it is stopped and restarted. To uniquely identify a connection when running adhoc queries, use a combination of sample_connection.sampling_id to identify the database, and connection_number and LoginTime to differentiate connections on different server instances. The sample_connection.connection_id_string can be used instead; all sample_connection rows with the same connection_id_string apply to the same single connection.

AutoDropped for this reason:

If this connection has been has been dropped by Foxhound's AutoDrop process, an AutoDropped for this reason: message will appear with an explanation; e.g.:
AutoDropped for this reason: #1: This connection has been blocking 1 or more (currently 1) 
   other connections for 5 or more (currently 5) samples

Note that a dropped connection may keep running for a while as SQL Anywhere rolls back its work even though the client application has been disconnected.

Time Connected

Time Connected is the elapsed time since this connection was established.

This number is based on the LoginTime property (shown in the tooltip for the displayed value).

Volume... Req, Commits, Bytes

Volume is a measure of how much work this connection has done.

The Req column shows how many times the server has started processing a new request or resumed processing an existing request for this connection since it started. A request is an atomic unit of work performed for a connection.

Commits is the total number of commit requests that have been handled by the server for this connection since it started.

The Bytes column (formerly displayed as Bytes In / Out) shows the total amount of data received by and sent by the server across client server connections, in the previous interval.

These numbers are based on the ReqCountActive, Commit, BytesReceived and BytesSent properties.

Locks Held, Conns Blocked, Transaction Time
Locks Held is the total number of locks held by this connection.
If Locks Held grows large during a period of high activity (high CPU, disk), and then *remains* high for a period of low activity, that may indicate an application flaw where a COMMIT is not issued as soon as it should be.

The Locks Held value does not include schema locks. For example, if a SELECT statement holds a schema lock that is blocking another connection from executing an ALTER TABLE statement, Locks Held may be empty even though Conns Blocked is 1.

Conns Blocked is the number of other connections that were blocked by this connection.

Transaction Running Time is the length of time a transaction has been running on this connection.

These columns are based on the LockCount, BlockedOn and TransactionStartTime properties.

Waiting Time, Busy, Wait, Idle

Waiting Time is the total amount of time this connection has been blocked or forced to wait.

The Busy, Wait, Idle columns are intended to give the user a rough idea of what's going on at the connection level. They are displayed as cumulative percentages since login:

These columns are based on the LoginTime, ReqTimeActive, ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties:

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

How To Enable Timing Information

Turn on the capturing of the timing information for each connection:

The RequestTiming setting controls the following SQL Anywhere statistical properties that Foxhound uses in calculations:

Changes to RequestTiming affect new connections, and they may or may not immediately affect existing connections as follows:

CPU %, Child Conns

The CPU % column shows how much of the overall CPU time available was used by this connection in the previous interval. The percentage is adjusted for the number of CPUs being used by the server.

The Child Conns column displays the number of internal child connections that have been started by primary parent connections.

When a connection makes use of the intra-query parallelism feature, it spawns a number of internal child connections which do most of the work; e.g., one INT: EXCHANGE child connection for each available processor.

SQL Anywhere tends to report the total CPU time used by all the child connections as the ApproximateCPUTime value for each child connection in use, and almost none for the parent connection. This inflates the amount of CPU time used by each child connection without reporting any CPU usage by the parent connection.

In an attempt to make sense of this behavior, Foxhound calculates the average non-zero ApproximateCPUTime for the child connections and reports it as as the CPU time for the parent connection. The inflated values reported by SQL Anywhere for each child connection are still shown by Foxhound; only the parent connection CPU time is adjusted.

One consequence of the Foxhound calculations is that the AutoDrop #5 CPU Usage process does apply to parent connections using intra-query parallelism. Note that the AutoDrop process is never performed on a child connection or any other internal connection that isn't directly associated with a client application.

These numbers are based on the following properties: NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax, ApproximateCPUTime and ParentConnection.

Temp Space, Rollback Log, Uncommitted

Temp Space shows how much temporary space is currently used by this connection.

Temporary pages are used for many purposes, most often to hold intermediate results during query processing. Even if there is no temporary file, as with an in-memory no write database, temporary space is allocated and used by the engine.

Rollback Log shows how much space in the rollback log is currently used by this connection.

Uncommitted shows how many operations have been performed by this connection but not yet committed.

These numbers are based on the PageSize, TempFilePages, RollbackLogPages and UncommitOp properties.

Low Memory, Cache Satisfaction

Low Memory is the number of times since this connection started that the target server had to change a query execution plan for this connection because cache memory ran low.

Cache Satisfaction is the percentage of times since this connection started that a database page lookup for this connection was satisfied by the cache.

These numbers are based on the QueryLowMemoryStrategy, CacheHits and CacheRead properties.

Time Since Last Request

Time Since Last Request is the elapsed time since the last time a request was started for this connection.

This number is based on the LastReqTime property.

Current Req Status

Current Req Status shows whether this connection was Idle, Waiting for thread, Waiting for I/O, Waiting for shared resource, Blocked by lock or Executing.

This column is based on the ReqStatus property, as follows:

   SQL Anywhere             Foxhound
    ReqStatus           Current Req Status                   SQL Anywhere Description
------------------  ---------------------------  -----------------------------------------------------
Idle                Idle                         The connection is not currently processing a request.
Unscheduled         Waiting for thread           The connection has work to do and is waiting for a 
                                                    worker thread.
BlockedIO           Waiting for I/O              The connection is blocked waiting for an I/O.
BlockedContention   Waiting for shared resource  The connection is blocked waiting for access to 
                                                    shared database server data structures.
BlockedLock         Blocked by lock              The connection is blocked waiting for a locked object.
Executing           Executing                    The connection is executing a request.

Disk Reads, Disk Writes, Log Writes

Disk Reads is the total number of pages that have been read from disk for this connection since it started.

Disk Writes is the total number of modified pages that have been written to disk for this connection since it started.

Log Writes is the total number of pages that have been written to the transaction log for this connection since it started.

These numbers are based on the DiskRead, DiskWrite and LogWrite properties.

Index Adds, Lookups, Satisfaction

Index Adds is the number of entries that were added to indexes for this connection since it started.

Index Lookups is the number of entries that were looked up in indexes for this connection since it started.

Index Satisfaction is the percentage of times that an index lookup was satisfied by the index without having to retrieve more information from the table data for this connection since it started. This is also called "Index Selectivity".

These numbers are based on the IndAdd, IndLookup and FullCompare properties.

Full Index Comps

Full Index Comps shows how many times additional information had to be obtained from the table data in order to satisfy an index lookup, for this connection since it started.

This number is based on the FullCompare property.

Isolation Level

Isolation Level shows the current isolation level and the updatable statement snapshot isolation level settings for this connection:
isolation level
0
1 No dirty reads
2 Repeatable reads
3 Serializable
Snapshot
Statement-snapshot
Readonly-statement-snapshot + n, where n is the updatable_statement_isolation value 0, 1, 2 or 3

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

This column is based on the Isolation_level and updatable_statement_isolation properties

Autodrop Result:

If Foxhound's AutoDrop process dropped or attempted to drop a connetion, the Autodrop Result: line will explain whether the attempt was successful or not; e.g.:
Autodrop Result: OK... issued at 2013-12-26 14:35:28.326, 
   processed at 2013-12-26 14:35:28.345

Blocked By:

The Blocked By: Conn #, User, OS User, IP, Name line identifies the connection that is blocking this one. You can click on this link to open the Connection History page in a separate browser window or tab, scrolled to the associated sample.

Conn # is the connection number of the connection.

SQL Anywhere assigns a unique connection number to each connection started since the database started. Recent versions of SQL Anywhere assign small numbers (1, 2, 3, ...) to external client connections, and large numbers to events, web services and internal ("temporary") connections (1000000065, 1000000066, ...).

User is the SQL Anywhere database user id that was used to make the connection; e.g., DBA.

OS User is the operating system user id associated with the client process.

When the OS User is available, it may be easier to use for administrative purposes than the SQL Anywhere user id or the IP address; e.g., when identifying individual users and/or contacting them when connections are dropped.

IP is the network IP address of the client side of the connection; e.g., 192.168.1.104.

Name is the connection name of the connection.

You can use the CON= connection parameter to assign a name to a client connection.

Foxhound's own connections have names like Foxhound_p001, Foxhound_p002, etc.

If no connection name is explicitly specified for a client connection, SQL Anywhere may assign a name like SQL_DBC_c657ef0.

Event connections have the event name assigned as connection name and web services use the service name.

Older versions of SQL Anywhere do not assign connection names to internal or "temporary" connections, while newer versions assign names like "INT: Exchange"

These columns are based on the BlockedOn, Userid, OSUser, NodeAddress and Name properties.

Block Reason:

The Block Reason: line describes what kinds of locks are causing the block; e.g.:

Block Reason: Row Transaction Write lock on DBA.t

This value is based on columns returned by sa_locks().

Locked Row Query:

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

This column is based on the LockName property and the sa_locks() row_identifier column.

Last Statement:

The Last Statement: line displays the last SQL statement received from the client application on this connection, as of the point this sample was recorded:
Last Statement: [Show More] select "COUNT_BIG"() -- 14 seconds from "SYSTAB" as "A" cross join "SYSTABCOL" as "B" c...

If the value is too long to show on one line, click on [Show More]:

Last Statement: [Show Less] 
                select "COUNT_BIG"() -- 14 seconds
                  from "SYSTAB" as "A"
                    cross join "SYSTABCOL" as "B"
                    cross join "SYSUSER" as "C"

How To Enable The Last Statement Line

Turn on the capturing of the most recently-prepared SQL statement for each connection: If that doesn't work, try turning off client statement caching on the target database:
SET TEMPORARY OPTION MAX_CLIENT_STATEMENTS_CACHED = '0'; 

- or -

SET OPTION PUBLIC.MAX_CLIENT_STATEMENTS_CACHED = '0';

The Last Statement line may not appear for a variety of reasons, and when it does appear it may not contain the statement that is currently executing.

Here's the full story:

The Last Statement line may or may not apply to the same query as the Last Plan Text line. The Last Statement line shows what came from the client application, whereas Last Plan Text applies to the last query run by the server whether it came from the client or came from within a stored procedure.

Last Plan Text:

The Last Plan Text: line displays the last query execution plan used by this connection, as of the point this sample was recorded:
Last Plan Text: [Show More] ( Plan ( SingleRowGroupBy ( Exchange [ 8 ] ( SingleRowGroupBy ( Nested...
If the value is too long to show on one line, click on [Show More]:
Last Plan Text: [Show Less] 
                ( Plan 
                ( SingleRowGroupBy 
                    ( Exchange [ 8 ]
                      ( SingleRowGroupBy 
                        ( NestedLoopsJoin
                          ( NestedLoopsJoin
                            ( ParallelTableScan ( ISYSUSER su ) )
                            ( TableScan ( ISYSTAB tab ) )
                          )
                          ( TableScan ( ISYSTABCOL col ) )
                        )
                      )
                    )
                  )
                )

How To Enable The Last Plan Line

Turn on the capturing of the long text plan for the last query executed on each connection:

The Last Plan Text line may or may not apply to the same query as the Last Statement line. The Last Plan Text line shows the last query run by the server whether it came from the client or came from within a stored procedure, whereas Last Statement shows what came from the client application.

[Top]



























































[Top]