Sign in to follow this  
Followers 0
Stephan_84

MSSQL - SSDs vs. HDDs

7 posts in this topic

Hi there,

I need some advice or maybe another opinion regarding a MS SQL Server disk configuration.

Requirements

  • Dell Server
  • 2TB usable disk space
  • No limited Warranty SSDs (Need more than 3yr Support)
  • Only Dell the SSD Modell “read intensive SSD” (SAS 6G, MLC) is cheap enough, to fit into our budget
  • Perc H710p Raid controller

These are the two current configuration Options. They are sold at nearly the same price.

Options 1
18 x 10k 300GB HDD
Raid10 (maybe two separate raid sets)

Option 2
4 x 800GB read intensive SSD
Raid5

SSD Specs
4.000 IOPS 4k Random Write
17.000 IOPS 8k Random Write
78.000 IOPS Random Read
140 MB/s Sequential Write
310 MB/s Sequential Read

Witch Option would you prefer? And why?

Thanks in advance,
Stephan

Edited by Stephan_84

Share this post


Link to post
Share on other sites

You're running SQL Server bare metal on just a single machine? Or are you clustering at least one other host with this one? Also, do you feel compelled to buy the storage directly from Dell or are you open to other options that you install yourself? Lastly, how big is your database, expected growth rate and importance of performance?

Share this post


Link to post
Share on other sites

Hello Brian,

I tried to keep the question simple, here are some more facts: There will be two bare metal server with identical configuration. Operating System will be Server 2012 R2, SQL Version will be 2014, configured as always on availability groups.

We need to buy the servers directly from Dell, but we will do the installation and configuration. Current database size is ~600GB (DB files and tlogs). Growth rate is 180GB each year.

Our performance calculation is based on the current servers with 8 x 300GB 10k HDDs. There are no performance bottlenecks at the moment. We used the database growth rate as a metric to scale up the IOPs from 8 disks to 18 disks. We assume that the “Option 1” would successful handle our performance requirements. Workload is OLTP.

The question: is the “Option 2” with SSDs faster/better/suitable? Is there a problem using “Read Intensive” SSDs on a Database Server, or is this type of SSD still much faster than 18 HDDs? As mentioned the Options are nearly the same price.

Thanks in advance,
Stephan

Edited by Stephan_84

Share this post


Link to post
Share on other sites

In general I'd lean towards the SSD option for database environments, but in this case being budget limited to the read-intensive model will put you at a disadvantage compared to the much larger HDD array.

If the installation/setup is being handled internally, are you able to buy individual components separately of the server? Say minimum boot drives and then purchasing Intel or Samsung enterprise SSDs?

Share this post


Link to post
Share on other sites

Hello Kevin,

thanks for your help!

In general I'd lean towards the SSD option for database environments, but in this case being budget limited to the read-intensive model will put you at a disadvantage compared to the much larger HDD array.

What exactly is the disadvantage? Is it sequential write speed only? Typical OLTP Workload should be faster even with the read-intensive model, right?

If the installation/setup is being handled internally, are you able to buy individual components separately of the server? Say minimum boot drives and then purchasing Intel or Samsung enterprise SSDs?

I am not able to buy individual components, because I need full Dell Support.

Thanks in advance,
Stephan

Share this post


Link to post
Share on other sites

The read intensive models have lower endurance spec, meaning they can and will wear out quicker with higher write loads. Generally when a drive fails from write activity, you don't get a free replacement, but instead pay for another replacement drive even under warranty.

This really depends on what your database traffic is like. If the bulk is read activity than you would probably be fine. If its write activity this would require more thought to choice the best drive for this application and budget.

Share this post


Link to post
Share on other sites

Hello Kevin,

thanks for your help.

The read intensive models have lower endurance spec, meaning they can and will wear out quicker with higher write loads. Generally when a drive fails from write activity, you don't get a free replacement, but instead pay for another replacement drive even under warranty.

The model mentioned above does not have limited warranty. If the SSD (read-intensive) will fail, the drive will be replaced by DELL.

This really depends on what your database traffic is like. If the bulk is read activity than you would probably be fine. If its write activity this would require more thought to choice the best drive for this application and budget.

Its not bulk read activity, the Workload is OLTP. Current workload:

Database traffic: 50% 64k read, 50% 64k write

T-Log traffic: 100% 4k write

Overall: 75% write (4k-64k), 25% read (64k)

I did a quick IOPs calculation based on this workload (not paying attention to the IO size, 140 IOPs each HDD):

18 x HDD - Raid 10
75% write, 25% read
Max. write IOPS: (Raid 10 write penalty: 2): (140*18)/2 = 1.260 IOPs
Max. total IOPs with 75% write, 25% read: (0,75*140*18)/2 + 0,25*140*18 = 945 + 630 = 1.575 IOPs

4 x SSD - Raid 5
75% write, 25% read
Max. write IOPS: (Raid 5 write penalty: 4): (4000*4)/4 = 4.000 IOPs
Max. total IOPs with 75% write, 25% read: (0,75*4.000*4)/4 + 0,25*78.000 = 3.000 + 19.500 = 22500 IOPs

If this calculation is correct, the read-intensive SSD type should me much faster, even on 100% write workload, right?

I am still anxious buying a type of SSD witch is labeled with "read-intensive", for my 75% write database workload. Is this something I should be worried about? Are 4 SSDs, based on the caluculation above, really that much faster?

Thanks in advance,
Stephan

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0