SQL Server Performance Tuning

Hardware And Performance Tuning In SQL Server

Updated
4 min read
Written by
Mark Varnas

What are the server hardware components SQL Server speed depends on the most?

There are only 4 components that matter:

  1. CPU
  2. RAM
  3. Disk
  4. Networking

Which hardware component causes SQL Server bottlenecks in 99% of cases? The disk.

I bet you $100, that your SQL Server bottleneck is happening on Storage, Disk, Input/Out (IO) or whatever you want to call it. It’s the disk.

In 99% of cases I would take your money. Maybe even 98%. Beats the Las Vegas odds!

Yes, yes, I know… some SQL Server workloads would fall outside of this. But we are not talking about you. You are unique. A snowflake. Rare. You probably should be in the “Rare SQL Server Animals” book.

How do I know? I have tuned, worked on or touched over a thousand SQL Servers. Maybe two thousand by now…

At my full-time jobs and while SQL Consulting. I would not take my advice on how to travel through a desert, but around SQL Servers, I know a couple of things.

Your databases will reach a point where they can’t go faster. And that bottleneck will be the disk.

Why does it matter where my SQL Server bottlenecks happen?

Do you want to make your databases go faster? That’s why it matters.

But we have the secret weapon now – we know the disk is why my SQL Server can’t go any faster.

And knowing that is 50% towards winning the battle!

What will we do with this data?

  1. We will be paying lots of attention to anything related to disk activity.
  2. Will work on reducing disk workload – anything that hits it will need to be addressed.
  3. Will re-balance disk activity and make sure all of them doing around similar amount of work and not staying idle.
  4. If a few programs hit the disk hard at the same time, say backup, some maintenance, or antivirus running, we will want to spread those apart.

And make sure those tasks do not compete and impact the SQL Server.

  1. If you have a server where some disks are faster than others, use different RAID levels, we will move data that’s “hit” often into fast disks, and data that is not needed as often to slower disks.
  2. We will do sanity checks on disk speed. And make sure the speed we get is close to the theoretical speed that we should be getting. If not, there is a good chance something is misconfigured.
  3. Will want to shrink data size. Compression is an excellent way to do it.

Even though compression adds a bit to CPU workload, there is less data for the SQL Server to read from the disk, less to store in memory, less to backup, less to move around for log shipping, mirroring or AlwaysOn. It helps.

  1. Anything outside of SQL Server that does significant activity on the disk should be looked at:
    • Are there services that should not be running?
    • Are there programs that maybe should be moved to another disk, server or removed?
  2. If this is VMWare or Hyper-V involved, we will want to make sure those disks are presented properly.
  3. If there is a SAN involved, we will want to make sure that PHYSICAL (notice Physical is in CAPS) disks presented to SQL Server are not shared with anything else. Here we want to make sure MS Exchange isn’t using the same physical disks.

I am probably forgetting some, but you get the point now…

So now that you know that your SQL Server bottlenecks are on the disks, you can do something about it!

P.S. How would you improve the above by 1%?

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