Re: Disk Queue Length counters not meaningful in SQL 2000?

From: Wei Xiao [MSFT] (weix_at_online.microsoft.com)
Date: 01/28/05 

Date: Thu, 27 Jan 2005 20:57:55 -0800

This means SQL Server 2000 tries not to issue 200 IO per second if your IO
system can only handle 100 IO per second. So normally you would not see
average disk queue length to be more than 1. However sometimes disk queue
length can be high (more than 1) in some heavily loaded systems. If that
happens then you know you have an IO bottleneck. The % disk busy is also a
good indication and it should be used in conjunction with average disk queue
length. 

-- 
-- 
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@gmail.com> wrote in message 
news:1106861683.038716.139100@c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about.  I am in the
> process of doing some research on multiple files vs. multiple
> filgroups.  I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up.  During my research I came across
> the following  from the "SQL Server 2000 Operations Guide"
>
> "Note on Disk Queue Length
>
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
>
> You can see it here
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
>
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
>
> ok what does this mean?  it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
>
> Thanks
> Bert
>

Relevant Pages

  • Disk Queue Length counters not meaningful in SQL 2000?
    ... "Note on Disk Queue Length ... I/O problems with SQL Server 2000. ... complete before issuing another request. ...
    (microsoft.public.sqlserver.server)
  • Re: Determine Disk Block Size ?
    ... > "SQL Server limits a single read-ahead request depth to 128 pages on most ... Microsoft SQL Server Enterprise Edition raises the ... > that it may state you need multiple files to spawn multiple threads. ... We have done so with a dramatic total disk I/O ...
    (microsoft.public.sqlserver.server)
  • Re: sql server i/o bottle neck ?
    ... It's hard to explain the I/O disparity without knowing the details of your ... My first guess is that you might be committing each SQL Server insert ... An I/O intensive process will generally run at the max speed of your disk ... The system monitor shows during my job: ...
    (comp.databases.ms-sqlserver)
  • Re: Determine Disk Block Size ?
    ... Sql Server can spawn multiple threads to read a single file as well. ... We have done so with a dramatic total disk I/O throughput. ... You're throughput will be based on type of> access, sequential versus random, and the size of each request. ... > additional disk threads per request. ...
    (microsoft.public.sqlserver.server)
  • Re: Determine Disk Block Size ?
    ... "SQL Server limits a single read-ahead request depth to 128 pages on most ... The read ahead is actually dynamic in the size of each read ahead request based somewhat on how fragmented the data is. ... Most of the documentation for 2000 is wrong in that it may state you need multiple files to spawn multiple threads. ... We have done so with a dramatic total disk I/O>> throughput. ...
    (microsoft.public.sqlserver.server)