SQL Server Performance Tuning

Why Disk Operations Matter The Most When Tuning T-SQL Calls For Performance

Updated
5 min read
Written by
Mark Varnas

“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:

  1. 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.
  2. Data that was just “read in” needs to be kept somewhere. That somewhere is RAM Memory.
  3. As more data is “read in”, “older” data is pushed out of RAM (or overwritten by new data). Capacity in RAM is limited.
  4. 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.
  5. 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!

  1. 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?
  2. 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.
  3. 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?

  1. 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.

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials