Paul White NZ (4/11/2010)
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.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
Hey Paul!
No, you aren't being "smart" at all! I gave a little one liner and you expounded on that. I agree 100% if you can do sequential reads spinning disks still have a lot to offer. The root of the problem is most people ether don't or can't separate IO patterns on their disks arrays. Moving to SSD's negates some of that handicap.
The other part of my short statement was to say not everyone runs Enterprise Edition of SQL Server, I should have been clearer.
Thanks again!