Solved

Choosing SSD drives for SQL Server

Posted on 2016-11-04
32
81 Views
Last Modified: 2016-11-08
Hi experts.

Let's compare only the performance of these two samsung SSDs which cost almost the same:
Let's not compare the other things like warranty, MTBF and TBW.

Consumer: Samsung 850 Pro 512GB
MLC chips, Read: 100K IOPS, Write: 90K IOPS, sequential 550 MBps (read)/ 470 MBps (write)      
vs.
Enterprise: Samsung SM863 480GB
MLC chips, Read: 97K IOPS, Write: 26K IOPS, sequential 530 MBps (read)/ 480 MBps (write)
The drives (4) will run as RAID 10.

If you were to build an SQL Server for sharepoint, which one would you prefer performance-wise and why?
0
Comment
Question by:McKnife
  • 14
  • 8
  • 4
  • +3
32 Comments
 
LVL 90

Expert Comment

by:John Hurst
ID: 41874252
I would choose the latter over the former. Why?  It should take more overall write operations for a longer period of time. You should get a 5 year life (and be able to get a 5 year warranty) for the enterprise drive.

Make sure it is a PCI-e drive as well which is faster than SATA.

I have a Samsung PCI-e 1 TB drive in my ThinkPad X1 and it is a very fast drive.

I have a 4 year warranty on the drive and machine.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41874261
Just one question before that - did you think about the fact they will just die after the certain MAX number of write cycles was written on them before considering using SSD for SQL database storage?
as detailed here: http://androidpcreview.com/are-torrents-damaging-your-mini-pc/2237/
"...the flash storage, including Solid State Drives (SSD), has a finite amount of read-writes that it can perform before the drive just plain dies. No clicking like normal hard drives….just dead." so I would think twice putting my SQL databases on SSD's...but maybe I'm wrong.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41874270
Hi John.

The numbers your comment on for sequential write are almost identical (+2% for the latter). The IOPS are by far better at the former drive (+300%).
The warranty does not matter and is by far better (10 years!) for the former.

"Make sure it is a PCI-e drive as well which is faster than SATA" - well... then please try to find a PCI-e-drive Raidcontroller for me, will you? That segment is pretty new.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41874277
@Icohan:
The TBW value you are talking about is huge for the consumer one as well: 150 TB (written). So if we write 50 GB per week, that would last about 60 years.
0
 
LVL 23

Expert Comment

by:Dr. Klahn
ID: 41874299
I agree with John on this one.  Consumer grade drives should not be used in any application where RAID is being considered.

By the time the drives are RAIDed together in a 4-pack, that 26,000 I/O per second will become 104,000 I/O per second.  I don't think it is possible to shove 20,000 meaningful disk I/O per second through a volume, much less 100,000 I/O per second.  I'm sure there's a synthetic test where a specially designed drive tester hammers the drive as hard as it can; in the real world this doesn't happen.  So I/O per second is not an issue once the drives are RAIDed.

But if it's important enough to RAID the drives, then it's also important to maximize the reliability of the data.  That's why enterprise quality drives must be used.  They allocate more spare blocks and warn early of impending write leveling failures.

The difference between less than a dozen $100 and $250 drives to your company is negligible.  The pain and suffering that will descend upon you from above if there are issues down the road and you are still employed by the company, is not.
0
 
LVL 90

Expert Comment

by:John Hurst
ID: 41874301
Here is a good article on PCI-e vs. SATA. PCI-e has more data paths and can be made faster whereas SATA has reached its limit for speed

http://www.overclock.net/t/1489684/ssd-interface-comparison-pci-express-vs-sata

I did a quick look for controllers and suggest you look at Intel PCE-e controllers.

Enterprise drives tend to be overprovisioned with extra cells to allow more internal failures without the drive failing. In a Server environment, I think that is important.

PCI-e is meant for Server operation so that is where I would head.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41874432
John,
"I did a quick look for controllers and suggest you look at Intel PCE-e controllers" - sure, but which one, at what cost?
"Enterprise drives tend to be overprovisioned with extra cells to allow more internal failures without the drive failing. " - it's hardly useful to speak in general. Where on the fact sheet for the drives do you find that?
"Here is a good article on PCI-e vs. SATA. PCI-e has more..." - sorry, but you are not speaking to a newbie when it comes to storage. The PCI-e is better, totally agreed, but the raid-requirement is what makes it next to impossible. For example, there is this controller: http://highpoint-tech.com/USA_new/series-rr3800a-overview.htm - it would suit. But even finding a price for it is hard.
There are not many conrollers that can do raid for these drive types, yet.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41874452
@Dr. Klahn
"Consumer grade drives should not be used in any application where RAID is being considered" - where does it say so?

