Wesley Brown (4/9/2010)

---

If you are using Standard Edition like I am you don't get the full benefit of SQL Servers read-ahead feature.

Paul White NZ (4/11/2010)

---

Well that is true to some extent - but SQL Server still tries to issue large I/O requests wherever it can, (for scatter/gather as well as sequential operations).

For read-ahead, each large read request might be as small as 32 pages (256KB) or as large as 128 pages (1MB) on Standard Edition. Enterprise Edition raises the limit to 1,024 pages (8MB). Read-ahead is used extensively for data and index access - and even on some RID/KEY lookups and loop join operations - look for the WITH (UN)ORDERED PREFETCH iterator tag in the query plan.

Many other operations in SQL Server generate large I/O too: log file initialization (512KB), bulk insert (multiples of 128KB), backup (1MB), restore (64KB), DBCC (varies)...

I'm not trying to be smart, or contradict you here (far from it) but large I/Os are pretty common in SQL Server, and it would have been nice to see some test results at those sizes, just to give the spinning magnets a fair go

Enjoyed the article very much thanks!

Paul

brett.hawton (4/9/2010)

---

Great article!
Just some personal observations:
1. On larger queries SQL Server read-ahead kicks in and it reads 512KB blocks at a time. In my observations disks are much better at long sequential reads like this. Yes they still dont get close to matching the performance of SSD's but their performance does improve markedly due to the fact that all modern disks use sector skewing allowing the head to move (during a sequential read) from one track to the next without incurring any significant latency (the sectors are offset such that the time it takes the head to move 1 track it finds the next sector directly under the head).

Brett Hawton
Idera Product Architect

Wesley Brown (3/9/2012)

---

...
I agree that the article could have a better title. Other than the title, was there anything else wrong with the article?
-wes