“Disk” Explained
“Disk” – has different names. I think that confuses anyone outside of DBA 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 mean the same thing. Its how many Disk operations are needed to get the data SQL Server wants from the Disk resource.
By the way, this check is a part of our SQL Server Health Check service.
95% 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 starting price is $100,000 for the mini version – 95% it will be disk.
Disk is where most SQL Servers bottleneck on. Period.
So we know – it’s not CPU or RAM we need to be worried about, right?
Here is why my unscientific way of explaining why this makes sense:
- Slow queries scan (just a fancy DBA way of saying “read”) lot of data in. Often unnecessarily. Which means your SQL server is doing a lot of those slooooow READ operations. (Stay with me).
- Data which 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 off RAM memory (or overwritten by new data). Capacity in RAM is limited.
- When SQL Server has less RAM memory 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. Which means some data is on the SLOOOOW disk and some of the data is on fast RAM. Data not in RAM needs to be read from disk, which is THE SLOOOOWWWEEEEEESSSTTT operation SQL box can do. SUPER SLOW….. Just like you’d be running, and I’d be tippy toeing- not the same level.
- So if query can be changed (tuned) to read 10 rows vs 10 Million – it means, SQL Server needs not only needs 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 she cares about how long the screen on the app take to return, right?
- Tuning to reduce CPU/RAM and other resources are helpful too – but this is more in the specialty cases. You tune for CPU, when your CPU constantly pegged at 90% or something crazy “db dangerous” like that.
- When queries are tuned to need less Disk operations, SQL Server gets more capacity. WAAAITTT A MINUTE. How is that possible?? You see if I have highway with four lanes, and all lanes are always busy. Say I remove 50% of the cars from the HWY – that HWY will last longer without any need for additional lanes, right? Same with tuning slow SQL calls. If I get each query to consume less resources, I just made more room for extra users, extra queries, extra growth. Right?
- Which means that same server can process double or triple the load. Which means it extends lifespan of the same server. Which means hardware upgrades can be pushed out further into the future. NICE, HUH?!
Conclusion
So now you know. Don’t believe anyone that says “ITS THE CPU THAT IS CAUSING THIS SQL TO BE SLOW!” You will now know in 95% of SQL Servers – its the Storage operations that are killing your SQL Server speed. And CPU is just a lagging indicator. Sure it can get to 95%, but if I get Storage reads from 1MM down to 10, your CPU usages will go down.