"By the time the drives are RAIDed together in a 4-pack, that 26,000 I/O per second will become 104,000 I/O per second" - no. It will become 52,000 (because it's a RAID10.  

"I don't think it is possible to shove 20,000 meaningful disk I/O per second through a volume, much less 100,000 I/O per second" - you dont think so, so why would Samsung build drives (like the 850 pro) that deliver next to 100,000 IOPS in a single drive, then? Purely for showing off? ;-)

"it's also important to maximize the reliability of the data.  That's why enterprise quality drives must be used.  They allocate more spare blocks and warn early of impending write leveling failures."
They do? Where does it say so for the model in question?

"The difference between less than a dozen $100 and $250 drives to your company is negligible." - what drives are you talking about? The drives in question cost almost the same and both over 250USD.
0
 
LVL 90

Expert Comment

by:John Hurst
ID: 41874457
From a strategy and business perspective

1. PCI-e (while more expensive) is today's technology and SATA is now yesterday's technology.
2. Enterprise equipment is made for commercial server environments,
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 125 total points
ID: 41874576
>
Let's compare only the performance of these two samsung SSDs which cost almost the same:
Let's not compare the other things like warranty, MTBF and TBW.

How about comparing the state of the data after an unexpected power loss?
The enterprise drive has capacitors and associated circuitry (PLP) so it can flush its internal RAM to the NAND, the consumer one doesn't. Be prepared to restore the databases if you have a powerout when using the 850 PRO as you won't know what data was lost in the buffer.

How about comparing sustained performance when TRIM is not available, i.e on a RAID controller?
The overprovisioning of the SM863 allows efficient garbage collection without being told what sectors are no longer in use, the 850 PRO doesn't have that capability.

Apart from those capacitors and the internal overprovisioning they are effectively the same device, or at least they have the same NAND modules on them.

Not sure what thinkpad is going on about PCIe based devices, think he may be suggesting that you get neither of these SSDs but get something like a fusion-IO NAND flash card instead. Not a bad idea although you won't be able to boot from it.
1
 
LVL 90

Expert Comment

by:John Hurst
ID: 41874600
PCI-e SSD drives are faster than SATA will ever be. That is why I suggested them.

The PCI-e SSD drive I have here has its own controller and I boot from it just fine.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41874614
@andyalder: Hi. Yes, how about comparing these things I didn't want to compare, why not do it although I explicitely wrote "please don't"? :-) Look, I know a thing or two about storage myself. I am not the guy who chooses the wrong stuff for his needs. I am consulting on this one. And the one being consulted looked at the enterprise one and looked at the IOPS and said "as far as I remember, this is pretty low" and he is right if we compare it with for example (you'll guess it) the 850 pro which is a better consumer product.

So I need support in deciding whether this difference in IOPS even matters for an SQL server. That's why I tried to reduce the comparison to the performance aspect.
--
@John
If PCI-e would be available in our price range, which, for storage alone should not exceed a certain limit, let's say controller plus 4 drives less or equal 2500 USD, then why not. but I am already having problems to locate a controller model, maybe you know one?
PS: booting does not matter. This RAID will not be used for booting.
0
 
LVL 90

Expert Comment

by:John Hurst
ID: 41874618
I only saw Intel controllers for PCI-e. The technology is still new. I imagine Samsung has a controller but I have not yet found one.
0
 
LVL 90

Expert Comment

by:John Hurst
ID: 41874625
0
 
LVL 53

Author Comment

by:McKnife
ID: 41874677
They do? Please link it.
The last link is about an onboard raid controller, not sold separately and not samsung.
0
 
LVL 90

Assisted Solution

by:John Hurst
John Hurst earned 125 total points
ID: 41874691
Try something like this

https://www.amazon.com/Syba-mSATA-Components-Other-SD-PEX40079/dp/B00KKO6N98

