SQL Server Performance Tuning

How We Slashed SQL Query Disk Read Operations by 1,152 Times

Updated
2 min read
Written by
Mark Varnas

Improvement after tuning

1,152x

DISK

Disk (number of reads)

884,014
Before tuning
767
After tuning

Reading from disk is the slowest operation SQL Server does.

Therefore, tuning for less disk “reads” is often the target.

Problem

The stored procedure was too slow and performed too many disk reads.

Solution

Here is what was done to get this improvement:

Before vs. After

The disk reads plummeted from 884,014 to just 767.

That’s an unimaginable 1,152 times less!

BeforeAfterImprovement
Disk, number of reads*884,014767~1152x (or 115,256%)
*The numbers are an average of multiple T-SQL runs

Final thoughts

The most common bottleneck for SQL Servers is disk access (or disk “reads”).

It’s neither the CPU nor the RAM, which are usually the first suspects for most customers.

And that makes much sense. Here is why:

  • Inefficient queries scan (or read) much data.
  • Data read in is stored in RAM. As more data is read in, “older” data is pushed out from RAM.
  • If there isn’t enough RAM to keep ALL data in memory (which is often not possible), SQL Server has to read from disk – and that is the slowest operation SQL Server can do.
  • When the query can be tuned to read 10 rows vs. 10 million – less CPU and RAM automatically are necessary.

Disk resources are the cause of 95% of SQL Server bottlenecks, so reducing the disk reads is usually the primary objective.

For the end-user, nothing is more crucial than the speed (or duration) of the query, in every case.

Tuning to reduce CPU/RAM resources is helpful too.

When queries are tuned to need less CPU & RAM, it means that the same server now has more capacity.

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