Sequential Read Ahead

re: Sequential Read Ahead

logicalmind — Fri, 26 Sep 2008 00:56:36 GMT

I hope the question I emailed you got you interested in blogging on this subject. Thanks for the reply btw.

You said, "The read ahead mechanism tries to stay 500 pages ahead of the scan." Is SQL Server doing 500 pagesized asynchronous I/O's in this case? A single (500*pagesize) I/O? Or some other combination? It would seem that this should vary depending on whether you are doing OLTP (better response time) or OLAP (better throughput).

re: Sequential Read Ahead

craigfr — Fri, 26 Sep 2008 20:57:26 GMT

SQL Server tries to combine up to 32 contiguous pages (256 Kbytes) into a single (asynchronous) I/O. So, in a best case scenario, it can read ahead 500 pages in just 16 I/Os. However, if the pages in the table are not contiguous (e.g., due to fragmentation), SQL Server cannot combine the I/Os and must issue one I/O per page (8 Kbytes).

Random Prefetching

Craig Freedman's SQL Server Blog — Wed, 08 Oct 2008 00:50:42 GMT

In my last post , I explained the importance of asynchronous I/O and described how SQL Server uses sequential

re: Sequential Read Ahead

SergeyL — Thu, 30 Oct 2008 00:27:09 GMT

Craig,

Few moments here I think need further elaboration.

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

There are 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 on 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 moving parts from the equation, but a performance gap remains." Do you mean performance = throughput here?

Thank you, Serge

re: Sequential Read Ahead

craigfr — Fri, 31 Oct 2008 01:22:25 GMT

First, I should have written 5 Gbytes per second per SOCKET. And, you are correct. With a shared bus, this bandwidth does not scale. I will clarify the post. Thanks.

Second, yes, I'm referring to the gap between memory and disk/SSD throughput.

Optimizing I/O Performance by Sorting – Part 1

Craig Freedman's SQL Server Blog — Wed, 25 Feb 2009 20:32:34 GMT

In this post from last year, I discussed how random I/Os are slower than sequential I/Os (particularly

Discrepancy with BOL

OLAP — Tue, 25 Aug 2009 15:48:55 GMT

Your comment: "SQL Server tries to combine up to 32 contiguous pages (256 Kbytes) into a single (asynchronous) I/O."

BOL ('Reading Pages'): "The read-ahead mechanism allows the Database Engine to read up to 64 contiguous pages (512KB) from one file."

Is it 32 or 64 contiguous pages?

Precise definitions for Contiguous and Fragmentation

OLAP — Tue, 25 Aug 2009 16:00:59 GMT

Your comment: "If the pages in the table are not contiguous (e.g., due to fragmentation), SQL Server cannot combine the I/Os and must issue one I/O per page (8 Kbytes)."

'Contiguous' loses its meaning, however, in a world based on RAID striping and proportional fill. Precisely what is meant by contiguous and fragmented? Are we only talking about page splits?

re: Sequential Read Ahead

craigfr — Mon, 31 Aug 2009 19:23:55 GMT

BOL is correct. SQL Server does combine up to 64 contiguous pages into a single read. However, it will combine only 32 contiguous pages into a single write. Thanks for the correction. I apologize for the error.

From the perspective of SQL Server, the terms contiguous and fragmented refer to the logical layout within a database file. SQL Server has no visibility to the physical layout.