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.



-
Published 16 November 2007 11:31 by simonsabin
Filed under: ,

Comments

19 November 2007 09:05 by GrumpyOldDBA

# re: SQL Server Myths - Disk Queue length a bit like buying Guiness

It's worth pointing out that this counter has absolutely no value when applied to disk arrays on a SAN ( well in all the instances where I've had servers with storage on a SAN ) I have to say I've not used this counter for many years - but I like the analogy. If you're using fibre DAS ( yes you can have fibre as DAS ) there are settings on the HBA(s) and optionally switches/hubs which may affect io queuing.