Monitoring Performance Counters
PSQL Server for Windows provides performance counters for use with the Windows Performance Monitor. (The PSQL performance counters are supported only on Windows Vista or later Windows operating systems.) The performance counters measure state or activity of the database engine, which allows you to analyze performance of your application. Windows Performance Monitor requests the current value of the performance counters at specified time intervals.
PSQL provides data only for display by the Performance Monitor and cannot modify the counter properties. Performance Monitor controls the following:
•
The display of data is in three formats: line graph (default), histogram, and as a text report.
•
The display fields are labeled Last, Average, Minimum, and Maximum.
•
The scaling of values for display. PSQL provides a default scale for each counter. Performance Monitor allows you to change the scaling of individual counters. See To Change a Counter Scale.
The counter values reflect all calls into the database engine regardless of their source. That is, the MicroKernel Engine, Relational Engine, native Btrieve applications, utilities, and so forth, all contribute to the counter values. Counter values are collected for all files. Counters on a per-file basis are not currently supported.
Note that the use of performance counters is an advanced feature intended primarily for application developers and other technical staff. Refer to the Microsoft documentation for details about the Windows Performance Monitor and on the use of counters in general.
Registration During Installation
By default, the PSQL installation registers the PSQL performance counters with Performance Monitor. The counters are available for use after installation completes.
Note that response to customer needs may result in additional PSQL collector sets or counters being installed that are not discussed in this chapter. If so, refer to the description of the collector set or counter provided in Windows Performance Monitor. See Add Sets or Individual Counters To Monitor.
Data Collector Sets
A data collector set organizes multiple counters into a single component that can be used to review or log performance. PSQL provides the following data collector sets.
•
PSQL MicroKernel Btrieve Operations
•
PSQL MicroKernel Cache
•
PSQL MicroKernel I/O
•
PSQL MicroKernel Locks and Waits
•
PSQL MicroKernel Transactions
PSQL MicroKernel Btrieve Operations
These counters are useful for characterizing the behavior of client applications in terms of the Btrieve API. The counters report the types of operations being processed by the database engine at a given point in time.
See also Btrieve API Operations in Btrieve API Guide.
 
Table 49
Counters for MicroKernel Btrieve Operations  
Counter
Description
Typical Use
Btrieve Close Operations per Second
The number of Btrieve Close operations per Second
To provide insight into the behavior of client applications. As a first step in troubleshooting issues, you may find it helpful to analyze your application behavior in terms of Btrieve operations.
Btrieve Get/Step Operations per Second
The number of Btrieve Get and Step operations per second.
See Btrieve API Operations in Btrieve API Guide for the various Get and Step operations.
Btrieve Open Operations per Second
The number of Btrieve Open operations per Second
Btrieve Records Deleted per Second
The number of Btrieve records deleted per second
Btrieve Records Inserted per Second
The number of Btrieve records inserted per second
Btrieve Records Updated per Second
The number of Btrieve records updated per second
Change Operations per Second
The number of Btrieve operations that modify the data files per second
Operations per Second
The number of Btrieve operations executed per second
Page Server Requests Per Second
The number of page server requests received per second
PSQL MicroKernel Cache
The database engine uses a two-level memory cache system to increase performance of data operations. The two caches are called Level 1 (L1) Cache and Level 2 (L2) Cache.
The more frequently the engine must read a page from disk to complete a user request, the lower the performance. These counters can be used collectively to view how successfully the database engine avoids disk reads and to determine if any changes need to be made to the cache size settings.
The best performance occurs when all data is stored in the L1 Cache. Because of the sizes of data files, however, the L1 cache cannot always hold all of the data pages, so the database engine also uses a second level cache. Pages in the L2 Cache are stored in a compressed format, allowing for more pages to fit in memory. Consequently, it is lower performing that the L1 Cache, but higher performing than the database engine reading the pages from disk.
See also To calculate the ideal size of the database memory cache.
 
