Contents IndexHow the optimizer works Chapter 22.  Database Collations

User's Guide
   Part III. Using SQL Anywhere
     Chapter 21. Monitoring and Improving Performance
      Monitoring database performance

SQL Anywhere provides a set of statistics that can be used to monitor database performance. These are accessible from Sybase Central, and client applications can access the statistics as functions. In addition, these statistics are made available by the database engine to the Windows NT performance monitor. The NT performance monitor can be used only to monitor Windows NT database engines or network servers.

This section describes how to access performance and related statistics from client applications, how to monitor database performance using Sybase Central, and how to monitor database performance using the Windows NT performance monitor.

Top of page


Obtaining database statistics from a client application

SQL Anywhere provides a set of system functions that can access information on a per-connection, per-database, or engine-wide basis. The kind of information available ranges from static information such as the server name, the database file associated with a database, or the page-size of a database, to detailed performance-related statistics concerning disk and memory usage. The performance-related statistics are also available, along with some other statistics, for the Windows NT engine and server in the Windows NT Performance Monitor.

For more information on the Performance Monitor, see "Monitoring database statistics from the NT Performance Monitor".

This section illustrates how to use the functions.

A complete list of system functions and of the properties available using the system functions is provided in the section "System functions".

Functions that retrieve system information

The following functions are used to retrieve system information:

property Provides the value of a given property on an engine-wide basis.

connection_property Provides the value of a given property for a given connection, or for the current connection by default.

db_property Provides the value of a given property for a given database, or for the current database by default. If you supply as argument only the name of the property you wish to retrieve, the functions return the value for the current server, connection, or database.

Examples

For example:

Improving query efficiency

For maximum efficiency, a client application monitoring database activity should use the property_number function to identify a named property, and then use the number to repeatedly retrieve the statistic. The following set of statements illustrates the process from ISQL:

     CREATE VARIABLE propnum INT ;
     CREATE VARIABLE propval INT ;
     SET propnum = property_number( 'cacheread' );
     SET propval = property( propnum )

Property names obtained in this way are available for many different database statistics, from the number of transaction log page write operations and the number of checkpoints carried out to the number of reads of index leaf pages from the memory cache.

Many of these statistics are made available in graphical form from the Sybase Central database management tool.

Top of page


Monitoring database statistics from Sybase Central

You can monitor database statistics from Sybase Central. The Sybase Central Performance Monitor is a graphing tool allowing database statistics to be plotted as a line graph or a bar graph.

To start the Sybase Central Performance Monitor:

  1. Click the icon for the server you wish to monitor in the left panel.
  2. Double-click the Statistics folder underneath the server.
  3. Select a statistic to graph, and drag it to the Performance Monitor icon to start graphing that statistic.

About the performance monitor

The Performance Monitor uses the regular SQL Anywhere communication mechanisms to gather statistics. This means some statistics (most notably Cache Reads) are affected by Sybase Central. For example, graphing Cache Reads/sec in Sybase Central shows a steady rate, even when nothing apart from the monitoring is going on.

If you have an NT client and server, the NT Performance monitor is preferable since it offers more statistics, and is not intrusive: updating the statistics will not affect the measurements. The extra statistics the NT performance monitor offers deal mainly with network communications---packets received, network buffers used, and so on.

Top of page


Monitoring database statistics from the NT Performance Monitor

The NT performance monitor is an application for viewing the behavior of objects such as processors, memory, and applications. SQL Anywhere provides many statistics for the performance monitor to display.

The NT performance monitor allows unintrusive monitoring of statistics: updating the statistics does not affect the measurements.

To start the NT performance monitor:

  1. Open the Administrative Tools program group.
  2. Double click Performance Monitor.

For information about Performance Monitor, see the Performance Monitor online Help.

To display SQL Anywhere statistics:

  1. With Performance Monitor running, select Add To Chart from the Edit menu, or click the Plus sign on the toolbar.

    The Add To Chart dialog appears.

  2. From the Object list, select SQL Anywhere.

    The Counter list then displays a list of the statistics provided.

  3. From the Counter list, click a statistic to be displayed.
  4. For a description of the selected counter, click Explain.
  5. To display the counter, click Add.
  6. When you have selected all the counters you wish to display, click Done.

