SQL Server Myths - Disk Queue length a bit like buying Guiness
If you attended SQLBits you may have seen my SQL Myths
session. One of the myths I didn't cover was that a Disk Queue length > 2 is
bad. Bob Dorr wrote a great blog post on it back in February. I like is
analogies. I've got another one.
You go into an Irish bar and you need 20 pints of guiness.You have a number
of options.
If you order one at a time and wait at the bar, its going to take forever and
your not going to get much conversation with your 19 friends.
You could order 1 and then sit down with your friends, get up when its ready
and order the next one. This will get you more conversation time, but your still
going to be wasting time getting up and down to the bar.
Your other option is to order 4 at a time and then get the bar man
to bring the pints over. This means you will get your pints quicker and you can
maximise your conversation time.
What ordering 4 pints allows the barman to do is use more of his pumps.
He's got 4 pumps, he can be pooring 4 pints at once.
If he had 20 pumps, you could order 20 at a time and that way you would get
all the pints quicker.
The barman also has another trick. If you've ever been in a good Irish bar
you will see that they have pints waiting for people to order them.
This is exactly how the IO works in SQL, the server sends a batch of IO
requests to the IO system and then gets on with something else. If SQL realises
that the IO system can handle more requests, i.e. we have more bar men and more
pumps it sends large requests, because it knows the system can handle it. Having
pints outstanding in your order isn't a big deal because you know the Barman can
get them served in the time that you want.
As for the barman having pints ready. This is just like IO readahead, he
knows more people are going to want Guiness so he's got them ready on his bar
(cache).
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
I mentioned a wiki of SQL Server
Myths at my talk. This is now done and we are reviewing the content so keep
posted.
-