Table 50
Counters for MicroKernel Cache  
Counter
Description
Typical Use
Level 1 Cache Dirty Percentage
The percentage of the Level 1 Cache in use that contains dirty pages
To help determine if heavily accessed pages are continuously being forced out of the cache, which may adversely affect performance.
Dirty pages, ones with changes that have not been written to disk, may only reside in the L1 Cache. Under heavy write loads, the L1 Cache may predominately contain dirty pages. This forces pages out of the L1 Cache and into the L2 Cache, if configured, or out of the L1 Cache entirely.
The database engine writes dirty pages to disk at scheduled intervals or when the L1 Cache gets close to full. Frequently writing pages to disk may also adversely affect performance.
It may benefit performance to adjust the L1 Cache size so that the percentage of dirty pages is not always high. See also Cache Allocation Size.
Level 1 Cache Hits per Second
The number of Level 1 Cache hits per second
To help determine how successfully the database engine finds requested pages in L1 Cache. A higher rate of hits-to-misses indicates that the engine is finding pages in L1 Cache rather than needing to access the L2 Cache or physical storage.
Level 1 Cache Hit Ratio
The percentage of cache hits to total cache access for the L1 cache
The percentage displayed applies to the life of the database engine since the MicroKernel was last started.
Level 1 Cache Misses per Second
The number of Level 1 Cache misses per second
Level 1 Cache Usage
The percentage of Level 1 Cache currently in use
To aid in adjusting the size of the L1 Cache to fit your application(s). For example, applications that use small or predominately read-only data files may not fill up the L1 Cache as configured by default. The unused memory is not available to the operating system or to other applications.
You can change the L1 Cache size to release the memory back to the operating system. Conversely, if you want to have an entire database in memory, you can monitor this value to know when the setting is as desired.
Level 2 Cache Hits per Second
The number of Level 2 Cache hits per second
To help determine how successfully the database engine finds requested pages in L2 Cache. A higher rate of hits-to-misses indicates that the engine is finding pages in L2 Cache rather than needing to access physical storage.
Level 2 Cache Hit Ratio
The percentage of cache hits to total cache access for the L2 cache
The percentage displayed applies to the life of the database engine since the MicroKernel was last started.
Level 2 Cache Misses per Second
The number of Level 2 Cache misses per second
Level 2 Cache Raw Size
The current size of the Level 2 Cache in bytes
To help determine the size of the optional L2 Cache.
The L2 Cache is one component of the setting for Max MicroKernel Memory Usage. That setting specifies the maximum proportion of total physical memory that the database engine is allowed to consume, which includes L1 Cache, L2 Cache, and all miscellaneous memory usage by the database engine.
If the setting for Max MicroKernel Memory Usage is non-zero, the L2 Cache sizes itself to stay within the memory limit of the setting. The L2 Cache monitors memory consumption of the system and resizes itself as needed. The memory used by the L2 Cache may also be swapped out by the operating system.
Level 2 Cache Raw Usage
The amount of the Level 2 Cache currently in use in bytes
Level 2 Cache Size Relative to Memory
Level 2 Cache size presented as a percentage of total system memory
To show what percentage of the total system memory the L2 Cache is using.
Level 2 Cache Usage
The percentage of Level 2 Cache currently in use
To show what percentage of the Level 2 Cache is currently being used.
PSQL MicroKernel I/O
The counters in this set are useful for understanding the interactions of the database engine and data read and written to physical storage. The pages reported by the counters are data file pages. These counters do not report data for pages in files used for archival logging or for transaction logging.
See also Pages in PSQL Programmer's Guide.
Table 51
Counters for MicroKernel Input/Output 
Counter
Description
Typical Use
Pages Read per Second
The number of pages read from disk per second
To determine the interaction of the database engine and data read and written to physical storage.
Pages Written per Second
The number of pages written to disk per second
PSQL MicroKernel Locks and Waits
Client requests may be delayed by waiting for a resource to become available. These counters give insight into the types of database resources on which a client request may have to wait until the resource is available. As such, these counters may provide insight into the behavior of the database engine when multiple clients access it. A value close to or equal to the number of clients may indicate collisions for the same resources. Any corrective actions that can be done to alleviate these collisions may improve responsiveness.
The counters Waits on Page Buffers and Waits on Page Reads are waits on a global resource. All of the other counters in this grouping apply to multiple clients, but each client may be waiting on resources that differ from client to client.
See also Data Integrity and Supporting Multiple Clients, both in PSQL Programmer's Guide.
 
