What are the server hardware components SQL Server speed depends on the most?
Only 4 components that matter:
- CPU
- RAM
- Disk
- Networking
Which hardware component SQL Server bottlenecks on in 99% of cases?
Disk.
I bet you $100, that your SQL Server is bottlenecking on Storage, Disk, input/out (IO) or whatever you want to call it. Its Disk.
In 99% of cases, I would take your money.
Maybe 98%. Beats the Las Vegas odds!
Yes, yes, I know… there are some SQL Workloads that would fall outside of this. But we not talking about you. You are unique. A snowflake. Rare. You probably should be in “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.
Why does it matter where my SQL Server is bottlenecking on?
and
What can we do with this data?
Your databases will reach a point where they can’t go faster.
And that bottleneck will be disk.
Do you want to make your databases go faster? It matters there.
But we have a weapon now – we know Disk is why my SQL Server can’t go any faster.
And knowing that is 50% of 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 there are few programs that hit 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 SQL Server.
- If you have a server where some disks as 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 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 SQL Server to read in 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 disk should be looked at:
- Are there services that should not be running?
- Are there programs that maybe should be move to another disk, server or removed?
- If this 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 that isn’t MS Exchange that is using same physical disks.
- I am probably forgetting some, but you get the point now.
So now that you know that your SQL Server is bottlenecking on disks, you can do something about it!
P.S. How would you improve above by 1%?
