Help for Foxhound 3.0.4386a

Table of Contents     [RisingRoad]

5. The Connection History Page

The Connection History page shows the connection-level performance statistics for a single connection, as well the server and database-level statistics corresponding to the top-most row in the connection section.

The Connection History page is similar to the Connection section of the Database Monitor and Sample History pages, except that multiple samples for a single connection are shown rather than multiple connections for a single sample.

In other words, you can use the Connection History page to see the history of a single connection at a glance, rather than having to jump from page to page (sample to sample) on the History page and then scroll to the connection you're interested in.

5.1 The Connection History Menu

5.2 Top Sample

5.3 Connection Identification

5.4 Connection Samples

5.1 The Connection History Menu    [Top] 

« Back to Menu   DSN: ddd12   New Menu   Schema   Monitor   History   Foxhound Options   Monitor Options   (?)   About

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Newest - Scroll to the most recent samples for this connection

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Oldest - Scroll down to the oldest sample for this connection

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

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

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

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

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

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

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

Goto: 1 works like Oldest.

Goto: 0 works like Oldest.

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

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

Go to: 9999999999999 works like Newest.

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

5.2 Top Sample    [Top]

The Top Sample section shows the database sample data corresponding to the top-most Connection History sample.

The Top Sample section consists of 5 or 6 lines:

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

The color highlighting in this section is controlled by values in the Peaks section shown on the corresponding Sample History page, as follows:

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

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

Top Sample

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

Interval

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

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

Latency... Heartbeat, Sample Time

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

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

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

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

Although Heartbeat and Sample times are calculated to the millisecond (0.001 second), Foxhound doesn't show them with any more precision than one decimal place (0.1 second) because the underlying logic is no more precise than that. Sample times are simply rounded to the nearest tenth of a second.

However, Heartbeat times are often very small and would appear as zero if they were rounded to the nearest tenth of a second. Because it may be important to see the difference between zero and non-zero values, Heartbeat times are handled differently: zero values are shown as 0s and non-zero values between 1 and 99 milliseconds are changed to 100 milliseconds; i.e, the smallest non-zero value shown is 0.1s even if the calculated Heartbeat time is as small as 0.001 second.

Throughput... Req, Commits, Bytes

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

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

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

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

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

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

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

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

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

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

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

Conns / Parent, Child Conns

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

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

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

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

Executing, Idle, Waiting Conns

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

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

Active Req, Max Req, Unsch Req

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

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

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

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

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

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

Locks Held, Conns Blocked, Waiting Time

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

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

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

Conns Blocked is the number of connections that were blocked.

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

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

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

How To Enable Timing Information

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

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

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

CPU Time

The CPU Time column shows four values:

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

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

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

Temp Space, Rollback Log, Uncommitted

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

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

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

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

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

Cache Panics, Low Memory, Satisfaction

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

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

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

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

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

Checkpoints, Checkpoint Urgency, Recovery Urgency

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

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

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

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

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

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

Disk Reads, Disk Writes, Log Writes

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

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

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

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

Index Adds, Lookups, Satisfaction

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

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

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

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

Full Index Comps

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

This number is based on the FullCompare property.

DB File, Used, Fragments

DB File is the SYSTEM dbspace file size.

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

Fragments is the number of SYSTEM dbspace file fragments.

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

5.3 Connection Identification    [Top]

The Connection Identification section displays the values that uniquely identify this connection apart from all other connections that exist now, or have ever existed, or will ever exist on any target database recorded by this copy of Foxhound.

Conn #, User, OS User, IP, Name

The Conn #, User, OS User, IP, Name columns identify this connection.

Conn # is the connection number of the connection.

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

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

Conn #, User, OS User, IP, Name
1 1000000090 / - / - / - / INT: Exchange

When two numbers are shown in the Conn # column, you can open a separate Connection History page for the parant connection by clicking on the separate Parent Conn #, User, OS User, IP, Name on the right.

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

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

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

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

Name is the connection name of the connection.

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

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

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

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

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

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

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

Login Time

The Login Time column helps to uniquely identify this connection apart from earlier or later connections which may have the same connection number because the target server was restarted.

Connection Id String

The Connection Id String column is an artificial identifier constructed by Foxhound to uniquely identify each target connection within the entire Foxhound database.

Foxhound uses this string to create URLs for opening new Connection History pages. You may find it useful when creating adhoc queries to select all the history for one connection. It is stored in the sample_connection.connection_id_string column, and there is an index on that column.

Here's the format of the Connection Id String: 