Table 52
Counters for MicroKernel Locks and Waits  
Counter
Description
Typical Use
Client Record Locks
The number of records explicitly locked by clients
To provide insight into the work load of client applications.
Waits on Active Reader Lock
The number of clients waiting on the Active Reader Lock. Multiple clients may hold the Active Reader Lock at the same time; however, the Active Reader Lock and the Active Writer Lock are exclusive. Consequently, a single client that holds the Active Reader Lock prevents any client from obtaining the Active Writer Lock. A single client that holds the Active Writer Lock prevents multiple clients from obtaining the Active Reader Lock. Each file has its own reader (and writer) lock.
See also Waits on Active Writer Lock counter.
Waits on Active Writer Lock
The number of clients waiting on the Active Writer Lock. Only one client may hold the Active Writer Lock for a file at a time. Each file has its own writer (and reader) lock.
See also Waits on Active Reader Lock counter.
Waits on File Locks
The number of clients currently waiting on a file lock
Waits on Page Buffers
The number of clients waiting on a page buffer to become available. If a page is not available to service a request, the request blocks until the MicroKernel is able to make a page available.
To indicate whether or not the database engine has a page buffer available in the cache. Use this value along with the memory cache counters to decide if the caches are sized appropriately for the work load. Increasing the cache size will increase the total number of available pages, which can reduce the waits on page buffers.
Three things may cause this value to spike when pages are not in cache:
•
A data file was recently opened.
•
First time or infrequent access of a data page.
•
The caches may be too small to contain all of the pages frequently accessed and modified.
The spike for the first two items cannot be avoided because of accessing a file for the first time. The third item can be avoided by using a larger cache. If the caches are full and the cache misses are high, it is possible that the caches may be too small to contain all the pages frequently accessed and modified.
See also Counters for MicroKernel Cache.
Waits on Page Locks
The number of clients currently waiting on page locks
To provide insight into the work load of client applications.
Waits on Page Reads
The number of clients waiting to read a page from disk. If a client is already in the process of reading the page, other clients must wait for the in-progress read to complete.
To help determine the number of clients trying to read the same page of the same file at the same time.
Waits on Record Locks
The number of clients currently waiting on record locks
To provide insight into the work load of client applications.
PSQL MicroKernel Transactions
These counters are useful for understanding the behavior of client applications in terms of transactions. For example, a few long-lasting transactions that involve many changes cause a different behavior than many short-lived transactions.
See also Begin Transaction (19 or 1019), End Transaction (20), and Abort Transaction (21) in Btrieve API Guide, and MicroKernel Engine Fundamentals in PSQL Programmer's Guide.
 
Table 53
Counters for MicroKernel Transactions  
Counter
Description
Typical Use
System Transactions in Progress
The number of system transactions in progress. A system transaction is a special type of transaction that prepares data file changes then persists the changes to the file.
To help determine if system transactions are occurring too frequently or not often enough.
The database engine writes changes to the data files during a system transaction. The frequency in which a system transaction occurs is determined by two server configuration parameters—Initiation Time Limit and Operation Bundle Limit—or triggered by a small amount of free space in the L1 Cache.
In general, running the system transaction too frequently or not often enough adversely affects performance. Typically, you will notice that the number of page writes per second may increase, the number of Btrieve operations that modify records may decrease, and the number of clients waiting on the Active Writer Lock may increase. It may take experimentation to determine an ideal interval for a particular work load.
Transaction Commits per Second
The number of commits executed per second
To determine the number of application transaction commits. See also End Transaction (20) in Btrieve API Guide.
Using Windows Performance Monitor
This section provides some rudimentary instructions on using Windows Performance Monitor to get started with the PSQL performance counters. Refer to the Microsoft document for complete details on using Windows Performance Monitor.
The following steps assume that the PSQL performance counters have been registered with Windows Performance Monitor by the PSQL installation.
►
Display PSQL Data Collector Sets
1
Start Windows Performance Monitor. The steps vary depending on the operating system, but generally the utility can be started from Control Panel --> Administrative Tools. You may also try the command “perfmon” in the Run window (Start --> Run).
2
In the tree on the left, click “Performance Monitor,” then click the plus sign on the right.
3
In the “Available counters” group, scroll to the PSQL data collector sets.
►
Add Sets or Individual Counters To Monitor
1
Perform one of the following:
a.
To add an entire set, click the desired set in the “Available counters” group.
b.
To add individual counters, expand the desired set and click the desired counters.
To view a description of the counter, ensure that the Show description option is selected.
2
Click Add, then OK.
►
To Change a Counter Scale
1
Right-click the desired counter, then click Properties.
2
On the “Data” tab, click the “Scale” list, then click the desired value.
You may need to adjust the Scale to display two or more counters on the same graph that have vastly different ranges. The counter value is multiplied by the Scale value before the data is graphed. For example, assume that one counter outputs values of 53 and 99, and a second counter outputs 578 and 784. You may want to set the Scale for the first counter to 10 so that its output is 530 and 990. This lets you look at the data from both counters more comparably (530, 990, 578, and 784).
3
Click OK.
Note that the scale on the display changes from its original value (“1” in this example) to the new value (“10” in this example):
4
On the “Graph” tab, set the desired values for Maximum and Minimum of the “Vertical scale.”
You may want to change the vertical scale if what is being graphed is very small (or very large) so you can easily see when the values change. For example, if the counter values are always under 20, you may want to change the vertical scale to be Maximum 20 and Minimum 0.
5
Click OK.