I'm interested in to understand how SQL Server (all versions) make a decision to read pages using assync IO : 1) to use or not to use assync IO; 2) how many calls to start. My concrete problem that I can't explain 800 avg.queue length during 15 minutes. I found a session that generates huge amount of reads (1gb) but it is not enough to explain: huge scans are evil but they never generates such absurd queue's within my knowledge. I understand that there could be a more complex scheme: when SQL Server starts assync calls as many as it need (for example if it in need of 200 pages - it starts 200 assync reads), then Windows Assync IO API decides start them asynchronously or not... Thats how it works by Windows Assync IO API, but really I'm not sure does sql server use standard windows assync api or use its own. Thank you for any ideas and thank you very much for links to documentation. UPDATE: Hardware: 1) 32 bit, 9 GB in AWE buffer. 2) IO - Fibber Channel to SAS disk array; 800 is total = 500 on disk where mdf islocated, 300 on the disk where ndf is located. New information: I've found that in "normal" situation the same scan produce only 6-10 members in the queue and then there are only 3 entries in the dm_io_pending_io_requests at one moment. It seems that AWE flash is responsible for huge queue, but I still want to know is there any control/management of queue length on the system/server/sqlserver level? I start thinking that at least in the case of fiber channel/disk-array this control is just impossible: when may be it is possible to know what queue is on this logical volume, it's to costly to decide how many assync reads could be performed effectively.
There is some awesome information awavilable on SQL Server IO on MSDN. The first thing to read and understand would be [SQL Server 2000 IO Basics], followed by [SQL Server IO Basics - Chapter 2] the first article is based on SQL 2000 (obvious by the title), but is a strong base for how SQL Server deals with I/O when talking to Windows and NTFS. There is a good section explaining the async IO methods (SQL Server uses the Windows APIs as you guessed). The major thing to consider is the read-ahead ability of SQL server, especially as you are using Enterprise Edition - as that feature is beefed up for EE. Those docs should shed some light on the whole thing. If you ever get chance, I suggest you watch Bob Ward present on the SQL Engine and compelte underbelly of SQL Server - he is/was a Senior Escalation Engineer for SQL Server and dives *deep* into the engine internals and talks about stuff like async io. He co-authored/tech reviewed those whitepapers I linked to too. :
The SQLOS decides if calls will be ASYNC or not. **SYNC** calls follow the Windows API and enter the Windows queue. If the resource requested is not available the O/S puts it on a wait queue until it is. **ASYNC** calls tell Windows to initiate them straightaway. SQLOS then creates its own internal wait queues until Windows says the desired resource is available. In your question you say the table is a 600 MB Clustered Index. With an index scan the query will attempt to read through all 600 MB. How long does this query generally take? What stats are you looking at in arriving at your conclusions? Want to make sure we're all comparing the same apples :) Can you provide some PerfMon I/O metrics for us? Edit ==> **io_pending From BOL:** > Indicates whether the I/O request is > pending or has been completed by > Windows. An I/O request can still be > pending even when Windows has > completed the request, but SQL Server > has not yet performed a context switch > in which it would process the I/O > request and remove it from this list. So io_pending may not truly represent reality in all cases. Another Edit ==> A quote from **Paul Randal**: > High disk queue length = an I/O > subsystem that isn't optimal for the > load.