InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 22:44:27 GMT20http://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxSS 2005, Win 2003 Server, running under ESXi 4.0, 3ware controller, Crucial SSDs.We started out with one 3ware 9650 controller that apparently could not keep up and so it corrupted our databases. We worked with 3ware support and we are now also running a 9750-4i to run these SSDs in RAID, leaving the 9650 for the spindles. I have yet to move any files back on to the array for testing. Make backups AND pay attention to what is going on in the environment. You best be running DBCC's and staying on top of it. Work with the vendors and manufacturers as much as possible - even if you spec everything out with plenty of fudge, you just never know.My opinion would be that the most prominent performance boost would be from tempdb and log files being on the SSD. Since changes are written back to the db file lazilly does it make sense that it is not as critical to have them on SSD? But I guess it all depends on whether your heavy transactional or heavy reads.Wed, 29 Dec 2010 07:06:19 GMTmramsey 91398http://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxMemory is all 28 Gb( 4 Gb left for OS) allocated to SQL server./SMon, 20 Dec 2010 00:42:38 GMTStaffan Olofssonhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxCPU's are only running at 65-80 %, it's dual CPU dual core machine, part of it is due to calculations taking place in the ETL process. The only waits I can obeserve is different latch waits and I believe that is ok.ETL is run as an SSIS package./StaffanMon, 20 Dec 2010 00:41:20 GMTStaffan Olofssonhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxHi Stefan,What are the CPU'S and memory when you are running ETL. Are you using SSIS or SQL Scripts?Best,macFri, 17 Dec 2010 08:01:26 GMTrmacleanhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxHi 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 ?StaffanFri, 17 Dec 2010 02:57:31 GMTStaffan Olofssonhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxhas 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 supportWed, 08 Dec 2010 13:21:24 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxOnce 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):-) BradP.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-.htmlIt 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). Wed, 08 Dec 2010 11:26:25 GMTBC Featherstonehttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxI 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.Wed, 08 Dec 2010 08:57:41 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxOne consideration is the 'TRIM' factorhttp://en.wikipedia.org/wiki/TRIMWed, 08 Dec 2010 07:49:32 GMTmark.marshhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxHi,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 OlofssonMCITPOCPSenior ConsultantB3IT Management ABWed, 08 Dec 2010 04:12:47 GMTStaffan Olofssonhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxThe editorial started with [i]"Solid State Drives (SSDs) and other flash-based devices.."[/i]. 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 [b]not[/b] something new. It can dramatically lower the amount of time it takes to accomplish tasks, by 2/3[sup]rds[/sup] 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 [i]"Storage Class Memory - the Future of Solid State Storage"[/i] at http://www.snia.org/forums/sssi/knowledge/education/Storage_Class_Memory_-_the_Future_of_Solid_State_Storage.pdfHere 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);-) BradTue, 07 Dec 2010 11:07:49 GMTBC Featherstonehttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxHi,Up-front declaration: I work for Red Gate. We’ve been looking into using the combination of our [url=http://www.red-gate.com/products/SQL_Storage_Compress/index.htm]SQL Storage Compress[/url] 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.Tue, 07 Dec 2010 10:22:24 GMTColin Millerchiphttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxIve 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.Mon, 06 Dec 2010 02:03:09 GMTmark.marshhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxJust 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.Sun, 05 Dec 2010 13:34:13 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxHere is SQLIO from OCZ Revo-X2 ([url=http://www.ocztechnology.com/products/solid-state-drives/pci-express/revodrive/ocz-revodrive-x2-pci-express-ssd-.html][/url] 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 takeRandom 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.SG4 threads writing for 30 secs to file S:\testfile.dat using 8KB sequential IOs enabling multiple I/Os per thread with 8 outstandingusing specified size: 500 MB for file: S:\testfile.datCUMULATIVE DATA:throughput metrics:IOs/sec: 79244.51MBs/sec: 619.09latency metrics:Min_Latency(ms): 0Avg_Latency(ms): 0Max_Latency(ms): 14histogram: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 outstandingusing specified size: 500 MB for file: S:\testfile.datinitialization doneCUMULATIVE DATA:throughput metrics:IOs/sec: 58097.21MBs/sec: 453.88latency metrics:Min_Latency(ms): 0Avg_Latency(ms): 0Max_Latency(ms): 12histogram: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 0Sun, 05 Dec 2010 12:16:51 GMTrmacleanhttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxIt'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.Sat, 04 Dec 2010 19:29:16 GMTDavid Datahttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxI just checked out the ioDrive Octal.Allow me be the first to say: OMG, I wantsss itSat, 04 Dec 2010 14:38:42 GMTjdurandthttp://www.sqlservercentral.com/Forums/Topic1030306-263-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Editorial/71813/">How will SSDs change SQL Server storage arrays?</A>[/B]Sat, 04 Dec 2010 12:38:39 GMTTony Davis