You will [probably have to do some searching to fit your own application.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 53

Author Comment

by:McKnife
ID: 41874735
Yes, "something" like this. That is surely no enterprise product and I guess reviews like this one are correctly rating it rather low.
But I'll look into similar things. If you again find that intel controller you read about, bring it on.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 41874825
Since this is SQL Server Sharepoint, that should factor into consideration as well.

It's been about 5 years since I managed SQL Sharepoint (hallelujah!), but it did tend to use lots of I/O.  Consider, also, how much data will be stored in SQL Server, whether directly or thru Filestream data.

Here's a MS link that confirms the importance of I/O performance for Sharepoint and gives guidelines on how to computed needed I/O:

https://technet.microsoft.com/en-us/library/cc298801.aspx#Section1_5a
Small, relevant excerpt:
"
...
On any server that hosts a SQL Server instance, it is very important that the server achieve the fastest response possible from the I/O subsystem.
More and faster disks or arrays provide sufficient I/O operations per second (IOPS) while maintaining low latency and queuing on all disks.
You cannot add other types of resources, such as CPU or memory, to compensate for slow response from the I/O subsystem. However, it can influence and cause issues throughout the farm. Plan for minimal latency before deployment, and monitor your existing systems.
...
In tests, we [MS technical people] found that the content databases tend to range from 0.05 IOPS/GB to around 0.2 IOPS/GB. We also found that a recommended practice is to increase the top-end to 0.5 IOPS/GB. This is more than necessary and can be much more than you'll need in your environment. Note that if you use mirroring, this results in much more IO than the primary content databases. Simply be aware that the mirrored content databases are never lightweight.


All that said, I see the concerns about a non-enterprise drive system.  One final consideration might be the expected years the drives need to be used; if 5 or less, either may be OK, if more than 5, enterprise may be more important.
0
 
LVL 55

Expert Comment

by:andyalder
ID: 41874878
Scott, He doesn't care if it crashes and reboots with wrong data so long as it runs fast. If there is a power glitch he's happy because SQL/Sharepoint is still running (albeit with wrong data).

Wish the lounge was still here. we could bat it out between two small boy's buttocks in the old days :)
0
 
LVL 53

Author Comment

by:McKnife
ID: 41874926
Good one. I wish it was.
No, you can believe I care.

Scott, about your numbers, IOPS per GB, could you explain the calculation? The bigger the DB, the...?
0
 
LVL 53

Author Comment

by:McKnife
ID: 41877116
Scott, about your link again: scratch my last question. I asked, because I was confused by the small numbers.

See, if we setup Raid 10 with 4x500 GBs, we have 1 TB usable, so even if the database used the whole drive that would mean multiplying
1000 GBs with 0.5 IOPS/GB, which gives just 500 IOPS.
So I thought, why would you even stress it and write "it did tend to use lots of I/O" while 500 is very little.

Still my question stands. If you were to build an SQL Server for sharepoint, which one would you prefer performance-wise and why?

PS:
@John: we have decided against using NVMe Raid, because it seems any of these controllers are new and have not been industry proven, yet.
0
 
LVL 90

Expert Comment

by:John Hurst
ID: 41877122
You can use SATA drives and controllers and that will work.

However to your original question, I would still use the Enterprise drives. That would be my choice.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41877187
I got those numbers from MS.  That was their specification.

When I had to manage Sharepoint, I noticed that I/O was by far the most difficult issues I faced.  And the performance I saw does not necessarily match what MS claimed.  Their values may be based on far more drive sets and/or much fewer users.

I'm also not sure what the best disk block size for Sharepoint is, but I would suggest researching it and using the best blocking, as that may improve performance significantly.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41877356
@John
You said so. And you added "From a strategy and business perspective" - while the question was about nothing but performance. So allow me to ask if you'd choose the enterprise one for performance reasons despite the IOPS being so low (1/4 of the consumer drive's IOPS). And if you did, why?

@Scott
So what drive of those two would you choose for performance?
You also went for other aspects like durability, but those don't matter.

Guys, don't get me wrong, I am not going for the fastest drive - it is just one aspect. It is simple: if you pick drives with a high durability, they do have low IOPS, it seems. So if we say, we don't need a drive that lasts 60 years, we might go for the performance and thus maybe not choose an "enterprise" drive.
All the concerns about power loss might not even apply if you are connected to a UPS.

