Jump to content


Photo

MSSQL - SSDs vs. HDDs


  • You cannot start a new topic
  • Please log in to reply
6 replies to this topic

#1 Stephan_84

Stephan_84

    Member

  • Member
  • 4 posts

Posted 16 July 2014 - 05:00 AM

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, 16 July 2014 - 05:01 AM.

#2 Brian

Brian

    SR Admin

  • Admin
  • 5,374 posts

Posted 16 July 2014 - 10:50 AM

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?


Brian

Publisher- StorageReview.com
Twitter - @StorageReview

 

#3 Stephan_84

Stephan_84

    Member

  • Member
  • 4 posts

Posted 17 July 2014 - 03:42 AM

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, 17 July 2014 - 03:44 AM.

#4 Kevin OBrien

Kevin OBrien

    StorageReview Editor

  • Admin
  • 1,473 posts

Posted 17 July 2014 - 09:22 AM

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?


#5 Stephan_84

Stephan_84

    Member

  • Member
  • 4 posts

Posted 18 July 2014 - 02:21 AM

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


#6 Kevin OBrien

Kevin OBrien

    StorageReview Editor

  • Admin
  • 1,473 posts

Posted 18 July 2014 - 10:07 AM

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.


#7 Stephan_84

Stephan_84

    Member

  • Member
  • 4 posts

Posted 21 July 2014 - 05:04 AM

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

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users