Use the Windows NT Performance Monitor with Mimer SQL
The Windows NT Performance Monitor is a system administration utility for Windows NT 3.51, Windows NT 4.0 and Windows 2000 platforms.
Although the Performance Monitor is not primarily a Mimer SQL tool, you can use it to collect statistics and information about the performance, throughput and behavior of your Mimer SQL system. You can also use it to monitor operating system metrics like CPU and disk usage.
In this article we assume that you are familiar with the Performance Monitor in general and we will only cover Mimer SQL specific aspects.
Note that the screenshots in this article are taken on a Windows NT 4.0 system.
As this article is quite long, here are a few of its main points:
Using the Performance Monitor
The Performance Monitor is located in the Mimer SQL program group.
To start the Performance Monitor:
- Click Start and navigate to your Mimer SQL program group.
- Select Performance Monitor, the monitor is displayed.
To add a Mimer SQL database server:
- In the System Monitor’s details pane, right-click and select Add to Chart.
About Database Servers
Local Database Servers
When you want to display information about local Mimer SQL database servers, you open the Add to Chart dialog box, for example:
In this example, all of the Mimer SQL counters are displayed in the Counter list. All available Mimer SQL database servers are displayed in the Instance list on the right. As you can see, on the computer 'BAMSE' we have as many as seven Mimer SQL database servers running.
To get on-line information about a counter, select it in the Counter list and then click Explain.
Remote Database Servers
Whenever you want to view performance data for a remote database server, you simply enter the computer name in the Computer field.
If the computer is a Windows NT or 2000 server, the Performance Monitor will try to connect and view the database servers specified in Mimer SQL Administrator on that server.
The Performance Monitor supports remote Windows NT and 2000 servers transparently. You won’t need to set any special configurations.
Foreign Database Servers
If the remote server is NOT running on Windows NT or 2000, things become a little more complicated. (Remember Mimer SQL also runs on most UNIX systems, including Linux, and OpenVMS.)
Mimer SQL Performance Monitor extensions support connecting to any Mimer SQL database server to collect performance data, but this functionality is not very well supported by the Performance Monitor itself.
In the Add to Chart dialog box below, we tried to view Mimer SQL database servers on the remote node GERE, which happens to be running OpenVMS 7.2. As you can see, the Performance Monitor was able to see one database server on that machine: NIAGARA.
Remote instances are found by looking at the configured remote database servers on your computer. So, in order to view a remote database server’s performance data, you have to define it in the Mimer SQL Administrator first.
Currently, the Mimer SQL Performance Monitor is not able to find remote servers without this configuration information.
Common Questions and Problems
The Performance Monitor is slow when displaying foreign instances. Why?
The Performance Monitor is designed to let software packages register their Performance Monitor extensions. Whenever a user enters a node name in the Performance Monitor, each of the extensions will try to find the computer in question, and specifically connect to Windows NT services on that computer.
If the computer is a foreign computer, for example, not Windows NT or 2000, these services will not be available and it will take some time for all the extensions to time-out and give up. There is a pretty good chance that the Mimer SQL Performance Monitor extension is the only one that will actually respond to foreign requests.
Another factor that affects performance is that the Mimer SQL Performance Monitor extension needs to connect to the remote servers. Under a heavy load that can take a second, and if you have defined several servers on the same remote node, a connection has to be made to each of them.
I can’t see any foreign database instances! Why?
The algorithm that matches the entered computer name with actual remote servers is not very sophisticated. All it does is a simple string comparison ignoring case.
If you have entered the node name using an IP-number in the Mimer SQL Administrator you have to use that same IP-number in the Performance Monitor.
If you entered the node name GERE in the Mimer SQL Administrator you have to enter GERE (or gere) in the Performance Monitor. The matching algorithm is unable to conclude that GERE.MIMER.SE is identical to GERE in this context.
Which Mimer SQL database servers support the Performance Monitor?
All database servers since 7.2 support the Performance Monitor, but newer servers are able to return more information than older ones.
You can use the Performance Monitor in any client version to view any database server, but you will always need the latest client to view the counter data added in the latest server.
Why can’t I log foreign counter data?
The Performance Monitor does not support logging foreign data.
Why can’t I view any foreign counter data on Windows 2000?
The Performance Monitor implementation has been changed in Windows 2000 and the new implementation doesn’t support foreign computers. You have to use the Windows NT 4.0 Performance Monitor. See Microsoft Knowledge Base Q243176 for more information.
How does the Mimer SQL Performance Monitor retrieve counter data?
The Mimer SQL Performance Monitor uses a special connection mode which doesn’t require a user name or a password, but will only give access to performance measurements. Each server allows only a few Performance Monitors to connect.
It’s not working! What could the problem be?
The Mimer SQL Performance Monitor logs error conditions to the Windows NT Event Log. Try examining the Event Log using the Event Viewer.
However, problems arising during the connect phase aren’t logged because that would easily flood the event log. If the Mimer SQL Performance Monitor extension is unable to connect to a server, it will try again a little later. In the meantime, the graph will show the database in question as zeroes.
If the event log is clear, try to connect to the server normally, preferably using a tool that uses Embedded SQL or Mimer BSQL. The Mimer SQL Performance Monitor uses the Embedded SQL interface to connect to the server. If that is working, the Performance Monitor should work as well.
The Counters and What They Mean
Displays the number of active users in the database server.
Pages written to disk
This counter shows the number of pages updated to disk per second.
2k page requests/sec
2k page faults/sec
% 2k page faults
2k pages swapped out/sec
16k page requests/sec
16k page faults/sec
% 16k page faults
16k pages swapped out/sec
64k page requests/sec
64k page faults/sec
% 64k page faults
64k pages swapped out/sec
Pages swapped out/sec
These counters indicate the usage load on the 2k, 16k and 64k buffer pool areas. The number of page requests for each area indicates the total number of requests. The page fault counters indicate how often the server needed to bring a page from disk into the buffer pool, and the % page fault counters indicate the number of page faults in relation to the total number of page requests.
The last two counters is a sum of all page requests and all page faults for all areas combined.
Each bufferpool area is populated by table data depending on the row size. 2k areas hold table data whose row data is up to about 500 bytes in size. The 16k area holds table data whose row data is at most about 4k in size, and the 64k pages hold the remaining tables.
If there many page faults in either area, you might want to increase the size of that particular buffer pool area in order to reduce the number of page faults.
This counter indicates the total number of dynamic extensions made to databanks since the server started. Databanks will be faster if they are allocated a continuous area on disk, rather than be extended in short pieces every now and then. The optimum approach is therefore to create the databanks after the disk has been defragmented, and to allocate a reasonably large area for the databank. See the CREATE DATABANK statement in the Mimer SQL Reference Manual for information on how to specify the initial size of the databank.
If the databank becomes full, the Mimer SQL database server will extend the databank to hold additional data. These dynamic extends vary in size depending in the situation. In most cases the databanks are extended by 64 KB.
If you can see that databanks are extended dynamically a lot, it means that rows are inserted into the database but never removed. You will gain a slight performance improvement if you extend the databank manually in advance, because then the server won’t have to extend the databanks while inserting data, and there is a bigger chance that the databank will reside continuously on disk afterwards.
Transaction commits (read/write)/sec
Transaction commits (readonly)/sec
These are the number of successful read/write, read-only and the total number of commits. The latter is a sum of the first two.
Read/write transactions impose a performance penalty on your system. Many transactions don’t have be read/write and you as an application programmer can reduce server workload by specifically requesting read-only transactions.
You can do this either using the statement SET SESSION READONLY where the changes are in effect for the remainder of the session or SET TRANSACTION READONLY where the change is only in effect for the duration of the next transaction.
This counter indicates the number of transaction checks per second. A high proportion of transaction checks in relation to the total number of transactions may indicate a poorly designed application program with long transactions that are more likely to give rise to transaction conflicts.
This counter indicates the frequency of transactions aborted by the optimistic concurrency algorithm. For more information on optimistic concurrency, read our article on optimistic concurrency control.
This counter indicates the number of restart operations stored in TRANSDB. A restart operation is something to be performed when a shadow comes back online. In a normal situation this number should be zero, but if a shadow for some reason becomes unavailable, this figure starts counting. For more information on shadowing, see the System Management Handbook found in the Mimer SQL Documentation Set.
Shows the number of databanks that have been determined not to be properly closed since the last database server startup. This number won’t count until the improper databank is accessed. In most situations when the server wasn’t shutdown properly at least SYSDB8, SQLDB, TRANSDB and LOGDB will be checked at server startup.
Shows the number of databanks that have been determined to have an unrecoverable failure. This is a situation so fatal that the database server decides not to try to fix it. For more information on backing-up and recovering data, see Mimer SQL Online Backup and How to Restore Data.
Pending background threads requests
Background threads in use
These two counters describe the situation of the background threads. The first counter shows how many background thread requests are piled up for processing. The second counter shows how any background threads are working.
If a situation arises where pending requests start piling up, all background threads are working and you are low on CPU usage, it might be worthwhile increasing the number of background threads. For more information, see the System Management Handbook found in the Mimer SQL Documentation Set.
Request threads in use
Indicates the number of request threads in use. If all request threads are busy, it might be worthwhile increasing the number of request threads.
Databank files open
These counters show how many databanks and tables are currently open in the system.
Shows the number of currently active transactions.
These counters show how many SQL statements and PSM programs that are being compiled.
A large number of SQL compilations will reduce server throughput. Application design influences this metric greatly. Some applications generate a lot of SQL statements on the fly to compile them. This is expensive and is particularly true for the modern scripting, such as Perl and PHP languages, where it is easy to concatenate strings, but it applies to any language.
In short, it is always better to compile statements in advance using parameter markers rather than inserting the parameter data as strings into the statement. Using precompiled statements will reduce this counter and improve server performance.
SQL share count
PSM share count
These counters display the number of SQL statements and PSM programs currently compiled and stored in the server. A statement can be shared by several applications and in these cases each statement is only counted once.
The share counters show how many of the statements are shared. A large proportion of shared statements indicates that your applications are good at reusing statements.
Data definition statements are not counted.
The number of statements active in the system. This is different from the number of SQL statements active listed above. Each reference to a shared SQL statement is counted and data definition statements are counted as well.
SQL pool memoryallocated
SQL pool memory used
%SQL pool memory used
These counters describe the memory consumption of the server. This includes network buffers, compiler work memory, cached statements and query plans and so forth.
SQL pool allocations/sec
SQL pool deallocations/sec
These counters indicate the frequency of server memory allocations and deallocations. The number of allocations and deallocations will affect the scalability of an application. An application which requires few allocations or deallocations will scale better.
One particular situation which will generate a lot of allocations is when the application compiles statements over and over again. The compiler will need working memory, and more space is needed to store the query plan and query description. Minimizing compilations will also minimize SQL pool allocations.
The number of cleanup operations per second. A cleanup occurs every time an application terminates without closing the database connection.
The number of cancelled operations received per second. A cancel operation is when an application decides to cancel a network request.
The total number of requests received by the server per second.
A new communication buffer may have to be allocated for very large requests to the database handler. This always results in several calls to the communication routines in use for the connection.
A situation where this can occur is when the client requests a large array of result data. It might be the right thing to do but it has a slight penalty at the server.
This is the number of calls made to the operating system communication routines. This may be larger than the number of requests due to communication protocol algorithms.
This is the number of failed login attempts since server startup. A large number may indicate a lot of security violation attempts.
A simple Boolean value showing if logins are enabled or not.
The number of times the database server had to wake the application after processing a request.
Commit set size
This counter shows how many 16k pages are being used for the commit set size. That is, committed transactions not yet flushed to shadow servers. Server restart times are roughly proportional to the size of the commit set.
If the shadow servers are busy and unable to receive requests, this figure will grow. Configuring more shadow servers might possibly correct the problem.
If applications are committing very large transactions, this figure will also become large.
Last updated: 2002-08-21