Sequential Read Ahead for SQL Server

Balancing CPU and I/O throughput are essential to achieve good overall performance and to maximize hardware utilization.

SQL Server includes asynchronous I/O mechanisms-sequential read ahead and random Prefetching-that is designed to Address this challenge.

To understand why asynchronous I/O is a important, consider the CPU to I/O performance gap. The memory subsystem on a modern CPU can deliver data sequentially at roughly 5 gbytes per second per socket (or for non-n UMA machines for all sockets sharing the same bus) and (depending in how do you measure it) can fetch random memory locations At roughly ten to million accesses per second. By comparison, a high end 15K SAS hard drive can read only Mbytes per second sequentially and can perform only Dom I/Os per second (IOPS). Solid State Disks (SSDS) can reduce the gap between sequential and random I/O performance by eliminating the moving parts From the equation, but a performance gap remains. In the effort to close this performance gap, it's not uncommon for servers to has a ratio of ten or more drives for every Cpu. (It is also important to consider and balance the entire I/O subsystem including the number and type of disk controllers n OT just the drives themselves but that'sThe focus of this post.)

unfortunately, a single CPU issuing Only synchronous I/Os can keep-one spindle active at a time. For a single CPU to exploit the available bandwidth and IOPs of multiple spindles effectively the server must issue Multip Le I/Os asynchronously. Thus, SQL Server includes the aforementioned read ahead and prefetching mechanisms. In this post, I'll take a look at sequential read ahead.

When SQL Server performs a sequential scan of a large table, the storage engine initiates the read ahead mechanism to Ensu Re that pages is in memory and ready to scan before they is needed by the query processor. The read ahead mechanism tries to stay pages ahead of the scan.  SQL Server tries to combine-contiguous pages (Kbytes) into a single scatter (asynchronous) I/O.  So, in a best case scenario, it can read ahead pages in just 8 I/Os. However, if the pages in the table is not contiguous (e.g., due to fragmentation), SQL Server cannot combine the I/Os and Must issue one I/O per page (8 Kbytes).

We can see the read ahead mechanism in action by checking the output of SET STATISTICS IO on. For example, I-ran the following query on a 1GB scale factor tpc-h database. The LINEITEM table has roughly 6 million rows.

SET STATISTICS IO on

SELECT COUNT (*) from LINEITEM

Table ' LINEITEM '. Scan count 3, logical reads 22328, physical reads 3, Read-ahead reads 20331, LOB logical reads 0, LOB physical reads 0, lo b read-ahead reads 0.

Repeating the query a second time shows that the table was now cached in the buffer pool:

SELECT COUNT (*) from LINEITEM

Table ' LINEITEM '. Scan count 3, logical reads 22328, physical reads 0, Read-ahead reads 0, LOB logical reads 0, LOB physical reads 0, LOB re Ad-ahead reads 0.

For sequential I/O performance, it is important to distinguish between allocation ordered and index ordered scans.

an allocation ordered scan tries to Read pages in the order in which they is physically stored on disk while an index ordered scan reads pages according to T He order in which the data on those index pages is sorted. (Note that in many cases there is multiple levels of indirection such as RAID devices or SANS between the logical volumes That SQL Server sees and the physical disks. Thus, even an allocation ordered scan could in fact is not truly optimally ordered.)

Although SQL Server tries to sort and read pages in allocation order even for a index ordered scan, an allocation ordered Scan is generally going to be faster since pages was read in the order that they was written on disk with the minimal num ber of seeks. Heaps has no inherent order and, thus, is always scanned in allocation order. Indexes is scanned in allocation order only if the isolation level is read UNCOMMITTED (or the NOLOCK hint is used) and O nly if the query process does not request an ordered scan. Defragmenting indexes can help to ensure the index ordered scans perform on par with allocation ordered scans.

Correction:

First-"The memory subsystem on a modern CPU can deliver data sequentially at roughly 5 gbytes per second per core"

There is NUMA and Non-numa hardware systems. Non-numa share FSB for exclusive access to RAM, so it becomes 5 gbytes perl all CPUs on board. For NUMA systesms-all cores in the same NUMA node share that 5Gbytes. Thus its not per core.

Second is "Solid state Disks (SSDS) can reduce the gap between sequential and random I/O performance by eliminating the MO Ving Tsun parts from the equation, but a performance gap remains. " Do you mean performance = throughput?

Thank you, Serge

The non-leaf nodes of the b-tree (specifically those nodes one level above the leaf nodes) has pointers to and can is use  D to prefetch multiple pages at a time. Of course, this optimization does work for heaps which does not have non-leaf nodes.