From spinners to SSD (SQL Database)

Question

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 spining on SAS Disks 10k - RAID10 at the moment. I want to make an upgrade from spinners to SSD but I would like to undertand if this will give a big difference in performance as the uprgade 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 eventualy, 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: 128 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

Answer 1

You need to approach this by figuring out where the time for your critical processes is going (the ones that you want to improve the performance of). Look at execution plans, wait stats, sp_whoisactive to get a picture of what this is going to be.

You have more memory than data, which means that there is a possibility that your disk usage is quite low and your important data is being serviced purely through memory.

If IO isn’t where your time is going then improving your IO performance is going to do nothing to help. At the worst case going to move more of your workload to CPU. This could give you a situation where you’ve spent money on better hardware but things get slower. No one likes to admit these observations. Cary Millsap calls this performance scenario a Yeti situation - an observation that makes little sense on the surface.