So I wondered, just reading the number of IOPS, if anyone else would be tempted to buy the consumer one just for the performance of if there is any aspect unknown to me that would in the end boost the performance of the IOPS-weak enterprise one so that it would be considered faster for SQL/sharepoint.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41877377
As I said, I would go for the faster one if you're very likely to replace the drive in 5 years (maybe 6) anyway.  Any drive should last that long.  But if that company is one that tends to really try to stretch the life of all hardware, then I'd go with the enterprise version.
0
 
LVL 55

Assisted Solution

by:andyalder
andyalder earned 125 total points
ID: 41877436
I think you are using the write IOPS figure wrong, the random write performance of the SM863 is most likely quoted as queue depth = 1 whereas the 90K IOPS figure you have listed is for QD=32. For QD=1 the 850 PRO has 36K write IOPS. the PRO write IOPS figures are probably also measured with TRIM on and the SM863 may be measured with TRIM off since it's designed for RAID, that gives a multiplier factor of about 0.9. Still 36K * 0.9 is better than 26K.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41877442
Correction to working:

As I said, I would go for the faster drive if you're very likely to replace it in 5 years (maybe 6) anyway.  But if your company is one that tends to really stretch the life of all hardware, then I'd go with the enterprise-level drive for its durability.

And again, for the record, I am a DBA, not a disk expert.  I am speaking purely in terms of best performance and management for SQL Server, not of the drives themselves.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41877566
@andyalder:
"you are using the write IOPS figure wrong, the random write performance of the SM863 is most likely quoted as queue depth = 1 whereas the 90K IOPS figure you have listed is for QD=32" - so how would you "use it right" to make it comparable? The numbers are from Samsung's datasheets and no QD is indicated.

Edit:
...wait a minute... I relied on someone else's figures.... and it seems that someone made an error quoting datasheets. There is a QD indicated.
Will take a while to sort this.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41877605
Ok, no, the numbers weren't wrong.
--850 pro---
4KB Random Read (QD1): Max. 10,000 IOPS
4KB Random Write(QD1): Max. 36,000 IOPS
4KB Random Read(QD32): Max. 100,000 IOPS
4KB Random Write(QD32): Max. 90,000 IOPS

---SM863 480GB---
4KB Random Read (QD1): no info on datasheet
4KB Random Write(QD1): no info on datasheet
4KB Random Read(QD32): Max. 97,000 IOPS
4KB Random Write(QD32): Max. 26,000 IOPS
0
 
LVL 53

Author Comment

by:McKnife
ID: 41877645
After more reading: if I get it right, the Power-Loss Protection is necessary to let the raid controller enable the write cache which significantly improves performance. I repeat: if I get it right. You can also read that people are finding ways to circumvent this in order to enable the write cache even on drives that don't have that feature.
Well, we have a UPS running and haven't had power loss crash in x years, but still, having to trick the controller is not what I feel we should be doing, though possible.

Source: https://forums.servethehome.com/index.php?threads/are-samsung-850-pros-not-lsi-9361-8i-compatible.5804/
Since we thought about that very controller as well, I guess I'll give Avagotech/LSI a call tomorrow. Time to go home now.
0
 
LVL 55

Expert Comment

by:andyalder
ID: 41877768
PLP is so that the SSD can flush its own RAM to flash, it's the equivalent of the battery on the RAID controller in that respect. There's a graph of that on page 14 of http://cdn-reichelt.de/documents/datenblatt/E600/MZ-7KMXXXE_DBA.pdf

And you're right QD=32 from that datasheet as well.
0
 
LVL 53

Author Closing Comment

by:McKnife
ID: 41879064
Time to close. Thanks all for participating and offering thoughts and critique.
I spoke with broadcom's (formerly avagotech formerly LSI) support and he said
"both drives are on the compatibility list, both are expected to work well. I cannot recommend one over the other since we are not allowed to do that. And no, the write cache will not be enabled by our controller, it will either be on or off, you can set that yourself based on your preference - without powerloss protection or UPS it will be of course recommended not to enable it".

So I looked at what info I have so far and the numbers are few. I have found one thread with poor numbers for the 850 pro and another thread with better numbers for the SM863 - too bad I cannot afford to test it myself. We have voted to buy the SM863 in the end.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now