|
Help for Foxhound 4.0.4740a
Table of Contents [RisingRoad]
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...
- you can scroll through all the old samples,
you can see more data at one time,
you can adjust (and freeze) the frame heights, and
the display is not automatically refreshed.
4.1 The Sample History Menu 4.4 Peaks
« Back to Menu New Menu Schema Monitor Foxhound Options Monitor Options About - Mmm Dd yyyy... The « Back to Menu link displays the main Foxhound menu page in the current browser window or tab.
The drop-down lets you choose the DSN or Connection String name of the target database currently being displayed.
You can switch between different target databases in the same Monitor page,You can also open multiple Monitor pages to show the same or different target databases.
Note that when you open multiple Monitor pages for the same target database, they show the same data; they do not represent different sampling sessions.
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 Criteria page in a new browser window or tab. That page lets you enable and disable different alerts, change the conditions under which alerts are issued and manage multiple Monitor sessions using a tab-delimited file of connection strings.
The About link opens the About Foxhound page in a new browser window or tab.
The Mmm Dd yyyy... timestamp shows when this Sample History page was displayed.
Newest
Tip: Click on
1 sample
Message
1 sample
20
100
500
Oldest
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
3 Hours
Day
Week
Month
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.Go to: 1 works like Oldest.
Go to: 0 works like Oldest.
Go to: -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.
Sample History Status Area DB DBSpace - Size, Used, Frags, Avail, File
GlobalDBID:
The Machine: name is the computer or host name of the computer running SQL Anywhere.The Machine: name is displayed for target databases running on SQL Anywhere 8 and later and is based on the server-level MachineName property.
The Server: name is the name dynamically assigned at runtime by SQL Anywhere to the executable instance of SQL Anywhere running this target database.It defaults to be the same as the Database: name, or it can be explicitly set by the -n server name command line parameter:Command Line Server Name Database Name ----------------------------- ----------- ------------- dbsrv17 demo1.db demo1 demo1 dbsrv17 demo2.db -n ddd2 ddd2 ddd2 dbsrv17 -n sss3 demo3.db sss3 demo3 dbsrv17 -n sss4 demo4.db -n ddd4 sss4 ddd4See also Alert #11 ServerName change.
The Server: name is displayed for target databases running on SQL Anywhere 10 and later and is based on the server-level ServerName property.
The Database: name is the name dynamically assigned at runtime by SQL Anywhere to this target database.It defaults to be the same as the file name portion of the database file specification, or it can be set by the -n database name command line parameter for this target database:The Database: name is based on the database-level Name property.Command Line Server Name Database Name ----------------------------- ----------- ------------- dbsrv17 demo1.db demo1 demo1 dbsrv17 demo2.db -n ddd2 ddd2 ddd2 dbsrv17 -n sss3 demo3.db sss3 demo3 dbsrv17 -n sss4 demo4.db -n ddd4 sss4 ddd4
The Started At: field tells you when the target server was started, relative to the computer on which Foxhound is running.It is possible for individual databases to be started via the START DATABASE statement long after the server was started (and stopped and re-started via STOP and START DATABASE statements). Generally, however, databases are started when the server starts, and the Started At field applies to the database as well as the server.Started At is displayed for target databases running on SQL Anywhere 8 and later, and is based on the server-level StartTime property.
The Running Time: field tells you how long the target server has been running.It is possible for individual databases to be started via the START DATABASE statement long after the server was started (and stopped and re-started via STOP and START DATABASE statements). Generally, however, databases are started when the server starts, and the Running Time field applies to the database as well as the server.Running Time is displayed for target databases running on SQL Anywhere 8 and later, and is based on the server-level StartTime property.
The Cache: and % of Max fields tells you how much RAM the server is using for the database cache, and how current cache size compares with the maximum.Performance Tip: A small Cache value together with 100% of Max may indicate that insufficient memory in the database cache is a performance bottleneck. Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.See also Alert #19 Cache size.Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.
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.
- DB Size is the size of the main (SYSTEM dbspace) file.
Performance Tip: A rapidly growing database file may indicate that a high rate of inserts is a performance bottleneck.DB Size is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the database-level PageSize and FileSize properties.
See also Alert #5 Database disk space.
- DB Used is the percentage of the main (SYSTEM dbspace) file space that is actually used for storing database objects.
Performance Tip: The only way to "reclaim" unused space by shrinking the database file is to delete and recreate the entire physical file; e.g., via unloading and reloading the database.However, when new data is to be inserted it is more efficient when free space is available for that purpose than when SQL Anywhere is forced to dynamically grow the physical file. Also, once a large file with lots of free space has been defragmented it will remain that way, whereas a database file growing in size will most likely grow into fragmented space.Even when a database is rebuilt via unload and reload it may be advantageous to run an ALTER DBSPACE statement to increase the amount of free space.
DB Used is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the database-level FileSize and FreePages properties.
- DB Frags is the number of physical file fragments in the main (SYSTEM dbspace) file.
Performance Tip: Physical file fragmentation may adversely affect disk I/O... but maybe not, in the case of RAID and SSD drives; internal table and index fragmentation may be more important.A defrag utility may be used to reduce file fragmentation but it won't do much for internal fragmentation. The best solution for internal table and index fragmentation is a full unload and reload of the database; the second best approach is to run REORGANIZE statements.DB Frags is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the database-level DBFileFragments property.
See also Alert #13 File fragmentation.
- DB Avail is the amount of free disk space on the drive holding the main (SYSTEM dbspace) database file.
Performance Tip: For heavily-used databases running on traditional disk drives, performance may be improved by using different disk drives for the DB, log and temp files.DB Avail is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the properties returned by the sa_disk_free_space() procedure.
For target databases running on SQL Anywhere version 12 and earlier, a user id with DBA authority is necessary for Foxhound to call the sa_disk_free_space() procedure.For target databases running on SQL Anywhere version 16 and later, the MANAGE ANY DBSPACE privilege is required.
- DB File is the full file specification for the main (SYSTEM dbspace) file.
DB File is based on the database-level File property.
- Log Size is the size of the transaction log file.
Performance Tip: A rapidly growing transaction log may indicate that a high rate of inserts, updates and deletes is a performance bottleneck.Performance Tip: The size of the transaction log file does not adversely affect ordinary database operations.
However, the transaction log file grows sequentially without limit; data is never updated, deleted, reclaimed, reused or retrieved, and at some point disk space may be exhausted. Also, recovery and replication processes may be adversely affected by an immense transaction log file.SQL Anywhere offers a number of techniques to control transaction log file size by truncating (deleting) and restarting the log file.
Performance Tip: Do not even think of running a SQL Anywhere database without a transaction log.
That will force SQL Anywhere to perform a physical database file CHECKPOINT with every COMMIT... for a busy database the performance penalty will be immense, not to mention the risk of losing everything in the event of a crash.See also Alert #7 Log disk space.
Log Size is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the database-level PageSize property and the extended database-level translog FileSize property.
- Log Used is the percentage of the transaction log file space that is actually used for storing log data.
Performance Tip: Free space is not an important consideration for the transaction log... don't worry about it.Log Used is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the extended database-level translog FileSize and FreePages properties.
- Log Frags is the number of physical file fragments in the transaction log file.
Performance Tip: Transaction log fragmentation does not adversely affect performance.Log Frags is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the database-level LogFileFragments property.
- Log Avail is the amount of free disk space on the drive holding the transaction log file.
Performance Tip: For heavily-used databases running on traditional disk drives, performance may be improved by using different disk drives for the DB, log and temp files.Log Avail is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the properties returned by the sa_disk_free_space() procedure.
For target databases running on SQL Anywhere version 12 and earlier, a user id with DBA authority is necessary for Foxhound to call the sa_disk_free_space() procedure.For target databases running on SQL Anywhere version 16 and later, the MANAGE ANY DBSPACE privilege is required.
- Log File is the full file specification for the transaction log file.
Log File is based on the database-level LogName property.
- Temp Size is the size of the temporary file.
Performance Tip: A large temporary file may indicate that one or more runaway queries are a performance bottleneck.
Temp Size is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the database-level PageSize property and the extended database-level temp FileSize property.
See also Alert #6 Temp disk space.
- Temp Used is the percentage of the temporary file space that is actually used for storing data.
Performance Tip: A large amount of free space indicates that one or more runaway queries may have used up a lot of temporary file space in the past, causing the file to grow in size, but those queries are no long running.Temp Used is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the extended database-level temp FileSize and FreePages properties.
- Temp Frags is the number of physical file fragments in the temporary file.
Performance Tip: If the temporary file is heavily used, fragmentation may adversely affect performance. It is not possible to pre-allocate the temporary file but it is possible to defragment it while the database is running, after the file has grown in size.Temp Frags is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the extended database-level temp DBFileFragments property.
- Temp Avail is the amount of free disk space on the drive holding the temporary file.
Performance Tip: For heavily-used databases running on traditional disk drives, performance may be improved by using different disk drives for the DB, log and temp files.Temp Avail is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the properties returned by the sa_disk_free_space() procedure.
For target databases running on SQL Anywhere version 12 and earlier, a user id with DBA authority is necessary for Foxhound to call the sa_disk_free_space() procedure.For target databases running on SQL Anywhere version 16 and later, the MANAGE ANY DBSPACE privilege is required.
- Temp File is the full file specification for the temporary file.
Performance Tip: The following table shows how SQL Anywhere chooses where to put the temporary file:SQL Anywhere Version From first to last... -------------------- ----------------------------------------------- 5.5 TMP, TMPDIR, TEMP environment variables, current directory 6, 7, 8, 9 ASTMP, TMP, TMPDIR, TEMP environment variables, current directory 10, 11, 12, 16, 17 -dt server option, SATMP, TMP, TMPDIR, TEMP environment variables, current directoryTemp File is based on the database-level TempFileName property.
The GlobalDBID field is displayed if the SET OPTION PUBLIC.global_database_id statement has been used set the GlobalDBId database property to a non-default value.The GlobalDBId value is used as the partition number for columns using the DEFAULT GLOBAL AUTOINCREMENT attribute.It also serves to uniquely identify the database in MobiLink synchronization and SQL Remote replication setups.
The value 0 is often used for the consolidated database, and the values 1, 2, 3... are used for remote databases.
The GlobalDBID is available for adhoc queries; e.g.:
SELECT TOP 1 GlobalDBID FROM sample_detail WHERE sampling_id = 23 ORDER BY sample_set_number DESC; GlobalDBID -------------------- 123GlobalDBID is displayed for target databases running on SQL Anywhere 7 and later, and is based on the database-level GlobalDBID property.
The SPs field displays YYY, NNN or some combination of Ys and Ns to indicate whether or not Foxhound has deployed, and is using, these three stored procedure on the target database: rroad_connection_properties, rroad_database_properties and rroad_engine_properties.Those three procedures optimize the Foxhound sampling process. Without them, Foxhound can still gather samples but it has to work harder to do it.Performance Tip: Foxhound's own performance can be greatly reduced if SPs isn't all Ys, or at least YNN.
How To Install The Foxhound SPs Three stored procedures are delivered with Foxhound for installation on your target databases.
These procedures are optional but highly recommended:
- rroad_connection_properties greatly improves Foxhound performance when the target database has a large number of client connections.
- rroad_database_properties improves Foxhound performance when gathering database-level properties, as well as enabling these Foxhound display fields:
- Log Size - based on DB_EXTENDED_PROPERTY ( 'FileSize', 'translog' )
- Log Used - based on DB_EXTENDED_PROPERTY ( 'FreePages', 'translog' )
- Temp Size - based on DB_EXTENDED_PROPERTY ( 'FileSize', 'temp' )
- Temp Used - based on DB_EXTENDED_PROPERTY ( 'FreePages', 'temp' )
- Temp Frags - based on DB_EXTENDED_PROPERTY ( 'DBFileFragments', 'temp' )
- rroad_engine_properties improves Foxhound performance when gathering server-level properties.
Foxhound will automatically install and call these stored procedures if it can.
The phrase "if it can" means "the user id with which Foxhound connects to the target database has the RESOURCE authority" as in:
Foxhound will keep trying to install those procedures each time it connects to the target database; e.g., each time sampling is stopped and started, or each time Foxhound is started.GRANT RESOURCE TO FOXHOUND;As mentioned above, Monitor performance will suffer without those procedures, especially if the target database has a lot of connections. The Foxhound Monitor page displays SPs YYY if finds the three procedures, and SPs NNN if it doesn't.
. . . but Wait, There Is Another Way!
If you don't want to let the Foxhound user id to have either RESOURCE or DBA privileges on the target database, but you are willing to install those three procedures on the target database yourself, here's how:
(Note: If you choose to use this method, you will have to repeat these steps for each target database you wish to monitor, and again each time you upgrade to a new version or build of Foxhound.)
- Create a user id to be used by Foxhound on the target database; e.g.:
GRANT CONNECT TO FOXHOUND IDENTIFIED BY 'ZAdt5Yq8';
- Find the following three text files in the Foxhound folder (which defaults to C:\ProgramData\RisingRoad\Foxhound4 on Windows 7 and C:\Documents and Settings\All Users\Application Data\RisingRoad\Foxhound4 on Windows XP):
rroad_connection_properties.sql rroad_database_properties.sql rroad_engine_properties.sql
- Edit each file to specify the user id you chose in step 1; e.g., change {OWNER} to FOXHOUND:
CREATE PROCEDURE FOXHOUND.rroad_connection_properties() CREATE PROCEDURE FOXHOUND.rroad_engine_properties() CREATE PROCEDURE FOXHOUND.rroad_database_properties(d INTEGER)
- Use dbisql to run those files against the target database.
- If the Foxhound Monitor is already connected to the target database, click on Stop Sampling, then Start Sampling.
- Check to make sure "SPs YYY" appears on the Foxhound Monitor page.
The Purge field summarizes what the Foxhound purge process is set up to do.It displays "Off" if no purging is enabled, and "After xx day(s)" if one or the other or these options are enabled:Purge all sample data enabled: [x] After [xx] day(s). Purge uninteresting connection data enabled: [x] After [xx] day(s).You can click on the Purge link to open Monitor Options - 6.6 Purge.
The Favorable? field displays a combination of three Y, N and - characters to indicate whether or not three settings on the target database are (or were) favorable to the recording of data for Foxhound to display on the Monitor, History and Connection History pages:
- Y means yes, the setting is (or was) favorable to Foxhound,
- N means no, and
- - means the setting is not supported by the SQL Anywhere target server.
You can click on the Favorable? link to open Monitor Options - 14. Change Target Settings to change these three settings:
- The RememberLastPlan server option controls whether or not anything is displayed in this connection-level field:
- Last Plan Text
- The RememberLastStatement server option controls whether or not anything is displayed in these connection-level fields:
- Blocked Statement
- Last Statement
- The RequestTiming server option controls whether or not anything is displayed in these connection-level columns:
- Throughput... Req
- Waiting Time
- Busy, Wait, Idle
Performance Tip: Number 2 (RememberLastStatement) is by far the most useful of the three.
Performance Tip: Changes to these server options only affect future samples gathered by Foxhound; there is no way to go back and "fix" historical data.
Performance Tip: Changes to these server options on a High Availability primary database or a Read-Only Scale-Out root database will not be automatically transmitted to a secondary or copy database.
However, since these are server options rather than database options, you can use the Favorable? field on the secondary or copy database to make the same changes even though the Foxhound connections to those databases are read-only.Performance Tip: After a High Availability failover, it is possible for the Favorable? field on the Monitor page for the secondary database to show different values from the Change Target Settings section on the Monitor Options page for the "same" database (which isn't actually the same any more).
If the target database is a High Availability secondary database and a failover occurs (primary fails, secondary takes over), the Favorable Current Settings? column may show [not available].The reason for this is that the Monitor Options - Change Target Settings section attempts to open a new connection to the secondary database, and the database that used to be the secondary is now the primary so there's no secondary database available. The Foxhound sampling session, however, remains connected to the original database (once the secondary, now the primary) so there is an apparent inconsistency between Favorable Current Settings? showing [not available], and the Foxhound Monitor page showing that everything is OK.
But wait, there's more! If the failed database that was the original primary database is restarted, it will become the new secondary database, and the Favorable Current Settings? column will show actual values rather than [not available]... but those values will be coming from a different database than the Foxhound sampling session is showing. That's because the Monitor Options - Change Target Settings section opens a new connection to the secondary database, and the Foxhound Monitor session remains connected to the original database (once the secondary, now the primary).
The RememberLastStatement server option is supported by target databases running on SQL Anywhere version 8 and later, while the RememberLastPlan and RequestTiming server options are supported by target databases running on SQL Anywhere version 9 and later.
The Software: Version field tells you what version and build number of SQL Anywhere software is being used to run the target database.Server version is based on the server-level ProductVersion property.
The DB File: Version field shows the version and build number of the SQL Anywhere software that was used to initialize the target database file.For databases created with SQL Anywhere 5.5 through 8 the DB File: Version is determined by a variety of methods. In some cases the result may be imprecise, especially for databases that have been processed by the dbupgrade utility.For database files created with SQL Anywhere Version 9 and later the DB File: Version is based on the SYSHISTORY table.
The untitled "database disposition" field describes how this target database appears to client connections and its relationship to other databases in a High Availability or Read-Only Scale-Out setup.Updatable primary database. Arbiter is connected. Partner is connected, synchronized. Read-only secondary (mirror) database. Arbiter is connected. Partner is connected, synchronized. Read-only copy database. Read-only database. Updatable database. [default, not displayed]See also...
Alert #9 Arbiter unreachableAlert #35 Separate ping failed
The database disposition field is displayed for target databases running on SQL Anywhere 8 and later.
For target databases running on SQL Anywhere 8 through 10 it is based on the database-level ReadOnly property, and for SQL Anywhere 11 and later it is based on the database-level properties ArbiterState, MirrorState, PartnerState and ReadOnly.
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".Note: The sample shown in the Top Sample section might not even be visible in the later Samples section.
That can happen when the successful sample shown in the Top Sample section is followed chronologically by so many unsuccessful samples (e.g., Database not found) that it doesn't appear on-screen in the later Samples section.It often happens when you press the Newest scroll button on a target database that has been shut down (or sampling has been turned off) for a long time. The Top Sample will show a successful sample that was recorded long ago, whereas the Samples section will be filled with unsuccessful samples (Foxhound stopped, Sampling stopped, etc).
The Top Sample section consists of 4 or 5 lines:
Performance Tip:
The other columns are highlighted according to the values in the Peaks section, 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" 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" column does not appear at all for version 5 and 6 target databases.
The Top Sample column shows the date/time that Foxhound recorded the topmost successful sample.You can click on the link to scroll the associated sample to the top of the Samples section on this page... this only makes a difference if the Samples section is full of messages recorded after (above) the topmost successful sample.
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.Foxhound uses the interval time to convert performance statistics from cumulative counts to rates; e.g., to convert the total number of disk writes into the number of disk writes per second.
A very long interval like 1h 19.8m may indicate the computer was in sleep, standby or hibernate mode.
See also Alert #1 Database unresponsive.
Response time, also known as latency or access time, is a measure of how long it takes the database to respond to a single request:The Response... Heartbeat time column shows how long it took for Foxhound to execute the SELECT * FROM DUMMY heartbeat or "canarian" query.
Performance Tip: Heartbeat time should be very small. Repeated values larger than 1s may indicate a performance bottleneck, and sudden increases in value may indicate a period of non-responsiveness.Note that there's a difference between precision and accuracy; for example, two successive Heartbeat measurements like 25ms and 50ms may be very precise but they may not accurately represent an immediate 100% slowdown in performance.On the other hand, a long period of Heartbeat values averaging 25ms, followed by a long period of 50ms numbers, may indeed accurately reflect a significant change in performance.
See also Alert #2 Long heartbeat.
Heartbeat time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
The Response... Sample time column shows how long it took for Foxhound to gather all the performance data for this sample.
Sample time should always be longer than Heartbeat time, and it will grow longer with an increasing number of connections because of the extra work involved to analyze each sample.The Sample time is calculated after Heartbeat time and the two values do not overlap. It is possible for the Heartbeat time to be much longer than the Sample Time (e.g., 10m versus 100ms) when the target database becomes unresponsive but eventually responds; the long Heartbeat time reflects the unresponsiveness whereas the subsequent short Sample Time measures the return to normal.
Performance Tip: A long Sample Time may indicate a performance problem with Foxhound rather than the target database; e.g.:
- Sample Time is over 1 second but everything else is running OK on the target database.
Sample Time is large but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time increases rapidly with the number of connections to the target database.
Sample Time increases with the number of connections but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time is over 10 seconds causing the Interval time to also exceed 10 seconds.
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;See also Alert #3 Long sample time.
Sample time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
The Response... Ping time column shows how long it took for Foxhound to connect and disconnect from the target database.
Performance Tip: Long Ping times may indicate that a performance bottleneck is preventing the target database from accepting new connections on a timely basis.Ping times for local "shared memory" connections may be similar to or even smaller than Sample times for the same database, but they will usually be larger than Heartbeat times.Ping times for network connections may be much longer than Ping times for local connections.
Foxhound uses a custom "ping" process to test separate connections to the target database.
Each time the ping process runs it opens a new connection to the target database via the embedded SQL interface, issues a SELECT @@SPID command and then immediately disconnects.
This is different from the Foxhound Monitor process which connects to the target database via ODBC and keeps that connection open while it collects multiple samples.Note that the Foxhound ping process does not use the dbping.exe utility that ships with SQL Anywhere, nor does it use the ODBC interface that is used to gather sample data.
Also note that the separate ping process "uses up" another SQL Anywhere connection number each time it runs, which is once approximately every 10 seconds for each target database.
The new ping process can be used in three ways:
- As an addition to the Foxhound Monitor sampling process, the ping process tests the target database's ability to accept new connections as well as providing data for a third measure of response time: ping response time.
See Monitor Options - Include Ping.
See also Alert #35 Separate ping failed.
- As an alternative to Foxhound's sampling process, ping-only sampling may be used to check for Alert #1 Database unavailable without storing a lot of data in the Foxhound database.
See Monitor Options - Ping-Only Sampling.
- As an alternative to on/off Sample Schedule settings, ping-only sampling may be specified at various times of the day.
For example, ping-only sampling might be scheduled during the overnight hours
- when a large connection pool is mostly idle, or
- when a heavy load is expected and nobody much cares about performance.
Ping time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
Throughput, also known as bandwidth, is a measure of how much work the database has performed:Throughput... Req is the rate at which the server has started processing a new request or resumed processing an existing request during the preceding interval.
Performance Tip: Large Throughput... Req values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.Note that "request" is an atomic internal unit of work processed by the server for a connection, not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived column for adhoc queries of connection-level data.Throughput... Req is based on the server-level Req property
(note that this is different from the connection-level Throughput... Req column which is based on the connection-level ReqCountActive property)Throughput... Commits is the approximate rate at which commit requests have been executed by all connections in the previous interval.
Performance Tip: Large Throughput... Commits values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.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.
Throughput... Commits is based on the sum of the connection-level Commit property.
Foxhound does gather the database-level Commit property for target databases running on SQL Anywhere 12 and later, and the value is stored in the sample_detail."Commit" column for adhoc queries, but it is not the value displayed by Foxhound on the Monitor and other pages.Historically speaking, the value that Foxhound does display (the sum of the connection-level Commit property) is known to be more reliable than the database-level Commit property. However, it is possible that Foxhound may not count commits performed by short-lived connections that aren't captured at all.
This and other values displayed by Foxhound are intended for performance analysis purposes, not accounting or other purposes that require absolute accuracy.
Throughput... Bytes is the rate at which data has been received by and sent by the server across the client server interface in the previous interval.
Performance Tip: Large Throughput... Bytes values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.Throughput... Bytes is displayed for target databases running on SQL Anywhere versions 7 and later. It is based on the server-level BytesReceived and BytesSent properties for target databases running on SQL Anywhere versions 7 through 12, and on the database-level BytesReceived and BytesSent properties for versions 16 and later.
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.- or -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.
Performance Tip: A large Conns or Parent Conns value may indicate that heavy client load on the database is a performance bottleneck.
See also Alert #26 Connections.Performance Tip: A large Child Conns value (up to the number of processors available) may indicate that heavy use of intra-query parallelism is a performance bottleneck.
With intra-query parallelism, multiple processors (child connections) can do work for one client connection (the parent).A small number of individual client connections (e.g., one) may be responsible for heavy CPU usage on multiple child connections, thus preventing other client connections from getting their work done. If you suspect this is happening, try turning down the level of intra-query parallelism (for example, SET OPTION PUBLIC.MAX_QUERY_TASKS = '4' on an eight-processor computer) or turning it off altogether (SET OPTION PUBLIC.MAX_QUERY_TASKS = '1').
The parent connection count is based on the database-level ConnCount property, and the child connection count is calculated by Foxhound as the number of connections with non-zero connection-level ParentConnection property values.
Performance Tip: 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 for the following reasons:
- The database-level ConnCount property and connection details are recorded at slightly different points in time.
- SQL Anywhere 17 and later does not count the short-lived connections created by the SQL Anywhere Cockpit in the database-level ConnCount property but Foxhound does show those connections in the connections detail section.
You can suppress those connections by not running the Cockpit at the same time as Foxhound.
- SQL Anywhere 17.0.4 does not count the INT: StmtPerfMngrConn internal connection in the database-level ConnCount property but Foxhound does show that connection in the connections detail section.
The Executing, Idle, Waiting Conns columns show how many connections were executing, idle or waiting, where "waiting" includes blocked connections.Performance Tip: A large Waiting Conns value may indicate a performance bottleneck; have a look at the connection-level Current Req Status column to find the affected connections.
The database-level Executing, Idle, Waiting Conns columns are closely related to the connection-level Current Req Status value:
Foxhound Foxhound Database-Level Connection-Level SQL Anywhere Executing Idle Waiting Current Req Status SQL Anywhere Description ReqStatus ---------------------- --------------------------- --------------------------------------- ----------------- Yes - - Executing The connection is executing a request. Executing - Yes Idle The connection is not currently Idle processing a request. - - Yes Waiting for thread The connection has work to do and Unscheduled is waiting for a worker thread. - - Yes Waiting for I/O The connection is waiting for an I/O. BlockedIO - - Yes Waiting for shared resource The connection is waiting for access to BlockedContention shared database server data structures. - - Yes Blocked by lock The connection is blocked waiting BlockedLock for a locked row. - - Yes Blocked by mutex The connection is blocked waiting BlockedLock (1) for a mutex. - - Yes Blocked by semaphore The connection is blocked waiting Executing (2) for a semaphore. Note 1: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'BlockedLock' for a connection blocked by a mutex on a target database running on SQL Anywhere 17. Note 2: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'Executing' for a connection blocked by a semaphore on a target database running on SQL Anywhere 17.Here's an analogy that doesn't prove anything but might help understanding:
- If Executing is like a car moving down the road, then
- Waiting is like a car stuck in traffic,
- Blocked is like a car waiting at a red light, and
- Idle is like a car parked at the side of the road.
Performance Tip: The sum of the Executing, Idle, Waiting Conns columns should match the sum of the Parent and Child Conns columns, but it may differ for the following reasons:
- Different properties are recorded at slightly different points in time.
- SQL Anywhere 17.0.4 does not count the INT: StmtPerfMngrConn internal connection in the database-level ConnCount property but it is counted in the connection-level ReqStatus property.
The Executing, Idle, Waiting Conns columns are displayed for target databases running on SQL Anywhere 9 and later.
- For target databases running on SQL Anywhere versions 9 through 16, the Executing, Idle, Waiting Conns columns are entirely based on the connection-level ReqStatus property.
- For target databases running on SQL Anywhere 17, the Executing, Idle, Waiting Conns columns are based on the connection-level ReqStatus property for connections that are not blocked by a mutex or semaphore.
- For connections blocked by a mutex, the Waiting Conns column is based on the sa_locks() procedure.
- For connections blocked by a semaphore, the Waiting Conns column is based on the connection-level BlockedOn, LockObjectType and LockObjectOID properties and the SYSMUTEXSEMAPHORE table.
Active Req is the number of server workers that were handling client-side requests.Performance Tip: Large Active Req values may indicate that heavy client load on the database is a performance bottleneck.Performance Tip: Small Active Req values may indicate that some other performance bottleneck is preventing the database from processing client requests.
Note that "request" is an atomic internal unit of work processed by the server for a connection, not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived column for adhoc queries of connection-level data.Active Req is based on the server-level ActiveReq property.
Max Req is the server multiprogramming level, or the maximum number of requests that could be processed at one time (the -gn option).
Performance Tip: A Max Req value that is too small may cause the Unsch Req value to increase, in turn causing a bottleneck that increases response time and/or decreases throughput.Performance Tip: A Max Req value that varies up and down may indicate the server is unable to correctly determine the best value.
If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the dynamic tuning of the multiprogramming level by specifying the -gna 0 and -gn options to set a fixed value. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the multiprogramming level up and down.Max Req is displayed for target databases running on SQL Anywhere 9 and later, and is based on the server-level Threads property for SQL Anywhere 9 and on the server-level MultiProgrammingLevel property for SQL Anywhere 10 and later.
Unsch Req (formerly called Waiting Req) shows the number of requests that were waiting to be processed.
Performance Tip: Large Unsch Req values may indicate that some performance bottleneck is preventing the database from processing its workload.The bottleneck may simply be a Max Req value that is too low (solution: increase the server -gn option), but the problem may be harder than that; for example, if the application design results in excessive blocking and/or too many long-running requests.See also Alert #14 Unscheduled requests.
Unsch Req is based on the server-level UnschReq property.
Locks Held is the total number of locks that were held by all connections.Performance Tip: Large numbers of Locks Held are not a problem in themselves; SQL Anywhere is perfectly capable of handling millions of locks being held at one time.When locks become a problem is when they are held too long and cause other connections to be blocked (prevented) from doing work.For example, when 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.
See also Alert #25 Locks.
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.
Locks Held is displayed for target databases running on SQL Anywhere 10 and later, and is based on the database-level LockCount property.
Conns Blocked is the number of connections that were blocked.
Performance Tip: Large numbers of blocked connections may indicate a serious performance bottleneck caused by locks being held too long.The cause may be an application that breaks this fundamental rule of transaction design:"Never yield control of the mouse or keyboard to the end user while database locks are being held; always do a COMMIT first."When that rule is broken a long-running transaction is often the result, with those locks preventing other users from getting their work done while the first user decides what to do (or takes a lunch break).
See also Alert #23 Blocked connections and Alert #24 Conn blocking others.
Conns Blocked is based on the connection-level BlockedOn property for target databases running on SQL Anywhere versions 5.5 through 16, and on the connection-level BlockedOn, LockObjectType and LockObjectOID properties for version 17.
Note that Foxhound calculates the database-level Conns Blocked value by summarizing data provided by SQL Anywhere at the connection level.Waiting Time is the total time all current connections were blocked or forced to wait during the previous interval.
Performance Tip: Long waiting times may indicate a serious performance bottleneck caused a lack of resources.The Waiting Time value is not cumulative. A obscenely large value (such as a Waiting Time of 4h in an Interval of only 13s) is possible when, for example, 1000 connections are all trying to get work done but SQL Anywhere is only able to execute a few requests at a time.During a period of long Waiting Time values a sudden spike (for example, 3h to 6h) may occur when SQL Anywhere performs a checkpoint.
Waiting Time is displayed for target databases running on SQL Anywhere 9 and later, and is based on the sum of the connection-level ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties.
The CPU column shows two values: the percent used during the preceding interval (nn%) and the number of processors used (of n).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.
For example, if four processors are used, and SQL Anywhere is using 100% of one processor and 0% of the other three, Foxhound will show the CPU time as 25% of 4.Performance Tip: CPU time is most important when the value is unexpected.
- A high value like 80% may indicate the server is spending too much time executing inefficient SQL statements, or it may simply indicate the server is busy getting a lot of work done.
- A low value like 10% may indicate everything's OK with the SQL code, or it may indicate some other bottleneck is preventing the CPU from getting its work done.
- An extremely high value like 100% usually means trouble, especially when other numbers are cause for concern; e.g., low throughput, high response time. The solution may be "get a more powerful CPU", but it also may be "find and fix the problem".
- A zero value usually means "idle server", but it may also be a symptom of "unresponsive server".
Performance Tip: If you suspect that too much CPU time is being used by SQL code inside stored procedures, triggers, events and/or web services, SQL Anywhere's own "procedure profiler" is the very best way to find those bottlenecks.
See also Alert #4 CPU time and Alert #27 Connection CPU.
The CPU time and CPU count values are displayed for target databases running on SQL Anywhere version 7 and later, and are based on the server-level NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU properties.
The Temp Space column shows the total amount of temporary file 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.Performance Tip: Large amounts of Temp Space may indicate that runaway queries are a performance bottleneck.
To find which connections are using the most temporary file space, click on the Temp Space column heading in the connection section of Foxhound Monitor or History page. That will sort the connections in decreasing order by Temp Space.To limit the amount of temporary file space any individual connection can use on a target database running on SQL Anywhere version 10 or later, use the MAX_TEMP_SPACE option; e.g., SET OPTION PUBLIC.MAX_TEMP_SPACE = '1G';
See also Alert #21 Temp file usage and Alert #22 Conn temp file usage.
Performance Tip: The term "temporary file" is misleading and it should be renamed "temporary space" because the data may or may not reside in the actual temporary file (which may not exist at all).
Temp Space is displayed for target databases running on SQL Anywhere version 8 and later and is based on the database-level PageSize property and the sum of the connection-level TempFilePages property.
Rollback Log shows how much space in the rollback log is currently used by all the connections.
Performance Tip: A large Rollback Log value at the server level may indicate that long-running transactions are a performance bottleneck.Look for confirmation of this in the server-level Conns Blocked column and the connection-level Transaction Time column.See also Alert #32 Rollback log usage.
Performance Tip: The final checkpoint stage of a normal server shutdown may also take a long time while the changes recorded in a large rollback log are being reversed.
RollBack Log is based on the database-level PageSize property and the sum of the connection-level RollbackLogPages property.
Uncommitted shows how many operations have been performed by all the connections but not yet committed.
Performance Tip: A large number of Uncommitted operations at the server level may indicate that long-running transactions are a performance bottleneck.Look for confirmation of this in the server-level Conns Blocked column and the connection-level Transaction Time column.See also Alert #33 Uncommitted operations and Alert #34 Long uncommitted.
Performance Tip: The count of Uncommitted operations includes (and may be exactly equal to) the number of uncommitted row-level inserts, updates and deletes.
The count of Uncommitted operations may be larger than the number of INSERT, UPDATE and DELETE statements because one statement can affect many rows.If a single row is inserted, then updated twice and finally deleted, for example, that counts as four Uncommitted operations.
If a single row matches the WHERE clause of an UPDATE statement but all of the new column values in the SET clause are the same as the current values in the row, that row is not updated and the operation is not counted as an Uncommitted operation.
A table-level schema lock obtained by a SELECT statement is not counted as an Uncommitted operation even though the lock is not cleared until a subsequent commit or rollback (and may thus be regarded as "uncommitted").
Uncommitted is based on the sum of the connection-level UncommitOp property.
Cache Panics is the number of times the target server failed to find a cache page to allocate in the previous interval.See also Alert #29 Cache panics.Cache Panics is displayed for target databases running on SQL Anywhere version 9 and later, and is based on the server-level CachePanics property.
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.
Low Memory is displayed for target databases running on SQL Anywhere version 8 or later and is based on the database-level QueryLowMemoryStrategy property.Cache Satisfaction is the percentage of times a database page lookup was satisfied by the cache in the previous interval.
Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck:
- large Cache Panics values (greater than zero),
- large Low Memory values (greater than zero) and/or
- small Cache Satisfaction values (less than 100%).
Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.
Note: Cache Satisfaction is different from Index Satisfaction; one compares memory and disk usage, the other compares index and table usage.
See also Alert #20 Cache satisfaction.
Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.
Performance Tip: A long-running UNLOAD TABLE statement may result in extreme Cache Satisfaction values like 37.74%.
Cache Satisfaction is based on the server-level CacheHits and CacheRead properties for target databases running on SQL Anywhere versions 5 and 6, and on the database-level CacheHits and CacheRead properties for versions 7 and later.
Checkpoints is the number of CHECKPOINT operations that have been executed by the server in the previous interval.Performance Tip: Checkpoint operations can be expensive. More than one checkpoint every once in a while may indicate that undesirable behavior is causing a performance bottleneck.Generally speaking, explicit CHECKPOINT statements are not required in application programs because the server does a good job of scheduling checkpoints to minimize their impact on performance. An explicit CHECKPOINT should never be used without careful consideration, especially in a busy multi-user environment.Checkpoints are different from commits; a commit ensures that the transaction log file is up to date whereas a checkpoint ensures that the database file is up to date. As long as commits are performed frequently, performance can be greatly improved by delaying checkpoint operations... which is what SQL Anywhere does by default.
Performance Tip: Think twice before running a database without a transaction log. Even if you don't need the log for recovery purposes, SQL Anywhere will perform a checkpoint after every commit if there's no transaction log... and that can be very bad for performance.
Checkpoints is based on the server-level Chkpt property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level Chkpt property for versions 7 and later.
Checkpoint Urgency is the percentage of the SET OPTION PUBLIC.CHECKPOINT_TIME value that has elapsed since the previous checkpoint.
See also Alert #17 Checkpoint urgency.Checkpoint Urgency is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level CheckpointUrgency property.
Recovery Urgency is the estimated time required to recover the database when restarting after an abnormal stoppage, expressed as a percentage of the SET OPTION PUBLIC.RECOVERY_TIME value.
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.
See also Alert #18 Recovery urgency.
Recovery Urgency is displayed for target databases running on SQL Anywhere version 7 or later, and is based on the database-level RecoveryUrgency property.
Performance Tip: Before changing how often SQL Anywhere takes checkpoints, think twice! SQL Anywhere almost always does an excellent job of picking the right time to perform a checkpoint without your help.
Disk/Cache: Internal Index is the percentage ratio between the number of index internal-node pages that have been read from disk and from the cache in the previous interval.Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadIndInt and CacheReadIndInt properties.Disk/Cache: Leaf is the percentage ratio between the number of index leaf pages that have been read from disk and from the cache in the previous interval.
Disk/Cache: Leaf is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadIndLeaf and CacheReadIndLeaf properties.Disk/Cache: Table is the percentage ratio between the number of table pages that have been read from disk and from the cache in the previous interval.
Disk/Cache: Table is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadTable and CacheReadTable properties.The CacheReadTable counter is incremented every time SQL Anywhere tries to read a table page.
The DiskReadTable counter is incremented every time SQL Anywhere can't find the table in the cache and has to read it from disk.
Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck.
- large Disk/Cache: Internal Index steady-state values (greater than 1%, with zero expected),
- large Disk/Cache: Leaf steady-state values (greater than 1%) and/or
- large Disk/Cache: Table steady-state values (greater than 1%).
The term "steady-state" means the database has serving the same application for a few minutes.
Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.
Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size.
With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.Performance Tip: Disk/Cache ratios that are consistently large may indicate that cache thrashing caused by a large database page size is a performance bottleneck.
A smaller database page size may allow important pages to remain in the cache longer rather than being swapped out to disk to make room for different (giant) pages.Performance Tip: A long-running UNLOAD TABLE statement may result in extreme Disk/Cache: Internal Index, Leaf, Table values like "- / - / 99%".
Incomplete Reads is the current number of file reads that have been started but not yet completed.Performance Tip: Consistently large Incomplete Reads values (greater than zero) may indicate that there is a performance bottleneck caused by the inability of the disk system to keep up with database activity.See also Alert #15 Incomplete I/O.Incomplete Reads is displayed for target databases running on SQL Anywhere version 17, and is based on the database-level CurrRead property.
Incomplete Writes is the current number of file writes that have been started but not yet completed.
Performance Tip: Consistently large Incomplete Writes values (greater than zero) may indicate that there is a performance bottleneck caused by the inability of the disk system to keep up with database activity.See also Alert #15 Incomplete I/O.Incomplete Writes is displayed for target databases running on SQL Anywhere version 17, and is based on the database-level CurrWrite property.
Disk Reads is the number of pages that have been read from disk in the previous interval.Disk Reads is based on the server-level DiskRead property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level DiskRead property for versions 7 and later.Disk Writes is the number of modified pages that have been written to disk in the previous interval.
Disk Writes is based on the server-level DiskWrite property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level DiskWrite property for versions 7 and later.Log Writes column shows the number of pages that have been written to the transaction log in the previous interval.
Log Writes is based on the server-level LogWrite property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level LogWrite property for versions 7 and later.Performance Tip: Large Disk Reads, Disk Writes, Log Writes values may indicate that heavy disk I/O is a performance bottleneck.
See also Alert #16 I/O operations.Performance Tip: Disk Reads and Disk Writes count page-level input and output operations on the SYSTEM, temporary and secondary dbspace files, and Log Writes counts pages written to the transaction log file. Other files are excluded; e.g., non-dbspace files used by LOAD and UNLOAD statements and by xp_read_file() and xp_write_file() procedure calls.
Index Adds is the number of times an entry has been added to an index in the previous interval.Performance Tip: A large Index Adds value may indicate that a large number of row inserts is a performance bottleneck ...or a large number of row updates that are changing index columns.Index Adds is based on the server-level IndAdd property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndAdd property for target databases running on SQL Anywhere versions 7 and later.
Index Lookups is the number of times an entry has been looked up in an index in the previous interval.
Performance Tip: A large Index Lookups value may indicate that heavy index usage is a performance bottleneck ...or just the opposite; heavy index usage is often better than the alternative (frequent retrieval of data pages).Index Lookups is based on the server-level IndLookup property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndLookup property for target databases running on SQL Anywhere versions 7 and later.
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.
Performance Tip: Small Index Satisfaction values (less than 100%) may indicate that low index selectivity is causing a performance bottleneck.Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To increase Index Satisfaction at the database level it may be necessary to find the individual queries that are responsible; try clicking on the connection section column titles Full Index Comps or Index Satisfaction to sort the connections in descending order.
When you find the responsible queries, use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Note: Index Satisfaction is different from Cache Satisfaction; one compares index and table usage, the other compares memory and disk.
Index Satisfaction is based on the server-level IndLookup and FullCompare properties for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndLookup and FullCompare properties for target databases running on SQL Anywhere versions 7 and later.
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.Performance Tip: Large values of Full Index Comps may indicate that low index selectivity is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To reduce the number of Full Index Comps at the database level it may be necessary to find the individual queries that are responsible; try clicking on the connection section column titles Full Index Comps or Index Satisfaction to sort the connections in descending order.
When you find the responsible queries, use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Full Index Comps is based on the server-level FullCompare property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level FullCompare property for target databases running on SQL Anywhere versions 7 and later.
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.
You can click on an individual peak value to scroll the History page to that sample holding that peak value.Note: 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.
Performance Tip: Think twice before pressing the button. That's because Foxhound doesn't let you save or restore old peak values once they have been reset.
One reason to reset the peaks is when most of the peaks are so old they're no longer relevant; for example, the peaks predate major improvements made to database performance or they point to samples that have been purged,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:
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 scroll the History page 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.
Response time, also known as latency or access time, is a measure of how long it takes the database to respond to a single request:The Response... Heartbeat time peak shows the longest time it took for Foxhound to execute the SELECT * FROM DUMMY heartbeat or "canarian" query.
Performance Tip: Heartbeat time should be very small. Repeated values larger than 1s may indicate a performance bottleneck, and sudden increases in value may indicate a period of non-responsiveness.Note that there's a difference between precision and accuracy; for example, two successive Heartbeat measurements like 25ms and 50ms may be very precise but they may not accurately represent an immediate 100% slowdown in performance.On the other hand, a long period of Heartbeat values averaging 25ms, followed by a long period of 50ms numbers, may indeed accurately reflect a significant change in performance.
See also Alert #2 Long heartbeat.
Heartbeat time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
The Response... Sample time peak shows the longest time it took for Foxhound to gather all the performance data for this sample.
Sample time should always be longer than Heartbeat time, and it will grow longer with an increasing number of connections because of the extra work involved to analyze each sample.The Sample time is calculated after Heartbeat time and the two values do not overlap. It is possible for the Heartbeat time to be much longer than the Sample Time (e.g., 10m versus 100ms) when the target database becomes unresponsive but eventually responds; the long Heartbeat time reflects the unresponsiveness whereas the subsequent short Sample Time measures the return to normal.
Performance Tip: A long Sample Time may indicate a performance problem with Foxhound rather than the target database; e.g.:
- Sample Time is over 1 second but everything else is running OK on the target database.
Sample Time is large but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time increases rapidly with the number of connections to the target database.
Sample Time increases with the number of connections but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time is over 10 seconds causing the Interval time to also exceed 10 seconds.
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;See also Alert #3 Long sample time.
Sample time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
The Response... Ping time peak column shows the longest time it took for Foxhound to connect and disconnect from the target database.
Performance Tip: Long Ping times may indicate that a performance bottleneck is preventing the target database from accepting new connections on a timely basis.Ping times for local "shared memory" connections may be similar to or even smaller than Sample times for the same database, but they will usually be larger than Heartbeat times.Ping times for network connections may be much longer than Ping times for local connections.
Foxhound uses a custom "ping" process to test separate connections to the target database.
Each time the ping process runs it opens a new connection to the target database via the embedded SQL interface, issues a SELECT @@SPID command and then immediately disconnects.
This is different from the Foxhound Monitor process which connects to the target database via ODBC and keeps that connection open while it collects multiple samples.Note that the Foxhound ping process does not use the dbping.exe utility that ships with SQL Anywhere, nor does it use the ODBC interface that is used to gather sample data.
Also note that the separate ping process "uses up" another SQL Anywhere connection number each time it runs, which is once approximately every 10 seconds for each target database.
The new ping process can be used in three ways:
- As an addition to the Foxhound Monitor sampling process, the ping process tests the target database's ability to accept new connections as well as providing data for a third measure of response time: ping response time.
See Monitor Options - Include Ping.
See also Alert #35 Separate ping failed.
- As an alternative to Foxhound's sampling process, ping-only sampling may be used to check for Alert #1 Database unavailable without storing a lot of data in the Foxhound database.
See Monitor Options - Ping-Only Sampling.
- As an alternative to on/off Sample Schedule settings, ping-only sampling may be specified at various times of the day.
For example, ping-only sampling might be scheduled during the overnight hours
- when a large connection pool is mostly idle, or
- when a heavy load is expected and nobody much cares about performance.
Ping time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
Throughput, also known as bandwidth, is a measure of how much work the database has performed:The Throughput... Req peak is highest rate at which the server started processing a new request or resumed processing an existing request in a single interval.
Performance Tip: Large Throughput... Req values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.Note that "request" is an atomic internal unit of work processed by the server for a connection, not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived column for adhoc queries of connection-level data.Throughput... Req is based on the server-level Req property
(note that this is different from the connection-level Throughput... Req column which is based on the connection-level ReqCountActive property)The Throughput... Commits peak is the highest rate at which COMMIT operations were executed in a single interval.
Performance Tip: Large Throughput... Commits values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.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.
Throughput... Commits is based on the sum of the connection-level Commit property.
Foxhound does gather the database-level Commit property for target databases running on SQL Anywhere 12 and later, and the value is stored in the sample_detail."Commit" column for adhoc queries, but it is not the value displayed by Foxhound on the Monitor and other pages.Historically speaking, the value that Foxhound does display (the sum of the connection-level Commit property) is known to be more reliable than the database-level Commit property. However, it is possible that Foxhound may not count commits performed by short-lived connections that aren't captured at all.
This and other values displayed by Foxhound are intended for performance analysis purposes, not accounting or other purposes that require absolute accuracy.
The Throughput... Bytes peak is the highest rate at which data was sent and received for client server connections in a single interval.
Performance Tip: Large Throughput... Bytes values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.Throughput... Bytes is displayed for target databases running on SQL Anywhere versions 7 and later. It is based on the server-level BytesReceived and BytesSent properties for target databases running on SQL Anywhere versions 7 through 12, and on the database-level BytesReceived and BytesSent properties for versions 16 and later.
The Conns peak shows the largest number of connections that existed on a target database running on SQL Anywhere 11 or earlier, with Foxhound itself counting as 1 connection.The Parent, Child Conns peaks show the largest numbers of primary (parent) and internal (child) connections on a target database running on SQL Anywhere 12 or later, with Foxhound itself counting as 1 parent connection.
Performance Tip: A large Conns or Parent Conns value may indicate that heavy client load on the database is a performance bottleneck.
See also Alert #26 Connections.Performance Tip: A large Child Conns value (up to the number of processors available) may indicate that heavy use of intra-query parallelism is a performance bottleneck.
With intra-query parallelism, multiple processors (child connections) can do work for one client connection (the parent).A small number of individual client connections (e.g., one) may be responsible for heavy CPU usage on multiple child connections, thus preventing other client connections from getting their work done. If you suspect this is happening, try turning down the level of intra-query parallelism (for example, SET OPTION PUBLIC.MAX_QUERY_TASKS = '4' on an eight-processor computer) or turning it off altogether (SET OPTION PUBLIC.MAX_QUERY_TASKS = '1').
The parent connection count is based on the database-level ConnCount property, and the child connection count is calculated by Foxhound as the number of connections with non-zero connection-level ParentConnection property values.
Performance Tip: 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 for the following reasons:
- The database-level ConnCount property and connection details are recorded at slightly different points in time.
- SQL Anywhere 17 and later does not count the short-lived connections created by the SQL Anywhere Cockpit in the database-level ConnCount property but Foxhound does show those connections in the connections detail section.
You can suppress those connections by not running the Cockpit at the same time as Foxhound.
- SQL Anywhere 17.0.4 does not count the INT: StmtPerfMngrConn internal connection in the database-level ConnCount property but Foxhound does show that connection in the connections detail section.
The Executing, Idle, Waiting Conns peaks show the largest numbers of connections that were executing, idle or waiting, where "waiting" includes blocked connections. Performance Tip: A large Waiting Conns value may indicate a performance bottleneck; have a look at the connection-level Current Req Status column to find the affected connections.The database-level Executing, Idle, Waiting Conns columns are closely related to the connection-level Current Req Status value:
Foxhound Foxhound Database-Level Connection-Level SQL Anywhere Executing Idle Waiting Current Req Status SQL Anywhere Description ReqStatus ---------------------- --------------------------- --------------------------------------- ----------------- Yes - - Executing The connection is executing a request. Executing - Yes Idle The connection is not currently Idle processing a request. - - Yes Waiting for thread The connection has work to do and Unscheduled is waiting for a worker thread. - - Yes Waiting for I/O The connection is waiting for an I/O. BlockedIO - - Yes Waiting for shared resource The connection is waiting for access to BlockedContention shared database server data structures. - - Yes Blocked by lock The connection is blocked waiting BlockedLock for a locked row. - - Yes Blocked by mutex The connection is blocked waiting BlockedLock (1) for a mutex. - - Yes Blocked by semaphore The connection is blocked waiting Executing (2) for a semaphore. Note 1: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'BlockedLock' for a connection blocked by a mutex on a target database running on SQL Anywhere 17. Note 2: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'Executing' for a connection blocked by a semaphore on a target database running on SQL Anywhere 17.Here's an analogy that doesn't prove anything but might help understanding:
- If Executing is like a car moving down the road, then
- Waiting is like a car stuck in traffic,
- Blocked is like a car waiting at a red light, and
- Idle is like a car parked at the side of the road.
Performance Tip: The sum of the Executing, Idle, Waiting Conns columns should match the sum of the Parent and Child Conns columns, but it may differ for the following reasons:
- Different properties are recorded at slightly different points in time.
- SQL Anywhere 17.0.4 does not count the INT: StmtPerfMngrConn internal connection in the database-level ConnCount property but it is counted in the connection-level ReqStatus property.
The Executing, Idle, Waiting Conns columns are displayed for target databases running on SQL Anywhere 9 and later.
- For target databases running on SQL Anywhere versions 9 through 16, the Executing, Idle, Waiting Conns columns are entirely based on the connection-level ReqStatus property.
- For target databases running on SQL Anywhere 17, the Executing, Idle, Waiting Conns columns are based on the connection-level ReqStatus property for connections that are not blocked by a mutex or semaphore.
- For connections blocked by a mutex, the Waiting Conns column is based on the sa_locks() procedure.
- For connections blocked by a semaphore, the Waiting Conns column is based on the connection-level BlockedOn, LockObjectType and LockObjectOID properties and the SYSMUTEXSEMAPHORE table.
The Active Req peak is the largest number of server workers that were handling client-side requests.Performance Tip: Large Active Req values may indicate that heavy client load on the database is a performance bottleneck.Performance Tip: Small Active Req values may indicate that some other performance bottleneck is preventing the database from processing client requests.
Note that "request" is an atomic internal unit of work processed by the server for a connection, not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived column for adhoc queries of connection-level data.Active Req is based on the server-level ActiveReq property.
The Max Req peak is the largest maximum number of requests that could be processed at one time (the -gn option).
Performance Tip: A Max Req value that is too small may cause the Unsch Req value to increase, in turn causing a bottleneck that increases response time and/or decreases throughput.Performance Tip: A Max Req value that varies up and down may indicate the server is unable to correctly determine the best value.
If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the dynamic tuning of the multiprogramming level by specifying the -gna 0 and -gn options to set a fixed value. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the multiprogramming level up and down.Max Req is displayed for target databases running on SQL Anywhere 9 and later, and is based on the server-level Threads property for SQL Anywhere 9 and on the server-level MultiProgrammingLevel property for SQL Anywhere 10 and later.
The Unsch Req peak (formerly called Waiting Req) is the largest number of requests that were waiting to be processed.
Performance Tip: Large Unsch Req values may indicate that some performance bottleneck is preventing the database from processing its workload.The bottleneck may simply be a Max Req value that is too low (solution: increase the server -gn option), but the problem may be harder than that; for example, if the application design results in excessive blocking and/or too many long-running requests.See also Alert #14 Unscheduled requests.
Unsch Req is based on the server-level UnschReq property.
The Locks Held peak is the largest total number of locks held by all connections.
Performance Tip: Large numbers of Locks Held are not a problem in themselves; SQL Anywhere is perfectly capable of handling millions of locks being held at one time.When locks become a problem is when they are held too long and cause other connections to be blocked (prevented) from doing work.For example, when 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.
See also Alert #25 Locks.
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.
Locks Held is displayed for target databases running on SQL Anywhere 10 and later, and is based on the database-level LockCount property.
The Conns Blocked peak 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.
Performance Tip: Large numbers of blocked connections may indicate a serious performance bottleneck caused by locks being held too long.The cause may be an application that breaks this fundamental rule of transaction design:"Never yield control of the mouse or keyboard to the end user while database locks are being held; always do a COMMIT first."When that rule is broken a long-running transaction is often the result, with those locks preventing other users from getting their work done while the first user decides what to do (or takes a lunch break).
See also Alert #23 Blocked connections and Alert #24 Conn blocking others.
Conns Blocked is based on the connection-level BlockedOn property for target databases running on SQL Anywhere versions 5.5 through 16, and on the connection-level BlockedOn, LockObjectType and LockObjectOID properties for version 17.
Note that Foxhound calculates the database-level Conns Blocked value by summarizing data provided by SQL Anywhere at the connection level.The Waiting Time peak is the largest value of the total time all current connections were blocked or forced to wait during the previous interval.
Performance Tip: Long waiting times may indicate a serious performance bottleneck caused a lack of resources.The Waiting Time value is not cumulative. A obscenely large value (such as a Waiting Time of 4h in an Interval of only 13s) is possible when, for example, 1000 connections are all trying to get work done but SQL Anywhere is only able to execute a few requests at a time.During a period of long Waiting Time values a sudden spike (for example, 3h to 6h) may occur when SQL Anywhere performs a checkpoint.
Waiting Time is displayed for target databases running on SQL Anywhere 9 and later, and is based on the sum of the connection-level ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties.
The CPU 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 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.
For example, if four processors are used, and SQL Anywhere is using 100% of one processor and 0% of the other three, Foxhound will show the CPU time as 25% of 4.Performance Tip: CPU time is most important when the value is unexpected.
- A high value like 80% may indicate the server is spending too much time executing inefficient SQL statements, or it may simply indicate the server is busy getting a lot of work done.
- A low value like 10% may indicate everything's OK with the SQL code, or it may indicate some other bottleneck is preventing the CPU from getting its work done.
- An extremely high value like 100% usually means trouble, especially when other numbers are cause for concern; e.g., low throughput, high response time. The solution may be "get a more powerful CPU", but it also may be "find and fix the problem".
- A zero value usually means "idle server", but it may also be a symptom of "unresponsive server".
Performance Tip: If you suspect that too much CPU time is being used by SQL code inside stored procedures, triggers, events and/or web services, SQL Anywhere's own "procedure profiler" is the very best way to find those bottlenecks.
See also Alert #4 CPU time and Alert #27 Connection CPU.
The CPU time and CPU count values are displayed for target databases running on SQL Anywhere version 7 and later, and are based on the server-level NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU properties.
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.Performance Tip: Large amounts of Temp Space may indicate that runaway queries are a performance bottleneck.
To find which connections are using the most temporary file space, click on the Temp Space column heading in the connection section of Foxhound Monitor or History page. That will sort the connections in decreasing order by Temp Space.To limit the amount of temporary file space any individual connection can use on a target database running on SQL Anywhere version 10 or later, use the MAX_TEMP_SPACE option; e.g., SET OPTION PUBLIC.MAX_TEMP_SPACE = '1G';
See also Alert #21 Temp file usage and Alert #22 Conn temp file usage.
Performance Tip: The term "temporary file" is misleading and it should be renamed "temporary space" because the data may or may not reside in the actual temporary file (which may not exist at all).
Temp Space is displayed for target databases running on SQL Anywhere version 8 and later and is based on the database-level PageSize property and the sum of the connection-level TempFilePages property.
The Rollback Log peak shows the largest amount of space in the rollback log used by all the connections.
Performance Tip: A large Rollback Log value at the server level may indicate that long-running transactions are a performance bottleneck.Look for confirmation of this in the server-level Conns Blocked column and the connection-level Transaction Time column.See also Alert #32 Rollback log usage.
Performance Tip: The final checkpoint stage of a normal server shutdown may also take a long time while the changes recorded in a large rollback log are being reversed.
RollBack Log is based on the database-level PageSize property and the sum of the connection-level RollbackLogPages property.
The Uncommitted peak shows the largest number of operations performed by all the connections but not yet committed.
Performance Tip: A large number of Uncommitted operations at the server level may indicate that long-running transactions are a performance bottleneck.Look for confirmation of this in the server-level Conns Blocked column and the connection-level Transaction Time column.See also Alert #33 Uncommitted operations and Alert #34 Long uncommitted.
Performance Tip: The count of Uncommitted operations includes (and may be exactly equal to) the number of uncommitted row-level inserts, updates and deletes.
The count of Uncommitted operations may be larger than the number of INSERT, UPDATE and DELETE statements because one statement can affect many rows.If a single row is inserted, then updated twice and finally deleted, for example, that counts as four Uncommitted operations.
If a single row matches the WHERE clause of an UPDATE statement but all of the new column values in the SET clause are the same as the current values in the row, that row is not updated and the operation is not counted as an Uncommitted operation.
A table-level schema lock obtained by a SELECT statement is not counted as an Uncommitted operation even though the lock is not cleared until a subsequent commit or rollback (and may thus be regarded as "uncommitted").
Uncommitted is based on the sum of the connection-level UncommitOp property.
Cache Panics is the highest rate at which the target server failed to find a cache page to allocate.See also Alert #29 Cache panics.Cache Panics is displayed for target databases running on SQL Anywhere version 9 and later, and is based on the server-level CachePanics property.
Low Memory is the highest rate at which the target server had to change a query execution plan because cache memory ran low.
Low Memory is displayed for target databases running on SQL Anywhere version 8 or later and is based on the database-level QueryLowMemoryStrategy property.Cache Satisfaction is the lowest percentage of times a database page lookup was satisfied by the cache.
Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck:
- large Cache Panics values (greater than zero),
- large Low Memory values (greater than zero) and/or
- small Cache Satisfaction values (less than 100%).
Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.
Note: Cache Satisfaction is different from Index Satisfaction; one compares memory and disk usage, the other compares index and table usage.
See also Alert #20 Cache satisfaction.
Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.
Performance Tip: A long-running UNLOAD TABLE statement may result in extreme Cache Satisfaction values like 37.74%.
Cache Satisfaction is based on the server-level CacheHits and CacheRead properties for target databases running on SQL Anywhere versions 5 and 6, and on the database-level CacheHits and CacheRead properties for versions 7 and later.
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.
See also Alert #17 Checkpoint urgency.Checkpoint Urgency is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level CheckpointUrgency property.
Recovery Urgency is the highest estimated time required to recover 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.
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.
See also Alert #18 Recovery urgency.
Recovery Urgency is displayed for target databases running on SQL Anywhere version 7 or later, and is based on the database-level RecoveryUrgency property.
Performance Tip: Before changing how often SQL Anywhere takes checkpoints, think twice! SQL Anywhere almost always does an excellent job of picking the right time to perform a checkpoint without your help.
The Disk/Cache: Internal Index peak is the highest percentage ratio between the number of index internal-node pages that have been read from disk and from the cache.Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadIndInt and CacheReadIndInt properties.The Disk/Cache: Leaf peak is the highest percentage ratio between the number of index leaf pages that have been read from disk and from the cache.
Disk/Cache: Leaf is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadIndLeaf and CacheReadIndLeaf properties.The Disk/Cache: Table peak is the highest percentage ratio between the number of table pages that have been read from disk and from the cache.
Disk/Cache: Table is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadTable and CacheReadTable properties.The CacheReadTable counter is incremented every time SQL Anywhere tries to read a table page.
The DiskReadTable counter is incremented every time SQL Anywhere can't find the table in the cache and has to read it from disk.
Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck.
- large Disk/Cache: Internal Index steady-state values (greater than 1%, with zero expected),
- large Disk/Cache: Leaf steady-state values (greater than 1%) and/or
- large Disk/Cache: Table steady-state values (greater than 1%).
The term "steady-state" means the database has serving the same application for a few minutes.
Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.
Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size.
With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.Performance Tip: Disk/Cache ratios that are consistently large may indicate that cache thrashing caused by a large database page size is a performance bottleneck.
A smaller database page size may allow important pages to remain in the cache longer rather than being swapped out to disk to make room for different (giant) pages.Performance Tip: A long-running UNLOAD TABLE statement may result in extreme Disk/Cache: Internal Index, Leaf, Table values like "- / - / 99%".
The Incomplete Reads peak is the highest number of file reads that have been started but not yet completed.Performance Tip: Consistently large Incomplete Reads values (greater than zero) may indicate that there is a performance bottleneck caused by the inability of the disk system to keep up with database activity.See also Alert #15 Incomplete I/O.Incomplete Reads is displayed for target databases running on SQL Anywhere version 17, and is based on the database-level CurrRead property.
The Incomplete Writes peak is the highest number of file writes that have been started but not yet completed.
Performance Tip: Consistently large Incomplete Writes values (greater than zero) may indicate that there is a performance bottleneck caused by the inability of the disk system to keep up with database activity.See also Alert #15 Incomplete I/O.Incomplete Writes is displayed for target databases running on SQL Anywhere version 17, and is based on the database-level CurrWrite property.
The Disk Reads peak is the highest rate at which pages were read from disk.Disk Reads is based on the server-level DiskRead property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level DiskRead property for versions 7 and later.The Disk Writes peak is the highest rate at which modified pages were written to disk.
Disk Writes is based on the server-level DiskWrite property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level DiskWrite property for versions 7 and later.The Log Writes peak the highest rate at which log pages were written to the transaction log.
Log Writes is based on the server-level LogWrite property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level LogWrite property for versions 7 and later.Performance Tip: Large Disk Reads, Disk Writes, Log Writes values may indicate that heavy disk I/O is a performance bottleneck.
See also Alert #16 I/O operations.Performance Tip: Disk Reads and Disk Writes count page-level input and output operations on the SYSTEM, temporary and secondary dbspace files, and Log Writes counts pages written to the transaction log file. Other files are excluded; e.g., non-dbspace files used by LOAD and UNLOAD statements and by xp_read_file() and xp_write_file() procedure calls.
The Index Adds peak is the highest rate at which entries were added to indexes.Performance Tip: A large Index Adds value may indicate that a large number of row inserts is a performance bottleneck ...or a large number of row updates that are changing index columns.Index Adds is based on the server-level IndAdd property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndAdd property for target databases running on SQL Anywhere versions 7 and later.
The Index Lookups peak is the highest rate at which entries were looked up in an index.
Performance Tip: A large Index Lookups value may indicate that heavy index usage is a performance bottleneck ...or just the opposite; heavy index usage is often better than the alternative (frequent retrieval of data pages).Index Lookups is based on the server-level IndLookup property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndLookup property for target databases running on SQL Anywhere versions 7 and later.
The Index Satisfaction peak 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.
Performance Tip: Small Index Satisfaction values (less than 100%) may indicate that low index selectivity is causing a performance bottleneck.Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To increase Index Satisfaction at the database level it may be necessary to find the individual queries that are responsible; try clicking on the connection section column titles Full Index Comps or Index Satisfaction to sort the connections in descending order.
When you find the responsible queries, use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Note: Index Satisfaction is different from Cache Satisfaction; one compares index and table usage, the other compares memory and disk.
Index Satisfaction is based on the server-level IndLookup and FullCompare properties for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndLookup and FullCompare properties for target databases running on SQL Anywhere versions 7 and later.
The Full Index Comps peak shows the highest rate at which additional information had to be obtained from the table data in order to satisfy an index lookup.Performance Tip: Large values of Full Index Comps may indicate that low index selectivity is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To reduce the number of Full Index Comps at the database level it may be necessary to find the individual queries that are responsible; try clicking on the connection section column titles Full Index Comps or Index Satisfaction to sort the connections in descending order.
When you find the responsible queries, use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Full Index Comps is based on the server-level FullCompare property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level FullCompare property for target databases running on SQL Anywhere versions 7 and later.
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:
The pair of numbers [in square brackets] above the "Samples" column title are Foxhound database primary key values that can be used when coding adhoc queries; e.g., [4,1796].The first number is the sampling_id which identifies the Foxhound Monitor session for this target database.
The meaning of the second number depends on the type of data displayed in the top row of this section; for example:
SELECT * FROM alert WHERE alert_occurrence = 1796; SELECT * FROM alert_cancelled WHERE alert_cancelled_occurrence = 1796; SELECT * FROM all_clear WHERE all_clear_occurrence = 1796; SELECT * FROM autodropped_connection WHERE autodrop_occurrence = 1796; SELECT * FROM email_failure WHERE email_failure_occurrence = 1796; SELECT * FROM sample_header WHERE sample_set_number = 1796;
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.
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.Foxhound uses the interval time to convert performance statistics from cumulative counts to rates; e.g., to convert the total number of disk writes into the number of disk writes per second.
A very long interval like 1h 19.8m may indicate the computer was in sleep, standby or hibernate mode.
See also Alert #1 Database unresponsive.
Response time, also known as latency or access time, is a measure of how long it takes the database to respond to a single request:The Response... Heartbeat time column shows how long it took for Foxhound to execute the SELECT * FROM DUMMY heartbeat or "canarian" query.
Performance Tip: Heartbeat time should be very small. Repeated values larger than 1s may indicate a performance bottleneck, and sudden increases in value may indicate a period of non-responsiveness.Note that there's a difference between precision and accuracy; for example, two successive Heartbeat measurements like 25ms and 50ms may be very precise but they may not accurately represent an immediate 100% slowdown in performance.On the other hand, a long period of Heartbeat values averaging 25ms, followed by a long period of 50ms numbers, may indeed accurately reflect a significant change in performance.
See also Alert #2 Long heartbeat.
Heartbeat time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
The Response... Sample time column shows how long it took for Foxhound to gather all the performance data for this sample.
Sample time should always be longer than Heartbeat time, and it will grow longer with an increasing number of connections because of the extra work involved to analyze each sample.The Sample time is calculated after Heartbeat time and the two values do not overlap. It is possible for the Heartbeat time to be much longer than the Sample Time (e.g., 10m versus 100ms) when the target database becomes unresponsive but eventually responds; the long Heartbeat time reflects the unresponsiveness whereas the subsequent short Sample Time measures the return to normal.
Performance Tip: A long Sample Time may indicate a performance problem with Foxhound rather than the target database; e.g.:
- Sample Time is over 1 second but everything else is running OK on the target database.
Sample Time is large but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time increases rapidly with the number of connections to the target database.
Sample Time increases with the number of connections but the Heartbeat time remains small; e.g., 0s or .1s.
Sample Time is over 10 seconds causing the Interval time to also exceed 10 seconds.
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;See also Alert #3 Long sample time.
Sample time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
The Response... Ping time column shows how long it took for Foxhound to connect and disconnect from the target database.
Performance Tip: Long Ping times may indicate that a performance bottleneck is preventing the target database from accepting new connections on a timely basis.Ping times for local "shared memory" connections may be similar to or even smaller than Sample times for the same database, but they will usually be larger than Heartbeat times.Ping times for network connections may be much longer than Ping times for local connections.
Foxhound uses a custom "ping" process to test separate connections to the target database.
Each time the ping process runs it opens a new connection to the target database via the embedded SQL interface, issues a SELECT @@SPID command and then immediately disconnects.
This is different from the Foxhound Monitor process which connects to the target database via ODBC and keeps that connection open while it collects multiple samples.Note that the Foxhound ping process does not use the dbping.exe utility that ships with SQL Anywhere, nor does it use the ODBC interface that is used to gather sample data.
Also note that the separate ping process "uses up" another SQL Anywhere connection number each time it runs, which is once approximately every 10 seconds for each target database.
The new ping process can be used in three ways:
- As an addition to the Foxhound Monitor sampling process, the ping process tests the target database's ability to accept new connections as well as providing data for a third measure of response time: ping response time.
See Monitor Options - Include Ping.
See also Alert #35 Separate ping failed.
- As an alternative to Foxhound's sampling process, ping-only sampling may be used to check for Alert #1 Database unavailable without storing a lot of data in the Foxhound database.
See Monitor Options - Ping-Only Sampling.
- As an alternative to on/off Sample Schedule settings, ping-only sampling may be specified at various times of the day.
For example, ping-only sampling might be scheduled during the overnight hours
- when a large connection pool is mostly idle, or
- when a heavy load is expected and nobody much cares about performance.
Ping time is calculated by Foxhound; it is not based on any builtin SQL Anywhere properties.
Throughput, also known as bandwidth, is a measure of how much work the database has performed:Throughput... Req is the rate at which the server has started processing a new request or resumed processing an existing request during the preceding interval.
Performance Tip: Large Throughput... Req values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.Note that "request" is an atomic internal unit of work processed by the server for a connection, not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived column for adhoc queries of connection-level data.Throughput... Req is based on the server-level Req property
(note that this is different from the connection-level Throughput... Req column which is based on the connection-level ReqCountActive property)Throughput... Commits is the approximate rate at which commit requests have been executed by all connections in the previous interval.
Performance Tip: Large Throughput... Commits values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.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.
Throughput... Commits is based on the sum of the connection-level Commit property.
Foxhound does gather the database-level Commit property for target databases running on SQL Anywhere 12 and later, and the value is stored in the sample_detail."Commit" column for adhoc queries, but it is not the value displayed by Foxhound on the Monitor and other pages.Historically speaking, the value that Foxhound does display (the sum of the connection-level Commit property) is known to be more reliable than the database-level Commit property. However, it is possible that Foxhound may not count commits performed by short-lived connections that aren't captured at all.
This and other values displayed by Foxhound are intended for performance analysis purposes, not accounting or other purposes that require absolute accuracy.
Throughput... Bytes is the rate at which data has been received by and sent by the server across the client server interface in the previous interval.
Performance Tip: Large Throughput... Bytes values may indicate that heavy client load on the database is a performance bottleneck. Small values may indicate that some other performance bottleneck is preventing the database from processing client requests.Throughput... Bytes is displayed for target databases running on SQL Anywhere versions 7 and later. It is based on the server-level BytesReceived and BytesSent properties for target databases running on SQL Anywhere versions 7 through 12, and on the database-level BytesReceived and BytesSent properties for versions 16 and later.
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.- or -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.
Performance Tip: A large Conns or Parent Conns value may indicate that heavy client load on the database is a performance bottleneck.
See also Alert #26 Connections.Performance Tip: A large Child Conns value (up to the number of processors available) may indicate that heavy use of intra-query parallelism is a performance bottleneck.
With intra-query parallelism, multiple processors (child connections) can do work for one client connection (the parent).A small number of individual client connections (e.g., one) may be responsible for heavy CPU usage on multiple child connections, thus preventing other client connections from getting their work done. If you suspect this is happening, try turning down the level of intra-query parallelism (for example, SET OPTION PUBLIC.MAX_QUERY_TASKS = '4' on an eight-processor computer) or turning it off altogether (SET OPTION PUBLIC.MAX_QUERY_TASKS = '1').
The parent connection count is based on the database-level ConnCount property, and the child connection count is calculated by Foxhound as the number of connections with non-zero connection-level ParentConnection property values.
Performance Tip: 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 for the following reasons:
- The database-level ConnCount property and connection details are recorded at slightly different points in time.
- SQL Anywhere 17 and later does not count the short-lived connections created by the SQL Anywhere Cockpit in the database-level ConnCount property but Foxhound does show those connections in the connections detail section.
You can suppress those connections by not running the Cockpit at the same time as Foxhound.
- SQL Anywhere 17.0.4 does not count the INT: StmtPerfMngrConn internal connection in the database-level ConnCount property but Foxhound does show that connection in the connections detail section.
The Executing, Idle, Waiting Conns columns show how many connections were executing, idle or waiting, where "waiting" includes blocked connections.Performance Tip: A large Waiting Conns value may indicate a performance bottleneck; have a look at the connection-level Current Req Status column to find the affected connections.
The database-level Executing, Idle, Waiting Conns columns are closely related to the connection-level Current Req Status value:
Foxhound Foxhound Database-Level Connection-Level SQL Anywhere Executing Idle Waiting Current Req Status SQL Anywhere Description ReqStatus ---------------------- --------------------------- --------------------------------------- ----------------- Yes - - Executing The connection is executing a request. Executing - Yes Idle The connection is not currently Idle processing a request. - - Yes Waiting for thread The connection has work to do and Unscheduled is waiting for a worker thread. - - Yes Waiting for I/O The connection is waiting for an I/O. BlockedIO - - Yes Waiting for shared resource The connection is waiting for access to BlockedContention shared database server data structures. - - Yes Blocked by lock The connection is blocked waiting BlockedLock for a locked row. - - Yes Blocked by mutex The connection is blocked waiting BlockedLock (1) for a mutex. - - Yes Blocked by semaphore The connection is blocked waiting Executing (2) for a semaphore. Note 1: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'BlockedLock' for a connection blocked by a mutex on a target database running on SQL Anywhere 17. Note 2: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'Executing' for a connection blocked by a semaphore on a target database running on SQL Anywhere 17.Here's an analogy that doesn't prove anything but might help understanding:
- If Executing is like a car moving down the road, then
- Waiting is like a car stuck in traffic,
- Blocked is like a car waiting at a red light, and
- Idle is like a car parked at the side of the road.
Performance Tip: The sum of the Executing, Idle, Waiting Conns columns should match the sum of the Parent and Child Conns columns, but it may differ for the following reasons:
- Different properties are recorded at slightly different points in time.
- SQL Anywhere 17.0.4 does not count the INT: StmtPerfMngrConn internal connection in the database-level ConnCount property but it is counted in the connection-level ReqStatus property.
The Executing, Idle, Waiting Conns columns are displayed for target databases running on SQL Anywhere 9 and later.
- For target databases running on SQL Anywhere versions 9 through 16, the Executing, Idle, Waiting Conns columns are entirely based on the connection-level ReqStatus property.
- For target databases running on SQL Anywhere 17, the Executing, Idle, Waiting Conns columns are based on the connection-level ReqStatus property for connections that are not blocked by a mutex or semaphore.
- For connections blocked by a mutex, the Waiting Conns column is based on the sa_locks() procedure.
- For connections blocked by a semaphore, the Waiting Conns column is based on the connection-level BlockedOn, LockObjectType and LockObjectOID properties and the SYSMUTEXSEMAPHORE table.
Active Req is the number of server workers that are currently handling client-side requests.
Performance Tip: Large Active Req values may indicate that heavy client load on the database is a performance bottleneck.Performance Tip: Small Active Req values may indicate that some other performance bottleneck is preventing the database from processing client requests.
Note that "request" is an atomic internal unit of work processed by the server for a connection, not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived column for adhoc queries of connection-level data.Active Req is based on the server-level ActiveReq property.
Max Req is the maximum number of requests that could be processed at one time (the -gn option).
Performance Tip: A Max Req value that is too small may cause the Unsch Req value to increase, in turn causing a bottleneck that increases response time and/or decreases throughput.Performance Tip: A Max Req value that varies up and down may indicate the server is unable to correctly determine the best value.
If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the dynamic tuning of the multiprogramming level by specifying the -gna 0 and -gn options to set a fixed value. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the multiprogramming level up and down.Max Req is displayed for target databases running on SQL Anywhere 9 and later, and is based on the server-level Threads property for SQL Anywhere 9 and on the server-level MultiProgrammingLevel property for SQL Anywhere 10 and later.
Unsch Req (formerly called Waiting Req) shows the number of requests that were waiting to be processed.
Performance Tip: Large Unsch Req values may indicate that some performance bottleneck is preventing the database from processing its workload.The bottleneck may simply be a Max Req value that is too low (solution: increase the server -gn option), but the problem may be harder than that; for example, if the application design results in excessive blocking and/or too many long-running requests.See also Alert #14 Unscheduled requests.
Unsch Req is based on the server-level UnschReq property.
Locks Held is the total number of locks held by all connections.Performance Tip: Large numbers of Locks Held are not a problem in themselves; SQL Anywhere is perfectly capable of handling millions of locks being held at one time.When locks become a problem is when they are held too long and cause other connections to be blocked (prevented) from doing work.For example, when 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.
See also Alert #25 Locks.
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.
Locks Held is displayed for target databases running on SQL Anywhere 10 and later, and is based on the database-level LockCount property.
Conns Blocked is the number of connections that are blocked.
Performance Tip: Large numbers of blocked connections may indicate a serious performance bottleneck caused by locks being held too long.The cause may be an application that breaks this fundamental rule of transaction design:"Never yield control of the mouse or keyboard to the end user while database locks are being held; always do a COMMIT first."When that rule is broken a long-running transaction is often the result, with those locks preventing other users from getting their work done while the first user decides what to do (or takes a lunch break).
See also Alert #23 Blocked connections and Alert #24 Conn blocking others.
Conns Blocked is based on the connection-level BlockedOn property for target databases running on SQL Anywhere versions 5.5 through 16, and on the connection-level BlockedOn, LockObjectType and LockObjectOID properties for version 17.
Note that Foxhound calculates the database-level Conns Blocked value by summarizing data provided by SQL Anywhere at the connection level.Waiting Time is the total time all current connections were blocked or forced to wait during the previous interval.
Performance Tip: Long waiting times may indicate a serious performance bottleneck caused a lack of resources.The Waiting Time value is not cumulative. A obscenely large value (such as a Waiting Time of 4h in an Interval of only 13s) is possible when, for example, 1000 connections are all trying to get work done but SQL Anywhere is only able to execute a few requests at a time.During a period of long Waiting Time values a sudden spike (for example, 3h to 6h) may occur when SQL Anywhere performs a checkpoint.
Waiting Time is displayed for target databases running on SQL Anywhere 9 and later, and is based on the sum of the connection-level ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties.
The CPU column shows two values: the percent used during the preceding interval (nn%) and the number of processors used (of n).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.
For example, if four processors are used, and SQL Anywhere is using 100% of one processor and 0% of the other three, Foxhound will show the CPU time as 25% of 4.Performance Tip: CPU time is most important when the value is unexpected.
- A high value like 80% may indicate the server is spending too much time executing inefficient SQL statements, or it may simply indicate the server is busy getting a lot of work done.
- A low value like 10% may indicate everything's OK with the SQL code, or it may indicate some other bottleneck is preventing the CPU from getting its work done.
- An extremely high value like 100% usually means trouble, especially when other numbers are cause for concern; e.g., low throughput, high response time. The solution may be "get a more powerful CPU", but it also may be "find and fix the problem".
- A zero value usually means "idle server", but it may also be a symptom of "unresponsive server".
Performance Tip: If you suspect that too much CPU time is being used by SQL code inside stored procedures, triggers, events and/or web services, SQL Anywhere's own "procedure profiler" is the very best way to find those bottlenecks.
See also Alert #4 CPU time and Alert #27 Connection CPU.
The CPU time and CPU count values are displayed for target databases running on SQL Anywhere version 7 and later, and are based on the server-level NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU properties.
The Temp Space column shows the total amount of temporary file 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.Performance Tip: Large amounts of Temp Space may indicate that runaway queries are a performance bottleneck.
To find which connections are using the most temporary file space, click on the Temp Space column heading in the connection section of Foxhound Monitor or History page. That will sort the connections in decreasing order by Temp Space.To limit the amount of temporary file space any individual connection can use on a target database running on SQL Anywhere version 10 or later, use the MAX_TEMP_SPACE option; e.g., SET OPTION PUBLIC.MAX_TEMP_SPACE = '1G';
See also Alert #21 Temp file usage and Alert #22 Conn temp file usage.
Performance Tip: The term "temporary file" is misleading and it should be renamed "temporary space" because the data may or may not reside in the actual temporary file (which may not exist at all).
Temp Space is displayed for target databases running on SQL Anywhere version 8 and later and is based on the database-level PageSize property and the sum of the connection-level TempFilePages property.
Rollback Log shows how much space in the rollback log is currently used by all the connections.
Performance Tip: A large Rollback Log value at the server level may indicate that long-running transactions are a performance bottleneck.Look for confirmation of this in the server-level Conns Blocked column and the connection-level Transaction Time column.See also Alert #32 Rollback log usage.
Performance Tip: The final checkpoint stage of a normal server shutdown may also take a long time while the changes recorded in a large rollback log are being reversed.
RollBack Log is based on the database-level PageSize property and the sum of the connection-level RollbackLogPages property.
Uncommitted shows how many operations have been performed by all the connections but not yet committed.
Performance Tip: A large number of Uncommitted operations at the server level may indicate that long-running transactions are a performance bottleneck.Look for confirmation of this in the server-level Conns Blocked column and the connection-level Transaction Time column.See also Alert #33 Uncommitted operations and Alert #34 Long uncommitted.
Performance Tip: The count of Uncommitted operations includes (and may be exactly equal to) the number of uncommitted row-level inserts, updates and deletes.
The count of Uncommitted operations may be larger than the number of INSERT, UPDATE and DELETE statements because one statement can affect many rows.If a single row is inserted, then updated twice and finally deleted, for example, that counts as four Uncommitted operations.
If a single row matches the WHERE clause of an UPDATE statement but all of the new column values in the SET clause are the same as the current values in the row, that row is not updated and the operation is not counted as an Uncommitted operation.
A table-level schema lock obtained by a SELECT statement is not counted as an Uncommitted operation even though the lock is not cleared until a subsequent commit or rollback (and may thus be regarded as "uncommitted").
Uncommitted is based on the sum of the connection-level UncommitOp property.
Cache Panics is the number of times the target server failed to find a cache page to allocate in the previous interval.See also Alert #29 Cache panics.Cache Panics is displayed for target databases running on SQL Anywhere version 9 and later, and is based on the server-level CachePanics property.
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.
Low Memory is displayed for target databases running on SQL Anywhere version 8 or later and is based on the database-level QueryLowMemoryStrategy property.Cache Satisfaction is the percentage of times a database page lookup was satisfied by the cache in the previous interval.
Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck:
- large Cache Panics values (greater than zero),
- large Low Memory values (greater than zero) and/or
- small Cache Satisfaction values (less than 100%).
Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.
Note: Cache Satisfaction is different from Index Satisfaction; one compares memory and disk usage, the other compares index and table usage.
See also Alert #20 Cache satisfaction.
Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.
Performance Tip: A long-running UNLOAD TABLE statement may result in extreme Cache Satisfaction values like 37.74%.
Cache Satisfaction is based on the server-level CacheHits and CacheRead properties for target databases running on SQL Anywhere versions 5 and 6, and on the database-level CacheHits and CacheRead properties for versions 7 and later.
Checkpoints is the number of CHECKPOINT operations that have been executed by the server in the previous interval.Performance Tip: Checkpoint operations can be expensive. More than one checkpoint every once in a while may indicate that undesirable behavior is causing a performance bottleneck.Generally speaking, explicit CHECKPOINT statements are not required in application programs because the server does a good job of scheduling checkpoints to minimize their impact on performance. An explicit CHECKPOINT should never be used without careful consideration, especially in a busy multi-user environment.Checkpoints are different from commits; a commit ensures that the transaction log file is up to date whereas a checkpoint ensures that the database file is up to date. As long as commits are performed frequently, performance can be greatly improved by delaying checkpoint operations... which is what SQL Anywhere does by default.
Performance Tip: Think twice before running a database without a transaction log. Even if you don't need the log for recovery purposes, SQL Anywhere will perform a checkpoint after every commit if there's no transaction log... and that can be very bad for performance.
Checkpoints is based on the server-level Chkpt property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level Chkpt property for versions 7 and later.
Checkpoint Urgency is the percentage of the SET OPTION PUBLIC.CHECKPOINT_TIME value that has elapsed since the previous checkpoint.
See also Alert #17 Checkpoint urgency.Checkpoint Urgency is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level CheckpointUrgency property.
Recovery Urgency is the estimated time required to recover the database when restarting after an abnormal stoppage, expressed as a percentage of the SET OPTION PUBLIC.RECOVERY_TIME value.
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.
See also Alert #18 Recovery urgency.
Recovery Urgency is displayed for target databases running on SQL Anywhere version 7 or later, and is based on the database-level RecoveryUrgency property.
Performance Tip: Before changing how often SQL Anywhere takes checkpoints, think twice! SQL Anywhere almost always does an excellent job of picking the right time to perform a checkpoint without your help.
Disk/Cache: Internal Index is the percentage ratio between the number of index internal-node pages that have been read from disk and from the cache in the previous interval.Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadIndInt and CacheReadIndInt properties.Disk/Cache: Leaf is the percentage ratio between the number of index leaf pages that have been read from disk and from the cache in the previous interval.
Disk/Cache: Leaf is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadIndLeaf and CacheReadIndLeaf properties.Disk/Cache: Table is the percentage ratio between the number of table pages that have been read from disk and from the cache in the previous interval.
Disk/Cache: Table is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the database-level DiskReadTable and CacheReadTable properties.The CacheReadTable counter is incremented every time SQL Anywhere tries to read a table page.
The DiskReadTable counter is incremented every time SQL Anywhere can't find the table in the cache and has to read it from disk.
Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck.
- large Disk/Cache: Internal Index steady-state values (greater than 1%, with zero expected),
- large Disk/Cache: Leaf steady-state values (greater than 1%) and/or
- large Disk/Cache: Table steady-state values (greater than 1%).
The term "steady-state" means the database has serving the same application for a few minutes.
Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.
Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size.
With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.Performance Tip: Disk/Cache ratios that are consistently large may indicate that cache thrashing caused by a large database page size is a performance bottleneck.
A smaller database page size may allow important pages to remain in the cache longer rather than being swapped out to disk to make room for different (giant) pages.Performance Tip: A long-running UNLOAD TABLE statement may result in extreme Disk/Cache: Internal Index, Leaf, Table values like "- / - / 99%".
Incomplete Reads is the current number of file reads that have been started but not yet completed.Performance Tip: Consistently large Incomplete Reads values (greater than zero) may indicate that there is a performance bottleneck caused by the inability of the disk system to keep up with database activity.See also Alert #15 Incomplete I/O.Incomplete Reads is displayed for target databases running on SQL Anywhere version 17, and is based on the database-level CurrRead property.
Incomplete Writes is the current number of file writes that have been started but not yet completed.
Performance Tip: Consistently large Incomplete Writes values (greater than zero) may indicate that there is a performance bottleneck caused by the inability of the disk system to keep up with database activity.See also Alert #15 Incomplete I/O.Incomplete Writes is displayed for target databases running on SQL Anywhere version 17, and is based on the database-level CurrWrite property.
Disk Reads is the number of pages that have been read from disk in the previous interval.Disk Reads is based on the server-level DiskRead property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level DiskRead property for versions 7 and later.Disk Writes is the number of modified pages that have been written to disk in the previous interval.
Disk Writes is based on the server-level DiskWrite property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level DiskWrite property for versions 7 and later.Log Writes column shows the number of pages that have been written to the transaction log in the previous interval.
Log Writes is based on the server-level LogWrite property for target databases running on SQL Anywhere versions 5 and 6, and on the database-level LogWrite property for versions 7 and later.Performance Tip: Large Disk Reads, Disk Writes, Log Writes values may indicate that heavy disk I/O is a performance bottleneck.
See also Alert #16 I/O operations.Performance Tip: Disk Reads and Disk Writes count page-level input and output operations on the SYSTEM, temporary and secondary dbspace files, and Log Writes counts pages written to the transaction log file. Other files are excluded; e.g., non-dbspace files used by LOAD and UNLOAD statements and by xp_read_file() and xp_write_file() procedure calls.
Index Adds is the number of times an entry has been added to an index in the previous interval.
Performance Tip: A large Index Adds value may indicate that a large number of row inserts is a performance bottleneck ...or a large number of row updates that are changing index columns.Index Adds is based on the server-level IndAdd property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndAdd property for target databases running on SQL Anywhere versions 7 and later.
Index Lookups is the number of times an entry has been looked up in an index in the previous interval.
Performance Tip: A large Index Lookups value may indicate that heavy index usage is a performance bottleneck ...or just the opposite; heavy index usage is often better than the alternative (frequent retrieval of data pages).Index Lookups is based on the server-level IndLookup property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndLookup property for target databases running on SQL Anywhere versions 7 and later.
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.
Performance Tip: Small Index Satisfaction values (less than 100%) may indicate that low index selectivity is causing a performance bottleneck.Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To increase Index Satisfaction at the database level it may be necessary to find the individual queries that are responsible; try clicking on the connection section column titles Full Index Comps or Index Satisfaction to sort the connections in descending order.
When you find the responsible queries, use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Note: Index Satisfaction is different from Cache Satisfaction; one compares index and table usage, the other compares memory and disk.
Index Satisfaction is based on the server-level IndLookup and FullCompare properties for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level IndLookup and FullCompare properties for target databases running on SQL Anywhere versions 7 and later.
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.Performance Tip: Large values of Full Index Comps may indicate that low index selectivity is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To reduce the number of Full Index Comps at the database level it may be necessary to find the individual queries that are responsible; try clicking on the connection section column titles Full Index Comps or Index Satisfaction to sort the connections in descending order.
When you find the responsible queries, use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Full Index Comps is based on the server-level FullCompare property for target databases running on SQL Anywhere versions 5.5 and 6, and on the database-level FullCompare property for target databases running on SQL Anywhere versions 7 and later.
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
you can scroll this section if there are more than 100 connections.
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.
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.
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:
- Click on the title text on the line; e.g., click on Last Statement:
- 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.
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 together.
For more help on these lines:
This message appears when "xxx" contains one or more N's:Favorable? xxx Some data may be missing because settings on the target database were not favorable to the recording of data.
The Favorable? field displays a combination of three Y, N and - characters to indicate whether or not three settings on the target database are (or were) favorable to the recording of data for Foxhound to display on the Monitor, History and Connection History pages:
- Y means yes, the setting is (or was) favorable to Foxhound,
- N means no, and
- - means the setting is not supported by the SQL Anywhere target server.
You can click on the Favorable? link to open Monitor Options - 14. Change Target Settings to change these three settings:
- The RememberLastPlan server option controls whether or not anything is displayed in this connection-level field:
- Last Plan Text
- The RememberLastStatement server option controls whether or not anything is displayed in these connection-level fields:
- Blocked Statement
- Last Statement
- The RequestTiming server option controls whether or not anything is displayed in these connection-level columns:
- Throughput... Req
- Waiting Time
- Busy, Wait, Idle
Performance Tip: Number 2 (RememberLastStatement) is by far the most useful of the three.
Performance Tip: Changes to these server options only affect future samples gathered by Foxhound; there is no way to go back and "fix" historical data.
Performance Tip: Changes to these server options on a High Availability primary database or a Read-Only Scale-Out root database will not be automatically transmitted to a secondary or copy database.
However, since these are server options rather than database options, you can use the Favorable? field on the secondary or copy database to make the same changes even though the Foxhound connections to those databases are read-only.Performance Tip: After a High Availability failover, it is possible for the Favorable? field on the Monitor page for the secondary database to show different values from the Change Target Settings section on the Monitor Options page for the "same" database (which isn't actually the same any more).
If the target database is a High Availability secondary database and a failover occurs (primary fails, secondary takes over), the Favorable Current Settings? column may show [not available].The reason for this is that the Monitor Options - Change Target Settings section attempts to open a new connection to the secondary database, and the database that used to be the secondary is now the primary so there's no secondary database available. The Foxhound sampling session, however, remains connected to the original database (once the secondary, now the primary) so there is an apparent inconsistency between Favorable Current Settings? showing [not available], and the Foxhound Monitor page showing that everything is OK.
But wait, there's more! If the failed database that was the original primary database is restarted, it will become the new secondary database, and the Favorable Current Settings? column will show actual values rather than [not available]... but those values will be coming from a different database than the Foxhound sampling session is showing. That's because the Monitor Options - Change Target Settings section opens a new connection to the secondary database, and the Foxhound Monitor session remains connected to the original database (once the secondary, now the primary).
The RememberLastStatement server option is supported by target databases running on SQL Anywhere version 8 and later, while the RememberLastPlan and RequestTiming server options are supported by target databases running on SQL Anywhere version 9 and later.
The Hide Details / Show Details buttons alternate between hiding and displaying the following lines for each connection:
- AutoDrop Result:
- Blocked By:
- Block Reason:
- Locked Row Query:
- Last Statement:
- Last Plan Text:
The Conn #, User, OS User, IP, Name columns identify each connection.Performance Tip: If the target database is running on SQL Anywhere Version 9 or later, the Conn #, User, OS User, IP, Name columns form a link that opens the Connection History page in a separate browser window or tab. That page will show just this one single connection over time.
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, ...).The range for external client connection numbers is 1 through 999,999,999. If that range is exceeded the numbering starts at 1 again, but numbers already in use are skipped.There will never be two connections with the same number until the SQL Anywhere server is stopped and restarted.
Two numbers may be shown in the Conn # column for temporary internal or "child" 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 1000000090Conn #, 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: ExchangeThe Conn # column contains a single number for target databases running on SQL Anywhere 5.5 through 11, and one or two numbers for target databases running on SQL Anywhere 12 and later.
When the Conn # contains a single number, it is based on the connection-level Number property.When the Conn # contains a two numbers, the first number is based on the connection-level ParentConnection property and the second number is based on the connection-level Number property.
Performance Tip: To see child connections together with their parent connections, click on the "Conn #" column title to sort the connection section on that column.
Performance Tip: The frequent appearance of multiple child connections (like the "INT: Exchange" entries shown above) may indicate a bottleneck caused by runaway intra-query parallelism.
Performance Tip: Be on the lookout for short-lived "INT: Exchange" connections that may indicate a waste of resources.
User is the SQL Anywhere database user id that was used to make the connection; e.g., DBA.
User is based on the connection-level Userid property.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.OS User is displayed for target databases running on SQL Anywhere version 11 and later and is based on the connection-level OSUser property.
IP is the network IP address of the client side of the connection; e.g., 192.168.1.104.
IP is based on the connection-level NodeAddress property.Name is the connection name of the connection.
Performance Tip: Try using the ConnectionName (CON=) parameter to uniquely identify the actual end user responsible for each client connection.This is sometimes helpful when it's too hard to figure out who's doing what by looking at the User, OS User or IP values in Foxhound output.Connection names may be automatically assigned as follows:
- Internal connections started by SQL Anywhere EVENT handlers have the event name assigned as connection name.
- Internal connections started by SQL Anywhere web SERVICE handlers have the service name assigned as connection name.
- Internal "temporary" or "child" connections created by recent versions of SQL Anywhere have standard connection names assigned in the format "INT:xxx".
For example, INT:Cleaner is the connection name for the background database cleaner process, and INT:Exchange is assigned to child connections used for intra-query parallelism.
- When no explicit connection name is specified for a client connection, SQL Anywhere may assign a name like SQL_DBC_c657ef0.
- By default the Foxhound Monitor process uses connection names like "Foxhound-Monitor-nnnn" when connecting to target databases, where "nnnn" is Windows Process Identifier (PID) of the SQL Anywhere dbsrv16.exe or dbsrv17.exe process that is running the Foxhound database.
You can change those Foxhound connection names by changing the template; see Monitor Options - Monitor Connection Settings.
Name is based on the connection-level Name property.
Time Connected is the elapsed time between the time this connection was established and the sample time.Time Connected is displayed for target databases running on SQL Anywhere 9 and later, and is based on the connection-level LoginTime property.
Throughput, also known as bandwidth, is a measure of how much work the database has performed:Throughput... Req is the rate at which the server has started processing a new request or resumed processing an existing request for this connection in the previous interval.
Performance Tip: Large Throughput... Req values may indicate that this connection is placing a heavy load on the database.Note that "request" is an atomic internal unit of work processed by the server for a connection, not a client-server communication request from a client application to the SQL Anywhere server.
The latter is not displayed by Foxhound; however, it is recorded by Foxhound in the sample_connection.RequestsReceived column for adhoc queries of connection-level data.Throughput... Req is displayed for target databases running on SQL Anywhere version 9 and later, and is based on the connection-level ReqCountActive property.
(note that this is different from the server-level Throughput... Req column which is based on the server-level Req property)Throughput... Commits is the rate at which commit requests have been handled by the server for this connection in the previous interval.
Performance Tip: Large Throughput... Commits values may indicate that this connection is placing a heavy load on the database.Throughput... Commits is based on the connection-level Commit property.
Throughput... Bytes is the rate at which data has been received by and sent by the server across the client server interface for this connection in the previous interval.
Performance Tip: Large Throughput... Bytes values may indicate that this connection is placing a heavy load on the database.Throughput... Bytes is displayed for target databases running on SQL Anywhere versions 8 and later, and is based on the connection-level BytesReceived and BytesSent properties.
Locks Held is the total number of locks held by this connection.Performance Tip: Large numbers of Locks Held are not a problem in themselves; SQL Anywhere is perfectly capable of handling millions of locks being held at one time.When locks become a problem is when they are held too long and cause other connections to be blocked (prevented) from doing work.For example, when 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.
See also Alert #25 Locks.
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.
Locks Held is displayed for target databases running on SQL Anywhere 10 and later, and is based on the connection-level LockCount property.
Conns Blocked is the number of other connections that were blocked by this connection.
Performance Tip: Large numbers of blocked connections may indicate a serious performance bottleneck caused by locks being held too long.The cause may be an application that breaks this fundamental rule of transaction design:"Never yield control of the mouse or keyboard to the end user while database locks are being held; always do a COMMIT first."When that rule is broken a long-running transaction is often the result, with those locks preventing other users from getting their work done while the first user decides what to do (or takes a lunch break).
See also Alert #23 Blocked connections and Alert #24 Conn blocking others.
Conns Blocked is based on the connection-level BlockedOn property for target databases running on SQL Anywhere versions 5.5 through 16, and on the connection-level BlockedOn, LockObjectType and LockObjectOID properties for target databases running on SQL Anywhere 17.
Transaction Time is the length of time since the database was first modified by this connection after a COMMIT or ROLLBACK.
Performance Tip: A long Transaction Time may indicate that a long-running transaction is a performance bottleneck.Note that while it is possible for a SELECT to acquire locks and thus block other connections (e.g., a shared lock obtained by a SELECT will block an ALTER TABLE), a SELECT does not count as a modification as far as the Transaction Time is concerned. In other words, a SELECT does not start a transaction.See also Alert #28 Long transaction.
Transaction Time is displayed for target databases running on SQL Anywhere 8 and later, and is based on the connection-level TransactionStartTime property.
Waiting Time is the total amount of time this connection has been blocked or forced to wait.
Performance Tip: Long waiting times may indicate a serious performance bottleneck caused a lack of resources.Waiting Time is displayed for target databases running on SQL Anywhere 9 and later, and is based on the sum of the connection-level ReqTimeBlockContention, ReqTimeBlockIO, ReqTimeBlockLock and ReqTimeUnscheduled properties.
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:
- Busy: A worker is dedicated to processing a request for the connection, and that worker is neither blocked waiting for I/O, waiting for access to a shared data structure nor waiting for a locked object. The worker may be actively executing the request, but it may also be honoring a WAITFOR statement or waiting for some external work to be done (remote procedure, remote table, web service client procedure, external function, and so on).
- Wait: The connection is waiting for the server to start processing a request, or the worker dedicated to processing that request is blocked waiting for I/O, waiting for access to a shared data structure or waiting for a locked object.
- Idle: The connection is neither waiting nor busy.
The Busy, Wait, Idle columns are displayed for target databases running on SQL Anywhere 9 and later, and are based on the connection-level 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
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.See also Alert #4 CPU time and Alert #27 Connection CPU.
The CPU % is displayed for target databases running on SQL Anywhere 10 and later, and is based on the connection-level ApproximateCPUTime property and the server-level NumLogicalProcessorsUsed property.
The Child Conns column displays the number of internal child connections that have been started by each primary parent connection.
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.Performance Tip: Some versions of SQL Anywhere 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 the CPU time for this 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.
Child Conns is displayed for target databases running on SQL Anywhere 12 and later, and is based on the connection-level ParentConnection property.
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.Performance Tip: Large amounts of Temp Space may indicate that runaway queries are a performance bottleneck.
To find which connections are using the most temporary file space, click on the Temp Space column heading in the connection section of Foxhound Monitor or History page. That will sort the connections in decreasing order by Temp Space.To limit the amount of temporary file space any individual connection can use on a target database running on SQL Anywhere version 10 or later, use the MAX_TEMP_SPACE option; e.g., SET OPTION PUBLIC.MAX_TEMP_SPACE = '1G';
See also Alert #21 Temp file usage and Alert #22 Conn temp file usage.
Performance Tip: The term "temporary file" is misleading and it should be renamed "temporary space" because the data may or may not reside in the actual temporary file (which may not exist at all).
Temp Space is displayed for target databases running on SQL Anywhere version 8 and later, and is based on the database-level PageSize property and the connection-level TempFilePages property.
Rollback Log shows how much space in the rollback log is currently used by this connection.
Performance Tip: A large Rollback Log value at the connection level may indicate that a long-running transaction is a performance bottleneck.Look for confirmation of this in the connection-level Conns Blocked and Transaction Time columns.See also Alert #32 Rollback log usage.
Performance Tip: The final checkpoint stage of a normal server shutdown may also take a long time while the changes recorded in a large rollback log are being reversed.
RollBack Log is based on the database-level PageSize property and the connection-level RollbackLogPages property.
Uncommitted shows how many operations have been performed by this connection but not yet committed.
Performance Tip: A large number of Uncommitted operations at the connection level may indicate that a long-running transaction is a performance bottleneck.Look for confirmation of this in the connection-level Conns Blocked and Transaction Time columns.See also Alert #33 Uncommitted operations and Alert #34 Long uncommitted.
Performance Tip: The count of Uncommitted operations includes (and may be exactly equal to) the number of uncommitted row-level inserts, updates and deletes.
The count of Uncommitted operations may be larger than the number of INSERT, UPDATE and DELETE statements because one statement can affect many rows.If a single row is inserted, then updated twice and finally deleted, for example, that counts as four Uncommitted operations.
If a single row matches the WHERE clause of an UPDATE statement but all of the new column values in the SET clause are the same as the current values in the row, that row is not updated and the operation is not counted as an Uncommitted operation.
A table-level schema lock obtained by a SELECT statement is not counted as an Uncommitted operation even though the lock is not cleared until a subsequent commit or rollback (and may thus be regarded as "uncommitted").
Uncommitted is based on the connection-level UncommitOp property.
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.Low Memory is displayed for target databases running on SQL Anywhere version 8 or later and is based on the connection-level QueryLowMemoryStrategy property.Cache Satisfaction is the percentage of times since this connection started that a database page lookup for this connection was satisfied by the cache.
Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck:
- large Cache Panics values (greater than zero),
- large Low Memory values (greater than zero) and/or
- small Cache Satisfaction values (less than 100%).
Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.
Note: Cache Satisfaction is different from Index Satisfaction; one compares memory and disk usage, the other compares index and table usage.
See also Alert #20 Cache satisfaction.
Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size. With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.
Performance Tip: A long-running UNLOAD TABLE statement may result in extreme Cache Satisfaction values like 37.74%.
Cache Satisfaction is displayed for target databases running on SQL Anywhere 8 and later, and is based on the connection-level CacheHits and CacheRead properties.
Time Since Last Request is the elapsed time since the last time a request was started for this connection.Performance Tip: If this connection is blocked, Time Since Last Request tells you exactly how long it has been waiting.Performance Tip: A long Time Since Last Request is usually associated with an idle connection, but if the connection is busy (high CPU, high disk activity, etc.) it could mean that some single operation is "stuck" using up resources but not getting anywhere.
Time Since Last Request is based on the connection-level LastReqTime property.
Current Req Status shows whether this connection was Idle, Waiting, Blocked or Executing.Performance Tip: A blocked or waiting connection is a dramatic form of performance bottleneck when it corresponds to an end user who is unable get any work done.
- Small numbers of short-lived blocks are OK if nobody notices... the blocks are probably just doing their job by preventing collisions between competing updates.
- However, large numbers of blocked connections, and/or long-lasting blocks can bring a multi-user system to its knees.
- Foxhound columns that can help pinpoint the problems include Blocked By, Transaction Time, Locks Held, Conns Blocked, Rollback Log, Uncommitted, Block Reason and Locked Row Query.
The connection-level Current Req Status is closely related to the database-level Executing, Idle, Waiting Conns columns:
Foxhound Foxhound Database-Level Connection-Level SQL Anywhere Executing Idle Waiting Current Req Status SQL Anywhere Description ReqStatus ---------------------- --------------------------- --------------------------------------- ----------------- Yes - - Executing The connection is executing a request. Executing - Yes Idle The connection is not currently Idle processing a request. - - Yes Waiting for thread The connection has work to do and Unscheduled is waiting for a worker thread. - - Yes Waiting for I/O The connection is waiting for an I/O. BlockedIO - - Yes Waiting for shared resource The connection is waiting for access to BlockedContention shared database server data structures. - - Yes Blocked by lock The connection is blocked waiting BlockedLock for a locked row. - - Yes Blocked by mutex The connection is blocked waiting BlockedLock (1) for a mutex. - - Yes Blocked by semaphore The connection is blocked waiting Executing (2) for a semaphore. Note 1: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'BlockedLock' for a connection blocked by a mutex on a target database running on SQL Anywhere 17. Note 2: Foxhound ignores the SQL Anywhere connection-level ReqStatus value 'Executing' for a connection blocked by a semaphore on a target database running on SQL Anywhere 17.Here's an analogy that doesn't prove anything but might help understanding:
- If Executing is like a car moving down the road, then
- Waiting is like a car stuck in traffic,
- Blocked is like a car waiting at a red light, and
- Idle is like a car parked at the side of the road.
Current Req Status is displayed for target databases running on SQL Anywhere 9 and later.
- For target databases running on SQL Anywhere versions 9 through 16, Current Req Status is based on the connection-level ReqStatus property.
- For target databases running on SQL Anywhere 17, Current Req Status is based on the connection-level ReqStatus property for connections that are not blocked by a mutex or semaphore.
- For connections blocked by a mutex, Current Req Status is based on the sa_locks() procedure.
- For connections blocked by a semaphore, Current Req Status is based on the connection-level BlockedOn, LockObjectType and LockObjectOID properties and the SYSMUTEXSEMAPHORE table.
Disk/Cache: Internal Index is the percentage ratio between the number of index internal-node pages that have been read from disk and from the cache for this connection in the previous interval.Disk/Cache: Internal Index is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the connection-level DiskReadIndInt and CacheReadIndInt properties.Disk/Cache: Leaf is the percentage ratio between the number of index leaf pages that have been read from disk and from the cache for this connection in the previous interval.
Disk/Cache: Leaf is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the connection-level DiskReadIndLeaf and CacheReadIndLeaf properties.Disk/Cache: Table is the percentage ratio between the number of table pages that have been read from disk and from the cache for this connection in the previous interval.
Disk/Cache: Table is displayed for target databases running on SQL Anywhere version 7 and later, and is based on the connection-level DiskReadTable and CacheReadTable properties.The CacheReadTable counter is incremented every time SQL Anywhere tries to read a table page.
The DiskReadTable counter is incremented every time SQL Anywhere can't find the table in the cache and has to read it from disk.
Performance Tip: These conditions may indicate that insufficient memory in the database cache is a performance bottleneck.
- large Disk/Cache: Internal Index steady-state values (greater than 1%, with zero expected),
- large Disk/Cache: Leaf steady-state values (greater than 1%) and/or
- large Disk/Cache: Table steady-state values (greater than 1%).
The term "steady-state" means the database has serving the same application for a few minutes.
Historical rules-of-thumb no longer apply to modern databases; for example, "The cache should be at least 10% as large as the database file" has been replaced with "You can never have too much RAM cache!" Depending on the workload, a cache that's even larger than the database file may be justified by queries that use enormous quantities of temporary space.
Performance Tip: If the computer is dedicated to running this SQL Anywhere server and no other process of importance, consider disabling the automatic cache size tuning process by specifying the -ca 0 and -c options to set a fixed cache size.
With a dedicated computer there may be no need for SQL Anywhere to perform extra work changing the cache size up and down.Performance Tip: Disk/Cache ratios that are consistently large may indicate that cache thrashing caused by a large database page size is a performance bottleneck.
A smaller database page size may allow important pages to remain in the cache longer rather than being swapped out to disk to make room for different (giant) pages.Performance Tip: A long-running UNLOAD TABLE statement may result in extreme Disk/Cache: Internal Index, Leaf, Table values like "- / - / 99%".
Disk Reads is the rate at which pages were read from disk for this connection in the previous interval.Disk Reads is based on the connection-level DiskRead property..Disk Writes is the rate at which modified pages were written to disk for this connection in the previous interval.
Disk Writes is based on the connection-level DiskWrite property..Log Writes is the rate at which pages were written to the transaction log for this connection in the previous interval.
Log Writes is based on the connection-level LogWrite property.Performance Tip: Large Disk Reads, Disk Writes, Log Writes values may indicate that heavy disk I/O is a performance bottleneck.
See also Alert #16 I/O operations.Performance Tip: Disk Reads and Disk Writes count page-level input and output operations on the SYSTEM, temporary and secondary dbspace files, and Log Writes counts pages written to the transaction log file. Other files are excluded; e.g., non-dbspace files used by LOAD and UNLOAD statements and by xp_read_file() and xp_write_file() procedure calls.
Index Adds is the rate at which entries were added to indexes for this connection in the previous interval.Performance Tip: A large Index Adds value may indicate that a large number of row inserts is a performance bottleneck ...or a large number of row updates that are changing index columns.Index Adds is based on the connection-level IndAdd property.
Index Lookups is the rate at which entries were looked up in indexes for this connection in the previous interval.
Performance Tip: A large Index Lookups value may indicate that heavy index usage is a performance bottleneck ...or just the opposite; heavy index usage is often better than the alternative (frequent retrieval of data pages).Index Lookups is based on the connection-level IndLookup property.
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. Index Adds is the rate at which entries were added to indexes for this connection in the previous interval.
Performance Tip: Small Index Satisfaction values (less than 100%) may indicate that low index selectivity is causing a performance bottleneck.Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To reduce the number of Full Index Comps, determine the SQL query that was running and then use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Note: Index Satisfaction is different from Cache Satisfaction; one compares index and table usage, the other compares memory and disk.
Index Satisfaction is based on the connection-level IndLookup and FullCompare properties.
Full Index Comps is the rate at which additional information had to be obtained from the table data in order to satisfy an index lookup, for this connection in the previous interval.Performance Tip: Large values of Full Index Comps may indicate that low index selectivity is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To reduce the number of Full Index Comps, determine the SQL query that was running and then use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Full Index Comps is based on the connection-level FullCompare property.
Isolation Level shows the current isolation level and the updatable statement snapshot isolation level settings for this connection:Performance Tip: Large values of Full Index Comps may indicate that low index selectivity is causing a performance bottleneck.
Index selectivity is the ability to locate individual entries within index pages without having to read table pages.When index selectivity is low it often means
- SQL Anywhere has chosen the wrong index for a particular query predicate (a rare event but possible),
- the index design is inadequate for the task of satisfying that query predicate (more likely), and/or
- the index is fragmented (also possible; see the sa_index_levels() procedure).
To reduce the number of Full Index Comps, determine the SQL query that was running and then use SQL Anywhere's Graphical Plan With Statistics feature to examine index choice and usage.
Full Index Comps is based on the connection-level FullCompare property.
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) samplesNote 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.
If Foxhound's AutoDrop process dropped or attempted to drop a connection, 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 AutoDrop Result: Failed... issued at 2014-02-07 16:30:06.090, failed at 2014-02-07 16:30:06.114: SQLCODE = -660, SQLSTATE = WO005, ERRORMSG() = Server 'p001': [Sybase][ODBC Driver][SQL Anywhere] Permission denied: you do not have permission to disconnect "29"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.
The Blocked By: Conn #, User, OS User, IP, Name line identifies the connection that is blocking this one.Performance Tip: If the target database is running on SQL Anywhere Version 9 or later, the Blocked By: Conn #, User, OS User, IP, Name columns form a link that opens the Connection History page in a separate browser window or tab. That page will show just this one single connection over time.
See also Alert #23 Blocked connections and Alert #24 Conn blocking others.
Blocked By: Conn # is the connection number of the blocking connection.
Blocked By: Conn # is based on the connection-level BlockedOn property.Blocked By: User is the SQL Anywhere database user id that was used to make the connection; e.g., DBA.
Blocked By: User is based on the connection-level Userid property.Blocked By: OS User is the operating system user id associated with the blocking client process.
Blocked By: OS User is displayed for target databases running on SQL Anywhere version 11 and later and is based on the connection-level OSUser property.Blocked By: IP is the network IP address of the client side of the blocking connection; e.g., 192.168.1.104.
Blocked By: IP is based on the connection-level NodeAddress property.Blocked By: Name is the connection name of the blocking connection.
Blocked By: Name is based on the connection-level Name property.
The Block Reason: line describes the lock that's blocking the connection; e.g.:The format of Block Reason: depends on the level of detail provided to Foxhound by the target database; for example, only SQL Anywhere 17 supports Mutex and Semaphore locks.Block Reason: Row Transaction Write lock on DBA.t Block Reason: Row Transaction Intent, Row Transaction Write, Row Transaction WriteNoPK lock on dba.t Block Reason: Mutex Connection Exclusive lock on dba.test_mutex Block Reason: Semaphore Connection lock on dba.test_semaphoreHere are the various layouts used, from high to low levels of detail:
Block Reason: [class] [scope/duration] [type] lock on [owner].[object] [class] [scope/duration] [type] lock on [object] [class] [scope/duration] [type] Lock on [owner].[object] Lock on [object] Fields... [class]: Schema, Row, Table, Position, Mutex, Semaphore [scope/duration]: Transaction, Position, Connection [type]: Shared, Exclusive, Write, ... [owner]: object creator [object]: table, mutex or semaphore nameBlock Reason is based on the sa_locks() procedure, the SYSMUTEXSEMAPHORE table, and the connection-level BlockedOn, LockObjectType and LockObjectOID properties.
See also Alert #23 Blocked connections and Alert #24 Conn blocking others.
Block Reason is displayed for target databases running on SQL Anywhere 7 and later, and is based on
- the connection-level BlockedOn property and sa_locks() procedure for target databases running on SQL Anywhere 7 through 16, and
- the connection-level BlockedOn, LockObjectType and LockObjectOID properties, sa_locks() procedure and SYSMUTEXSEMAPHORE table for version 17.
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;See also Alert #23 Blocked connections and Alert #24 Conn blocking others.
Locked Row Query is displayed for target databases running on SQL Anywhere 10 and later, and is based on the sa_locks() procedure.
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"Last Statement is displayed for target databases running on SQL Anywhere 8 and later, and is based on the connection-level LastStatement property.
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 ) ) ) ) ) ) )Last Plan Text is displayed for target databases running on SQL Anywhere 10 and later, and is based on the connection-level LastPlanText property.