Disk Queue Length Performance Counter Useless?

From http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx :


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.


Thoughts?

Interesting article. I think I'll reserve opinion here - but from my understanding - if this is purely looked at from the issue with regards to disk length by the SQL process, then it seems very valid that its no longer such an issue - but there are other operations from the OS that can be affected - including paging - that the SQL Server still queues up behind - so disk queue length is still a valid parameter to look for.

Too many companies are sold on the ability to purchase really big drives instead of numbers of arms. Too many hardware vendors then claim that disk performance is "invalid" - even though its obvious where the bottlenecks are during production !

Instead of purchasing a couple of really big 700Gb drives - spread the cost over smaller drives and it will give you lots of arms, lots of redundancy - and in my book, lots of performance.

[ QUOTE ]
Instead of purchasing a couple of really big 700Gb drives - spread the cost over smaller drives and it will give you lots of arms, lots of redundancy - and in my book, lots of performance.

[/ QUOTE ]

Have any idea how much I have to fight with IBM to get 73G drives? Lawdy......

IBM just announced 282 GB 15K drives. You can't use them for a load source, though, and you'll need V5R4. But the real performance issue is still the same -- arms over data. More arms equals better performance.

[ QUOTE ]
IBM just announced 282 GB 15K drives. You can't use them for a load source, though, and you'll need V5R4. But the real performance issue is still the same -- arms over data. More arms equals better performance.

[/ QUOTE ]

Back to the question at hand:


"SQL Server monitors the throughput on these requests, and will manage the amount of I/O that the disks can effectively handle."

[ QUOTE ]

"SQL Server monitors the throughput on these requests, and will manage the amount of I/O that the disks can effectively handle."


[/ QUOTE ]
Right - so if this is true, what is the new SQL Server perfmon statistic that people should be looking at ? Surely instead of "disk queue length" we should be looking at "sql disk queue length" or something similar ?

What occurs, for example, if the disk queue length is high because of some other sort of disk activity - outside of SQL - such as paging or some other OS function ?

I still believe its valid to watch disk queue length in the statistics - but it sounds like "sql can now adapt its disk activity based on the available queue length statistics". This doesn't mean, however, that you can suddenly stick your SQL Database on a RAID5 set of 5 x 250Gb drives and expect it to perform like it does on a RAID10 set of 24 x 20Gb drives - though from the marketing it sounds as if the hardware suppliers might make it sound like that !!!

I find that hardware suppliers like to push the newer, bigger drives because of the amount of profitability in those new disks. When you realize - too late - that those disks can't perform as you expected, then you have to fork out a ton more money to get the arms up - thereby creating even more profit. HP and IBM therefore make it "difficult" to get older, smaller disks (such as the 36Gb or 73Gb drives) - even though these are plentiful and cheap if you're brave enough to go outside the usual supply channels !

[ QUOTE ]
[ QUOTE ]

"SQL Server monitors the throughput on these requests, and will manage the amount of I/O that the disks can effectively handle."


[/ QUOTE ]
Right - so if this is true, what is the new SQL Server perfmon statistic that people should be looking at ? Surely instead of "disk queue length" we should be looking at "sql disk queue length" or something similar ?

What occurs, for example, if the disk queue length is high because of some other sort of disk activity - outside of SQL - such as paging or some other OS function ?

I still believe its valid to watch disk queue length in the statistics - but it sounds like "sql can now adapt its disk activity based on the available queue length statistics". This doesn't mean, however, that you can suddenly stick your SQL Database on a RAID5 set of 5 x 250Gb drives and expect it to perform like it does on a RAID10 set of 24 x 20Gb drives - though from the marketing it sounds as if the hardware suppliers might make it sound like that !!!

I find that hardware suppliers like to push the newer, bigger drives because of the amount of profitability in those new disks. When you realize - too late - that those disks can't perform as you expected, then you have to fork out a ton more money to get the arms up - thereby creating even more profit. HP and IBM therefore make it "difficult" to get older, smaller disks (such as the 36Gb or 73Gb drives) - even though these are plentiful and cheap if you're brave enough to go outside the usual supply channels !

[/ QUOTE ]

Again I found a reference that disk queue length counters are not as reliable for SQL2000 and later as they used to be


"Be careful with the performance counters because SQL Server takes full advantage of asynchronous I/O capabilities that push the disk queue lengths heavily. Therefore, longer disk queue lengths alone do not indicate a problem."

-http://support.microsoft.com/kb/897284/en-us

I know that I personally used disk queue length as a key measure of disk IO performance; I wonder now if I should reconsider my stance.