Disk Queue Length counters not meaningful in SQL 2000?

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/pr...in/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
BertThis 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
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx[/url
]
> 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
>|||Disk queue length isn't particularly interesting because SQL Server throws a
lot of IOs at the disk and then goes to do other work. For instance,
read-ahead is a good example: if the server has a reasonable idea that it
will need several pages from disk in order to complete a scan, it will issue
the IOs for those pages before it actually needs them, with the expectation
that when we actually do need the pages, the IOs will have completed and we
won't have to wait. Here's the executive summary, I guess: by design, SQL
Server loads up the disk subsystem with IO requests so that we don't have to
wait for the pages when we need them. That causes IO queue lengths to
increase since there are typically always outstanding IOs posted by the
server. In some applications this might indicate a bottleneck, but SQL
Server is smart about it and uses asynchronous IO to its benefit.
Perhaps the following article (just recently posted) might illuminate the
subject a bit:
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.[/
url]
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
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
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx[/url
]
> 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
>|||Hello,
I am also interested in disk queue lengths reported by System Monitor when r
unning SQL Server 2000.
I have tempdb on a dedicated (mirrored) disk. I am seeing average disk queue
lengths of up to 8000 for a 5 minute sampling period on this disk. This see
ms to contradict Wei's point that SQL Server 2000 throttle's disk accesses w
hilst corroborating Ryan's that it loads the system up with requests.
However, as I am seeing response times of above a ten seconds on this disk ,
I still think I have a disk bottleneck. As I already have a fast disk syste
m I believe the application needs rewriting to reduce its use of tempdb.
I would be grateful for any further comment or insight on my particular prob
lem.
Thanks,
Paul