“Disk” explained
“Disk” – has different names. I think that confuses anyone outside of DBA’s little world.
Alternate names some DBAs call “Disk” (including myself) are:
- Storage
- I/O (input/output operations)
- Disk operations
- or simply “Reads”
All of those refer to the same concept: “The number of disk operations required for SQL Server to retrieve the desired data from the disk resource.”
95% of SQL Servers bottleneck on Disk resource.
It doesn’t matter, how loaded your SQL Server is either.
- If you have 5,000RPM slow-moving old-school hard drives – 95% it will be disk.
- If you running the best-in-class flash storage arrays or SANs where the starting price is $100,000 for the mini version – 95% of it will be the disk.
The disk is where most SQL Servers experience bottlenecks. Period.
So we know – it’s not CPU or RAM we need to be worried about, right?
Why disk reads matter?
Here is my unscientific way of explaining why this makes sense:
- Slow queries scan (just a fancy DBA way of saying “read”) a lot of data in. Often unnecessarily. This means your SQL Server is doing a lot of those slow-read operations.
- Data that was just “read in” needs to be kept somewhere. That somewhere is RAM Memory.
- As more data is “read in”, “older” data is pushed out of RAM (or overwritten by new data). Capacity in RAM is limited.
- When SQL Server has less RAM than your database size – which is 100% of the time – it means there isn’t enough RAM to keep all the data in the memory.
This means some data is on the slow disk and some of the data is on fast RAM. Data not in RAM must be read from disk, which is the slowest operation the SQL box can perform.
SUPER SLOW….. Just like you’d be running, and I’d be tippy-toeing – not the same level. - So if the query can be changed (tuned) to read 10 rows vs 10 million – it means, the SQL Server needs not only less disk operations but also less CPU and RAM to process all those rows.
Therefore, tuning for less disk “reads” is very often the main goal!
- To the end user, nothing is more important than speed (or duration of the query) though.
All they care about is how long it takes for the screen on the app to return, right? - Tuning to reduce CPU/RAM and other resources is also beneficial, but this is more applicable in special cases.
You tune for CPU when your CPU usage is constantly high, around 90%, or in any other situation considered dangerously high for the database. - When queries are tuned to need less disk operations, SQL Server gets more capacity.
Wait a minute! How is that possible??
You see if I have a highway with four lanes, and all lanes are always busy. Say I remove 50% of the cars from the highway – that highway will last longer without any need for additional lanes, right?
Same with tuning slow SQL calls.
If I get each query to consume fewer resources, I just make more room for extra users, extra queries, and extra growth. Right?
- This indicates the server can manage two to three times the workload, thereby prolonging its service life.
This also means hardware updates can be postponed to a later date. Pretty great, right?!
Conclusion
So now you know.
Don’t believe anyone saying, ‘It’s the CPU causing this SQL to be slow!’
Now you know that in 95% of SQL Servers, it’s the storage operations that are slowing down your SQL Server, not the CPU.
Sure, the CPU might get up to 95%, but if you reduce storage reads from 1 million to 10, your CPU usage will go down too.