• This article and all the replies were very interesting and serve a purpose. I too have a few questions about some of the concepts used. I actually think this solution is overthinking a common problem.

    If I think I/O is a problem, I will run a SQL Profiler Trace and parse it with my favorite parsing tool and sort by Reads or Writes (typically in my systems if I/O is a problem it is reads... there is probably a table scan or index scan someplace most of the time that can be better implemented through better code, better indexing or a better table/normalization structure) and find the worst queries.

    As colin has said earlier (and in numerous posts ) if you have a lot of reads you are bringing in a lot of data and possibly flushing out your cache and definitely using (over?) your disks and CPU..

    Take your reads multiply by 8 and divide by 1024 twice. This is number of GB you are reading. I have seen a horribly written query perform 23,000,000 reads on a database whose total size is only 5GB!!

    So I like looking at the "simple" things first. That normally solves the biggest headaches.