I decided to expand on some scripts to look at how queries use CPU and perform reads, and found some really interesting stuff. I’ll talk through some results and how I’d approach tuning them afterwards.
Interestingly, I just dealt with a mini-consulting engagement in which I saw the opposite: CPU sitting there twiddling its thumbs because of I/O insanity—and not even slow disks. In that case, the advice generally was “add this obviously missing index from this rather large table and stop scanning when you get 1 row on these really busy queries.” There was a little more nuance than that—and in fairness, physical reads were bad as well—but that’s why we investigate systematically.
Also, I generally accede to Erik’s point: for most busy environments, logical reads are unlikely to be the constraining factor and there are plenty of times where I choose the query form with more logical reads because it reduces CPU and memory requirements.