December 4, 2010 at 12:38 pm
Comments posted to this topic are about the item How will SSDs change SQL Server storage arrays?
December 4, 2010 at 2:38 pm
I just checked out the ioDrive Octal.
Allow me be the first to say: OMG, I wantsss it
December 4, 2010 at 7:29 pm
It's only a 30 GByte database, so perhaps small beer for most of you, but as an indication of SSD impact my development machine - over-clocked Intel i5-760 (4 core), 8 G RAM and a 90 G SSD, and costing about £700 - does a complete data build significantly faster than the production server, which is a recent-model dual Xeon (2x4 core, with hyperthreading) with 12G RAM and 6 x 15,000 Ultra-SCSI drives in a RAID array - and which cost £7.000. The SS 2008 database file is on the SSD, the log file on a separate 1T rotating drive. A large part of the performance increase is due to the SSD.
December 5, 2010 at 12:16 pm
Here is SQLIO from OCZ Revo-X2 (
Dual Six-Core Xeon Nehalem -- This is a PCI-E 480GB Running on W2K8 R2 with the Win 7 drivers (no issues).
Sequential 8K IOPS are 79,000 give or take
Random 8K IOPS are just Shy of 60,000.
We have used both PCI-E Solid State and SATA/SAS drives. We are using MLC and E-MLC in our production appliances as we find that we will never get near the wear out for the writes in the course of 5 years expected life. Also -- note that the size is 480GB. The drive actually has 512 GB of MLC on it. This allows for spare NAND in the event part of the drive gets weak. What would make this drive even better is the addition of a battery / supercap to allow write flushing in the event of a power failure. However all of our appliances wind up in data centers and power outages are pretty rare. So the drive sells for about 1,500 and so to have it mirrored you are looking at $3,000 -- now to get the same IOPS in a spinning disk SAN array you would need to have -- 250 spindles - if they are mirrored then that is 500 Hard disks -- which is a very, very expensive proposition.
Bottom line is that we use the SAN for backup etc. but not for production data. The speed is absolutely addictive and is fantastic for BI / Reporting.
The other factor that isn't discussed much is the simplicity of the operation. No HBA'S, no storage switch, no dealing with Mr. SAN Manager who in my experience can be a bigger bottleneck than the hardware. At the end of the day you have a democratization of storage that adds to the agility of any project.
Here are the results from SQLIO --
sqlio v1.5.SG
4 threads writing for 30 secs to file S:\testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 500 MB for file: S:\testfile.dat
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 79244.51
MBs/sec: 619.09
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 14
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 99 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
threads reading for 30 secs from file S:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 500 MB for file: S:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 58097.21
MBs/sec: 453.88
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 12
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 97 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
December 5, 2010 at 1:34 pm
Just about to put an SSD in my laptop for VMs and I will be interested to see how it helps.
I think SSDs have a place for some operations, but there is a lifetime issue as they are written to over and over. I know some people that have used them in tempdb and love them, some that have had reliability issues because of the large number of writes.
December 6, 2010 at 2:03 am
Ive been using SSD's on our main Database for the past 2 years and can confirm a huge speed benefit from them. The database is serviceing 500+ Users.
But it hasn't been all rosey.
In the early days we had a number of disk failures, some disks lasted until now fine (touch wood) while a batch of them died within 3 months. We are using intel SSD's on HP Controllers, the other weird thing is that when the SSD died it took the controller down with it. Weve recently upgraded the firmware on the Controllers to help narrow down the cause of failures and so HP will actually help resolve the issue.
Ive been dissapointed with the reliability of the Intel drives given their cost and lack of any moving parts.
December 7, 2010 at 10:22 am
Hi,
Up-front declaration: I work for Red Gate. We’ve been looking into using the combination of our SQL Storage Compress product, on top of SSD technologies like Fusion-io, and talking to customers who are "early adopters" of this combination of technologies. We believe that for some customers this will prove to be a very cost-effective, high performance storage solution.
Cheers,
Colin.
December 7, 2010 at 11:07 am
The editorial started with "Solid State Drives (SSDs) and other flash-based devices..". After I read the editorial I decided that more needs to be known about SSD by the general SQL Server community.
SSD has been used in production environments for many years. It is not something new. It can dramatically lower the amount of time it takes to accomplish tasks, by 2/3rds in my case.
The reason that SSD has not been widely adopted is that enterprise quality SSD currently costs an arm and a leg.
Flash SSD is the hot topic because it is fast & cheap. I would be happy to use Flash SSD for what it is good at in my enterprise, but it has not proven itself to me to be reliable in enterprise environments, using the long track record of Static & Dynamic based SSD as the baseline for measurement. In other words: You get what you pay for.
IMHO, more knowledge about what types SSD are out there needs to be spread to the community at large.
SNIA (Storage Networking Industry Association) is a good resource, and has a web site dedicated to all the types of SSD at http://www.snia.org/forums/sssi/knowledge/education. It is well worth the time if you are interested in or thinking about buying SSD. Be aware that SNIA is a vendor association and that the resources can reflect that.
A real interesting article to me is "Storage Class Memory - the Future of Solid State Storage" at http://www.snia.org/forums/sssi/knowledge/education/Storage_Class_Memory_-_the_Future_of_Solid_State_Storage.pdf
Here is a list of the types of SSD technology out there from the SNIA article and other sources:
Flash MLC (Commercially available)
Flash SLC (Commercially available)
Static RAM (Commercially available)
Dynamic RAM (Commercially available)
Flash & Static RAM/DRAM combo (Commercially available)
FeRAM (Ferroelectric RAM) (Commercially available)
MRAM (Magnetic RAM) (Commercially available)
PCRAM (PCM, Phase Change Memory) (Advanced Development)
Improved Flash (Advanced Development)
Solid Electrolyte (Development)
Memristor (RRAM, Resistive RAM) (Early Development)
Racetrack (MRAM variant) (Basic Research)
😉 Brad
December 8, 2010 at 4:12 am
Hi,
I have been testing a ECZ RevoDrive 480 Gb for a couple of days now and I must say I'm very impressed.
I setup a new development environment for a datawarehosue project on an old retired IBM 3650 server and copied the current production enviornment from a 3650 M2 server.
New dev server having slower CPU's and less RAM.
The production enviornment uses a dual path 4 Gb FC HBA connection to a NetApp 3170 with two aggregates, a total of 80 spindles.
On this I run a ETL job, creating stage tables from network sources, loading a warehouse database and finaly processing a cube in Analytic server. All on the same server, with everything on one OCZ RevoDrive. In the production server this takes almost 4 hours to complete. In the dev environment just over 2 hours.
I'm pretty amazed by the performance I get for this kind of money.
Next week I will get the bigger OCZ Z-drive R2 p84 with almost the double performance.
That will be even better I think.
We are planning on implementing this kind of solution for production systems next year.
My only concern i reliablity, how long will they work, do I need to mirror two cards to be safe ...???
Best Regards,
Staffan Olofsson
MCITP
OCP
Senior Consultant
B3IT Management AB
December 8, 2010 at 7:49 am
One consideration is the 'TRIM' factor
December 8, 2010 at 8:57 am
I think the current generation of FusionIO, OCZ, Intel, Patriot, etc SSDs might not have enough of a track record to determine if they are really enterprise stable. There just haven't been enough people using them for a long enough time to determine how well they stand up. Note that I'm not saying you shouldn't use them, but be careful. There are people that have had great luck, using them for over a year in busy systems, and other people that have seen them burn out.
I would make doubly sure my backups are running, copied, and safe, but then go ahead with testing and see how they perform. I've had an Intel in my laptop for about 8 months and it works great.
December 8, 2010 at 11:26 am
Once again, FLASH based tech is absolutely fabulous.
But, it does physically wear out over time - that’s the physics of the pup. And there is nothing inherent to FLASH that will warn of imminent failure, so cover your *** big time. Using some sort of RAID 1+0 / RAID 0+1 sounds like a winner to me in this case - it has the lowest number of logical reads & writes that are implemented at the RAID level compared to the other RAID types. And keep a spare on the shelf.
Otherwise get a non-FLASH based SSD (10x the price or so)
🙂 Brad
P.S.:
I took a look at the OCZ drive specs at http://www.ocztechnology.com/products/solid-state-drives/pci-express/revodrive/ocz-revodrive-pci-express-ssd-.html
It is sorta weird to me that a solid-state component manufacturer cites MTBF (Mean Time Between Failure) at 2,000,000 hours (288+ years) and the limited warranty is 3 years (26,280 hours or 1.3% of MTBF).
December 8, 2010 at 1:21 pm
has anyone used SSD's on SQL 2005 on Windows 2003? I just sent for a quote for 4 Proliant DL 380 G7's with 2 of them getting SSD's for tempdb. For now we're staying on Windows 2003 R2 and SQL 2005 and the only thing i'm worried about is the lack of TRIM support
December 17, 2010 at 2:57 am
Hi again,
I have done some more testing and one thing that took me by surprice at first , was the fact that compressed tables are slower on SSD drives....
Running a ETL job between two REVO Drives (Running the same ETL with everything on one drive is just as fast... )is actually faster if the tables involved are not compressed. I believe that this is because there is no disk latency what so ever and compressing an uncompressing actually adds an extra overhead. CPU's are not fully utilized so that is prabably not the reason either. I have no waits on the server while running the ETL.
When I run the same ETL on normal disks, compressed disks are a bit faster then uncompressed, but there is no dramatic perfromance gain. Space usage on the other hand is wastly reduced.
Any thoughts on this ?
Staffan
December 17, 2010 at 8:01 am
Hi Stefan,
What are the CPU'S and memory when you are running ETL. Are you using SSIS or SQL Scripts?
Best,
mac
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply