Upgrade to SSD

[EugenX]

Hello everyone.

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

 

[Armageus]

What database software? (realised it's SQL Server)

If your dataset fits in RAM on most databases there should be very little need to touch disks (other than actually committing changed data to disk), and consequently moving to SSD won't benefit much in terms of performance.

If you're using Windows, you should be able to use resource monitor or perfmon to view the disk queue / response time

 

[droyden]

What other workload is on the esx host, is there obvious bottlenecks either at the VM level or the esx level?

Remember it could be just a badly optomised app or sql statement and throwing resources at it won't really help.

Assume that both VM host and sql / windows are configured with best practices

 

[Nikumba]

When they ask to boost performance what are they asking for? Are they using an app that talks to the DB and they want the app quicker?

Are the DB and logs on different disks/arrays?

What is resource monitor / perfmon telling you about the disks, network usage.

What about switches in your network are they congested?

 

[Dolph]

Have you been able to confirm that the challenge is definitely I/O related and not poor database design or query construction?

A poorly optimised database will cripple even the most powerful hardware.

 

[ChrisD.]

You have a dual socket board and how much RAM exactly? You say 100 GB and the VM has 90 GB. How many vCPU is allocated to the VM (cores and sockets)? What other VMs do you have running on the host?

I suspect that you might running the VM across two NUMA nodes which is going to hurt performance as there’s going to be much higher latency for one CPU to get data out of RAM that’s on the other NUMA node.