How can I disable async IO or isolate one session that slows or freezes other sessions?

Question

Each day I have a session that produces Avg.Disk Queue Length > 800 (5min interval).

I have found that just before this moment Sql Server optimizer have made an error: it decides to use a not-suitable plan with clustered index scan which produces about 100Mb-1GB of IO operations. Let's say that I found the reason for the queue, but it is not the whole story.

IMHO the huge IO should not necessarily freeze other users for 15 minutes. While I don't know who is responsible for creating the excessive asynchronous IO: Sql Server, the Windows filesystem, or maybe the physical IO controller, but the asynchronous IO that is produced causes a hugely abnormal queue, and doesn't leave any chances for other sessions to read from the disk.

Is there any way to control asynchronous IO? I think if I will be able to lower the queue length (btw "more real" current queue length counter shows 400 operations in the queue) those problems with query plans will not freeze other sessions.

However situation is more complex:

• Those sql queries are generated from NAV 4, so I mostly can't change anything on t-sql level.
• The server (Windows 2003 Data Center) is 32-bit and sql server (data center edition) use AWE to use additional 8 GB for buffer; Therefore there is some possibility that AWE manager flash generates this huge queue.
• There are maximum 50 sessions on the sql server. So I'm ready to see 50 queue length but not 800! BTW normal length is 0.3-4, but it's really normal since I have suitable RAID there.
• This mad session, that generates query with 1GB IO, also generates about 100000 "quick" queries in 40 sec on the same table just before sql optimizer have made an error. So sql optimizer really could be somehow misled by changes in the index selectiveness during previous operations in the same session.

UPDATE: The table is not interesting at all, excepting that it is too big (1 GB with indexes) for NAV.

An example of some of the queries:

declare @p1 int  set @p1=180150129  declare @p3 int  set @p3=16  declare @p4 int  set @p4=1  declare @p5 int  set @p5=0  
exec **sp_cursoropen** @p1 output,N'SELECT  * FROM "DBNAME"."dbo"."COMPANYNAME$Cust_ Ledger Entry" WITH (READUNCOMMITTED)  WHERE 
(("Customer No_"=@P1)) AND 
(("Open"=@P2)) AND  
"Customer No_"=@P3 AND 
"Open"=@P4 AND "Positive"=@P5 
AND "Due Date"=@P6 
AND "Currency Code"=@P7 
AND **"Entry No_"<@P8** 
ORDER BY "Customer No_" DESC,"Open" DESC,"Positive" DESC,"Due Date" DESC,"Currency Code" DESC,"Entry No_" DESC OPTION (FAST 5)',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 tinyint,@P3 varchar(20),@P4 tinyint,@P5 tinyint,@P6 datetime,@P7 varchar(10),@P8 int','PARAMETERVALUE',1,'PARAMETERVALUE',1,1,'vas 25 2011 12:00:00:000AM','',3604177  select @p1, @p3, @p4, @p5

And about 100000 such operations. The key for understanding is "Entry No_"<@P8 it means - get next, get next, get next. That the way how NAV works there: something like iterations on the table.

As you can see for the SQL Optimizer is a very big temptation to switch from not covered index (Customer No_, Due Date, Currency Code.. ) to the clustered index/primary key (Entry No_) . And at one moment it decides to use clustered index, then server have the explosion of IO operations. After that SQL Optimizer decide to return to the not coverred but optimal index... But black work is done :) we have queue of 800 for 10-15 minutes.

I need to note that I'm not interested there in solving this Optimizer problem (ok I'm interested, but not there). I'm interested in to achieve that this session will not slow all my other sessions to the point of unusability. So I'm interested to know how to configure Assinchornous IO or at least to know is this possible?

IMHO, some subsystem (IO controller, windows, sql server) makes an error when decide to create such huge queue. I think this is an error in the sql server configuration or in the IO controller, since I have never heard before that "simple" clustered index scan even on big table could generate huge queue. Or you have seem this behavior already?

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.

Answer 1

A single user can generate more than one queued IO. There is no correlation between the number of users and the number of IOs which are in queue. With the select * in the query, and without being able to tune the query you'll have to resort to a plan guide.

There is no way to control the IO that the SQL Server sends to the IO subsystem other than index tuning. Would it make more sense to move the clustered index so a different column or columns?

A Clustered index scan will generate a lot of IO if the table isn't already in memory, as it needs to read the data from the disk into memory to scan through it, especially if the table is large.

Answer 2

This looks to me as a good place where to read and test this MSDN white paper - Forcing Query Plans. See if it helps to force it using the correct plan. There is an example of how to solve a problem in the Appendix of this white paper.