ss-cc-yyyymmddhhnnss-sss
where...
ss                   sample_connection.sampling_id - unique identifier to each target database sampling session
cc                   sample_connection.connection_number  
yyyymmddhhmmss-sss   DATEFORMAT ( sample_connection.LoginTime, 'YYYYMMDDHHNNSS-SSS' )

Note: It is possible for two or more different Connection Id String values to represent the same actual connection. This can happen if you accidentally create two or more separate Foxhound sampling sessions for the same target database; e.g., by using a DSN and a Connection String that point to the same target database. The result will be two or more sets of data about the same connection, using different values in the sampling_id column. This is not a fatal error, simply a waste of Foxhound resources, and it shouldn't affect your adhoc queries; it is still true that a single Connection Id String value will uniquely identify a single actual connection.

Parent Conn #, User, OS User, IP, Name

The Parent Conn #, User, OS User, IP, Name link is displayed only when the current connection is a child connection: 
     Conn #, User, OS User, IP, Name       ...   Parent Conn #, User, OS User, IP, Name
3 1000008782 / - / - / - / INT: Exchange             3 / DBA / Breck / - / ddd16-1
You can click on the Parent link to open a separate Connection History page for the parent connection to this child connection.

5.4 Connection Samples    [Top]

The Connection Samples section shows performance statistics for a single connection over time.

[9,9999]

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

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

Hide Details / Show Details

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

Connection Samples

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

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

You can click on the date/time link to scroll the current Connection History page to the associated sample.

Interval

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

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

Time Connected

Time Connected is the elapsed time between the time this connection was established and the sample time.

This number is based on the LoginTime property (shown in the tooltip for the column title).

Volume... Req, Commits, Bytes

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

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

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

The Bytes column (formerly displayed as Bytes In / Out) shows the total amount of data received by and sent by the server across the client server connection, for this connection since it started.

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

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

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

Conns Blocked is the number of other connections that were blocked by this connection at the point this sample was recorded.

Transaction Running Time is the length of time a transaction has been running on this connection at the point this sample was recorded.

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

Waiting Time, Busy, Wait, Idle

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

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

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

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

How To Enable Timing Information

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

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

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

CPU %, Child Conns

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

The Child Conns column displays the number of internal child connections that were running under this primary parent connection, as of the point this sample was recorded.

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

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

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

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

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

Temp Space, Rollback Log, Uncommitted

Temp Space shows how much temporary space was being used by this connection at the point this sample was recorded.

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

Rollback Log shows how much space in the rollback log was being used by this connection at the point this sample was recorded.

Uncommitted shows how many operations had been performed by this connection but not yet committed as of the point this sample was recorded.

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

Low Memory, Cache Satisfaction

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

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

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

Time Since Last Request

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

This number is based on the LastReqTime property.

Current Req Status

Current Req Status shows whether this connection was idle, waiting or executing at the point this sample was recorded.

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

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

Disk Reads, Disk Writes, Log Writes

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

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

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

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

Index Adds, Lookups, Satisfaction

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

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

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

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

Full Index Comps

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

This number is based on the FullCompare property.

Isolation Level

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

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

This column is based on the Isolation_level and updatable_statement_isolation properties.

AutoDropped for this reason:

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

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

Autodrop Result:

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

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"

Blocked By:

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

Conn # is the connection number of the other connection.

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

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

OS User is the operating system user id associated with the client process that made the other connectiion.

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

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

Name is the connection name of the other connection.

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

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

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

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

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

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

Block Reason:

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

Block Reason: Row Transaction Write lock on DBA.t

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

Locked Row Query:

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

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

Last Statement:

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

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

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

How To Enable The Last Statement Line

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

- or -

SET OPTION PUBLIC.MAX_CLIENT_STATEMENTS_CACHED = '0';

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

Here's the full story:

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

Last Plan Text:

The Last Plan Text: line displays the last query execution plan used by this connection, as of the point this sample was recorded: 
Last Plan Text: [Show More] ( Plan ( SingleRowGroupBy ( Exchange [ 8 ] ( SingleRowGroupBy ( Nested...

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

Last Plan Text: [Show Less] 
                ( Plan 
                ( SingleRowGroupBy 
                    ( Exchange [ 8 ]
                      ( SingleRowGroupBy 
                        ( NestedLoopsJoin
                          ( NestedLoopsJoin
                            ( ParallelTableScan ( ISYSUSER su ) )
                            ( TableScan ( ISYSTAB tab ) )
                          )
                          ( TableScan ( ISYSTABCOL col ) )
                        )
                      )
                    )
                  )
                )

How To Enable The Last Plan Line

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

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

[Top]



























































[Top]