A well-tuned Sybase IQ insert operation exhibits certain characteristics. You can observe these characteristics from the Windows Task Manager and Windows Performance Monitor.
Insert operations are generally CPU-bound. All CPUs within the system should be running at close to 100%, with 95% or higher of the CPU being executed in user mode. You can see this easily by clicking on the Performance tab of the Windows Task Manager with the View-Show Kernel Times option set.
Physical memory should not be overallocated and in particular, the virtual address space for the Sybase IQ process should be less than the physical memory (RAM) for the machine. On machines with large amounts of physical memory, that is, 512MB to 2GB, this will not be a problem. On machines with small amounts of memory, that is, 256MB or less, see the additional guidelines in the following section.
Hard page faults (I/O to the volume containing pagefile.sys) should be low and ideally close to 0 (zero).
I/O operations to the IQ Store should be steady and within the I/O capacity of the disk subsystem.
Sybase IQ uses the Windows CreateFile option (for both creating and opening a file) that specifies a file is to be read for sequential access. This option is used on the files specified in the LOAD TABLE command. As a result, load performance is improved through read ahead and reduced NTFS Cache memory utilization.
Load performance can be further improved, sometimes significantly, by setting the size of the main and temporary Sybase IQ buffer caches considerably smaller than the calculated recommended values in “Sybase IQ main and temp buffer caches”. The reasons for this performance improvement are described in “Using the NTFS cache”. You can set the main and temporary Sybase IQ buffer caches as much as 50% smaller than the calculated recommended values.
