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.

  • Can you give an example of the query being run as well as table structures as many people might not be familiar with NAV? – Eric Humphrey - lotsahelp Feb 14 '11 at 4:20
  • Thank you Eric, at least I have tried to explain whole situation. – Roman Pokrovskij Feb 14 '11 at 13:22

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.

| improve this answer | |
  • Do you mean assync IO or something else? I know about assync IO. The question is how many assync operations can be generated for reading one table? 800 ? It's too much. I'm interesting there how SQL Server decide that it will generate "X" assync calls. It can't create it "blind way". Possibly I have a problem with IO controller on this server. – Roman Pokrovskij Feb 17 '11 at 21:28
  • I do not see any logic in the recommendation to move clustered index on different column. Clustered index is where it should be - on the identity column. This is not a problem of index. It could be a problem of SQL Optimizer that sometimes choose wrong plan, but also I could imagine his logic (selectiveness of ">" on identity column could be sometimes very high) – Roman Pokrovskij Feb 17 '11 at 21:39
  • Of course ">" on clustered index /identity looks strange but that how Navision works and do iterations. – Roman Pokrovskij Feb 17 '11 at 22:19
  • If the SQL Server needs to load the entire clustered index from disk into memory, and that table is 800 pages in size (or there are 800 pages which aren't in memory) then it will generate 800 IO to load the table. The IO subsystem only requests from disk what SQL OS tells it that it needs. As to the clustered index, you didn't specify what the clustered index was created on so I had to guess. For all I knew it was created on a GUID. SQL Server will send very large IO requests to the storage, causing large queues so that the IO is returned as quickly as possible. – mrdenny Feb 18 '11 at 0:22
  • If you are reading lots of data from disk, then you may want to look at more memory. How much memory is in the server currently? What's the PLE for the server? – mrdenny Feb 18 '11 at 0:23

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.

| improve this answer | |
  • I know about sp_control_plan_guide but this article is new for me. Thank you. – Roman Pokrovskij Feb 17 '11 at 22:03
  • Anyway I'm not interesting to solve the problem with plans. I'm going to understand why sql server decide to create such huge queue. What about plans the situation is even more complex: 1) I always have good plans in the cache (but sometimes Query Optimizer decide to "make an experiment", this I can't explain good). 2) I have not only one such query but about 10 with different parameter's sets, and 4 big tables where scans sometimes provoke huge queue. So I don't like an idea to fix 40 plans in cache, that are always good (see 1). – Roman Pokrovskij Feb 17 '11 at 22:04
  • Maybe you have a problem with memory and the buffer cache is emptied at some point, and all data is read from the disk directly - thus the huge IO. I had once a problem where a job was loading lots of data in memory and everything else was just down to knees, forced to take data from disk. Maybe it's your case too. You should monitor your system in the good old fashioned way: Perfmon, Profiler. You will find great information in this training session from SqlServerPedia. That, if you like ugly men talking about SQL. – Marian Feb 17 '11 at 22:14
  • Yes, this is also very possible: buffer flash. I've ordered dmv on pending assyncs already. (Unfortunately I am even can't access the server, "hoster" understands security this way). – Roman Pokrovskij Feb 17 '11 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.