What are the server hardware components SQL Server speed depends on the most?
There are only 4 components that matter:
- CPU
- RAM
- Disk
- 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?
- We will be paying lots of attention to anything related to disk activity.
- Will work on reducing disk workload – anything that hits it will need to be addressed.
- Will re-balance disk activity and make sure all of them doing around similar amount of work and not staying idle.
- 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.
- 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.
- 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.
- 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.
- 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?
- If this is VMWare or Hyper-V involved, we will want to make sure those disks are presented properly.
- 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%?