Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Killer I/O

Here’s a big statement


WHEN WRITING DATABASE QUERIES, 95% OF THE TIME THE BOTTLENECK IS IN THE I/O, not the CPU, and not the memory. Often, when queries run slowly, the newer database developer will suspect that the hardware is insufficient. While beefier hardware can make a dent, the well written query (one that correctly utilizes proper indexes, good tables structures, etc.) will normally give you gains over hardware upgrades in orders of magnitude.


If you can cut down on the I/O operations, your query will run faster. In fact, other queries will perform better as well because they don’t have to compete with your query as much!


Now, this statement is huge, so let’s repeat it. In fact, let’s chant it under our breath as we write queries (everyone already thinks we’re strange, so there is no damage done here.)


WHEN WRITING DATABASE QUERIES, 95% OF THE TIME THE BOTTLENECK IS IN THE I/O, not the CPU, and not the memory.


(Copy and Paste is great, eh?)


The enormity of this statement just BEGS for an analogy. Check out my standard analogy here and notice how much of the time is taken by I/O (reading through files) vs. all the other activities.


Note: This post is part of a series on performance tuning.

Comments

Posted by Steve Jones on 10 June 2010

Excellent point and a good reminder. Learn to write better code, it makes a huge difference.

Posted by mjswart on 10 June 2010

Hear Hear!

Posted by Bob Hovious on 10 June 2010

Huge agreement.   I/O especially gets to be a killer when physical reads happen, because hardware speeds drop from electronic to mechanical.

Posted by Jason Brimhall on 10 June 2010

Hear Hear - Most excellent.

Leave a Comment

Please register or log in to leave a comment.