Physical I/O Tuning

First Scan - ColScanReadAheadBlocks

When any query is executed there will always be at least one Batch Primitive Step. An extent for at least one column must be read to achieve this. Additional column blocks may need to be read depending on the selectivity of the the filter. Once the first column is read, other column values are projected using the row identifier from the results of the first column read.

The ColScanReadAheadBlocks parameter configures the number of blocks to be read at one time and the system default value of 512 (blocks) should work well for a variety of configurations and workloads. The number of cores should divide equally into this number and it should also be a multiple of whatever the underlying storage supports optimally for bulk block reads.

Additional Column Reads - PrefetchThreshold

In reading data blocks from storage, there is a balance between bulk reading when a lot of data is going to be needed and reading just the blocks you need. The PM server will make a choice based on statistics gathered over past reads.

The PrefetchThreshold parameter configures a percentage threshold of previous block reads that were needed to satisfy the query. If the actual percentage is higher than this threshold, bulk multi block reads will continue to be used. If lower then reads will switch to using individual block lookups. The default value is 5 (percent). A key point to note is that the calculation is based on blocks (many row columns) not rows.