Performance Monitor statistics

The statistics made available for Performance Monitor by SQL Anywhere are as follows:

Statistic Description
Active Requests Active Requests is the number of engine threads that are currently handling a request.
Asynchronous Reads/sec Asynchronous Reads/sec is the rate at which pages are being read asynchronously from disk.
Asynchronous Writes/sec Asynchronous Writes/sec is the rate at which pages are being written asynchronously to disk.
Bytes Received/sec Bytes Received/sec is the rate at which network data (in bytes) are being received.
Bytes Transmitted/sec Bytes Transmitted/sec is the rate at which bytes are being transmitted over the network.
Cache Hits/sec Cache Hits/sec is the rate at which database page lookups are satisfied by finding the page in the cache.
Cache Index Internal Reads/sec Cache Index Internal Reads/sec is the rate at which index internal-node pages are being read from the cache.
Cache Index Leaf Reads/sec Cache Index Leaf Reads/sec is the rate at which index leaf pages are being read from the cache.
Cache Reads/sec Cache Reads/sec is the rate at which database pages are being looked up in the cache.
Cache Table Reads/sec Cache Table Reads/sec is the rate at which table pages are being read from the cache.
Cache Writes/sec Cache Writes/sec is the rate at which pages in the cache are being modified (in pages/sec).
Checkpoint Flushes/sec Checkpoint Flushes/sec is the rate at which ranges of adjacent pages are being written out during a checkpoint.
Checkpoint Log/sec Checkpoint Log/sec is the rate at which the transaction log is being checkpointed.
Checkpoint Urgency Checkpoint Urgency is expressed as a percentage. See documentation for details.
Checkpoints/sec Checkpoints/sec is the rate at which checkpoints are being performed.
Commit files/sec Commit files/sec is the rate at which the engine is forcing a flush of the disk cache. On NT and NetWare platforms, the disk cache does not need to be flushed since unbuffered (direct) IO is used.
Commits/sec Commits/sec is the rate at which Commit requests are being handled.
Context Switch Checks/sec Context Switch Checks/sec is the rate at which the current engine thread is volunteering to give up the CPU to another engine thread.
Context Switches/sec Context Switches/sec is the rate at which the current engine thread is being changed.
Continue Requests/sec Continue Requests/sec is the rate at which "CONTINUE" requests are being issued to the engine.
Corrupt Packets/sec Corrupt Packets/sec is the rate at which corrupt network packets are being received.
Current IO Current IO is the current number of file IOs issued by the engine which have not yet completed.
Current Reads Current Reads is the current number of file reads issued by the engine which have not yet completed.
Current Writes Current Writes is the current number of file writes issued by the engine which have not yet completed.
Cursor Cursor is the number of declared cursors that are currently being maintained by the engine.
Dirty Pages Dirty Pages is the number of pages in the cache which must be written out and which do not belong to temporary files.
Disk Index Internal Reads/sec Disk Index Internal Reads/sec is the rate at which index internal-node pages are being read from disk.
Disk Index Leaf Reads/sec Disk Index Leaf Reads/sec is the rate at which index leaf pages are being read from disk.
Disk Reads/sec Disk Reads/sec is the rate at which pages are being read from file.
Disk SyncReads/sec Disk SyncReads/sec is the rate at which pages are being read synchronously from disk.
Disk SyncWrite Other/sec Disk SyncWrite Other/sec is the rate at which pages are being written synchronously to disk for a reason not covered by other "Disk SyncWrites _____/sec" counters.
Disk SyncWrites Checkpoint/sec Disk SyncWrites Checkpoint/sec is the rate at which pages are being written synchronously to disk for a checkpoint.
Disk SyncWrites Extend/sec Disk SyncWrites Extend/sec is the rate at which pages are being written synchronously to disk while extending a database file.
Disk SyncWrites Free Current/sec Disk SyncWrites Free Current/sec is the rate at which pages are being written synchronously to disk to free a page that cannot remain in the in-memory free list.
Disk SyncWrites Free Push/sec Disk SyncWrites Free Push/sec is the rate at which pages are being written synchronously to disk to free a page that can remain in the in-memory free list.
Disk SyncWrites Log/sec Disk SyncWrites Log/sec is the rate at which pages are being written synchronously to the transaction log.
Disk SyncWrites Rollback/sec Disk SyncWrites Rollback/sec is the rate at which pages are being written synchronously to the rollback log.
Disk SyncWrites/sec Disk SyncWrites/sec is the rate at which pages are being written synchronously to disk. It is the sum of all the other "Disk SyncWrites _____/sec" counters.
Disk Table Reads/sec Disk Table Reads/sec is the rate at which table pages are being read from disk.
Disk Waitreads/sec Disk Waitreads/sec is the rate at which the engine is waiting synchronously for the completion of a read IO operation which was originally issued as an asynchronous read. Waitreads often occur due to cache misses on systems that support asynchronous IO.
Disk Waitwrites/sec Disk Waitwrites/sec is the rate at which the engine is waiting synchronously for the completion of a write IO operation which was originally issued as an asynchronous write.
Disk Writes/sec Disk Writes/sec is the rate at which modified pages are being written to disk.
Dropped Packets/sec Dropped Packets/sec is the rate at which network packets are being dropped due to lack of buffer space.
Extend Database/sec Extend Database/sec is the rate (in pages/sec) at which the database file is being extended.
Extend Temporary File/sec Extend Temporary File/sec is the rate (in pages/sec) at which temporary files are being extended.
Free Buffers Number of free network buffers.
Freelist Write Current/sec Freelist Write Current/sec is the rate at which pages that cannot remain in the in-memory free list are being freed.
Freelist Write Push/sec Freelist Write Push/sec is the rate at which pages that can remain in the in-memory free list are being freed.
Full compares/sec Full compares/sec is the rate at which comparisons beyond the hash value in an index must be performed.
IO to Recover IO to Recover is the estimated number of IO operations required to recover the database.
Idle Active/sec Idle Active/sec is the rate at which the engine's idle thread becomes active to do idle writes, idle checkpoints, etc.
Idle Checkpoints/sec Idle Checkpoints/sec is the rate at which checkpoints are completed by the engine's idle thread. An idle checkpoint occurs whenever the idle thread writes out the last dirty page in the cache.
Idle Waits/sec Idle Waits/sec is the number of times per second that the server goes idle waiting for IO completion or a new request.
Idle Writes/sec Idle Writes/sec is the rate at which disk writes are being issued by the engine's idle thread.
Index Fills Index Fills is the number of times a new temporary merge index is created.
Index Merges Index Merges is the number of times a temp index has been merged into a main index
Index adds/sec Index adds/sec is the rate at which entries are being added to indexes.
Index lookups/sec Index lookups/sec is the rate at which entries are being looked up in indexes.
Lock Table Pages Lock Table Pages is the number of pages used to store lock information.
Main Heap Pages Main Heap Pages is the number of pages used for global engine data structures.
Map Pages Map Pages is the number of map pages used for accessing the lock table, frequency table, and table layout.
Maximum IO Maximum IO is the maximum value that "Current IO" has reached.
Maximum Reads Maximum Reads is the maximum value that "Current Reads" has reached.
Maximum Writes Maximum Writes is the maximum value that "Current Writes" has reached.
Multi-packets Received/sec Multi-packets Received/sec is the rate at which multi-packet deliveries are being received.
Multi-packets Transmitted/sec Multi-packets Transmitted/sec is the rate at which multi-packet deliveries are being transmitted.
Open cursors Open cursors is the number of open cursors that are currently being maintained by the engine.
Packets Received/sec Packets Received/sec is the rate at which network packets are being received.
Packets Transmitted/sec Packets Transmitted/sec is the rate at which network packets are being transmitted.
Page Relocations/sec Page Relocations/sec is the rate at which relocatable heap pages are being read from the temporary file.
Pending requests/sec Pending requests/sec is the rate at which the engine is detecting the arrival of new requests.
Ping1/sec Ping1/sec is the rate at which ping requests which go all the way down into the engine are serviced.
Ping2/sec Ping2/sec is the rate at which ping requests which are turned around at the top of the protocol stack are serviced.
Procedure Pages Procedure Pages is the number of relocatable heap pages used for procedures.
Read Hints Used/sec Read Hints Used/sec is the rate at which page-read operations are being satisfied immediately from cache thanks to a earlier read hint.
Read Hints/sec A read hint is an asynchronous read operation for a page that the database engine is likely to need soon. Read Hints/sec is the rate at which such read operations are being issued.
Recovery Urgency Recovery Urgency is expressed as a percentage. See documentation for details.
Redo Free Commits/sec A "Redo Free Commit" occurs when a commit of the transaction (redo) log is requested but the log has already been written (so the commit was done for "free").
Redo Rewrites/sec Redo Rewrites/sec is the rate at which pages that were previously written to the transaction log (but were not full) are being written to the transaction log again (but with more data added).
Redo Writes/sec Redo Writes/sec is the rate at which pages are being written to the transaction (redo) log.
Relocatable Heap Pages Relocatable Heap Pages is the number of pages used for relocatable heaps (cursors, statements, procedures, triggers, views, etc.).
Remoteput Wait/sec Remoteput Wait/sec is the rate at which the communication link must wait because it does not have buffers available to send information. This statistic is collected for NetBIOS (both sessions and datagrams) and IPX protocols only.
Requests/sec Requests/sec is the rate at which the engine is being entered to allow it to handle a new request or continue processing an existing request.
Rereads Queued/sec A reread occurs when a read request for a page is received by the database IO subsystem while an asynchronous read IO operation has been posted to the operating system but has not completed. Rereads Queued/sec is the rate at which this condition is occurring.
Rereceived Packets/sec Rereceived Packets/sec is the rate at which duplicate network packets are being received.
Retransmitted Packets/sec Retransmitted Packets/sec is the rate at which network packets are being retransmitted.
Rollback Log Pages Rollback Log Pages is the number of pages in the rollback log.
Rollback/sec Rollbacks/sec is the rate at which Rollback requests are being handled.
SQL Anywhere The SQL Anywhere object provides information about the Sybase SQL Anywhere Server, Client or Engine.
Sends Failed/sec Sends Failed/sec is the rate at which the underlying protocol(s) failed to send a packet.
Statement Statements is the number of prepared statements that are currently being maintained by the engine.
TotalBuffers Total number of network buffers.
Trigger Pages Trigger Pages is the number of relocatable heap pages used for triggers.
Unscheduled requests Unscheduled requests is the number of requests that are currently queued up waiting for an available engine thread.
View Pages View Pages is the number of relocatable heap pages used for views.
Voluntary blocks/sec Voluntary blocks/sec is the rate at which engine threads voluntarily block on pending disk IO.
Waitread Full Compare/sec Waitread Full Compare/sec is the rate at which read requests associated with a full comparison (a comparison beyond the hash value in an index) must be satisfied by a synchronous read operation.
Waitread Optimizer/sec Waitread Optimizer/sec is the rate at which read requests posted by the optimizer must be satisfied by a synchronous read operation.
Waitread Other/sec Waitread Other/sec is the rate at which read requests from other sources must be satisfied by a synchronous read operation.
Waitread SysConnection/sec Waitread SysConnection/sec is the rate at which read requests posted from the system connection must be satisfied by a synchronous read operation. The system connection is a special connection used as the context before a connection is made and for operations performed outside of a any client connection.
Waitread Temporary Table/sec Waitread Temporary Table/sec is the rate at which read requests for a temporary table must be satisfied by a synchronous read operation.

Top of page


Contents IndexHow the optimizer works Chapter 22.  Database Collations