Cost of a scan on allpages-locked table

The I/O cost of a table scan on an allpages-locked table using 2K I/O is one physical I/O and one logical I/O for each page in the table:

Table scan cost = Number of pages * 18 + Number of pages * 2

If the table uses a cache with large I/O, the number of physical I/Os is estimated by dividing the number of pages by the I/O size and using a factor that is based on the data page cluster ratio to estimate the number of large I/Os that need to be performed. Since large I/O cannot be performed on any data pages on the first extent in the allocation unit, each of those pages must be read with 2K I/O.

The logical I/O cost is one logical I/O for each page in the table. The formula is:

Table scan cost = (pages /pages per IO) * Clustering adjustment* 18+ Number of pages * 2

See “How cluster ratios affect large I/O estimates” for more information on cluster ratios.

NoteAdaptive Server does not track the number of pages in the first extent of an allocation unit for an allpages-locked table, so the optimizer does not include this slight additional I/O in its estimates.