Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Solid State Disks and SQL Server Expand / Collapse
Author
Message
Posted Wednesday, April 7, 2010 9:12 PM


SSChasing Mays

SSChasing Mays

Group: Moderators
Last Login: Wednesday, July 23, 2014 1:21 PM
Points: 609, Visits: 407
Comments posted to this topic are about the item Solid State Disks and SQL Server

http://www.sqlserverio.com
http://www.cactuss.org
http://www.salssa.org
Post #899232
Posted Wednesday, April 7, 2010 11:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 12, 2012 5:10 PM
Points: 11, Visits: 9
I'm surprised your article didn't mention anything about first testing whether your database IO performance is the bottleneck in your environment. We tested a Fusion IO card, and whilst had similar IO results, the speed improvement in the application was virtually zero.

I'd suggest to anyone looking at SSD drives for databases to first do performance monitoring on their existing hardware and find out if you're adding unnecessary risk and memory wastage for something you don't require.


Post #899280
Posted Thursday, April 8, 2010 12:44 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
Nice article sir, thorough analysis at the I/O level, and well explained.

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #899304
Posted Thursday, April 8, 2010 1:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 13, 2011 1:10 PM
Points: 1, Visits: 11
The fastest way to improve database performance is to put the tempdb on a ram disk (free download at dataram.com). That works even if you have to reduce the ram available to the database itself. The tempdb is created from scratch every time you start up the SQL server (put the temp files in the root directory of the ram drive). I had a 4gb ram 2xopteron 2,2ghz hp dl385 g1 server with 6x72gb 15k u320 in a raid 10 running windows 2008 x64 an SQL server standard x64. I gave the SQL server 2,5gb ram and put the tempdb on a 512mb ramdrive leaving 1 gb for windows (+ pagefile). This performed very well for a 22gb database. in production I have more ram (25gb) but still take some from the SQL server and use it for a ramdrive. I strongly recommend using quest spotlight on SQL server for further optimization. I bought it before investing in new hardware but there is also a 30 day trial version.
Post #899319
Posted Thursday, April 8, 2010 2:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
I'm surprised you didn't set the sector size to 8KB. That's the page size for SQL Server and would have been a better choice than 4KB.

On my production servers, I usually format those volumes not requiring compression (i.e. non-backup) that are used exclusively for SQL Server to 64KB. This keeps the MFT nice and small, ensures minimum fragmentation and fits well with SQL Server's extent size of 64KB: Allocating an extent maps directly to a disk sector.
Post #899339
Posted Thursday, April 8, 2010 2:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 4:21 AM
Points: 216, Visits: 194
We use fusionIO for tempdb too, beautiful! But why all the messing around with PCI expansion etc, if you need more space than you can squeeze into a box why not get a solid state SAN, then all you have to do is find enough space for HBA cards to give you enough bandwidth.
Now I don't have one of these so can't recommend them or comment on their suitability for SQL server but they do look the biz and I'd love to give one a test drive.

http://www.ramsan.com
Post #899348
Posted Thursday, April 8, 2010 2:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
A solid state SAN won't come close - maximum bandwidth per slot is 4Gbps (500MB/s). Plus you have the added latency from the fabric switches, etc. But the real SAN-SSD killer is cost - it's too prohibitive for a dedicated IO subsystem.
Post #899354
Posted Thursday, April 8, 2010 2:50 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 4:21 AM
Points: 216, Visits: 194
What do you mean per slot? Per port yes, but you can get dual ported HBAs which will double that, so 2 dual ported cards give you 2GB/s. And that is if you stay on fibre. What about infini-band that is 10 or 20 Gb/s isn't it?
Well I've never poked around the price of these things but if you need 4TB of storage at that level I would expect the hoops you would have to go through to get enough fusion IO cards in single system would probably make it fairly cost effective and certianly simpler and easier to maintain.
I guess you also get into the traditional arguments of SAN vs DAS storage efficiencies and sharability etc.

I'd still like one for christmas
Post #899357
Posted Thursday, April 8, 2010 3:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
Look at the price of a regular SAN with regular disks. It still can't compete on price! I did a price/performance comparison of a single MSA70 (25 disks) to an EVA3000 - it was 8x cheaper per MB/s. And that's a really cheap EVA. As you move to bigger SANs, the cost per MB/s gets higher still - they're not designed for high performance service levels to an individual server.
Post #899373
Posted Thursday, April 8, 2010 6:34 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, Visits: 836
great article. Thanks for sharing!

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Post #899479
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse