From spinners to SSD

Notice: Page may contain affiliate links for which we may earn a small commission through services like Amazon Affiliates or Skimlinks.

EugenX

New Member
Mar 24, 2022
1
0
1
Hi Folks.

I have a SQL Server database running on a virtualized Windows Server 2012 R2 (VMware ESXi). I have a task from Accountancy to boost the performance of the database. The database has about 70GB and the RAM allocated is about 90GB. The virtual machine is spinning on SAS Disks 10k - RAID10 at the moment. I want to make an upgrade from spinners to SSD but I would like to understand if this will give a big difference in performance as the upgrade is somewhat expensive as I'm talking about Enterprise SSD. Down bellow I've written what spinners I use at he the moment and the SSD that will be bought eventually, the SSD's will be used in RAID10. I would like to ask you to help me understand if the upgrade will give me the needed boost in performance? Thank you.

Hardware-
Server: Lenovo x3650 M5
CPU: 2x - Intel Xeon E5-2630 v4 10Cores 2.2 GHz 25MB
RAM: 100 GB
Storage: Lenovo Storwize V3700 V2 (SAS Disks 10k - RAID10)




What I Have:
IBM (Seagate ST900MM0168 900GB 10K RPM 12Gbps 2.5" SAS Hard Drive)

Brand: IBM
Model: 01EJ586
Capacity: 900Gb
Interface: SAS 2.5 inch
Data Transfer Rate: 12Gb/s
Rotational Speed: 10,000rpm
External Transfer Rate: 1200 MB/s
Sustained Transfer Rate (Outer to Inner Diameter): 215 to 108 MB/s
Average Latency: 2.9ms
Average Seek Time: 4.6ms
Internal Cache: 128Mb
Hot-swappable: Yes
Caddie: Yes


The Upgrade:
IBM AS7J 1.6TB 12G SAS 2.5" MLC G3HS Enterprise SSD

Based on proven HGST Ultrastar SSD1600MM drive technology
Uses 20 nm Multi-Level Cell (MLC) NAND flash memory
Part number - 2.5" G3HS: 00FN409
Interface: 12 Gbps SAS
Capacity: 1.6 TB
Endurance (drivewrites per day over 5 years): 10 DWPD
Endurance (total bytes written): 29.2 PB
Data reliability: 1 in 10(17) bits read
MTBF: 2,500,000 hours (0.35% AFR)
IOPS reads (4 KB blocks): 130,000
IOPS writes (4 KB blocks): 100,000
Sequential read rate (64 KB blocks): 1100 MBps
Sequential write rate (64 KB blocks): 765 MBps
Read latency (seq): 100 µs
Write latency (seq): 45 µs
 

gregsachs

Active Member
Aug 14, 2018
559
192
43
Back of napkin calculations:
1 10k sas drive=151 IOPS.
24 drives=3600 IOPS
1 ssd: 130k IOPS.
Minimum of 4 drives for raid 10= 560k IOPS.
Even if it doesn't scale at all, still should be roughly a 40x improvement.
Now, it is also possible that the db can be optimized, I was playing with a db this week and by adding an index i cut a query from 18 seconds to ~500ms.
 

i386

Well-Known Member
Mar 18, 2016
4,218
1,540
113
34
Germany
Even if it doesn't scale at all, still should be roughly a 40x improvement.
I would be carefully with such a statement :D
I would like to ask you to help me understand if the upgrade will give me the needed boost in performance?
"it depends"
One of our customer went from 16 to 32 gb ram and after that it didn't make a difference for reads/reporting services if the underlying storage was on ssds or hdds.
With writes it was a different story:
hdds only: meh/bad
ssds only: a lot better but expensive (customer perspective, they are on the smaller side of smb :))
"best" solution in terms of costs & performance: db on spinner, tempdb/logfiles on small and superfast (iops & latency) ssd arrays
 

986box

Active Member
Oct 14, 2017
230
41
28
44
Have you looked at the stats on SQL server? I assumed you have index maint scheduled. Indexes needs to be tweak with growth of data. Stats changed over time.

what is the cause? Memory, cpu, page or buffer waits? Or tempdb needs tuning?
 
  • Like
Reactions: T_Minus

T_Minus

Build. Break. Fix. Repeat
Feb 15, 2015
7,625
2,043
113
Have you looked at the stats on SQL server? I assumed you have index maint scheduled. Indexes needs to be tweak with growth of data. Stats changed over time.

what is the cause? Memory, cpu, page or buffer waits? Or tempdb needs tuning?
This first.

Then what about just 2x NVME in mirror setup?
 

Evan

Well-Known Member
Jan 6, 2016
3,346
598
113
SQL server is most likely windows and possibly Linux but that wound have been released after the hardware indicated.
but same thing you need to dig into the performance statistics to see if it will actually make a difference.
 
  • Like
Reactions: edge

edge

Active Member
Apr 22, 2013
203
71
28
I agree with Evan: you need to look at the SQL Server stats to determine if upgrading the storage will make a difference. I also do perfmon logs so over time so I can see how the hardware is being used at differents points in the day.

The biggest performance gains I have made have always come from optimizing problematic queries.