Why Disk Operations Matter Most When Tuning T-SQL

When tuning T-SQL, disk I/O is usually the real bottleneck, not CPU or RAM. Why Disk Operations Matter Most When Tuning T-SQL

Calls for Performance

Why Disk I/O Matters Most

What Do We Mean by “Disk”?

“Disk” goes by a lot of different names. I think this confuses everyone outside the small world of DBAs. Some alternative names DBAs use for Disk include:

• Storage
• I/O (input/output operations)
• Disk operations
• Or simply “Reads”

These all mean the same thing: how many Disk operations are needed for SQL Server to retrieve the data it wants from Disk.

Disk Is the Bottleneck for 95% of SQL Server.

It doesn’t matter how loaded your SQL Server is.

• If you have old-style 5,000RPM slow-spinning hard drives — it’ll be 95% disk.
• If you’re running best-in-class flash storage arrays or SANs that start at $100,000 for the mini version — it’ll still be 95% disk.

Disk is where most SQL Servers bottleneck. Period.

So it’s not CPU or RAM we need to worry about, right?

Here’s my unscientific way of explaining why this makes sense:

1. Slow queries scan large amounts of data (a fancy DBA way of saying “read”). Often unnecessarily. This means your SQL Server ends up doing most of these slow READ operations.
2. Data that’s been “read” has to be held somewhere. That somewhere is RAM.
3. As more data gets “read,” “older” data gets pushed out of RAM (or overwritten by new data). RAM’s capacity is limited.
4. When SQL Server has less RAM than your database size — which is most of the time, 100% of the time really — that means there isn’t enough RAM to keep ALL the data in memory. That means some data sits on slow disk, and some sits in fast RAM. Data that isn’t in RAM has to be read from disk, which is the slowest operation a SQL box can perform. SUPER SLOW.
5. So, if a query can be tuned (adjusted) to read 10 rows instead of 10 million, that means SQL Server needs not just fewer disk operations to process all those rows, but also less CPU and RAM. That’s why reading less from disk is often the MAIN goal!
6. For the end user, nothing matters more than speed (or how long the query takes).
7. Tuning to reduce CPU/RAM and other resources is also useful — but that tends to happen in more specific situations. You tune CPU when it’s pegged at 90% constantly, or when database performance is at risk in some similar way.
8. When queries are tuned to need fewer Disk operations, SQL Server ends up with more capacity. How is that possible? Picture a four-lane highway where every lane is always busy. Now imagine we remove 50% of the cars from that highway — it’ll handle traffic for a lot longer without needing an extra lane, right? It’s the same with tuning slow SQL calls. If I get every query to consume fewer resources, I’ve made room for extra users, extra queries, and extra growth.
9. This means the same server can handle two or three times the load. This means the same server’s lifespan gets extended. This means hardware upgrades can get pushed further into the future.

The Bottom Line

Now you know. Don’t believe people who say “IT’S THE CPU MAKING THIS SQL SLOW!” Now you’ll know that in 95% of SQL Servers, it’s actually Storage operations that are killing your SQL Server’s speed. And CPU is just a lagging indicator. Sure, it can hit 95% too, but if I bring Storage reads down from 1MM to 10, your CPU usage will drop too.