Re: Disk Queue Length counters not meaningful in SQL 2000?
From: Ryan Stonecipher [MSFT] (ryanston_at_microsoft.com)
Date: 01/28/05
- Next message: Ryan Stonecipher [MSFT]: "Re: Underutilized Hardware on a SQL Server"
- Previous message: Wei Xiao [MSFT]: "Re: Disk Queue Length counters not meaningful in SQL 2000?"
- In reply to: bertcord_at_gmail.com: "Disk Queue Length counters not meaningful in SQL 2000?"
- Next in thread: bert: "Re: Disk Queue Length counters not meaningful in SQL 2000?"
- Reply: bert: "Re: Disk Queue Length counters not meaningful in SQL 2000?"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 27 Jan 2005 20:56:52 -0800
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:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.
-- 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 > 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 >
- Next message: Ryan Stonecipher [MSFT]: "Re: Underutilized Hardware on a SQL Server"
- Previous message: Wei Xiao [MSFT]: "Re: Disk Queue Length counters not meaningful in SQL 2000?"
- In reply to: bertcord_at_gmail.com: "Disk Queue Length counters not meaningful in SQL 2000?"
- Next in thread: bert: "Re: Disk Queue Length counters not meaningful in SQL 2000?"
- Reply: bert: "Re: Disk Queue Length counters not meaningful in SQL 2000?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
